Thread: after delete trigger behavior

after delete trigger behavior

From
"Russell Simpkins"
Date:
Hello,

I have created a trigger function to update the sort_order column of a 
mapping table. I have table a that has a many to many relation ship with 
table b that is mapped as a_b where a_id, and b_id are the pk columns and 
there is a sort_order column.  Since a_b is a mapping table there are 
foreign key constraints with a cascade option. So, if i delete an entry from 
b, an entry in a_b is deleted. What I want though is for the sort_order 
column to be updated so that all entries of a_b for a given a entry remain 
in order.

a_id, b_id, sort_order
1, 2, 0
1, 3, 1
1, 4, 2
1, 7, 3

if I delete b_id = 4 then the b_id 7 should get a sort order of 2. I created 
an after delete trigger and the trigger works just fine when i delete only 
one row, but if I delete all using "delete from a_b" I am only able to 
delete one row. Here is an example:
-----------------------------
-- a test table
CREATE TABLE test1 (
a int,
b int,
c int);
-----------------------------
-- a resort function
CREATE OR REPLACE FUNCTION resort_test1() RETURNS TRIGGER AS '
DECLARE  eachrow RECORD;  innerrow RECORD;  sort INT := 0;
BEGIN  EXECUTE ''UPDATE portfolio.test1 set c = c - 1 where a = '' || OLD.a || 
'' and c > '' || OLD.c;  RETURN OLD;
END;
' language 'plpgsql';
---------------------------------
-- the trigger
CREATE TRIGGER u_test1 BEFORE DELETE ON portfolio.test1 FOR EACH ROW EXECUTE 
PROCEDURE resort_test1();
------------------------------------------
-- dummy data
insert into test1 values(1,1,0);
insert into test1 values(1,2,1);
insert into test1 values(1,3,2);
insert into test1 values(1,4,3);
insert into test1 values(1,5,4);

insert into test1 values(2,1,0);
insert into test1 values(2,2,1);
insert into test1 values(2,3,2);
insert into test1 values(2,4,3);
insert into test1 values(2,5,4);

-- delete that works
delete from test1 where b = 3;
-- review results
select c from test1 where a = 1 order by c;
-- delete all
delete from test1;

---- note that it will only delete one row.

Is this by design? Is there something I can do to remedy this behavior? I 
would expect to have all rows delete and not just the first one.

Any help is appreciated.

Russ




Re: after delete trigger behavior

From
Stephan Szabo
Date:
On Wed, 22 Jun 2005, Russell Simpkins wrote:

> Hello,
>
> I have created a trigger function to update the sort_order column of a
> mapping table. I have table a that has a many to many relation ship with
> table b that is mapped as a_b where a_id, and b_id are the pk columns and
> there is a sort_order column.  Since a_b is a mapping table there are
> foreign key constraints with a cascade option. So, if i delete an entry from
> b, an entry in a_b is deleted. What I want though is for the sort_order
> column to be updated so that all entries of a_b for a given a entry remain
> in order.
>
> a_id, b_id, sort_order
> 1, 2, 0
> 1, 3, 1
> 1, 4, 2
> 1, 7, 3
>
> if I delete b_id = 4 then the b_id 7 should get a sort order of 2. I created
> an after delete trigger and the trigger works just fine when i delete only
> one row, but if I delete all using "delete from a_b" I am only able to
> delete one row. Here is an example:
> -----------------------------
> -- a test table
> CREATE TABLE test1 (
> a int,
> b int,
> c int);
> -----------------------------
> -- a resort function
> CREATE OR REPLACE FUNCTION resort_test1() RETURNS TRIGGER AS '
> DECLARE
>    eachrow RECORD;
>    innerrow RECORD;
>    sort INT := 0;
> BEGIN
>    EXECUTE ''UPDATE portfolio.test1 set c = c - 1 where a = '' || OLD.a ||
> '' and c > '' || OLD.c;
>    RETURN OLD;
> END;
> ' language 'plpgsql';
> ---------------------------------
> -- the trigger
> CREATE TRIGGER u_test1 BEFORE DELETE ON portfolio.test1 FOR EACH ROW EXECUTE
> PROCEDURE resort_test1();

