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:

Structure without the shadow table: Structure without the shadow table

Structure with the shadow table: Structure with the shadow table

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;

Notice the 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
    --'on delete cascade' takes care of the related rows
    delete from main_shadow where main_id = old.main_id;
    return old;
    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:

  1. Using alter table [table_name] disable trigger [tg_name], see documentation. Locks the table and thus is not really of any use.
  2. 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.
  3. 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).

Edit 1:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.