search

PecanProject / bety

Web-interface to the Biofuel Ecophysiological Traits and Yields Database (used by PEcAn and TERRA REF)
https://www.betydb.org
BSD 3-Clause "New" or "Revised" License
16 stars 38 forks source link

foreign key errors on load into Postgres 10.1 #555

Open infotroph opened 6 years ago

infotroph commented 6 years ago

Upgraded my local Postgres from 9.6 to 10.1 and Postgis from 2.3.x to 2.4.2. Load works except for a few foreign key errors that seem to be records with bad IDs: 

$ psql -d postgres -f pgdump_20171124.sql
...
ALTER TABLE
COMMENT
2017-11-25 00:24:18.841 EST [60301] ERROR:  insert or update on table "citations_sites" violates foreign key constraint "fk_citations_sites_citations_1"
2017-11-25 00:24:18.841 EST [60301] DETAIL:  Key (citation_id)=(733) is not present in table "citations".
2017-11-25 00:24:18.841 EST [60301] STATEMENT:  ALTER TABLE ONLY citations_sites
        ADD CONSTRAINT fk_citations_sites_citations_1 FOREIGN KEY (citation_id) REFERENCES citations(id) ON DELETE CASCADE;
psql:/Users/chrisb/Desktop/pgdump_20171124:3269280: ERROR:  insert or update on table "citations_sites" violates foreign key constraint "fk_citations_sites_citations_1"
DETAIL:  Key (citation_id)=(733) is not present in table "citations".
ALTER TABLE
ALTER TABLE
...
ALTER TABLE
ALTER TABLE
2017-11-25 00:24:18.911 EST [60301] ERROR:  insert or update on table "formats_variables" violates foreign key constraint "fk_formats_variables_formats_1"
2017-11-25 00:24:18.911 EST [60301] DETAIL:  Key (format_id)=(5000000002) is not present in table "formats".
2017-11-25 00:24:18.911 EST [60301] STATEMENT:  ALTER TABLE ONLY formats_variables
        ADD CONSTRAINT fk_formats_variables_formats_1 FOREIGN KEY (format_id) REFERENCES formats(id) ON DELETE CASCADE;
psql:/Users/chrisb/Desktop/pgdump_20171124:3269448: ERROR:  insert or update on table "formats_variables" violates foreign key constraint "fk_formats_variables_formats_1"
DETAIL:  Key (format_id)=(5000000002) is not present in table "formats".
2017-11-25 00:24:18.924 EST [60301] ERROR:  insert or update on table "formats_variables" violates foreign key constraint "fk_formats_variables_variables_1"
2017-11-25 00:24:18.924 EST [60301] DETAIL:  Key (variable_id)=(5000000001) is not present in table "variables".
2017-11-25 00:24:18.924 EST [60301] STATEMENT:  ALTER TABLE ONLY formats_variables
        ADD CONSTRAINT fk_formats_variables_variables_1 FOREIGN KEY (variable_id) REFERENCES variables(id) ON DELETE CASCADE;
psql:/Users/chrisb/Desktop/pgdump_20171124:3269456: ERROR:  insert or update on table "formats_variables" violates foreign key constraint "fk_formats_variables_variables_1"
DETAIL:  Key (variable_id)=(5000000001) is not present in table "variables".
2017-11-25 00:24:18.926 EST [60301] ERROR:  insert or update on table "inputs" violates foreign key constraint "fk_inputs_formats_1"
2017-11-25 00:24:18.926 EST [60301] DETAIL:  Key (format_id)=(5000000002) is not present in table "formats".
2017-11-25 00:24:18.926 EST [60301] STATEMENT:  ALTER TABLE ONLY inputs
        ADD CONSTRAINT fk_inputs_formats_1 FOREIGN KEY (format_id) REFERENCES formats(id);
psql:/Users/chrisb/Desktop/pgdump_20171124:3269464: ERROR:  insert or update on table "inputs" violates foreign key constraint "fk_inputs_formats_1"
DETAIL:  Key (format_id)=(5000000002) is not present in table "formats".
ALTER TABLE
...

As per @robkooper, these IDs correspond to the following records for me:

bety=> select * from citations_sites where citation_id = 733;
 citation_id |  site_id   |         created_at         |         updated_at         |     id     
