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.mika@xxxxxxxxxxxxx
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@xxxxxxx>:
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
