Message-ID: <BAY103-F174A397AB72F788D4AAA5B5EB0@phx.gbl>
In-Reply-To: <20666.1119469601@sss.pgh.pa.us>
From: "Russell Simpkins" <russellsimpkins@hotmail.com>
To: tgl@sss.pgh.pa.us, sszabo@megazone.bigpanda.com
Cc: pgsql-sql@postgresql.org
Subject: Re: after delete trigger behavior
Date: Wed, 22 Jun 2005 16:09:45 -0400
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

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