Re: a row not deletes

Hi,

On 2014-04-27 10:23:18 +0200, Rafał Pietrak wrote:
> I've just experienced an unexpected (for me) "loss" of DELETE. Is this a
> feature or a bug (postgres v.s. SQL)?
>
> -------------------- test case -------------------------
> test=# CREATE TABLE test (a int, b text);
> test=# INSERT INTO test (a,b) values (1,'asd');
> test=# INSERT INTO test (a,b) values (2,'dfg');
> test=# INSERT INTO test (a,b) values (3,'ghj');
> test=# CREATE or replace FUNCTION test_del () returns trigger language
> plpgsql as $$ begin update test t set b = 'will delete this' where
> t.a=old.a; return old; end; $$;
> test=# CREATE TRIGGER test_trig BEFORE DELETE ON test for each row execute
> procedure test_del();
>
> test=# DELETE FROM test where a=2;
> DELETE 0
> test=# SELECT * from test;
> a | b
> ----+-----
> 1 | asd
> 3 | ghj
> 2 | will delete this
> (3 rows)
> --------------------------------------------------------
>
> e.g.: an indicated row is not deleted, despite the fact, that the selector
> wasn't changed by the intermediate UPDATE. I understand, that the bucket
> was changed by the update, but should that matter?

I guess you're using 9.2 or older? You are not allowed to update the
deleted row in a BEFORE trigger. The source has this comment about it
(in 9.3 onwards):

/*
* 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 (hufd.cmax != estate->es_output_cid)
ereport(ERROR,
(errcode(ERRCODE_TRIGGERED_DATA_CHANGE_VIOLATION),
errmsg("tuple to be updated 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.")));

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services