Thread: Best way to handle table trigger on update

Best way to handle table trigger on update

From
"Justin Pasher"
Date:
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


Re: Best way to handle table trigger on update

From
"Jim C. Nasby"
Date:
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

Re: Best way to handle table trigger on update

From
"Justin Pasher"
Date:
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
>


Re: Best way to handle table trigger on update

From
Scott Ribe
Date:
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



Re: Best way to handle table trigger on update

From
Sven Willenberger
Date:
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.


Re: Best way to handle table trigger on update

From
"Justin Pasher"
Date:
> -----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


Re: Best way to handle table trigger on update

From
Sven Willenberger
Date:
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


Re: Best way to handle table trigger on update

From
Sven Willenberger
Date:
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.