Postgresql – INSERT ON CONFLICT DO NOTHING and RAISE unique_violation

postgresql

I am processing messages from a distributed queue that guarantees at least once delivery. My strategy for handling duplicate messages has been to use ON CONFLICT DO NOTHING in my inserts.

I had to convert a few tables in my schema to use table partitioning and a trigger to handle which subtable to direct the insert into. The trigger function checks for duplicate entries and performs a RAISE unique_violation. Unfortunately the ON CONFLICT clause doesn't catch these errors and causes my query to error.

I can change the trigger to ignore these duplicates internally, but I don't want to hide these conflicts from other users of the system.

Is there a way to catch the unique_violation from the partition table via ON CONFLICT?

TL;DR How to I RAISE in a trigger that I can handle with ON CONFLICT?

Best Answer

Is there a way to catch a unique_violation via ON CONFLICT?

ON CONFLICT itself is a unique_violation catching mechanism. From the docs

ON CONFLICT can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error. (See ON CONFLICT Clause below.)

If you don't want the behavior just don't use ON CONFLICT then you catch the unique_violation yourself and the choices are endless.

That said, one of the drawbacks of partitioning from Limitations,

Using the ON CONFLICT clause with partitioned tables will cause an error, because unique or exclusion constraints can only be created on individual partitions. There is no support for enforcing uniqueness (or an exclusion constraint) across an entire partitioning hierarchy.

That's because when you partition you don't have a single index that represents uniqueness and so you either have to lock all the indexes during the check and before the insertion, or forgo that functionality.

Related Question