Project

General

Profile

Bug #5608

Enable all FK constraints in Metacat production [copies]

Added by ben leinfelder over 7 years ago. Updated about 7 years ago.

Status:
New
Priority:
Normal
Category:
metacat
Target version:
Start date:
05/14/2012
Due date:
% Done:

0%

Estimated time:
Bugzilla-Id:
5608

Description

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.

History

#2 Updated by ben leinfelder over 7 years ago

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

#3 Updated by ben leinfelder over 7 years ago

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.

#4 Updated by ben leinfelder over 7 years ago

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

#5 Updated by ben leinfelder over 7 years ago

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

#6 Updated by ben leinfelder over 7 years ago

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

#7 Updated by ben leinfelder about 7 years ago

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

#8 Updated by Redmine Admin over 6 years ago

Original Bugzilla ID was 5608

Also available in: Atom PDF