I think this will work in an after delete trigger, but not in a before
delete trigger (and seems to in my tests). I'm not sure what the spec says
about the visibility of rows in cases like this.


Re: after delete trigger behavior

From
Tom Lane
Date:
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
>> CREATE TRIGGER u_test1 BEFORE DELETE ON portfolio.test1 FOR EACH ROW EXECUTE
>> PROCEDURE resort_test1();

> I think this will work in an after delete trigger, but not in a before
> delete trigger (and seems to in my tests). I'm not sure what the spec says
> about the visibility of rows in cases like this.

Well, the actual effect is that the first trigger's UPDATE changes all
the rows that the DELETE might later delete, thus overriding the delete.
(A query cannot modify rows already modified by commands started later
in the same transaction, such as commands issued by triggers fired by
the query itself.)

Depending on the order that the DELETE hits the rows in, there might be
more than one row that can get processed before the UPDATEs have touched
all remaining rows, so this is all pretty messy and not to be relied on.

I suspect that if you read the spec carefully it would want a "triggered
data change violation" error raised here.  My advice is not to use a
BEFORE trigger for this.

Even an AFTER trigger will have some pretty significant problems with
this, I'm afraid, because of the uncertainty about the order in which
the rows are deleted (and hence the order in which the trigger instances
fire).  For instance, suppose you delete the rows with c=1 and c=2, and
they get visited in that order.  The UPDATE for c=1 will update the row
currently having c=3 to c=2 ... whereupon that row will NOT be seen as
an update candidate by the UPDATE for c=2.  (You could work around that
case by using ">= OLD.c" instead of "> OLD.c", but it could still fail
with more than 2 rows being deleted.)  The proposed trigger only works
cleanly if the rows are deleted in decreasing order of c, and there's no
very easy way to guarantee that.
        regards, tom lane


Re: after delete trigger behavior

From
Stephan Szabo
Date:
On Wed, 22 Jun 2005, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> >> CREATE TRIGGER u_test1 BEFORE DELETE ON portfolio.test1 FOR EACH ROW EXECUTE
> >> PROCEDURE resort_test1();
>
> > I think this will work in an after delete trigger, but not in a before
> > delete trigger (and seems to in my tests). I'm not sure what the spec says
> > about the visibility of rows in cases like this.
>
> Well, the actual effect is that the first trigger's UPDATE changes all
> the rows that the DELETE might later delete, thus overriding the delete.
> (A query cannot modify rows already modified by commands started later
> in the same transaction, such as commands issued by triggers fired by
> the query itself.)
>
> Depending on the order that the DELETE hits the rows in, there might be
> more than one row that can get processed before the UPDATEs have touched
> all remaining rows, so this is all pretty messy and not to be relied on.
>
> I suspect that if you read the spec carefully it would want a "triggered
> data change violation" error raised here.  My advice is not to use a
> BEFORE trigger for this.
>
> Even an AFTER trigger will have some pretty significant problems with
> this, I'm afraid, because of the uncertainty about the order in which
> the rows are deleted (and hence the order in which the trigger instances
> fire).  For instance, suppose you delete the rows with c=1 and c=2, and
> they get visited in that order.  The UPDATE for c=1 will update the row
> currently having c=3 to c=2 ... whereupon that row will NOT be seen as
> an update candidate by the UPDATE for c=2.  (You could work around that
> case by using ">= OLD.c" instead of "> OLD.c", but it could still fail
> with more than 2 rows being deleted.)  The proposed trigger only works
> cleanly if the rows are deleted in decreasing order of c, and there's no
> very easy way to guarantee that.

