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