bety
bety copied to clipboard
Import (sync) mechanism does not maintain database invariants.
The load.bety.sh
script circumvents database triggers (which enforce constraints) by using doing ALTER TABLE ${T} DISABLE TRIGGER ALL;
before adding new rows from the server being imported from. Re-enabling the triggers doesn't check that invariants still hold. While value constraints are most likely maintained (assuming they are maintained on the server being imported from) foreign-key constraints and uniqueness constraints may not hold after the import is done.
For example, machine 0 (ebi-forecast) contains the citations_sites
rows
bety=# select * from citations_sites where id in (1272,1273);
citation_id | site_id | created_at | updated_at | id
-------------+------------+----------------------------+----------------------------+------
844 | 1000000191 | 2015-10-26 17:36:01.566832 | 2015-10-26 17:36:01.566832 | 1272
845 | 1000000191 | 2015-10-26 17:38:28.2554 | 2017-04-05 04:01:19.780502 | 1273
If a user imports from this machine but does not also import sites from machine 1, the side_id
1000000191 will likely refer to a row that doesn't exist.
UPDATE: Since uniqueness constraints are implemented via indexes, they seem to be unaffected by the "DISABLE TRIGGER" command. So it is probably mainly foreign-key constraints that are at issue.