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

[Adrien Nayrat]

Hello,




It seems due to this fix :



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 FUNCTION s1.f3 () RETURNS SETOF s1.t1
LANGUAGE sql
AS $$
  SELECT *
  FROM s1.t1
$$;

The resulting dump is attached.





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

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

A customer encountered an issue while restoring a dump of its database after applying 15.6 minor version. > 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. CREATE TABLE s1.t1 (c1 text, c2 text GENERATED ALWAYS AS (s2.f2 (c1)) STORED); 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. HINT: No function matches the given name and argument types. You might need to add explicit type casts.

test-case-dump.sql
Description: application/sql

test-case.sql
Description: application/sql