-------------+------------+----------------------------+----------------------------+------------
         733 | 1000000009 | 2014-10-16 23:28:14.976628 | 2014-10-16 23:28:14.976628 | 1000000062
         733 | 1000000010 | 2014-11-06 15:47:24.110529 | 2014-11-06 15:47:24.110529 | 1000000063
(2 rows)

bety=> select * from formats_variables where variable_id=5000000001;
     id     | format_id  | variable_id |      name       |  unit  | storage_type | column_number |         created_at         |         updated_at         
------------+------------+-------------+-----------------+--------+--------------+---------------+----------------------------+----------------------------
 1000000153 | 5000000002 |  5000000001 | TIMESTAMP_START | ymd_hm | %Y%m%d%H%M   |             1 | 2017-04-20 21:33:23.715341 | 2017-04-20 21:33:23.715341
 1000000154 | 1000000034 |  5000000001 | TIMESTAMP_START |        | %Y%m%d%H%M   |             1 | 2017-04-28 18:35:15.293559 | 2017-04-28 18:35:15.293559
 1000000051 | 1000000024 |  5000000001 | Date            |        | %m/%d/%y     |               | 2016-10-27 17:53:18.325521 | 2016-10-27 17:53:18.325521
 1000000077 | 1000000025 |  5000000001 | date            |        | %Y-%m-%d     |               | 2016-11-25 22:33:46.639675 | 2016-11-25 22:33:46.639675
(4 rows)

select * from inputs where format_id=5000000002;
     id     |  site_id   | notes |         created_at         |         updated_at         |     start_date      |      end_date       |            name            | parent_id |  user_id   | access_level | raw | format_id
