search

NCEAS / metacat

Data repository software that helps researchers preserve, share, and discover data
https://knb.ecoinformatics.org/software/metacat
GNU General Public License v2.0
26 stars 12 forks source link

Enable all FK constraints in Metacat production [copies] #819

Closed mbjones closed 5 years ago

mbjones commented 6 years ago

Author Name: ben leinfelder (ben leinfelder) Original Redmine Issue: 5608, https://projects.ecoinformatics.org/ecoinfo/issues/5608 Original Date: 2012-05-14 Original Assignee: ben leinfelder

Looks like the FK constraints have been removed from the production knb database.

select conname, contype, conkey, confkey from pg_constraint;

Need to see what FKs are no long satisfied and potentially fix them as best we can so that the constraints can be re enabled.

mbjones commented 6 years ago

Original Redmine Comment Author Name: ben leinfelder (ben leinfelder) Original Date: 2012-05-14T19:27:02Z

14.4.9. Some Notes About pg_dump:

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

mbjones commented 6 years ago

Original Redmine Comment Author Name: ben leinfelder (ben leinfelder) Original Date: 2012-05-15T00:08:29Z

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".

mbjones commented 6 years ago

Original Redmine Comment Author Name: ben leinfelder (ben leinfelder) Original Date: 2012-05-15T00:10:48Z

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.

mbjones commented 6 years ago

Original Redmine Comment Author Name: ben leinfelder (ben leinfelder) Original Date: 2012-05-15T00:14:38Z

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

mbjones commented 6 years ago

Original Redmine Comment Author Name: ben leinfelder (ben leinfelder) Original Date: 2012-05-15T00:16:34Z

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

mbjones commented 6 years ago

Original Redmine Comment Author Name: ben leinfelder (ben leinfelder) Original Date: 2012-05-15T00:17:08Z

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

mbjones commented 6 years ago

Original Redmine Comment Author Name: ben leinfelder (ben leinfelder) Original Date: 2012-05-22T23:52:29Z

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).

mbjones commented 6 years ago

Original Redmine Comment Author Name: Redmine Admin (Redmine Admin) Original Date: 2013-03-27T21:31:04Z

Original Bugzilla ID was 5608