Re: Use ctid in where clause in update from statement
От | Achilleas Mantzios |
---|---|
Тема | Re: Use ctid in where clause in update from statement |
Дата | |
Msg-id | 42c67590-0726-fc7e-c7f7-4268ee474be9@matrix.gatewaynet.com обсуждение исходный текст |
Ответ на | Re: Use ctid in where clause in update from statement (Dirk Mika <Dirk.Mika@mikatiming.de>) |
Список | pgsql-general |
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 insertedbut 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@mikatiming.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@gmx.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
В списке pgsql-general по дате отправления:
Следующее
От: Thomas KellererДата:
Сообщение: Re: Use ctid in where clause in update from statement