Horton

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

Tom Lane <tgl@sss.pgh.pa.us>

View thread
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Date: 2020-11-05T16:20:29Z
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.

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