1

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:

  • every row in a subtable always has a corresponding row in the super table
  • for a given row in a subtable the super_table_id always stays the same
  • if a row in a subtable is deleted the corresponding row in the super table is deleted
  • ... and some more

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:

  • allows me to maintain the super_table and subtables without triggers?
  • or uses a differen approach to ensure integrity on a database level?
Improve this question

1 Answer 1

Reset to default
2

As I see it, the solution can be simple:

  • define the foreign key from the subtables to the super_table to be DEFERRABLE INITIALLY DEFERRED, so that integrity is checked at the end of the transaction

  • use an AFTER INSERT trigger on the subtables, so that the referenced row is created after the insert into the subtable

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I saw you deleted the update i added to my question. There was indeed an error in my revised trigger. Below I posted a new trgger. I have one question though regarding your anwser. To ensure this works I have to set all super_id columns in the subtables to NULL? In order to not make it NULL I would have to change the type of the super_tables id col to UUID and corespondingly also the super_id cols in the subtables. Then i could use an BEFORE insert trigger that would generate a UUID and add it to the row. The after insert trigger would then INSERT a row in the super table.
I should have commented. It is not considered good practice to add the answer to the question. If my answer is lacking, you can either edit it or ask me to improve it.
You could use an uuid, but you need not. You could as well use the sequence used to generate primary keys for the supertable.
Ok, duly noted, I'll not update a question with an anwser in the future. The suggested approach of using the SEQUENCE didn't appear to me that works quite neatly. The only thing I observed - if understood correctly - is, that some IDs of the sequence are 'wasted' for every INSERT during upserts that evoke a CONFLICT because the BEFORE INSERT trigger that generates the next id from the sequence runs before a conflict might arise. Given the enormous size of bigint that appears to be no problem to me.
Right, that is no problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.