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

On 9/25/24 4:41 PM, Tom Lane wrote:
> 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.

I've done a git bisect between 15.5 and 15.6 and this commit trigger the 
error.



> 
>> 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.

I see. So I understand we were lucky it worked before the commit added 
the check of volatility in generated column ?

> 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.
> 

Oh, thanks !

-- 
Adrien NAYRAT
https://pro.anayrat.info