From: | Adrien Nayrat <adrien(dot)nayrat(at)anayrat(dot)info> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #18097: Immutable expression not allowed in generated at |
Date: | 2024-09-25 16:36:45 |
Message-ID: | 0267c3d0-f5f3-42e2-bf07-29ec10dd728e@anayrat.info |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
On 9/25/24 4:41 PM, Tom Lane wrote:
> Adrien Nayrat <adrien(dot)nayrat(at)anayrat(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-09-25 16:48:01 | Re: BUG #18097: Immutable expression not allowed in generated at |
Previous Message | 曾满 | 2024-09-25 15:10:44 | Re: BUG #18632: Whether you need to consider modifying the array's handling of delimiters? |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-09-25 16:48:01 | Re: BUG #18097: Immutable expression not allowed in generated at |
Previous Message | Alvaro Herrera | 2024-09-25 16:05:27 | Re: Possible null pointer dereference in afterTriggerAddEvent() |