From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
cc: Carl Smith <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: <ae4f109912f70d6628eafe8efb99a6f5fbfdde83.camel@cybertec.at>
References: <18392-142078457489d3a5@postgresql.org>
 <a07a7bc4d0442e7dadce1ec57c014c2ebced13ee.camel@cybertec.at>
 <ED83A0DA-A6E7-400E-B445-8B8FC8890014@msupply.foundation>
 <ae4f109912f70d6628eafe8efb99a6f5fbfdde83.camel@cybertec.at>
Comments: In-reply-to Laurenz Albe <laurenz.albe@cybertec.at>
	message dated "Thu, 14 Mar 2024 09:55:25 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2383725.1710452970.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 14 Mar 2024 17:49:30 -0400
Message-ID: <2383726.1710452970@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2383726.1710452970%40sss.pgh.pa.us>
Precedence: bulk

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 d=
epends on the state
> of the database, in your case on the existence of a certain table (and o=
n 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