Re: a row not deletes - Mailing list pgsql-general

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
otherrows."))); 



Greetings,

Andres Freund

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