https://projects.ecoinformatics.org/ecoinfo/https://projects.ecoinformatics.org/ecoinfo/ecoinfo/favicon.ico?14691340362012-05-14T19:27:02ZEcoinformatics RedmineMetacat - Bug #5608: Enable all FK constraints in Metacat production [copies]https://projects.ecoinformatics.org/ecoinfo/issues/5608?journal_id=194512012-05-14T19:27:02Zben leinfelderleinfelder@nceas.ucsb.edu
<ul></ul><p>14.4.9. Some Notes About pg_dump:</p>
<p><a class="external" href="http://www.postgresql.org/docs/8.3/interactive/populate.html">http://www.postgresql.org/docs/8.3/interactive/populate.html</a></p> Metacat - Bug #5608: Enable all FK constraints in Metacat production [copies]https://projects.ecoinformatics.org/ecoinfo/issues/5608?journal_id=194522012-05-15T00:08:29Zben leinfelderleinfelder@nceas.ucsb.edu
<ul></ul><p>The errors encountered were....</p>
<p>ALTER TABLE xml_documents ADD<br />CONSTRAINT xml_documents_rep_fk<br />FOREIGN KEY (server_location) REFERENCES xml_replication;<br />--ERROR: insert or update on table "xml_documents" violates foreign key constraint "xml_documents_rep_fk" <br />--DETAIL: Key (server_location)=(5) is not present in table "xml_replication".</p>
<p>ALTER TABLE xml_revisions ADD<br />CONSTRAINT xml_revisions_rep_fk<br />FOREIGN KEY (server_location) REFERENCES xml_replication;<br />--ERROR: insert or update on table "xml_revisions" violates foreign key constraint "xml_revisions_rep_fk" <br />--DETAIL: Key (server_location)=(-2) is not present in table "xml_replication".</p>
<p>ALTER TABLE xml_revisions ADD<br />CONSTRAINT xml_revisions_catalog_fk<br />FOREIGN KEY (catalog_id) REFERENCES xml_catalog;<br />--ERROR: insert or update on table "xml_revisions" violates foreign key constraint "xml_revisions_catalog_fk" <br />--DETAIL: Key (catalog_id)=(27) is not present in table "xml_catalog".</p>
<p>ALTER TABLE xml_index ADD<br />CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes;<br />--ERROR: insert or update on table "xml_index" violates foreign key constraint "xml_index_nodeid_fk" <br />--DETAIL: Key (nodeid)=(471661167) is not present in table "xml_nodes".</p>
<p>ALTER TABLE xml_index ADD<br />CONSTRAINT xml_index_docid_fk<br />FOREIGN KEY (docid) REFERENCES xml_documents;<br />--ERROR: insert or update on table "xml_index" violates foreign key constraint "xml_index_docid_fk" <br />--DETAIL: Key (docid)=(MV.7) is not present in table "xml_documents".</p>
<p>ALTER TABLE xml_path_index ADD<br />CONSTRAINT xml_path_index_docid_fk<br />FOREIGN KEY (docid) REFERENCES xml_documents;<br />--ERROR: insert or update on table "xml_path_index" violates foreign key constraint "xml_path_index_docid_fk" <br />--DETAIL: Key (docid)=(MV.7) is not present in table "xml_documents".</p> Metacat - Bug #5608: Enable all FK constraints in Metacat production [copies]https://projects.ecoinformatics.org/ecoinfo/issues/5608?journal_id=194532012-05-15T00:10:48Zben leinfelderleinfelder@nceas.ucsb.edu
<ul></ul><p>For the index errors we can safely delete the index records in violation of the constraint and apply it.</p>
<p>For the server_ids, <del>2 and 5 we perhaps should add placeholders for them in xml_replication -</del> who knows where they came from.</p>
<p>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.</p> Metacat - Bug #5608: Enable all FK constraints in Metacat production [copies]https://projects.ecoinformatics.org/ecoinfo/issues/5608?journal_id=194542012-05-15T00:14:38Zben leinfelderleinfelder@nceas.ucsb.edu
<ul></ul><p>catalog_id = 27 is a "-//ecoinformatics.org//eml-software-2.0.0beta5//EN" doctype with docid=jdoe.23.1</p>
<p>We have that entry as catalog_id = 41, though we may want to use a relative path (no server) for the location:<br />41 | DTD | null | null | -//ecoinformatics.org//eml-software-2.0.0beta5//EN | <a class="external" href="http://metacat.nceas.ucsb.edu/knb/dtd/eml-software-2.0.0.beta5e.dtd">http://metacat.nceas.ucsb.edu/knb/dtd/eml-software-2.0.0.beta5e.dtd</a></p> Metacat - Bug #5608: Enable all FK constraints in Metacat production [copies]https://projects.ecoinformatics.org/ecoinfo/issues/5608?journal_id=194552012-05-15T00:16:34Zben leinfelderleinfelder@nceas.ucsb.edu
<ul></ul><p>the server_location=5 docs are 'seabloom' and 'borer' prefixes:<br /> borer.13.2<br /> borer.14.2<br /> seabloom.10.1<br /> seabloom.6.2<br /> seabloom.7.1<br /> seabloom.8.1<br /> seabloom.9.1</p> Metacat - Bug #5608: Enable all FK constraints in Metacat production [copies]https://projects.ecoinformatics.org/ecoinfo/issues/5608?journal_id=194562012-05-15T00:17:08Zben leinfelderleinfelder@nceas.ucsb.edu
<ul></ul><p>The server_location = -2 docs are a mismash:</p>
<pre><code>docid | rev <br />----------------------------------+-----<br /> SHS15X_015MHP2009R00_20110516.50 | 1<br /> knb-lter-gce.292 | 17<br /> knb-lter-bnz.89 | 10<br /> knb-lter-gce.95 | 28<br /> knb-lter-bes.439 | 47<br /> knb-lter-bes.493 | 47<br /> knb-lter-gce.40 | 37<br /> knb-lter-gce.91 | 16<br /> knb-lter-bes.559 | 47<br /> knb-lter-gce.114 | 16<br /> knb-lter-gce.288 | 17<br /> knb-lter-gce.184 | 26<br /> knb-lter-gce.90 | 16<br /> knb-lter-gce.93 | 37<br /> knb-lter-gce.220 | 27<br /> knb-lter-bes.344 | 44<br /> CMRX00_XXXIBTNXMBR12_20110626.40 | 1<br /> knb-lter-gce.206 | 17<br /> SHLX00_XXXIBTNXMBR21_20110509.40 | 1<br /> knb-lter-gce.285 | 17<br /> knb-lter-bes.503 | 47<br /> knb-lter-gce.92 | 38<br /> knb-lter-bes.165 | 47<br /> RKPX00_XXXITV2XLSR02_20101010.50 | 2<br /> knb-lter-bes.499 | 46<br /> GHVX00_XXXITV2XHSR01_20110320.50 | 1</code></pre> Metacat - Bug #5608: Enable all FK constraints in Metacat production [copies]https://projects.ecoinformatics.org/ecoinfo/issues/5608?journal_id=194572012-05-22T23:52:29Zben leinfelderleinfelder@nceas.ucsb.edu
<ul></ul><p>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).</p> Metacat - Bug #5608: Enable all FK constraints in Metacat production [copies]https://projects.ecoinformatics.org/ecoinfo/issues/5608?journal_id=194582013-03-27T21:31:04ZRedmine Admin
<ul></ul><p>Original Bugzilla ID was 5608</p>