Unfortunately for us all, Postgres does not support the IGNORE_ROW_ON_DUPKEY_INDEX hint. So if you have highly concurrent code that inserts data to a table with a unique constraint, you’re in for allot of potential problems.
There is no easy fix (beside handling that scenario in the code). But if the insert rate to the table is not high, the following solution can work for you:
CREATE OR REPLACE FUNCTION lock_table() RETURNS trigger AS $$ DECLARE cnt integer; p_table_name varchar; p_query varchar; p_query_temp varchar; p_param_name varchar; p_param_type varchar; BEGIN p_param_type = TG_ARGV; p_param_name = TG_ARGV; p_table_name = TG_TABLE_NAME || '_lock'; EXECUTE 'SELECT $1."' || p_param_name || '"' USING NEW INTO p_query_temp; IF p_param_type = 'string' THEN p_query = 'select 1 from ' || TG_TABLE_NAME || ' where ' || p_param_name || '=''' || p_query_temp || ''''; ELSE p_query = 'select 1 from ' || TG_TABLE_NAME || ' where ' || p_param_name || '=' || p_query_temp; END IF; execute 'lock table ' || p_table_name || ' in exclusive mode'; execute p_query into cnt; IF cnt = 1 THEN RETURN NULL; else RETURN NEW; END IF; END; $$ LANGUAGE plpgsql; / create table my_table_lock(id varchar2(1024) not null); / CREATE TRIGGER my_table_on_duplicate_ignore BEFORE INSERT OR UPDATE ON parameter_name FOR EACH ROW EXECUTE procedure lock_table('string','name'); /
Now, locking table for exclusive mode forces the database to write rows one by one, and disable concurrency. It’s not perfect – but it will work.
Another word of caution – this solution might break your ORM tool (specifically – it happened for me on Hibernate), as returning NULL from the trigger causes the update count to be decreased to 0. Couldn’t find a good way around that problem. If you have an idea – I’d be happy to hear it.