pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedRe[2]: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
2+ messages / 2 participants
[nested] [flat]
* Re[2]: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
@ 2022-12-06 18:19 Nunya Business <nb3425586@gmail.com>
2022-12-06 20:47 ` Re: Re[2]: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Nunya Business @ 2022-12-06 18:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@postgresql.org; pgsql-bugs
>"Nunya Business" <nb3425586@gmail.com> writes:
>>Within my schema there is a table that has a GENERATED ALWAYS column that calls a plpgsql function. The called function has a "row type" variable declared that references a view. While the schema itself functions properly day to day, and pg_dumpall works as expected, the generated SQL fails to successfully execute. The table in question is restored with no rows, and an error is generated during the COPY stating that the type does not exist.
>
>Hmm, do you have actually circular dependencies in that? pg_dump has
>some heuristics for dealing with such cases, but maybe it needs more.
>Please create a self-contained example and submit it to pgsql-bugs.
>
> regards, tom lane
Thanks Tom. There are indeed circular references in the schema and the
whole thing sort of doesn't pass the smell test, but this is my first
look at it. The generated column on the table calls a function which
selects from a view that references the table. The production schema
where I ran into this is pretty large and complex, so the contrived
example that follows may not be the minimum working example but it's
pretty small and has the same behavior regarding the SQL generated by
pg_dumpall.
It seems that the schema is probably invalid according to the GENERATED
rules and that pg_dumpall is operating as intended, but somehow the
check in the ALTER TABLE isn't deep enough to prevent the issue, but
maybe I'm mistaken. Once this is created, if you insert a few rows and
execute pg_dumpall, the resulting SQL cannot be loaded and will fail
during the COPY, complaining that the view referenced by the function
doesn't exist.
Here is the schema. CCing pgsql-bugs as requested.
--------------------------CUT
CREATE TABLE tblA (
id serial unique not null,
dt timestamp with time zone not null default now(),
data text
);
CREATE OR REPLACE VIEW viewA as (
SELECT sum(id) FROM tblA
);
CREATE OR REPLACE FUNCTION fnA(askid INTEGER) RETURNS integer
LANGUAGE 'plpgsql' IMMUTABLE
AS $$
declare
varA viewA;
ret integer;
begin
SELECT viewA.*
INTO varA
FROM viewA;
ret = varA.sum;
return ret;
end;
$$;
ALTER TABLE tblA ADD COLUMN idsum INTEGER GENERATED ALWAYS AS (fnA(id))
STORED;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Re[2]: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
2022-12-06 18:19 Re[2]: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns Nunya Business <nb3425586@gmail.com>
@ 2022-12-06 20:47 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2022-12-06 20:47 UTC (permalink / raw)
To: Nunya Business <nb3425586@gmail.com>; +Cc: pgsql-general@postgresql.org; pgsql-bugs
"Nunya Business" <nb3425586@gmail.com> writes:
> Thanks Tom. There are indeed circular references in the schema and the
> whole thing sort of doesn't pass the smell test, but this is my first
> look at it. The generated column on the table calls a function which
> selects from a view that references the table. The production schema
> where I ran into this is pretty large and complex, so the contrived
> example that follows may not be the minimum working example but it's
> pretty small and has the same behavior regarding the SQL generated by
> pg_dumpall.
Hm. The actual problem here is that fnA() is making unwarranted
assumptions about the search_path it's run under, so it fails when the
pg_dump script invokes it with a restrictive search_path. If you change
the function text so that the references to viewA are schema-qualified,
then it restores without errors.
"Doesn't pass the smell test" is putting it mildly, btw. Labeling
that function IMMUTABLE is a huge lie, and what it means is that
your GENERATED column doesn't have the amount of stability that
it's supposed to per spec. I'm not sure exactly what sorts of
misbehaviors might ensue from that, but I'm pretty certain that the
data in the GENERATED column after dump/restore won't match what
you had there beforehand.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-12-06 20:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-06 18:19 Re[2]: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns Nunya Business <nb3425586@gmail.com>
2022-12-06 20:47 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox