Metacat

14.4.9. Some Notes About pg_dump:

http://www.postgresql.org/docs/8.3/interactive/populate.html

The errors encountered were....

ALTER TABLE xml_documents ADD
CONSTRAINT xml_documents_rep_fk
FOREIGN KEY (server_location) REFERENCES xml_replication;
--ERROR: insert or update on table "xml_documents" violates foreign key constraint "xml_documents_rep_fk"
--DETAIL: Key (server_location)=(5) is not present in table "xml_replication".

ALTER TABLE xml_revisions ADD
CONSTRAINT xml_revisions_rep_fk
FOREIGN KEY (server_location) REFERENCES xml_replication;
--ERROR: insert or update on table "xml_revisions" violates foreign key constraint "xml_revisions_rep_fk"
--DETAIL: Key (server_location)=(-2) is not present in table "xml_replication".

ALTER TABLE xml_revisions ADD
CONSTRAINT xml_revisions_catalog_fk
FOREIGN KEY (catalog_id) REFERENCES xml_catalog;
--ERROR: insert or update on table "xml_revisions" violates foreign key constraint "xml_revisions_catalog_fk"
--DETAIL: Key (catalog_id)=(27) is not present in table "xml_catalog".

ALTER TABLE xml_index ADD
CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes;
--ERROR: insert or update on table "xml_index" violates foreign key constraint "xml_index_nodeid_fk"
--DETAIL: Key (nodeid)=(471661167) is not present in table "xml_nodes".

ALTER TABLE xml_index ADD
CONSTRAINT xml_index_docid_fk
FOREIGN KEY (docid) REFERENCES xml_documents;
--ERROR: insert or update on table "xml_index" violates foreign key constraint "xml_index_docid_fk"
--DETAIL: Key (docid)=(MV.7) is not present in table "xml_documents".

ALTER TABLE xml_path_index ADD
CONSTRAINT xml_path_index_docid_fk
FOREIGN KEY (docid) REFERENCES xml_documents;
--ERROR: insert or update on table "xml_path_index" violates foreign key constraint "xml_path_index_docid_fk"
--DETAIL: Key (docid)=(MV.7) is not present in table "xml_documents".

For the index errors we can safely delete the index records in violation of the constraint and apply it.

For the server_ids, 2 and 5 we perhaps should add placeholders for them in xml_replication - who knows where they came from.

For the xml_catalog 27, we should check what the doctype might be and choose accordingly - either add a record or update the xml_documents entry.

catalog_id = 27 is a "-//ecoinformatics.org//eml-software-2.0.0beta5//EN" doctype with docid=jdoe.23.1

We have that entry as catalog_id = 41, though we may want to use a relative path (no server) for the location:
41 | DTD | null | null | -//ecoinformatics.org//eml-software-2.0.0beta5//EN | http://metacat.nceas.ucsb.edu/knb/dtd/eml-software-2.0.0.beta5e.dtd

the server_location=5 docs are 'seabloom' and 'borer' prefixes:
borer.13.2
borer.14.2
seabloom.10.1
seabloom.6.2
seabloom.7.1
seabloom.8.1
seabloom.9.1

The server_location = -2 docs are a mismash:

docid               | rev 
----------------------------------+-----
 SHS15X_015MHP2009R00_20110516.50 |   1
 knb-lter-gce.292                 |  17
 knb-lter-bnz.89                  |  10
 knb-lter-gce.95                  |  28
 knb-lter-bes.439                 |  47
 knb-lter-bes.493                 |  47
 knb-lter-gce.40                  |  37
 knb-lter-gce.91                  |  16
 knb-lter-bes.559                 |  47
 knb-lter-gce.114                 |  16
 knb-lter-gce.288                 |  17
 knb-lter-gce.184                 |  26
 knb-lter-gce.90                  |  16
 knb-lter-gce.93                  |  37
 knb-lter-gce.220                 |  27
 knb-lter-bes.344                 |  44
 CMRX00_XXXIBTNXMBR12_20110626.40 |   1
 knb-lter-gce.206                 |  17
 SHLX00_XXXIBTNXMBR21_20110509.40 |   1
 knb-lter-gce.285                 |  17
 knb-lter-bes.503                 |  47
 knb-lter-gce.92                  |  38
 knb-lter-bes.165                 |  47
 RKPX00_XXXITV2XLSR02_20101010.50 |   2
 knb-lter-bes.499                 |  46
 GHVX00_XXXITV2XHSR01_20110320.50 |   1

I've drafted a script that corrects FK violations and re-applies the original constraints. The xml_index table takes quite a long time to process (5 hours and counting as of right now for the reference to docid in xml_documents).

Original Bugzilla ID was 5608