From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Carl Smith <carl@msupply.foundation>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18392: Can't restore database (using pg_restore) after latest Feb 8 update
Date: Thu, 14 Mar 2024 17:49:30 -0400
Message-ID: <2383726.1710452970@sss.pgh.pa.us> (raw)
In-Reply-To: <ae4f109912f70d6628eafe8efb99a6f5fbfdde83.camel@cybertec.at>
References: <18392-142078457489d3a5@postgresql.org>
<a07a7bc4d0442e7dadce1ec57c014c2ebced13ee.camel@cybertec.at>
<ED83A0DA-A6E7-400E-B445-8B8FC8890014@msupply.foundation>
<ae4f109912f70d6628eafe8efb99a6f5fbfdde83.camel@cybertec.at>
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> That's not a PostgreSQL bug, that's a bug in your code.
> You must have declared the function public.get_template_code(integer) as IMMUTABLE, but
> it SELECTs from public.template, so it clearly is *not* immutable. It depends on the state
> of the database, in your case on the existence of a certain table (and on its contents).
Yeah. The concrete problem so far as pg_dump is concerned is that
it can't see the dependencies that the body of get_template_code has,
so it doesn't realize that it would have to postpone creation of this
table till after public.template is created. There are various ways
that you could hack around that by preventing the function from being
considered for inlining, but as Laurenz suggests, that's just
band-aiding over a fundamentally unsafe design. Pretending that this
function is immutable will bite you eventually.
BTW, I believe that what broke it for you as of the current releases
is commits 743ddafc7 et al, which caused GENERATED expressions to be
run through expression preprocessing at CREATE TABLE time:
Two actual bugs of this ilk are fixed here. We failed to preprocess
column GENERATED expressions before checking mutability, so that the
code could fail to detect the use of a volatile function
default-argument expression, or it could reject a polymorphic function
that is actually immutable on the datatype of interest. Likewise,
column DEFAULT expressions weren't preprocessed before determining if
it's safe to apply the attmissingval mechanism. A false negative
would just result in an unnecessary table rewrite, but a false
positive could allow the attmissingval mechanism to be used in a case
where it should not be, resulting in unexpected initial values in a
new column.
One of the things that happens in that preprocessing is inlining
of inline-able SQL functions. This particular function is not
inline-able I think, but we'd hit the parser error on the way to
discovering that. (Hmm, I wonder if we could make inline_function
do some tests on the raw parse tree so it could bail out earlier;
I think we could verify that it's a SELECT with no FROM before
invoking parse analysis. The extra complication could be justified
as saving cycles for obviously non-inlinable SQL functions.)
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, laurenz.albe@cybertec.at, carl@msupply.foundation, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18392: Can't restore database (using pg_restore) after latest Feb 8 update
In-Reply-To: <2383726.1710452970@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