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

Hello,

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)
> These places could fail to detect insertion of a volatile function
default-argument expression, or decide that a polymorphic function is
volatile although it is actually immutable on the datatype of interest.
This could lead to improperly rejecting or accepting a GENERATED clause,
or to mistakenly applying the constant-default-value optimization in
ALTER TABLE ADD COLUMN.

Related commit 9057ddbef

I managed to reproduce it with a simple test case :

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);

CREATE FUNCTION s1.f3 () RETURNS SETOF s1.t1
LANGUAGE sql
AS $$
SELECT *
FROM s1.t1
$$;

The resulting dump is attached.

You will notice that the table s1.t1 is created before the function
s2.f1. This is due to the function s1.f3 which returns a SETOF s1.t1

I understand Postgres has to create s1.t1 before s1.f3. Unfortunately,
the function s2.f1 is created later.

When we try to restore the dump, we have this error :
CREATE TABLE s1.t1 (
c1 text,
c2 text GENERATED ALWAYS AS (s2.f2(c1)) STORED
);
psql:b2.sql:61: ERROR: function s2.f1(text) does not exist
LINE 2: SELECT s2.f1(c1);
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY:
SELECT s2.f1(c1);

CONTEXT: SQL function "f2" during inlining

Thanks to Jordi Morillo, Alexis Lucazeau, Matthieu Honel for reporting this.

Regards,

--
Adrien NAYRAT