I have a date-partitioned table (inheritance partitioninng just like the one
pg_partman does) which is referenced by quite a few tables in my database. To resolve the FKs checking for referenced field only in the main table (not the partitions) I created a so-called shadow table which holds the primary key and is referenced by the main table. Here's the before and after just to make it clear:
The table is partitioned on
main_part timestamp field.
Here's the DDL for the second ERD:
create table main ( main_id int8 not null, main_part timestamp, primary key (main_id)); create table main_shadow ( main_id bigserial not null, primary key (main_id)); create table sub_one ( main_id int8 not null, sub_one_data_field text); alter table main add constraint test_cnstrnt_main_main_shadow_main_id foreign key (main_id) references main_shadow (main_id) on update Cascade on delete Cascade; alter table sub_one add constraint test_cnstrnt_sub_one_main_shadow_main_id foreign key (main_id) references main_shadow (main_id) on update Cascade on delete Cascade;
on update/delete cascade statement on sub_one FK constraints. We want all the related rows deleted when the main (referenced) row is.
Inserts are handled in the same fashion the official documentation suggests.
To make deletes work properly a trigger function is created and assigned to the parent and to each child table as follows:
create or replace function test_tfnc_main_delete() returns trigger as $body$ begin --'on delete cascade' takes care of the related rows delete from main_shadow where main_id = old.main_id; return old; end; $body$ language plpgsql volatile; create trigger test_tg_ad_main_delete after delete on main for each row execute procedure test_tfnc_main_delete(); create trigger test_tg_ad_main_p1_delete after delete on main_p1 for each row execute procedure test_tfnc_main_delete(); create trigger test_tg_ad_main_p2_delete after delete on main_p2 for each row execute procedure test_tfnc_main_delete(); -- and so on for each partition
However for updates it gets a bit more complicated, since an update can cause a partition change. It is fairly easy to detect if a partition will change just by analyzing the partition field (
main_part in this case) and knowing the partitioning rules (e.g. monthly).
The easiest way to move a row from one partition to another (if that is required by an update) is to delete it from the current table and insert it into the parent table (with explicitly set
main_id field) and let the insert trigger to take care of the rest. The only problem is that the delete will fire the
test_tg_ad_main_delete after delete trigger, removing all the related rows.
The question is:
How can I prevent the said trigger from firing? Or can I use a completely different approach here?
Possible solutions I came up with but I feel like they are far from perfect:
alter table [table_name] disable trigger [tg_name], see documentation. Locks the table and thus is not really of any use.
- Adding an update-flag to the shadow-table and changing delete trigger so that it checks the flag before removing any rows: if the flag is set the trigger does nothing (keeps the shadow table record and thus all the related rows in other tables). The flag should be set by the update trigger before deleting the said row a removed right after.
- Change delete trigger so that it deletes from shadow table only if the row is not referenced by any rows in main table or its child-tables. Insert an updated row (this won't cause unique violation on the primary key since each child table has its own unique constraint and no insert is issued on the shadow-table), then delete the old one (thus the delete won't cause shadow-row being removed since its already referenced by the new copy).
Since there's not much attention I'm going to elaborate on the proposed solutions.
So far the best solution performance vise was the 3rd one. The only flaw I was able to detect is related to the case when an external app orders an insert with explicitly set
main_id and it is a duplicate. There's no good way that I can think of to deal with it. However the 2nd solution allows to work this problem around by checking if the update-flag is set (since its the only situation when duplicates are allowed) and raise an UV-exception if its not.
That being said, I guess I'm gonna wait until the end of the week and if no comments or answers are proposed I'm gonna post these proposals as an answer (edit: I never did nor am I willing to).
In any case I'll be glad to hear any thoughts on the topic.