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 ...
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
insert ... on conflict ... do update set ...
alter table ... disable trigger ...
enable
cannot ALTER TABLE ... because it is being used by active queries in this session
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.