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