Hi,

While fixing the report at https://postgr.es/m/19321.1554567...@sss.pgh.pa.us
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


Reply via email to