Thread: after delete trigger behavior
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
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.
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
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)?
>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.
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
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
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;