pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16703: pg-dump fails to process recursive view definition
9+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2020-11-06 eeda7f6338 4352c2394a ec516818a8 549fd3d3bc 9e555180f2 f07811009f 43c2e97ed4 Revert "Accept relations of any kind in LOCK TABLE".
2020-11-06 d3adaabaf7 768ab4d676 f4fa4a8213 802c460a27 9e98ddd16e dee1fe5e33 fa3840c880 Revert "pg_dump: Lock all relations, not just plain tables".
2020-11-06 33862cb9c1 583fcf2f57 8b4121903c Doc: undo mistaken adjustment to LOCK TABLE docs in back branches.
2020-11-05 0bdf1ef3d5 7dc18c619d 44b973b910 5b7bfc3972 Don't throw an error for LOCK TABLE on a self-referential view.
* BUG #16703: pg-dump fails to process recursive view definition
@ 2020-11-05 12:00 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 9+ messages in thread
From: PG Bug reporting form @ 2020-11-05 12:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: exclusion@gmail.com
The following bug has been logged on the website:
Bug reference: 16703
Logged by: Alexander Lakhin
Email address: exclusion@gmail.com
PostgreSQL version: 13.0
Operating system: Ubuntu 20.04
Description:
The following recursive views (borrowed from regress/sql/lock.sql, see
"detecting infinite recursions in view definitions"):
CREATE VIEW lock_view2(a) AS SELECT NULL::integer;
CREATE VIEW lock_view3 AS SELECT * from lock_view2;
CREATE OR REPLACE VIEW lock_view2 AS SELECT * from lock_view3;
can be created successfully, but make a subsequent pg_dump fail:
pg_dump: error: query failed: ERROR: infinite recursion detected in rules
for relation "lock_view2"
pg_dump: error: query was: LOCK TABLE public.lock_view2 IN ACCESS SHARE
MODE
The offending commit is 64fc3e03.
Thanks to Andrew Bille for discovering this.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #16703: pg-dump fails to process recursive view definition
@ 2020-11-05 15:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2020-11-05 15:00 UTC (permalink / raw)
To: exclusion@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org, Alvaro Herrera <alvherre@2ndquadrant.com>
PG Bug reporting form <noreply@postgresql.org> writes:
> The following recursive views (borrowed from regress/sql/lock.sql, see
> "detecting infinite recursions in view definitions"):
> CREATE VIEW lock_view2(a) AS SELECT NULL::integer;
> CREATE VIEW lock_view3 AS SELECT * from lock_view2;
> CREATE OR REPLACE VIEW lock_view2 AS SELECT * from lock_view3;
> can be created successfully, but make a subsequent pg_dump fail:
> pg_dump: error: query failed: ERROR: infinite recursion detected in rules
> for relation "lock_view2"
> pg_dump: error: query was: LOCK TABLE public.lock_view2 IN ACCESS SHARE
> MODE
> The offending commit is 64fc3e03.
Yeah, not surprising.
It seems like the least painful solution might be to teach
LockTableRecurse to detect recursion and just not recurse into
a view it's already locked. (BTW, I wonder if we shouldn't
have a stack depth check there, too.)
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #16703: pg-dump fails to process recursive view definition
@ 2020-11-05 15:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2020-11-05 15:41 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
I wrote:
> It seems like the least painful solution might be to teach
> LockTableRecurse to detect recursion and just not recurse into
> a view it's already locked. (BTW, I wonder if we shouldn't
> have a stack depth check there, too.)
Oh, I see it already has a recursion test ... it's just that throwing
an error is an unnecessarily harsh reaction. We can just stop
recursing. Will push a fix shortly.
(Also, we need no check_stack_depth call here, because
expression_tree_walker has one.)
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #16703: pg-dump fails to process recursive view definition
@ 2020-11-05 16:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2020-11-05 16:20 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
Further news: I thought to myself "let's leave a self-referential view
behind in the final regression test state, so we can actually exercise
pg_dump/pg_upgrade with one". It turns out that that's not gonna work,
at least not right away. pg_dump dumps the view all right, but it dumps
CREATE VIEW "public"."self_referential_view" AS
SELECT "self_referential_view"."key",
"self_referential_view"."data"
FROM "public"."self_referential_view";
which of course fails to load, complaining "relation
"public.self_referential_view" does not exist".
I'm not particularly desperate to do anything about that. It's important
that pg_dump not fail on such a view, so you don't have a risk that your
backups didn't work at all. But if you have to do some finagling to
restore it, that's less critical. Also, this has been the situation all
along and there have been no complaints.
One could imagine getting pg_dump to handle this by treating the
self-reference as a circular reference and then doing what it does
to break reference loops with views. I experimented briefly with
that, but it's a bigger can of worms than it seems; pg_dump_sort.c
does not seem to have quite enough info to tell whether references
are explicit self-references or not.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #16703: pg-dump fails to process recursive view definition
@ 2020-11-06 14:28 Andrew Bille <andrewbille@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Andrew Bille @ 2020-11-06 14:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
Hello!
pg_dump is also fails to process the view created by the following script
(excerpt from privileges.sql):
CREATE USER user1;
CREATE TABLE test (col1 varchar(10), col2 boolean);
SET SESSION AUTHORIZATION user1;
CREATE VIEW testv AS SELECT * FROM test;
pg_dump: error: query failed: ERROR: permission denied for table test
pg_dump: error: query was: LOCK TABLE public.testv IN ACCESS SHARE MODE
regards, Andrew Bille
<tgl@sss.pgh.pa.us>:
> Further news: I thought to myself "let's leave a self-referential view
> behind in the final regression test state, so we can actually exercise
> pg_dump/pg_upgrade with one". It turns out that that's not gonna work,
> at least not right away. pg_dump dumps the view all right, but it dumps
>
> CREATE VIEW "public"."self_referential_view" AS
> SELECT "self_referential_view"."key",
> "self_referential_view"."data"
> FROM "public"."self_referential_view";
>
> which of course fails to load, complaining "relation
> "public.self_referential_view" does not exist".
>
> I'm not particularly desperate to do anything about that. It's important
> that pg_dump not fail on such a view, so you don't have a risk that your
> backups didn't work at all. But if you have to do some finagling to
> restore it, that's less critical. Also, this has been the situation all
> along and there have been no complaints.
>
> One could imagine getting pg_dump to handle this by treating the
> self-reference as a circular reference and then doing what it does
> to break reference loops with views. I experimented briefly with
> that, but it's a bigger can of worms than it seems; pg_dump_sort.c
> does not seem to have quite enough info to tell whether references
> are explicit self-references or not.
>
> regards, tom lane
>
>
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #16703: pg-dump fails to process recursive view definition
@ 2020-11-06 15:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andrew Bille <andrewbille@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Tom Lane @ 2020-11-06 15:05 UTC (permalink / raw)
To: Andrew Bille <andrewbille@gmail.com>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
Andrew Bille <andrewbille@gmail.com> writes:
> pg_dump is also fails to process the view created by the following script
> (excerpt from privileges.sql):
> CREATE USER user1;
> CREATE TABLE test (col1 varchar(10), col2 boolean);
> SET SESSION AUTHORIZATION user1;
> CREATE VIEW testv AS SELECT * FROM test;
Hm, yeah, so more to do here. (Sure glad we found these issues before
next week's releases, not after.)
I propose that what we'd better do is
(1) Make pg_dump use LOCK TABLE ONLY, not LOCK TABLE.
(2) Make LOCK TABLE ONLY on a view not recurse to the view's dependencies.
It's quite unclear to me why it didn't work that way all along.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #16703: pg-dump fails to process recursive view definition
@ 2020-11-06 16:42 Andrew Bille <andrewbille@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Andrew Bille @ 2020-11-06 16:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
Thanks.
After removing views like this from my dump, produced from the modified
regression test results (with most of DROP's deleted), I did not find any
other problems
regards, Andrew Bille
On Fri, Nov 6, 2020 at 10:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andrew Bille <andrewbille@gmail.com> writes:
> > pg_dump is also fails to process the view created by the following script
> > (excerpt from privileges.sql):
>
> > CREATE USER user1;
> > CREATE TABLE test (col1 varchar(10), col2 boolean);
> > SET SESSION AUTHORIZATION user1;
> > CREATE VIEW testv AS SELECT * FROM test;
>
> Hm, yeah, so more to do here. (Sure glad we found these issues before
> next week's releases, not after.)
>
> I propose that what we'd better do is
>
> (1) Make pg_dump use LOCK TABLE ONLY, not LOCK TABLE.
>
> (2) Make LOCK TABLE ONLY on a view not recurse to the view's dependencies.
> It's quite unclear to me why it didn't work that way all along.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #16703: pg-dump fails to process recursive view definition
@ 2020-11-06 20:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2020-11-06 20:25 UTC (permalink / raw)
To: Andrew Bille <andrewbille@gmail.com>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
I wrote:
> Andrew Bille <andrewbille@gmail.com> writes:
>> pg_dump is also fails to process the view created by the following script
>> (excerpt from privileges.sql):
>> CREATE USER user1;
>> CREATE TABLE test (col1 varchar(10), col2 boolean);
>> SET SESSION AUTHORIZATION user1;
>> CREATE VIEW testv AS SELECT * FROM test;
> Hm, yeah, so more to do here. (Sure glad we found these issues before
> next week's releases, not after.)
After some off-list discussion with Alvaro, we're thinking the best way
forward is to revert the pg_dump and LOCK TABLE changes for now, and
try again after next week's releases. My proposal about
> (2) Make LOCK TABLE ONLY on a view not recurse to the view's dependencies.
seems like it needs wider discussion, and there's not really time to
get that done before the wrap. Having found these two bugs in the patch
set doesn't inspire confidence that there aren't others, too.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #16703: pg-dump fails to process recursive view definition
@ 2020-11-06 20:51 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Alvaro Herrera @ 2020-11-06 20:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Bille <andrewbille@gmail.com>; exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
On 2020-Nov-06, Tom Lane wrote:
> > Hm, yeah, so more to do here. (Sure glad we found these issues before
> > next week's releases, not after.)
>
> After some off-list discussion with Alvaro, we're thinking the best way
> forward is to revert the pg_dump and LOCK TABLE changes for now, and
> try again after next week's releases.
Nod.
Thanks Andrew for reporting these problems, and thanks Tom for quickly
addressing them.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2020-11-06 20:51 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-05 12:00 BUG #16703: pg-dump fails to process recursive view definition PG Bug reporting form <noreply@postgresql.org>
2020-11-05 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-11-05 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-11-05 16:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-11-06 14:28 ` Andrew Bille <andrewbille@gmail.com>
2020-11-06 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-11-06 16:42 ` Andrew Bille <andrewbille@gmail.com>
2020-11-06 20:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-11-06 20:51 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
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