Re: BUG #18392: Can't restore database (using pg_restore) after latest Feb 8 update

Thanks Tom and Laurenz,

This is very helpful information and I’m sure will allow me to fix our database problem.

Kind regards,

Carl

> On 15 Mar 2024, at 10:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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