A case for UPDATE DISTINCT attribute

From: Gajus Kuizinas <gajus(at)gajus(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: A case for UPDATE DISTINCT attribute
Date: 2018-12-14 16:39:13
Message-ID: CA+6d-n69Z4Z6XX0XtcC3VOUreYCZzmiU3FW3NCaZh56Q1zTjbA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I have observed that the following pattern is repeating in our data
management programs:

UPDATE
event
SET
fuid = ${fuid},
venue_id = ${venueId},
url = ${url}
WHERE
id = ${id} AND
fuid IS != ${fuid} AND
venue_id IS != ${venueId} AND
url IS DISTINCT FROM ${url};

Note: "url" can be null. Therefore, using IS DISTINCT FROM.

The reasons we are using this pattern are multiple:

- an empty update will trigger matching triggers.
- an empty update will be WAL-logged
- an empty update create dead tuples that will need to be cleaned up by
AUTOVACUUM

In cases where the data does not change, all of these are undesirable side
effects.

Meanwhile, a WHERE condition that excludes rows with matching values makes
this into a noop in case of matching target column values.

It appears this that this pattern should be encouraged, but the verbosity
(and the accompanying risk of introducing logical error, e.g. accidentally
using = comparison on a NULLable column) makes this a rarely used pattern.

I suggest that introducing an attribute such as "UPDATE DISTINCT", e.g.

UPDATE DISTINCT
event
SET
fuid = ${fuid},
venue_id = ${venueId},
url = ${url}
WHERE
id = ${id}

would encourage greater adoption of such pattern.

Is there a technical reason this does not existing already?


Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2018-12-14 16:48:45 Re: removal of dangling temp tables
Previous Message Alvaro Herrera 2018-12-14 16:28:43 removal of dangling temp tables