Check creation of triggers in playbook

glamod / glamod-ingest

Database preparation and ingestion for GLAMOD

BSD 2-Clause "Simplified" License

2 stars 1 forks source link

Check creation of triggers in playbook #29

Open agstephens opened 4 years ago

agstephens commented 4 years ago

[x] are the triggers necessary? Yes.
[ ] cdm_lite: are they structurally correct? I.e. are the year ranges etc correct?
[ ] cdm_lite: are they being created by the playbook?
[ ] cdm_lite: do they PREVENT WRITING data into the wrong partition?
[ ] cdm_full: are they structurally correct? I.e. are the year ranges etc correct?
[ ] cdm_full: are they being created by the playbook?
[ ] cdm_full: do they PREVENT WRITING data into the wrong partition?

agstephens commented 3 years ago

So, which triggers exist?

cdm=# select count(*) from pg_trigger where tgname like 'observations_table_insert_check_%';
 count
-------
   855
(1 row)

cdm=# select count(*) from pg_trigger where tgname like 'header_table_insert_check_%';
 count
-------
   855
(1 row)

cdm=# select count(*) from pg_trigger where tgname like 'observations_insert_check_%';
 count
-------
     0
(1 row)

Okay, so triggers are needed for the cdmlite...

david-i-berry commented 3 years ago

cdm_lite: do they PREVENT WRITING data into the wrong partition?

No they don't, the constraints on the tables should do this. If the constraints do not exist it may be possible to write to the wrong table.

The triggers just redirect from the parent table to the appropriate child table when someone attempts to insert into the parent table. For this to work the fields associated with the constraints need to be set correctly.