Check Constraints and pg_dump

Hello again,

A project I am working on has been having problems with pg_dump's output, using 7.3. Our project's database includes functions that do constraint checking for us, as well as circular dependencies.

We heard about the changes on the pgsql HEAD/7.5, and have given it a try. It fixed nearly all our problems; however, there is one that is cropping up that we feel should be reviewed: check constraints do not get deferred when loading the data back in to the database using pg_dump's default script.

I have written a script which should be able to reproduce the problem we are encountering. We do not have binary data, so we just use the regular SQL output of pg_dump. The functions and tables create just fine, but when it gets to the COPY part of the sql script, it tries to load tables in what really is the wrong order. The check constraint is making sure there is a "plan" before there is a "contract", yet pg_dump is trying to load the contract table before there is anything in the plan table. This may seem weird at first, as the plan table is referencing the contract table's PK. Our intention is to make sure that EVERY contract has at least one plan.

Please feel free to ask me about this script and associated files. You will most likely want to edit the Test file, and make it point to the correct HEAD/7.5 run environment. I do not specify PGPORT/PGHOST etc in the file, so you will need to make sure you have those set in your environment.

Jonathan Scott

--
Jonathan Scott, Programmer, Vanten K.K.
jwscott(at)vanten(dot)com Tel: 03-5919-0266
http://www.vanten.com Fax: 03-5919-0267