Re: BUG #16703: pg-dump fails to process recursive view definition

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(at)sss(dot)pgh(dot)pa(dot)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
>
>
>
>
>