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