Re: BUG #12106: pg_dump does not resolve circular dependency

On Sun, 30 Nov 2014 17:31:14 +0100, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> bouda@edookit.com writes:
>> A function may reference itself via default values of some of its
>> arguments.
>> It is rather tricky to create one, but it is possible:
>
> Unless you can demonstrate a credible use-case for this, I don't think
> it's worth adding complexity to pg_dump for.
>
>             regards, tom lane

I admin I have no real world use case for the mentioned situation,
although I think it should be documented as a pg_dump limitation, at least.

I suggest adding a new section called Limitations on the pg_dump
documentation page, which would list all currently known limitations.
Looking at the object attributes and their possible dependencies on other
objects, I found these cases not handled by pg_dump (i.e., resulting in an
erroneous dump):


1) Function argument default referring to other objects, causing a
circular dependency (even referring to the function itself) - this is the
originally reported one.


2) Domain default calling a function - which therefore must return that
domain type. E.g.,

CREATE DOMAIN dom AS INT;

CREATE FUNCTION get_default_dom() RETURNS dom
AS $$ SELECT 42::dom; $$ LANGUAGE sql;

ALTER DOMAIN dom SET DEFAULT get_default_dom();

For these, pg_dump emits CREATE FUNCTION first, then CREATE DOMAIN. The
CREATE FUNCTION fails as the domain has not been created yet. Worse, it
even emits no warnings - the pg_dump output looks entirely clear.


3) View referring to itself (or other view which in turn refers to the
first one...). E.g.,

CREATE VIEW selfview (o) AS SELECT NULL::regclass;
CREATE OR REPLACE VIEW selfview (o) AS SELECT 'selfview'::regclass;

Here, pg_dump emits CREATE VIEW selfview AS SELECT 'selfview'::regclass AS
o; - which fails (ERROR:  relation "selfview" does not exist). Similarly
to 2), no warnings are given, i.e., one finds out only when actually
trying to restore the dump.


Generally, any object may be referenced by expressions like
'seq'::regclass within:
- table column defaults
- domain defaults
- function argument defaults
- view definitions
Any of them may result in a circular dependency which pg_dump is unable to
cut.


I am aware of the fact all these cases might look silly (except for case
2), maybe), but still - mentioning them in the docs might help.

In addition, it would be nice if pg_dump could at least detect the
circular dependency in cases 2) and 3) and issue a warning, similarly to
case 1).


Ondřej Bouda

P.S. Sorry for not coming out with a patch right away - I am a newbie
here...