How to counteract Before-trigger, twice called update for "insert ... on conflict"?
Ok, this will sound strange, but burn with me.
I've defined a BEFORE trigger that does a couple of things:
- adapt the current line to save by adding some calculated data
- conditionally write some changes to another table.
While I know it's idiomatic to do conditionally write some changes to a different table
in an AFTER trigger instead of a BEFORE trigger, it seems nearly impossible to extract logic 1 and 2.
So with that in mind, I have a problem:
I am doing insert on conflict
which results in row updates running twice before starting: once for insert and once for update. (ie: PG correctly raises an "already existing exception", but it happens AFTER startup before startup)
This invokes some logic in the BEFORE trigger, for example writes the changes twice, which is obviously wrong.
How to counteract this?
Although I'm not very smart, I can imagine how to manually fire an "id already exists" exception on a BEFORE trigger when a pre-existing row enters the trigger with a state TG_OP = 'INSERT'
. This will short-circuit the first flip-flop and allow the PG to take care of the rest: if a conflict occurs, a problem arises that causes the update.
However, I couldn't get this to work. Is it even possible to manually throw a manual exception that is caught by "insert .. on conflict"?
Any alternatives?
source to share
You can use a writable CTE, I think it is a PostgreSQL extension. It can emulate UPSERT this way.
Example:
CREATE TABLE trigger_table (id int, column1 text, column2 int);
INSERT INTO trigger_table VALUES (13, 'test2', 5);
CREATE OR REPLACE FUNCTION trigger_fkey()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
raise exception 'test';
END;
$function$;
CREATE TRIGGER asdf BEFORE INSERT
ON trigger_table FOR EACH ROW
EXECUTE PROCEDURE trigger_fkey();
--works, update is done, insert not
WITH cte AS (UPDATE trigger_table SET column1 = 'test1', column2 = 1
WHERE id = 13 RETURNING id)
INSERT INTO trigger_table (id, column1, column2)
SELECT id, column1, column2
FROM (VALUES (13, 'test1', 1)) AS b(id, column1, column2)
WHERE id NOT IN(SELECT id FROM cte);
--after this before insert trigger will fire (on above insert) and exception is thrown
DELETE FROM trigger_table WHERE id = 13;
source to share