------------+------------+-------+----------------------------+----------------------------+---------------------+---------------------+----------------------------+-----------+------------+--------------+-----+------------
 1000013514 | 1000000009 |       | 2017-07-07 13:42:30.740149 | 2017-07-07 13:42:30.740149 | 1993-01-01 06:00:00 | 2015-12-31 06:00:00 | NC_pita Ameriflux          |           | 1000000682 |            4 | f   | 5000000002
 1000011244 |        756 |       | 2017-03-24 18:28:06.309279 | 2017-03-24 18:28:06.309279 | 1997-01-01 00:00:00 | 2009-12-31 00:00:00 | AmerifluxLBL_site_0-756    |           |            |            4 |     | 5000000002
 1000013308 | 1000005140 |       | 2017-06-15 11:50:41.119211 | 2017-06-15 11:50:41.119211 | 2009-01-01 00:00:00 | 2009-12-31 00:00:00 | AmerifluxLBL_site_1-5140   |           |            |            4 |     | 5000000002
 1000013546 | 1000000145 |       | 2017-07-19 13:40:50.072112 | 2017-07-19 13:40:50.072112 | 2005-01-01 00:00:00 | 2006-12-31 00:00:00 | AmerifluxLBL_site_1-145    |           |            |            4 |     | 5000000002
 1000012943 | 1000005158 |       | 2017-05-25 11:26:37.65847  | 2017-06-14 01:47:06.437885 | 1990-09-01 00:00:00 | 2009-12-31 00:00:00 | AmerifluxLBL_site_1-5158   |           |            |            4 |     | 5000000002
 1000011247 |        757 |       | 2017-03-28 11:30:55.717352 | 2017-03-28 11:48:00.685518 | 2004-01-01 00:00:00 | 2006-12-31 00:00:00 | AmerifluxLBL_site_0-757    |           |            |            4 |     | 5000000002
 1000011260 |        853 |       | 2017-04-19 17:36:10.998492 | 2017-04-20 19:51:52.748237 | 1998-01-01 06:00:00 | 2009-01-01 06:00:00 | AMF_US-Dk3                 |           | 1000000682 |            4 | f   | 5000000002
 1000012904 | 1000000038 |       | 2017-05-09 13:38:00.634372 | 2017-06-23 15:42:58.20616  | 2000-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-38     |           |            |            4 |     | 5000000002
 1000013482 | 1000000061 |       | 2017-06-23 14:56:48.377823 | 2017-06-23 14:56:48.377823 | 2005-01-01 00:00:00 | 2006-12-31 00:00:00 | AmerifluxLBL_site_1-61     |           |            |            4 |     | 5000000002
 1000012965 |        676 |       | 2017-05-31 12:43:34.185124 | 2017-08-11 22:58:35.022877 | 1979-01-01 00:00:00 | 2010-01-01 00:00:00 | AmerifluxLBL_site_0-676    |           |            |            4 |     | 5000000002
 1000012964 | 1000000421 |       | 2017-05-31 14:10:02.564758 | 2017-05-31 14:10:02.564758 | 1997-01-01 06:00:00 | 2016-01-01 05:59:00 | Ameriflux                  |           | 1000000682 |            4 | f   | 5000000002
 1000013621 | 1000000009 |       | 2017-08-05 11:34:16.473961 | 2017-08-05 11:34:16.473961 | 2005-01-01 06:00:00 | 2011-01-01 05:59:00 | AMF_US-NC2_BASE_HH_3-1.csv |           | 1000000682 |            4 | f   | 5000000002
 1000012892 | 1000000022 |       | 2017-05-01 11:25:47.958181 | 2017-06-23 15:25:52.825527 | 2004-01-01 00:00:00 | 2007-12-31 00:00:00 | AmerifluxLBL_site_1-22     |           |            |            4 |     | 5000000002
 1000013326 |        755 |       | 2017-06-20 11:32:36.373762 | 2017-08-02 06:31:45.392295 | 1996-01-01 00:00:00 | 2006-12-31 00:00:00 | AmerifluxLBL_site_0-755    |           |            |            4 |     | 5000000002
 1000013339 | 1000000030 |       | 2017-06-23 10:25:42.190336 | 2017-06-23 10:25:42.190336 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-30     |           |            |            4 |     | 5000000002
 1000013341 | 1000000016 |       | 2017-06-23 10:26:16.112865 | 2017-06-23 10:26:16.112865 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-16     |           |            |            4 |     | 5000000002
 1000013343 | 1000000017 |       | 2017-06-23 10:27:53.38964  | 2017-06-23 10:27:53.38964  | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-17     |           |            |            4 |     | 5000000002
 1000013345 | 1000000018 |       | 2017-06-23 10:29:21.1032   | 2017-06-23 10:29:21.1032   | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-18     |           |            |            4 |     | 5000000002
 1000013349 | 1000000019 |       | 2017-06-23 10:30:14.646116 | 2017-06-23 10:30:14.646116 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-19     |           |            |            4 |     | 5000000002
 1000013353 | 1000000074 |       | 2017-06-23 10:34:21.64961  | 2017-06-23 10:34:21.64961  | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-74     |           |            |            4 |     | 5000000002
 1000013355 | 1000000075 |       | 2017-06-23 10:35:09.692071 | 2017-07-19 17:37:13.190782 | 2004-01-01 00:00:00 | 2006-12-31 00:00:00 | AmerifluxLBL_site_1-75     |           |            |            4 |     | 5000000002
 1000013293 | 1000000041 |       | 2017-06-09 15:10:09.562348 | 2017-06-09 15:10:09.562348 | 2005-01-01 00:00:00 | 2016-12-31 00:00:00 | AmerifluxLBL_site_1-41     |           |            |            4 |     | 5000000002
 1000013359 | 1000000076 |       | 2017-06-23 10:36:15.717093 | 2017-06-23 10:36:15.717093 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-76     |           |            |            4 |     | 5000000002
 1000013572 | 1000000066 |       | 2017-07-30 16:41:46.819897 | 2017-07-30 16:41:46.819897 | 2005-01-01 00:00:00 | 2006-12-31 00:00:00 | AmerifluxLBL_site_1-66     |           |            |            4 |     | 5000000002
 1000013361 | 1000000078 |       | 2017-06-23 10:37:44.929434 | 2017-06-23 10:37:44.929434 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-78     |           |            |            4 |     | 5000000002
 1000013365 | 1000000112 |       | 2017-06-23 10:45:16.023226 | 2017-06-23 10:45:16.023226 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-112    |           |            |            4 |     | 5000000002
 1000013369 | 1000000128 |       | 2017-06-23 10:46:58.440344 | 2017-06-23 10:46:58.440344 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-128    |           |            |            4 |     | 5000000002
 1000013371 | 1000000131 |       | 2017-06-23 10:49:12.16078  | 2017-06-23 10:49:12.16078  | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-131    |           |            |            4 |     | 5000000002
 1000013375 | 1000000132 |       | 2017-06-23 10:49:46.307005 | 2017-06-23 19:11:53.76658  | 2004-01-01 00:00:00 | 2006-12-31 00:00:00 | AmerifluxLBL_site_1-132    |           |            |            4 |     | 5000000002
 1000013379 |        769 |       | 2017-06-23 10:50:57.680506 | 2017-06-23 10:50:57.680506 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-769    |           |            |            4 |     | 5000000002
 1000013383 | 1000000136 |       | 2017-06-23 10:51:29.244899 | 2017-06-23 10:51:29.244899 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-136    |           |            |            4 |     | 5000000002
 1000013385 | 1000000137 |       | 2017-06-23 10:52:13.124477 | 2017-06-23 10:52:13.124477 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-137    |           |            |            4 |     | 5000000002
 1000013387 | 1000000138 |       | 2017-06-23 10:53:22.819427 | 2017-06-23 10:53:22.819427 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-138    |           |            |            4 |     | 5000000002
 1000013316 |        768 |       | 2017-06-19 16:31:57.503065 | 2017-06-23 14:42:00.774266 | 2004-01-01 00:00:00 | 2006-12-31 00:00:00 | AmerifluxLBL_site_0-768    |           |            |            4 |     | 5000000002
 1000013389 | 1000000142 |       | 2017-06-23 10:55:52.16675  | 2017-06-23 10:55:52.16675  | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-142    |           |            |            4 |     | 5000000002
 1000013391 | 1000000143 |       | 2017-06-23 10:56:41.589078 | 2017-06-23 10:56:41.589078 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-143    |           |            |            4 |     | 5000000002
 1000013317 |        767 |       | 2017-06-19 16:39:02.215636 | 2017-06-23 14:59:16.245132 | 2004-01-01 00:00:00 | 2006-12-31 00:00:00 | AmerifluxLBL_site_0-767    |           |            |            4 |     | 5000000002
 1000013395 |        762 |       | 2017-06-23 11:00:36.316004 | 2017-06-23 11:00:36.316004 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-762    |           |            |            4 |     | 5000000002
 1000013399 |        775 |       | 2017-06-23 11:06:58.077029 | 2017-06-23 11:06:58.077029 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-775    |           |            |            4 |     | 5000000002
 1000013403 |        751 |       | 2017-06-23 11:08:02.505937 | 2017-06-23 11:08:02.505937 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-751    |           |            |            4 |     | 5000000002
 1000013408 |        678 |       | 2017-06-23 11:09:04.798613 | 2017-06-23 11:09:04.798613 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-678    |           |            |            4 |     | 5000000002
 1000013412 |        753 |       | 2017-06-23 11:10:01.47504  | 2017-06-23 11:10:01.47504  | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-753    |           |            |            4 |     | 5000000002
 1000013322 |        796 |       | 2017-06-20 10:18:29.168859 | 2017-09-08 20:39:40.708941 | 2004-01-01 00:00:00 | 2016-12-31 00:00:00 | AmerifluxLBL_site_0-796    |           |            |            4 |     | 5000000002
 1000013416 |        908 |       | 2017-06-23 11:10:34.602728 | 2017-06-23 11:10:34.602728 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-908    |           |            |            4 |     | 5000000002
 1000013418 |        754 |       | 2017-06-23 11:12:27.169247 | 2017-06-23 11:12:27.169247 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-754    |           |            |            4 |     | 5000000002
 1000013422 |        771 |       | 2017-06-23 11:13:32.484245 | 2017-06-23 11:13:32.484245 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-771    |           |            |            4 |     | 5000000002
 1000013426 |        770 |       | 2017-06-23 11:14:11.54717  | 2017-06-23 11:14:11.54717  | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-770    |           |            |            4 |     | 5000000002
 1000013430 |        761 |       | 2017-06-23 11:15:00.700724 | 2017-06-23 11:15:00.700724 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-761    |           |            |            4 |     | 5000000002
 1000013432 |        763 |       | 2017-06-23 11:18:33.753366 | 2017-06-23 11:18:33.753366 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-763    |           |            |            4 |     | 5000000002
 1000013436 |        752 |       | 2017-06-23 11:19:49.614125 | 2017-06-23 11:19:49.614125 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-752    |           |            |            4 |     | 5000000002
 1000013440 |        764 |       | 2017-06-23 11:20:40.434827 | 2017-08-11 23:01:54.396236 | 2004-01-01 00:00:00 | 2010-12-31 00:00:00 | AmerifluxLBL_site_0-764    |           |            |            4 |     | 5000000002
 1000013444 |        774 |       | 2017-06-23 11:21:28.379771 | 2017-06-23 11:21:28.379771 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-774    |           |            |            4 |     | 5000000002
 1000013448 |        679 |       | 2017-06-23 11:22:41.094176 | 2017-06-23 11:22:41.094176 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-679    |           |            |            4 |     | 5000000002
 1000013452 |        777 |       | 2017-06-23 11:24:15.259516 | 2017-06-23 11:24:15.259516 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-777    |           |            |            4 |     | 5000000002
 1000013456 | 1000000020 |       | 2017-06-23 11:25:17.863048 | 2017-06-23 11:25:17.863048 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-20     |           |            |            4 |     | 5000000002
 1000013460 | 1000000067 |       | 2017-06-23 11:29:32.081134 | 2017-06-23 11:29:32.081134 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-67     |           |            |            4 |     | 5000000002
 1000013464 | 1000000068 |       | 2017-06-23 11:30:08.083139 | 2017-06-23 11:30:08.083139 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-68     |           |            |            4 |     | 5000000002
 1000013468 | 1000000032 |       | 2017-06-23 11:33:50.24938  | 2017-06-23 11:33:50.24938  | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-32     |           |            |            4 |     | 5000000002
 1000013470 | 1000000028 |       | 2017-06-23 11:37:26.284727 | 2017-06-23 11:37:26.284727 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-28     |           |            |            4 |     | 5000000002
 1000013472 | 1000000029 |       | 2017-06-23 11:40:03.103326 | 2017-06-23 11:40:03.103326 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-29     |           |            |            4 |     | 5000000002
 1000013474 | 1000000031 |       | 2017-06-23 11:40:32.53831  | 2017-06-23 11:40:32.53831  | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-31     |           |            |            4 |     | 5000000002
 1000013478 | 1000000047 |       | 2017-06-23 11:41:22.125835 | 2017-06-23 11:41:22.125835 | 2004-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_1-47     |           |            |            4 |     | 5000000002
 1000011238 |        772 |       | 2017-03-23 18:05:51.603805 | 2017-06-23 17:05:22.594319 | 2003-01-01 00:00:00 | 2006-12-31 00:00:00 | AmerifluxLBL_site_0-772    |           |            |            4 |     | 5000000002
 2000000122 | 1000000045 |       | 2017-03-24 07:27:14.985375 | 2017-03-24 07:27:14.985375 | 2005-01-01 00:00:00 | 2005-12-31 00:00:00 | AmerifluxLBL_site_1-45     |           |            |            4 |     | 5000000002
 2000000123 |        622 |       | 2017-03-24 07:49:07.622529 | 2017-03-24 07:54:57.53226  | 2001-01-01 00:00:00 | 2005-12-31 00:00:00 | AmerifluxLBL_site_0-622    |           |            |            4 |     | 5000000002
 2000000133 |        778 |       | 2017-05-11 13:50:05.501109 | 2017-05-11 17:54:59.0454   | 2001-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-778    |           |            |            4 |     | 5000000002
 2000000120 |        772 |       | 2017-03-23 17:04:41.144625 | 2017-03-23 17:04:41.144625 | 2001-01-01 00:00:00 | 2005-12-31 00:00:00 | AmerifluxLBL_site_0-772    |           |            |            4 |     | 5000000002
 2000000128 |        758 |       | 2017-03-24 11:14:13.737997 | 2017-03-24 11:14:13.737997 | 2001-01-01 00:00:00 | 2004-12-31 00:00:00 | AmerifluxLBL_site_0-758    |           |            |            4 |     | 5000000002
(68 rows)
robkooper commented 6 years ago

before anything, please check to make sure I parsed the error correctly and listed the right records.

gsrohde commented 6 years ago

This may be related to issue #500.

robkooper commented 6 years ago

This is due to the fact that there seem to be orphaned items in the database. Problem is that a citation was removed, maybe updating a record betydb.org, but not on the host that holds the master version of that record. Next sync the record is reset the original state.

gsrohde commented 6 years ago

Another factor here may be that the terra-ref database has some constraints that aren't checked in and that aren't on the EBI database. We did this because we wanted to avoid some of the problems the EBI database has—duplicate rows and dangling foreign keys—without having to clean up the EBI database first.

One unforeseen consequence of this is that it makes a difference whether a user first creates his or her database from the terra-ref database or from the EBI database. Starting from the latter results in a database with more constraints.