Re: after delete trigger behavior
От | Russell Simpkins |
---|---|
Тема | Re: after delete trigger behavior |
Дата | |
Msg-id | BAY103-F174A397AB72F788D4AAA5B5EB0@phx.gbl обсуждение исходный текст |
Ответ на | Re: after delete trigger behavior (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: