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

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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