Re: BUG #18097: Immutable expression not allowed in generated at

Adrien Nayrat <adrien(dot)nayrat(at)anayrat(dot)info> writes:
> A customer encountered an issue while restoring a dump of its database
> after applying 15.6 minor version.
> It seems due to this fix :
>>> Fix function volatility checking for GENERATED and DEFAULT
>>> expressions (Tom Lane)

I don't believe this example has anything to do with that.

> CREATE SCHEMA s1;
> CREATE SCHEMA s2;
> CREATE FUNCTION s2.f1 (c1 text) RETURNS text
> LANGUAGE SQL IMMUTABLE
> AS $$
> SELECT c1
> $$;
> CREATE FUNCTION s2.f2 (c1 text) RETURNS text
> LANGUAGE SQL IMMUTABLE
> AS $$
> SELECT s2.f1 (c1);
> $$;
> CREATE TABLE s1.t1 (c1 text, c2 text GENERATED ALWAYS AS (s2.f2 (c1))
> STORED);

The problem here is that to pg_dump, the body of s2.f2 is just an
opaque string, so it has no idea that that depends on s2.f1, and
it ends up picking a dump order that doesn't respect that
dependency.

It used to be that there wasn't much you could do about this
except choose object names that wouldn't cause the problem.
In v14 and up there's another way, at least for SQL-language
functions: you can write the function in SQL spec style.

CREATE FUNCTION s2.f2 (c1 text) RETURNS text
IMMUTABLE
BEGIN ATOMIC
SELECT s2.f1 (c1);
END;

Then the dependency is visible, both to the server and to pg_dump,
and you get a valid dump order.

regards, tom lane