Horton

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

Andrew Bille <andrewbille@gmail.com>

View thread
From: Andrew Bille <andrewbille@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>, exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Date: 2020-11-06T14:28:09Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Revert "Accept relations of any kind in LOCK TABLE".

  2. Revert "pg_dump: Lock all relations, not just plain tables".

  3. Doc: undo mistaken adjustment to LOCK TABLE docs in back branches.

  4. Don't throw an error for LOCK TABLE on a self-referential view.

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
>
>
>
>
>