Thread: Best way to handle table trigger on update
Postgres 7.4.7 (I know, a little old, but we haven't had a chance to upgrade) I have a table that stores menu items for a side navigation menu for a web site. Each menu item has a "position" column set that determines where to put the menu item in the display. At any given time, the menu items should not have any conflicting positions and should be sequential. For example id | name | position -----+-------------------+---------- 1 | About Us | 1 2 | History | 2 3 | Support | 3 4 | Job Opportunities | 4 5 | Sitemap | 5 ... I have an UPDATE trigger defined on the table to handle keeping the positions correct. CREATE TRIGGER "update_menu_item" BEFORE UPDATE ON "menu_items" FOR EACH ROW EXECUTE PROCEDURE update_menu_item(); When I update an existing row (say ID 3) with a new position (let's say 1), the trigger will bump the menu items with a lower position up by one (position 2 becomes 3, 1 becomes 2) and everything is back to normal. The catch is the trigger performs this position bumping by making an update on the menu items table, thus firing the trigger again for each updated row (and leading to chaos). Currently, the only workaround I have found is to drop the trigger at the start of the stored procedure, make the updates, then recreate the trigger. What is the best way to handle a situation like this? I can't imagine that dropping and recreating the trigger is the ideal solution. Thanks. Justin Pasher
You should be able to detect for the case where NEW.position > OLD.position and ignore it, no? On Tue, Jan 31, 2006 at 01:45:09PM -0600, Justin Pasher wrote: > Postgres 7.4.7 (I know, a little old, but we haven't had a chance to > upgrade) > > I have a table that stores menu items for a side navigation menu for a web > site. Each menu item has a "position" column set that determines where to > put the menu item in the display. At any given time, the menu items should > not have any conflicting positions and should be sequential. For example > > id | name | position > -----+-------------------+---------- > 1 | About Us | 1 > 2 | History | 2 > 3 | Support | 3 > 4 | Job Opportunities | 4 > 5 | Sitemap | 5 > > ... > > I have an UPDATE trigger defined on the table to handle keeping the > positions correct. > > CREATE TRIGGER "update_menu_item" BEFORE UPDATE ON "menu_items" FOR EACH ROW > EXECUTE PROCEDURE update_menu_item(); > > When I update an existing row (say ID 3) with a new position (let's say 1), > the trigger will bump the menu items with a lower position up by one > (position 2 becomes 3, 1 becomes 2) and everything is back to normal. The > catch is the trigger performs this position bumping by making an update on > the menu items table, thus firing the trigger again for each updated row > (and leading to chaos). Currently, the only workaround I have found is to > drop the trigger at the start of the stored procedure, make the updates, > then recreate the trigger. > > What is the best way to handle a situation like this? I can't imagine that > dropping and recreating the trigger is the ideal solution. Thanks. > > > Justin Pasher > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
I must not be following what you are saying. There are cases where
NEW.position could be greater than OLD.position and it cannot ignore it.
Here's the pseudo code for my store procedure.
IF OLD.position <> NEW.position THEN
drop the trigger
IF NEW.position > OLD.position THEN
bump the menu items down by one position that have position >
OLD.position and position <= NEW.position
END IF;
IF NEW.position < OLD.position THEN
bump the menu items up by one position that have position <
OLD.position and position >= NEW.position
END IF;
finally, loop through all of the menu items that are in the same
"group" as NEW and renumber them starting at 1
create the trigger
END IF;
RETURN NEW;
The "bumping" is done with a pretty straight forward UPDATE query. The
looping through to renumber the items also does a simple UPDATE with a
counter starting at one. If an INSERT is done that assigns a position higher
than the max position+1, it can form gaps.
So taking my simplified example below, here is the possible sequence of
events for an update (changing position of ID 3 entry to position 1).
UPDATE menu_items SET position = 1 WHERE id = 3;
-- fires trigger
-- trigger sees NEW.position < OLD.position, so UPDATE menu_items and bump
position+1
-- ID=1 and ID=2 need updating, so....
---- fires trigger for ID=1 (because position goes from 1 to 2)
---- trigger sees NEW.position > OLD.position, so UPDATE menu_items and bump
position-1
---- ID=2 need updating, so....
------ fires trigger for ID=2 (position goes from 2 to 1)
------ now ID=2 has position=1, which conflicts with our new update (and is
the wrong position)
I think this is right. It kinda makes my head hurt trying to actually follow
the recursion. :) But I don't see how I can ignore the case of NEW.position
> OLD.position, because if I go the opposite route (UPDATE menu_items SET
position = 3 WHERE id = 1), the comparison in the trigger would fail and
nothing would update right.
Justin Pasher
> -----Original Message-----
> From: Jim C. Nasby [mailto:jnasby@pervasive.com]
> Sent: Tuesday, January 31, 2006 4:01 PM
> To: Justin Pasher
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Best way to handle table trigger on update
>
>
> You should be able to detect for the case where NEW.position >
> OLD.position and ignore it, no?
>
> On Tue, Jan 31, 2006 at 01:45:09PM -0600, Justin Pasher wrote:
> > Postgres 7.4.7 (I know, a little old, but we haven't had a chance to
> > upgrade)
> >
> > I have a table that stores menu items for a side navigation
> menu for a web
> > site. Each menu item has a "position" column set that
> determines where to
> > put the menu item in the display. At any given time, the
> menu items should
> > not have any conflicting positions and should be
> sequential. For example
> >
> > id | name | position
> > -----+-------------------+----------
> > 1 | About Us | 1
> > 2 | History | 2
> > 3 | Support | 3
> > 4 | Job Opportunities | 4
> > 5 | Sitemap | 5
> >
> > ...
> >
> > I have an UPDATE trigger defined on the table to handle keeping the
> > positions correct.
> >
> > CREATE TRIGGER "update_menu_item" BEFORE UPDATE ON
> "menu_items" FOR EACH ROW
> > EXECUTE PROCEDURE update_menu_item();
> >
> > When I update an existing row (say ID 3) with a new
> position (let's say 1),
> > the trigger will bump the menu items with a lower position up by one
> > (position 2 becomes 3, 1 becomes 2) and everything is back
> to normal. The
> > catch is the trigger performs this position bumping by
> making an update on
> > the menu items table, thus firing the trigger again for
> each updated row
> > (and leading to chaos). Currently, the only workaround I
> have found is to
> > drop the trigger at the start of the stored procedure, make
> the updates,
> > then recreate the trigger.
> >
> > What is the best way to handle a situation like this? I
> can't imagine that
> > dropping and recreating the trigger is the ideal solution. Thanks.
> >
> >
> > Justin Pasher
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
I do something similar, and I have the front end generate updates to the position column of all rows, executed in a single transaction. Then you can fiddle around with the options and tradeoffs of how to ensure in the db that no update is accepted that violates the constraints... -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 665-7007 voice
On Tue, 2006-01-31 at 13:45 -0600, Justin Pasher wrote: > Postgres 7.4.7 (I know, a little old, but we haven't had a chance to > upgrade) > > I have a table that stores menu items for a side navigation menu for a web > site. Each menu item has a "position" column set that determines where to > put the menu item in the display. At any given time, the menu items should > not have any conflicting positions and should be sequential. For example > > id | name | position > -----+-------------------+---------- > 1 | About Us | 1 > 2 | History | 2 > 3 | Support | 3 > 4 | Job Opportunities | 4 > 5 | Sitemap | 5 > > ... > > I have an UPDATE trigger defined on the table to handle keeping the > positions correct. > > CREATE TRIGGER "update_menu_item" BEFORE UPDATE ON "menu_items" FOR EACH ROW > EXECUTE PROCEDURE update_menu_item(); > > When I update an existing row (say ID 3) with a new position (let's say 1), > the trigger will bump the menu items with a lower position up by one > (position 2 becomes 3, 1 becomes 2) and everything is back to normal. The > catch is the trigger performs this position bumping by making an update on > the menu items table, thus firing the trigger again for each updated row > (and leading to chaos). Currently, the only workaround I have found is to > drop the trigger at the start of the stored procedure, make the updates, > then recreate the trigger. Rather than using a trigger why not create a function to do the update? The following will do the trick with the only modification needed to your table is the addition of the boolean column "isupdate" which should default to false. The two arguments taken by the function are the current position of the intended menu item and its new target position: create or replace function update_menu_item(int,int) returns void as ' update menu_items set isupdate = true where position = $1; update menu_items set position = case when $1 > $2 THEN position +1 when $2 > $1 then position - 1 else position end where position <= case when $1 > $2 then $1 else $2 end and position >= case when $1 > $2 then $2 else $1 end and isupdate = false; update menu_items set position = $2 where position = $1 and isupdate; update menu_items set isupdate = false where isupdate = true; ' LANGUAGE sql volatile; Then if you want to move Job Opportunities from position 4 to position 2, just call the function: select update_menu_item(4,2); HTH, Sven P.S. The function is pretty rough and I am sure could be better optimized, but you get the idea of the logic flow there.
> -----Original Message----- > From: Sven Willenberger [mailto:sven@dmv.com] > Sent: Wednesday, February 01, 2006 2:13 PM > To: Justin Pasher > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Best way to handle table trigger on update > > > On Tue, 2006-01-31 at 13:45 -0600, Justin Pasher wrote: > > Postgres 7.4.7 (I know, a little old, but we haven't had a chance to > > upgrade) > > > > I have a table that stores menu items for a side navigation > menu for a web > > site. Each menu item has a "position" column set that > determines where to > > put the menu item in the display. At any given time, the > menu items should > > not have any conflicting positions and should be > sequential. For example > > > > id | name | position > > -----+-------------------+---------- > > 1 | About Us | 1 > > 2 | History | 2 > > 3 | Support | 3 > > 4 | Job Opportunities | 4 > > 5 | Sitemap | 5 > > > > ... > > > > I have an UPDATE trigger defined on the table to handle keeping the > > positions correct. > > > > CREATE TRIGGER "update_menu_item" BEFORE UPDATE ON > "menu_items" FOR EACH ROW > > EXECUTE PROCEDURE update_menu_item(); > > > > When I update an existing row (say ID 3) with a new > position (let's say 1), > > the trigger will bump the menu items with a lower position up by one > > (position 2 becomes 3, 1 becomes 2) and everything is back > to normal. The > > catch is the trigger performs this position bumping by > making an update on > > the menu items table, thus firing the trigger again for > each updated row > > (and leading to chaos). Currently, the only workaround I > have found is to > > drop the trigger at the start of the stored procedure, make > the updates, > > then recreate the trigger. > > Rather than using a trigger why not create a function to do > the update? > The following will do the trick with the only modification needed to > your table is the addition of the boolean column "isupdate" > which should > default to false. The two arguments taken by the function are the > current position of the intended menu item and its new target > position: > > create or replace function update_menu_item(int,int) returns void as ' > update menu_items set isupdate = true where position = $1; > update menu_items set position = case when $1 > $2 THEN > position +1 when > $2 > $1 then position - 1 else position end > where position <= case when $1 > $2 then $1 else $2 end and > position >= > case when $1 > $2 then $2 else $1 end and isupdate = false; > update menu_items set position = $2 where position = $1 and isupdate; > update menu_items set isupdate = false where isupdate = true; > ' > LANGUAGE sql volatile; > > Then if you want to move Job Opportunities from position 4 to position > 2, just call the function: > select update_menu_item(4,2); > > HTH, > > Sven This would work, but my goal is to create something that is transparent to the user that is inserting the data (i.e. they perform a normal INSERT/UPDATE on the table and "It Just Works"). Thanks for the suggestion. Justin Pasher
On Thu, 2006-02-02 at 08:58 -0600, Justin Pasher wrote: > > -----Original Message----- > > From: Sven Willenberger [mailto:sven@dmv.com] > > Sent: Wednesday, February 01, 2006 2:13 PM > > To: Justin Pasher > > Cc: pgsql-general@postgresql.org > > Subject: Re: [GENERAL] Best way to handle table trigger on update > > > > > > On Tue, 2006-01-31 at 13:45 -0600, Justin Pasher wrote: > > > Postgres 7.4.7 (I know, a little old, but we haven't had a chance to > > > upgrade) > > > > > > I have a table that stores menu items for a side navigation > > menu for a web > > > site. Each menu item has a "position" column set that > > determines where to > > > put the menu item in the display. At any given time, the > > menu items should > > > not have any conflicting positions and should be > > sequential. For example > > > > > > id | name | position > > > -----+-------------------+---------- > > > 1 | About Us | 1 > > > 2 | History | 2 > > > 3 | Support | 3 > > > 4 | Job Opportunities | 4 > > > 5 | Sitemap | 5 > > > > > > ... > > > > > > I have an UPDATE trigger defined on the table to handle keeping the > > > positions correct. > > > > > > CREATE TRIGGER "update_menu_item" BEFORE UPDATE ON > > "menu_items" FOR EACH ROW > > > EXECUTE PROCEDURE update_menu_item(); > > > > > > When I update an existing row (say ID 3) with a new > > position (let's say 1), > > > the trigger will bump the menu items with a lower position up by one > > > (position 2 becomes 3, 1 becomes 2) and everything is back > > to normal. The > > > catch is the trigger performs this position bumping by > > making an update on > > > the menu items table, thus firing the trigger again for > > each updated row > > > (and leading to chaos). Currently, the only workaround I > > have found is to > > > drop the trigger at the start of the stored procedure, make > > the updates, > > > then recreate the trigger. > > > > Rather than using a trigger why not create a function to do > > the update? > > The following will do the trick with the only modification needed to > > your table is the addition of the boolean column "isupdate" > > which should > > default to false. The two arguments taken by the function are the > > current position of the intended menu item and its new target > > position: > > > > create or replace function update_menu_item(int,int) returns void as ' > > update menu_items set isupdate = true where position = $1; > > update menu_items set position = case when $1 > $2 THEN > > position +1 when > > $2 > $1 then position - 1 else position end > > where position <= case when $1 > $2 then $1 else $2 end and > > position >= > > case when $1 > $2 then $2 else $1 end and isupdate = false; > > update menu_items set position = $2 where position = $1 and isupdate; > > update menu_items set isupdate = false where isupdate = true; > > ' > > LANGUAGE sql volatile; > > > > Then if you want to move Job Opportunities from position 4 to position > > 2, just call the function: > > select update_menu_item(4,2); > > > > HTH, > > > > Sven > > > This would work, but my goal is to create something that is transparent to > the user that is inserting the data (i.e. they perform a normal > INSERT/UPDATE on the table and "It Just Works"). Thanks for the suggestion. > > > Justin Pasher > In that case you could create a rule: ON INSERT ... DO INSTEAD ... (and ON UPDATE ... DO INSTEAD ...) and invoke the function that way. Barring that, I think that dropping the trigger and re-adding it the way you have done is about the only way to avoid all that recursion. Sven
On Thu, 2006-02-02 at 10:16 -0500, Sven Willenberger wrote: > On Thu, 2006-02-02 at 08:58 -0600, Justin Pasher wrote: > > > -----Original Message----- > > > From: Sven Willenberger [mailto:sven@dmv.com] > > > Sent: Wednesday, February 01, 2006 2:13 PM > > > To: Justin Pasher > > > Cc: pgsql-general@postgresql.org > > > Subject: Re: [GENERAL] Best way to handle table trigger on update > > > > > > > > > On Tue, 2006-01-31 at 13:45 -0600, Justin Pasher wrote: > > > > Postgres 7.4.7 (I know, a little old, but we haven't had a chance to > > > > upgrade) > > > > > > > > I have a table that stores menu items for a side navigation > > > menu for a web > > > > site. Each menu item has a "position" column set that > > > determines where to > > > > put the menu item in the display. At any given time, the > > > menu items should > > > > not have any conflicting positions and should be > > > sequential. For example > > > > > > > > id | name | position > > > > -----+-------------------+---------- > > > > 1 | About Us | 1 > > > > 2 | History | 2 > > > > 3 | Support | 3 > > > > 4 | Job Opportunities | 4 > > > > 5 | Sitemap | 5 > > > > > > > > ... > > > > > > > > I have an UPDATE trigger defined on the table to handle keeping the > > > > positions correct. > > > > > > > > CREATE TRIGGER "update_menu_item" BEFORE UPDATE ON > > > "menu_items" FOR EACH ROW > > > > EXECUTE PROCEDURE update_menu_item(); > > > > > > > > When I update an existing row (say ID 3) with a new > > > position (let's say 1), > > > > the trigger will bump the menu items with a lower position up by one > > > > (position 2 becomes 3, 1 becomes 2) and everything is back > > > to normal. The > > > > catch is the trigger performs this position bumping by > > > making an update on > > > > the menu items table, thus firing the trigger again for > > > each updated row > > > > (and leading to chaos). Currently, the only workaround I > > > have found is to > > > > drop the trigger at the start of the stored procedure, make > > > the updates, > > > > then recreate the trigger. > > > > > > Rather than using a trigger why not create a function to do > > > the update? > > > The following will do the trick with the only modification needed to > > > your table is the addition of the boolean column "isupdate" > > > which should > > > default to false. The two arguments taken by the function are the > > > current position of the intended menu item and its new target > > > position: > > > > > > create or replace function update_menu_item(int,int) returns void as ' > > > update menu_items set isupdate = true where position = $1; > > > update menu_items set position = case when $1 > $2 THEN > > > position +1 when > > > $2 > $1 then position - 1 else position end > > > where position <= case when $1 > $2 then $1 else $2 end and > > > position >= > > > case when $1 > $2 then $2 else $1 end and isupdate = false; > > > update menu_items set position = $2 where position = $1 and isupdate; > > > update menu_items set isupdate = false where isupdate = true; > > > ' > > > LANGUAGE sql volatile; > > > > > > Then if you want to move Job Opportunities from position 4 to position > > > 2, just call the function: > > > select update_menu_item(4,2); > > > > > > HTH, > > > > > > Sven > > > > > > This would work, but my goal is to create something that is transparent to > > the user that is inserting the data (i.e. they perform a normal > > INSERT/UPDATE on the table and "It Just Works"). Thanks for the suggestion. > > > > > > Justin Pasher > > > In that case you could create a rule: ON INSERT ... DO INSTEAD ... (and > ON UPDATE ... DO INSTEAD ...) and invoke the function that way. Barring > that, I think that dropping the trigger and re-adding it the way you > have done is about the only way to avoid all that recursion. > > Sven I just realize that this won't work as it suffers the same recursion problem that the trigger does. Interesting puzzle for which I believe you have already found the optimal solution.