Re: Use ctid in where clause in update from statement

Hello Dirk,

pls don't top post, that's the rule here

On 1/7/19 2:40 μ.μ., Dirk Mika wrote:
> 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.

A before trigger cannot know the actual. So a BEFORE INSERT trigger will behave as such even if no INSERT takes place.
If you want to skip your particular "normal" trigger , just run :
set session_replication_role to 'replica';
and run your upsert.

>
> 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.
>
>
>
>
>

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt