How to write an upsert trigger in PostgreSQL?

Question

In PostgreSQL 9.6 and later, what is the correct way to define a trigger function that will perform an
update whenever an insert would fail because of a uniqueness constraint?

I know it is straightforward to write

insert ... on conflict ... do update set ...

statements, but my
idea is that I want to have some tables that treat repeated inserts as updates; otherwise that piece
of logic would have to be taken care of by the application, not the DB.

One solution that I have found and that superficially does work is this:

create table versions (

  key           text primary key,

  version       text );



/* ### TAINT not sure whether there may be race conditions with this upsert trigger */

create function on_before_insert_versions() returns trigger language plpgsql volatile as $$ begin

  if exists ( select 1 from versions where key = new.key ) then

    update versions set version = new.version where key = new.key;

    return null;

    end if;

  return new;

  end; $$;



create trigger on_before_insert_versions

  before insert on versions for each row execute procedure on_before_insert_versions();



insert into versions values

  ( 'server', '3.0.3' ),

  ( 'api',    '2' );



insert into versions values

  ( 'api',    '3' );



select * from versions;



  key   | version 

--------+---------

 server | 3.0.3

 api    | 3

However, isn't the trigger prone to race conditions? I tried to use an

insert ... on conflict ... do update set ...

statement in the trigger, but that failed of course
because it triggers the trigger function itself, leading to infinite regress.

I also tried to use a pair of

alter table ... disable trigger ...

/

enable

statements, but that
errors with

cannot ALTER TABLE ... because it is being used by active queries in this session

.

What is the canonical form to always perform an update instead of an insert on uniqueness constraint
violation in PostgreSQL?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

I agree with Ilya that it would be better to do this in the application in a straightforward way.

But I take it in the spirit of a thought experiment, and my solution uses the power of pg_trigger_depth() to escape endless recursion:

CREATE OR REPLACE FUNCTION on_before_insert_versions() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF pg_trigger_depth() = 1 THEN
      INSERT INTO versions (key, version) VALUES (NEW.key, NEW.version)
         ON CONFLICT (key)
         DO UPDATE SET version = NEW.version;
      RETURN NULL;
   ELSE
      RETURN NEW;
   END IF;
END;$$;

Your solution is definitely vulnerable to a race condition: two concurrent INSERTs can lead to concurrently running triggers, both of which cannot find a matching row in versions and consequently lead to an INSERT, one of which must fail.

How to write an upsert trigger in PostgreSQL?

Sign up