Is there anything we have right now that will handle this kind of thing
without requiring either updating all the counts after a deletion in a
statement trigger or once per row updating all the counts for records with
the same "a" (doing something like make a sequence and using it in a
subselect matching keys)?


Re: after delete trigger behavior

From
"Russell Simpkins"
Date:
>I suspect that if you read the spec carefully it would want a "triggered
>data change violation" error raised here.  My advice is not to use a
>BEFORE trigger for this.

What would you recommend then. I am using Hibernate in my java application 
and if the sort_order column (in this example column.c) gets out of sync, 
then the Hibernate application fails. In hind site, the after delete trigger 
would be the better choice.

The trigger works fine for one row deletes, but I noticed that you can't 
even do multiple row deletes with the trigger. Postgres only executes one 
row delete.




Re: after delete trigger behavior

From
Tom Lane
Date:
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> Is there anything we have right now that will handle this kind of thing
> without requiring either updating all the counts after a deletion in a
> statement trigger or once per row updating all the counts for records with
> the same "a" (doing something like make a sequence and using it in a
> subselect matching keys)?

The best thing I can think of is your first idea, ie, renumbering all
the rows in a statement-level AFTER DELETE trigger.  Something like
(untested)
DECLARE    rec record;    n integer := 1;BEGIN    FOR rec IN        SELECT * FROM table        WHERE <<grouping cols =
rec'sgrouping cols>>        ORDER BY sort_order    LOOP        IF rec.sort_order != n THEN            UPDATE table SET
sort_order= n            WHERE <<primary key = rec's primary key>>;        END IF;        n := n + 1;    END LOOP;END;
 

Ugly as this is, it's at least linear in the number of rows to be
changed; the originally proposed trigger was O(N^2) in the number of
rows affected, and would surely be intolerably slow for multiple deletes
in a reasonably sized table.  Given an index on the grouping columns
plus sort_order, it could even be reasonably fast (don't forget to make
the ORDER BY match the index).
        regards, tom lane


Re: after delete trigger behavior

From
"Russell Simpkins"
Date:
Actually, I had a larger script that did exactly what you propose. However I 
started to think that a profecient where clause would do the trick.

In my mapping table, a and b are primary keys. a_id, b_id, c_sort_order. 
a_id is the parent and b_id is the child for my purposes, so if a_id is 
deleted then all relations are deleted, but if b_id is deleted, then there 
stands a chance for an index order in c_sort_order appearing.

Rather then selecting and looping, I thought I could short circut the 
procedure by saying

update mapping set c_sort_order = c_sort_order - 1 where a_id = OLD.a_id and 
c_sort_order > OLD.c_sort_order.

My thought was that there was no real reason to select and loop as this 
function would perform the resort for this series of a_id mappings. It seems 
to me that your code does the exact same thing, only in a longer form. Also 
there is no need to do anyone less then sort_order since sort_order will be 
0 to n-1 where n is the total number of mappings.

a_id, b_id, c_sort_order
1, 1, 0
1, 2, 1
1, 3, 2
1, 4, 3

if you delete where b_id = 1 then you want to update where b_id = 2, 3 and 4 
since a_id = 1 and c_sort_order is greater then 0.

Again, the issue was that postgres only executes one delete.

After changing the trigger to an after delete, I was able to delete all and 
even delete multiple rows. I now have one small problem that I will have to 
test more on. Using my where statement, if i delete from table where b_id = 
2 or b_id = 3, c_sort_order becomes out of sync. I will do another test and 
see if the select loop fairs any better.

I have a real-world function like so:

CREATE OR REPLACE FUNCTION cms.resort_content_flash() RETURNS TRIGGER AS '
DECLARE  eachrow RECORD;  innerrow RECORD;  sort INT := 0;
BEGIN  FOR eachrow IN EXECUTE ''SELECT * FROM cms.content_flash WHERE flash_id = 
'' || OLD.flash_id LOOP   sort := 0;   FOR innerrow IN EXECUTE ''SELECT * FROM cms.content_flash WHERE 
content_id = '' || eachrow.content_id || '' ORDER BY sort_order'' LOOP       IF innerrow.flash_id != OLD.flash_id THEN
        EXECUTE ''UPDATE cms.content_flash SET sort_order = '' || sort || 
 
