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 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

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.