Thread: pg_dump and circular dependency

Table A has a CHECK constraint testing boolean function F.
Function F has a SELECT from Table A.

In my manual build script, I create the table, then the function, and then at the very end of the script to I do an
ALTERTABLE ADD CHECK. 

pg_dump appears to put the CHECK constraint as a clause in the CREATE TABLE statement. Now I'm in trouble, because
whicheverof the table or the function is read in first by pg_restore refers to an object that hasn't been created yet.
Therestore fails. 

I suppose I can replace the CHECK with an explicit trigger (that uses an exception) and then pg_dump will get the
creationsequence correct. Is there another way? 

andrew@pillette.com writes:
> Table A has a CHECK constraint testing boolean function F.
> Function F has a SELECT from Table A.

> In my manual build script, I create the table, then the function, and then at the very end of the script to I do an
ALTERTABLE ADD CHECK. 

> I suppose I can replace the CHECK with an explicit trigger (that uses an exception) and then pg_dump will get the
creationsequence correct. Is there another way? 

In 7.4 you could create the function first using SET
check_function_bodies = false.  7.5 will have a proper solution...
pg_dump can now actually detect reference circularities and fix them
by using ALTER ...

            regards, tom lane