From: | Dirk Mika <Dirk(dot)Mika(at)mikatiming(dot)de> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Use ctid in where clause in update from statement |
Date: | 2019-07-01 11:40:19 |
Message-ID: | B606DA07-17D2-4203-8BE5-E6C6D5B7084F@mikatiming.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleas Mantzios | 2019-07-01 12:04:59 | Re: Use ctid in where clause in update from statement |
Previous Message | Thomas Kellerer | 2019-07-01 11:33:06 | Re: Use ctid in where clause in update from statement |