Bug in pg_dump

Hello,

There's a long pending issue with pg_dump and extensions that have table members with foreign keys. This was previously reported in this thread http://www.postgresql.org/message-id/CA+TgmoYVZkAdMGh_8EL7UVM472GerU0b4pnNFjQYe6ss1K9wDQ@mail.gmail.com and discuss by Robert. All PostgreSQL users that use the PostGis extension postgis_topology are facing the issue because the two members tables (topology and layer) are linked by foreign keys.

If you dump a database with this extension and try to import it you will experience this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3345; 0 157059176 TABLE DATA layer gilles
pg_restore: [archiver (db)] COPY failed for table "layer": ERROR:  insert or update on table "layer" violates foreign key constraint "layer_topology_id_fkey"
DETAIL:  Key (topology_id)=(1) is not present in table "topology".
WARNING: errors ignored on restore: 1

The problem is that, whatever export type you choose (plain/custom and full-export/data-only) the data of tables "topology" and "layer" are always exported in alphabetic order. I think this is a bug because outside extension, in data-only export, pg_dump is able to find foreign keys dependency and dump table's data in the right order but not with extension's members. Default is alphabetic order but that should not be the case with extension's members because constraints are recreated during the CREATE EXTENSION order. I hope I am clear enough.

Here we have three solutions:

    1/ Inform developers of extensions to take care to alphabetical order when they have member tables using foreign keys.
    2/ Inform DBAs that they have to restore the failing table independently. The use case above can be resumed using the following command:

         pg_restore -h localhost -n topology -t layer -Fc -d testdb_empty testdump.dump

    3/ Inform DBAs that they have to restore the schema first then the data only using --disable-triggers
    4/ Patch pg_dump to solve this issue.

I attach a patch that solves the issue in pg_dump, let me know if it might be included in Commit Fest or if the three other solutions are a better choice. I also join a sample extension (test_fk_in_ext) to be able to reproduce the issue and test the patch. Note that it might exists a simpler solution than the one I used in this patch, if this is the case please point me on the right way, I will be pleased to rewrite and send an other patch.

In the test extension attached, there is a file called test_fk_in_ext/SYNOPSIS.txt that describe all actions to reproduce the issue and test the patch. Here is the SQL part of the test extension:

CREATE TABLE IF NOT EXISTS b_test_fk_in_ext1 (
        id int PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS a_test_fk_in_ext1 (
        id int REFERENCES b_test_fk_in_ext1(id)
);

SELECT pg_catalog.pg_extension_config_dump('b_test_fk_in_ext1', '');
SELECT pg_catalog.pg_extension_config_dump('a_test_fk_in_ext1', '');

Best regards,

-- 
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org