What is the correct behaviour for a wCTE UPDATE followed by a DELETE?

Hi,

While fixing the report at https://postgr.es/m/19321.1554567786@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
toother 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