From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
cc: exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16703: pg-dump fails to process recursive view definition
In-reply-to: <1473888.1604590908@sss.pgh.pa.us>
References: <16703-e348f58aab3cf6cc@postgresql.org>
 <1438431.1604588428@sss.pgh.pa.us> <1473888.1604590908@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Thu, 05 Nov 2020 10:41:48 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1477408.1604593229.1@sss.pgh.pa.us>
Date: Thu, 05 Nov 2020 11:20:29 -0500
Message-ID: <1477409.1604593229@sss.pgh.pa.us>
Precedence: bulk

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