'' WHERE content_id = '' || innerrow.content_id || '' AND flash_id = '' || 
innerrow.flash_id || '''';              sort := sort +1;       END IF;   END LOOP;  END LOOP;  RETURN OLD;
END;
' language 'plpgsql';

that I will rejigger to the test table and try out.

Thanks for the input.

>From: Tom Lane <tgl@sss.pgh.pa.us>
>To: Stephan Szabo <sszabo@megazone.bigpanda.com>
>CC: Russell Simpkins <russellsimpkins@hotmail.com>, 
>pgsql-sql@postgresql.org
>Subject: Re: [SQL] after delete trigger behavior Date: Wed, 22 Jun 2005 
>15:46:41 -0400
>
>Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > Is there anything we have right now that will handle this kind of thing
> > without requiring either updating all the counts after a deletion in a
> > statement trigger or once per row updating all the counts for records 
>with
> > the same "a" (doing something like make a sequence and using it in a
> > subselect matching keys)?
>
>The best thing I can think of is your first idea, ie, renumbering all
>the rows in a statement-level AFTER DELETE trigger.  Something like
>(untested)
>
>    DECLARE
>        rec record;
>        n integer := 1;
>    BEGIN
>        FOR rec IN
>            SELECT * FROM table
>            WHERE <<grouping cols = rec's grouping cols>>
>            ORDER BY sort_order
>        LOOP
>            IF rec.sort_order != n THEN
>                UPDATE table SET sort_order = n
>                WHERE <<primary key = rec's primary key>>;
>            END IF;
>            n := n + 1;
>        END LOOP;
>    END;
>
>Ugly as this is, it's at least linear in the number of rows to be
>changed; the originally proposed trigger was O(N^2) in the number of
>rows affected, and would surely be intolerably slow for multiple deletes
>in a reasonably sized table.  Given an index on the grouping columns
>plus sort_order, it could even be reasonably fast (don't forget to make
>the ORDER BY match the index).
>
>            regards, tom lane




Re: after delete trigger behavior

From
"Russell Simpkins"
Date:
After delete worked and the a foreach execute update seems to work best. 
Below is a satisfactory test set.

-- create test table
CREATE TABLE test1 (
a int,
b int,
c int);
-- create resort function
CREATE OR REPLACE FUNCTION resort_test1() RETURNS TRIGGER AS '
DECLARE  eachrow RECORD;  innerrow RECORD;  sort INT := 0;
BEGIN   sort := 0;  FOR eachrow IN EXECUTE ''SELECT * FROM portfolio.test1 WHERE a = '' || 
OLD.a LOOP      IF eachrow.b != OLD.b THEN          EXECUTE ''UPDATE portfolio.test1 SET c = '' || sort || '' WHERE a 
= '' || eachrow.a || '' AND b = '' || eachrow.b || '''';          sort := sort +1;      END IF;  END LOOP;  RETURN
OLD;
END;
' language 'plpgsql';

-- create trigger
CREATE TRIGGER u_test1 AFTER DELETE ON portfolio.test1 FOR EACH ROW EXECUTE 
PROCEDURE resort_test1();

-- sample data
insert into test1 values(1,1,0);
insert into test1 values(1,2,1);
insert into test1 values(1,3,2);
insert into test1 values(1,4,3);
insert into test1 values(1,5,4);

insert into test1 values(2,1,0);
insert into test1 values(2,2,1);
insert into test1 values(2,3,2);
insert into test1 values(2,4,3);
insert into test1 values(2,5,4);

-- test delete
delete from test1 where b = 2 or b = 4;
-- view test results
select * from test1 order by a, b, c;