Hi,
While fixing the report at https://postgr.es/m/[email protected]
I noticed that our behaviour for deleting (or updating albeit less
drastically) a row previously modified in the same query isn't
particularly useful:
DROP TABLE IF EXISTS blarg;
CREATE TABLE blarg(data text, count int);
INSERT INTO blarg VALUES('row', '1');
WITH upd AS (UPDATE blarg SET count = count + 1 RETURNING *)
DELETE FROM blarg USING upd RETURNING *;
SELECT * FROM blarg;
┌──────┬───────┐
│ data │ count │
├──────┼───────┤
│ row │ 2 │
└──────┴───────┘
(1 row)
I.e. the delete is plainly ignored. That's because it falls under:
/*
* The target tuple was already updated or
deleted by the
* current command, or by a later command in
the current
* transaction. The former case is possible in
a join DELETE
* where multiple tuples join to the same
target tuple. This
* is somewhat questionable, but Postgres has
always allowed
* it: we just ignore additional deletion
attempts.
*
* The latter case arises if the tuple is
modified by a
* command in a BEFORE trigger, or perhaps by a
command in a
* volatile function used in the query. In
such situations we
* should not ignore the deletion, but it is
equally unsafe to
* proceed. We don't want to discard the
original DELETE
* while keeping the triggered actions based on
its deletion;
* and it would be no better to allow the
original DELETE
* while discarding updates that it triggered.
The row update
* carries some information that might be
important according
* to business rules; so throwing an error is
the only safe
* course.
*
* If a trigger actually intends this type of
interaction, it
* can re-execute the DELETE and then return
NULL to cancel
* the outer delete.
*/
if (tmfd.cmax != estate->es_output_cid)
ereport(ERROR,
(errcode(ERRCODE_TRIGGERED_DATA_CHANGE_VIOLATION),
errmsg("tuple to be
deleted was already modified by an operation triggered by the current command"),
errhint("Consider
using an AFTER trigger instead of a BEFORE trigger to propagate changes to
other rows.")));
/* Else, already deleted by self; nothing to do
*/
I'm not sure what the right behaviour is. But it feels to me like the
current behaviour wasn't particularly intentional, it's just what
happened. And certainly the "already deleted by self" comment doesn't
indicate understanding that it could just as well be an update. Nor does
the comment above it refer to the possibility that the update might have
been from a [different] wCTE in a different ModifyTable node, rather
than just a redundant update/delete by the same node.
Nor do I feel is there proper tests attesting to what the behaviour
should be.
Marko, Hitoshi, Tom, was there some intended beheaviour in
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=389af951552ff2209eae3e62fa147fef12329d4f
?
Kevin, did you know that that could happen when writing
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6868ed7491b7ea7f0af6133bb66566a2f5fe5a75
?
Anyone, do you have a concrete and doable proposal of how we should
actually handle this?
Greetings,
Andres Freund
