Re: Use ctid in where clause in update from statement

Hi,

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.

Since the SELECT statement only returns records whose PK already exists, I thought an UPDATE FROM would be the better choice.
As I said, with the PK as JOIN condition it works fine too. I'm only interested in why no tid scan is used.

Dirk

--
Dirk Mika
Software Developer

[cid:image001_d0a5cf51-5dd3-4ff5-b428-1016a6d95a0f.png]

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

[cid:CCh2U_f27a5ce6-8556-4db1-8aa4-fd610d6e9efe.jpg]<https://youtu.be/qfOFXrpSKLQ>

Von: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
Datum: Montag, 1. Juli 2019 um 11:27
An: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Betreff: Re: Use ctid in where clause in update from statement

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

Postgresql supports upserts : https://www.postgresql.org/docs/11/sql-insert.html "ON CONFLICT"

BR
Dirk

--
Dirk Mika
Software Developer

[cid:part1(dot)679C32D5(dot)E7787ACA(at)matrix(dot)gatewaynet(dot)com]

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk(dot)mika(at)mikatiming(dot)de<mailto:dirk(dot)mika(at)mikatiming(dot)de>
www.mikatiming.de<http://www.mikatiming.de>

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika

[cid:part2(dot)E58C80B6(dot)31D80ADB(at)matrix(dot)gatewaynet(dot)com]<https://youtu.be/qfOFXrpSKLQ>

--

Achilleas Mantzios

IT DEV Lead

IT DEPT

Dynacom Tankers Mgmt