postgresqltriggersupsertclass-table-inheritance

Problem with INSERT and UPDATE Trigger in case of INSERT ON CONFLICT in Postgres

I have a problem regarding ON INSERT BEFORE triggers firing on rows that are inserted via insert on conflict update upserts.

I have a postgres (v.17) database with a base table or super table that 5 other tables have a one-to-one relation to or "extend" (a sort of class table inheritance). So for every row in one of this four tables there must be a corresponding row in the base table.

CREATE TABLE super_table (
   id BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY
   -- other fields
);

CREATE TABLE sub_table1(
    id BIGINT  GENERATED ALWAYS AS IDENTITY PRIMAR KEY,
    super_id BIGINT REFERENCES super_table UNIQUE
   -- other fields
);
-- other sub_tables ...

This class table inheritance warrants that several rules are fullfilled:

Relevant with regard to my problem are the first two rules. To ensure that they are fullfilled I use two triggers. An on insert and and on update trigger.

On every of the 5 tables an ON INSERT trigger creates a new row in the base table and returns the id:
IF NEW.super_id IS NULL THEN
    INSERT INTO super_table (type, created_at, updated_at, is_deletable)
    VALUES ('some_type', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, false)
    RETURNING id INTO NEW.super_id;
END IF;
RETURN NEW;

Correspondingly there's an ON UPDATE Trigger that ensures that no matter what base_id is always set to NEW.super_id = OLD.super_id to guarantee that there will be only one row in the super_table in any of the subtables.

Problem:

My problem arises when I do Batch inserts with ON CONFLICT: That leads to both triggers firing, also on rows that are updated. The reason is that a before insert trigger will fire during insert on conflict operations before a conflict arises if a unique constraint is violated and postgres trys an update. This behaviour leads to a situation where for all inserts that resolve to updates the On insert trigger fires and creates a new row in the super table. Then the conflict arises and the operatio is aborted and postgres tries an update. That leads to this new super_table_id not inserted in the row in the subtable. Instead the on update trigger then ensures that the OLD.super_table_id is used. So integrity is preserved and for updated rows the super_table_id stays the same but a lot of usless rows are created in the super_table.

I thought about adding a new field super_table_id_created BOOLEAN to every subtable and make super_table_id NULL so that I could change the ON INSERT trigger to AFTER which would ensure that it fires during INSERT ON CONFLICT operations only after a conflict arises and thus only for rows that are actually inserted not updated. The on update trigger could then use this field.

Does anyone know a solution that either:

Solution

  • As I see it, the solution can be simple: