From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nunya Business <nb3425586@gmail.com>
Cc: pgsql-general@postgresql.org
Cc: pgsql-bugs@postgresql.org
Subject: Re: Re[2]: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
Date: Tue, 06 Dec 2022 15:47:26 -0500
Message-ID: <3892582.1670359646@sss.pgh.pa.us> (raw)
In-Reply-To: <emd5c018bd-ee54-4ca6-b61d-741ad84aa451@81da44b0.com>
References: <emad8fa821-2230-4b8b-893b-cb2b8b376e9b@81da44b0.com>
<3635234.1670340237@sss.pgh.pa.us>
<emd5c018bd-ee54-4ca6-b61d-741ad84aa451@81da44b0.com>
"Nunya Business" <nb3425586@gmail.com> writes:
> Thanks Tom. There are indeed circular references in the schema and the
> whole thing sort of doesn't pass the smell test, but this is my first
> look at it. The generated column on the table calls a function which
> selects from a view that references the table. The production schema
> where I ran into this is pretty large and complex, so the contrived
> example that follows may not be the minimum working example but it's
> pretty small and has the same behavior regarding the SQL generated by
> pg_dumpall.
Hm. The actual problem here is that fnA() is making unwarranted
assumptions about the search_path it's run under, so it fails when the
pg_dump script invokes it with a restrictive search_path. If you change
the function text so that the references to viewA are schema-qualified,
then it restores without errors.
"Doesn't pass the smell test" is putting it mildly, btw. Labeling
that function IMMUTABLE is a huge lie, and what it means is that
your GENERATED column doesn't have the amount of stability that
it's supposed to per spec. I'm not sure exactly what sorts of
misbehaviors might ensue from that, but I'm pretty certain that the
data in the GENERATED column after dump/restore won't match what
you had there beforehand.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, nb3425586@gmail.com, pgsql-general@postgresql.org
Subject: Re: Re[2]: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
In-Reply-To: <3892582.1670359646@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox