Re: Use ctid in where clause in update from statement

I've tried it with the following trigger:

CREATE TRIGGER tr_tl_test1
BEFORE INSERT
ON public.test_large
FOR EACH ROW
EXECUTE PROCEDURE tf_tr_tl_test1 ()

The trigger function does nothing special:

CREATE OR REPLACE FUNCTION public.tf_tr_tl_test1 ()
RETURNS trigger
LANGUAGE 'plpgsql'
VOLATILE
NOT LEAKPROOF
SECURITY INVOKER
PARALLEL UNSAFE
AS
$$
BEGIN
RAISE NOTICE 'Trigger called with: %', new;
RETURN new;
END;
$$

If I do a

EXPLAIN ANALYZE
INSERT INTO test_large (id)
VALUES (2)
ON CONFLICT
ON CONSTRAINT pk_test_large
DO NOTHING;

I get the following:

NOTICE: Trigger called with: (2,,)
QUERY PLAN
---------------------------------------------------------------------------------------------------
Insert on test_large (cost=0.00..0.01 rows=1 width=40) (actual time=0.153..0.153 rows=0 loops=1)
Conflict Resolution: NOTHING
Conflict Arbiter Indexes: pk_test_large
Tuples Inserted: 0
Conflicting Tuples: 1
-> Result (cost=0.00..0.01 rows=1 width=40) (actual time=0.003..0.004 rows=1 loops=1)
Planning Time: 0.142 ms
Trigger tr_tl_test1: time=0.116 calls=1
Execution Time: 0.180 ms

As you can see the trigger function is called for the row I try to insert, but you can also see that there's no tuple inserted but one conflicting.

Dirk

--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk(dot)mika(at)mikatiming(dot)de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika

## How2Use
## the ChampionChip by mika:timing
## https://youtu.be/qfOFXrpSKLQ
Am 01.07.19, 13:33 schrieb "Thomas Kellerer" <spam_eater(at)gmx(dot)net>:

Dirk Mika schrieb am 01.07.2019 um 13:18:
> The problem with the INSERT ON CONFLICT is that an insert is tried here first, which may fire triggers.
>
> In my case there is a fairly expensive INSERT Trigger ON EACH ROW, which I would like to avoid.

The insert trigger will only be fired if an INSERT actually takes place.

If INSERT ON CONFLICT results in an UPDATE (or a DO NOTHING), no INSERT trigger will be fired.