Implementing IGNORE_ROW_ON_DUPKEY_INDEX in Postgres

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[0];
  p_param_name = TG_ARGV[1];
  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.