Thread: Use ctid in where clause in update from statement

Hello,

 

I come from the Oracle world and am trying to convert some queries to PostgreSQL syntax. One of these queries is a MERGE statement, which I converted into an UPDATE SET FROM WHERE construct. In the original query I use the pseudo column ROWID to match a source row with a target row.

This is a simplified version of such a query:

 

 

BR

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

 

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

> I come from the Oracle world and am trying to convert some queries to
> PostgreSQL syntax. One of these queries is a MERGE statement, which I
> converted into an UPDATE SET FROM WHERE construct. In the original
> query I use the pseudo column ROWID to match a source row with a
> target row.
> 
> In the PostgreSQL version I use the column ctid for this. The above query becomes:
> 
> *UPDATE*test_large d
>    *SET* grp = s.grp
>   *FROM* (*SELECT* ctid, test_large.*
>           *FROM* test_large
>          *WHERE* grp = 1) s
>  *WHERE* d.ctid = s.ctid;

Why don't you join on the primary key column? 
The ctid comparison is typically quite slow.

Thomas

 

UPDATE test_large d

   SET grp = s.grp

  FROM (SELECT ctid, test_large.*

          FROM test_large

         WHERE grp = 1) s

 WHERE d.ctid = s.ctid;

 

 

BR

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

 

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

I know I can join using the pk, but in oracle using the rowid is faster so I wanted to know, if this is possible in
PostgreSQLas well.
 

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, 11:36 schrieb "Thomas Kellerer" <spam_eater@gmx.net>:

    > I come from the Oracle world and am trying to convert some queries to
    > PostgreSQL syntax. One of these queries is a MERGE statement, which I
    > converted into an UPDATE SET FROM WHERE construct. In the original
    > query I use the pseudo column ROWID to match a source row with a
    > target row.
    >
    > In the PostgreSQL version I use the column ctid for this. The above query becomes:
    >
    > *UPDATE*test_large d
    >    *SET* grp = s.grp
    >   *FROM* (*SELECT* ctid, test_large.*
    >           *FROM* test_large
    >          *WHERE* grp = 1) s
    >  *WHERE* d.ctid = s.ctid;

    Why don't you join on the primary key column?
    The ctid comparison is typically quite slow.

    Thomas

Dirk Mika schrieb am 01.07.2019 um 12:02:
> I know I can join using the pk, but in oracle using the rowid is
> faster so I wanted to know, if this is possible in PostgreSQL as
> well.
Well, in Postgres  ctid is not necessarily faster. 

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.

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.
 

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.

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

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

Ah, right. 

Thinking about it, it _has_ to call any BEFORE trigger function 
as that might change values of the row to be inserted that 
could possibly change the outcome of the test. 

Hi

    Thinking about it, it _has_ to call any BEFORE trigger function
    as that might change values of the row to be inserted that
    could possibly change the outcome of the test.

Yeah, that was my thought, too. Unfortunately, the affected trigger changes two columns, so I can't change it to an
AFTERROW trigger.
 

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

Dirk Mika <Dirk.Mika@mikatiming.de> writes:
> I know I can join using the pk, but in oracle using the rowid is faster so I wanted to know, if this is possible in
PostgreSQLas well. 

Existing Postgres releases are not very bright about joins on CTID ---
basically merge join is the only plan type you can get for that.
v12 will improve that somewhat.

            regards, tom lane