From: Tom Lane <tgl@sss.pgh.pa.us>
To: Adrien Nayrat <adrien.nayrat@anayrat.info>
cc: Aleksander Alekseev <aleksander@timescale.com>,
        pgsql-hackers@lists.postgresql.org, James Keener <jim@jimkeener.com>
Subject: Re: BUG #18097: Immutable expression not allowed in generated at
In-reply-to: <594289a1-922d-497b-939e-20a95a71126d@anayrat.info>
References: <18097-ebb179674f22932f@postgresql.org>
 <CAKFQuwZW=wyr_Nfi8Ww+U-jxdh4tvztvMjT23iv2Ly+Oc0n5eQ@mail.gmail.com>
 <118FF128-C9CA-490C-8E67-65888EE8283F@jimkeener.com>
 <1887197.1694189299@sss.pgh.pa.us> <2250243.1694287087@sss.pgh.pa.us>
 <2275069.1694300202@sss.pgh.pa.us>
 <CAJ7c6TNNG_x8enNdhqmGdg_9A0fNa0LBadecBkuHc-=a7YqaFg@mail.gmail.com>
 <2502479.1699984134@sss.pgh.pa.us>
 <CAJ7c6TN3myMtC4_r+68=a_z60NkBipowWyX76e1j_7Vp05TPnA@mail.gmail.com>
 <2661435.1700061355@sss.pgh.pa.us>
 <CAJ7c6TNX7itd4TCUOmUXWng_C695t9WQsp8bZ_A61PBXvEV0kQ@mail.gmail.com>
 <389130.1700147198@sss.pgh.pa.us>
 <594289a1-922d-497b-939e-20a95a71126d@anayrat.info>
Comments: In-reply-to Adrien Nayrat <adrien.nayrat@anayrat.info>
	message dated "Wed, 25 Sep 2024 13:07:13 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3646823.1727275267.1@sss.pgh.pa.us>
Date: Wed, 25 Sep 2024 10:41:07 -0400
Message-ID: <3646824.1727275267@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3646824.1727275267%40sss.pgh.pa.us>
Precedence: bulk

Adrien Nayrat <adrien.nayrat@anayrat.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