Обсуждение: A case for UPDATE DISTINCT attribute
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?
ᐧ
Hello Gajus,
For this to hold, you need your conditions in WHERE to be ORed, not ANDed.
At least a bunch of questions and concerns arise. Like these:
1) We cannot treat it as a syntactic sugar only and just expand it on parsing stage,
as the expression to generate the value assigned may be volatile, like UPDATE ... SET ... = random();
2) How should this interact with triggers? E.g. when NEW and OLD were the same
before BEFORE UPDATE trigger execution, but would be different after. Or visa versa.
Should they be included into transition tables?
3) Should RETURNING clause return the non-updated rows?
4) It must be not easy to guarantee anything if there is a FROM clause, a target row is present in the join more than once.
5) We need to fail correctly if one of the column data types doesn't have an equality operator.
Best regards,
Alexey
...I have observed that the following pattern is repeating in our data management programs:UPDATEeventSETfuid = ${fuid},venue_id = ${venueId},url = ${url}WHEREid = ${id} ANDfuid IS != ${fuid} ANDvenue_id IS != ${venueId} ANDurl IS DISTINCT FROM ${url};
Meanwhile, a WHERE condition that excludes rows with matching values makes this into a noop in case of matching target column values.
For this to hold, you need your conditions in WHERE to be ORed, not ANDed.
UPDATE DISTINCTeventSETfuid = ${fuid},venue_id = ${venueId},url = ${url}WHEREid = ${id}would encourage greater adoption of such pattern.Is there a technical reason this does not existing already?ᐧ
At least a bunch of questions and concerns arise. Like these:
1) We cannot treat it as a syntactic sugar only and just expand it on parsing stage,
as the expression to generate the value assigned may be volatile, like UPDATE ... SET ... = random();
2) How should this interact with triggers? E.g. when NEW and OLD were the same
before BEFORE UPDATE trigger execution, but would be different after. Or visa versa.
Should they be included into transition tables?
3) Should RETURNING clause return the non-updated rows?
4) It must be not easy to guarantee anything if there is a FROM clause, a target row is present in the join more than once.
5) We need to fail correctly if one of the column data types doesn't have an equality operator.
Best regards,
Alexey