Обсуждение: a row not deletes
Hi the list, 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? -R
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
Andres Freund-3 wrote > 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)? > > 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): IOW, it is a bug discovered during the 9.2 release that was deemed improper to back-patch. However, the bug applies to behavior that should only happen by mistake; you should not have a trigger that updates the row you are currently deleting. But since the behavior results is working code breaking it in a released branch is frowned upon. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/a-row-not-deletes-tp5801654p5801658.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Thenx for explanations. W dniu 27.04.2014 16:56, David G Johnston pisze: > Andres Freund-3 wrote >> 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)? >> 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): I'm using 9.1 (as of debian wheezy) > IOW, it is a bug discovered during the 9.2 release that was deemed improper > to back-patch. > > However, the bug applies to behavior that should only happen by mistake; you > should not have a trigger that updates the row you are currently deleting. Hmmm. I was just exersising it, as the most "elegant" resolve to my case: 1. I have a shopping chart with items 2. which (conditionally) turns into an invoice on chart deletion. 3. the assumption is: the chart is not very rigouroiusly checked during its lifetime. 4. but the invoice have to .... so some "cleanup" is due just before an item is deleted from the chart. But, I understand that as of now, I cannot do that by "trigger avalanche" :( -R