On Dec 21, 2007, at 15:19 , Bryan Wilkerson wrote:
> I've implemented in my model code but it has some deadlock issues
> and I really strongly have believed all along that this belongs in
> the db anyway. Implementing the above with triggers is a tricky
> problem because the trigger would key off the priority change and
> the successive updates would recusively trigger.
>
I recommend wrapping the manipulations in functions and allow access
to the table only through these functions. Then you can be sure that
the operations are handled within a transaction and it's all in the
database.
If you have a unique constraint on priority, you'll probably want to
do something along the lines of (untested)
CREATE FUNCTION new_workitem(in_priority integer, in_workitem text)
RETURNS void
LANGUAGE SQL AS $func$
UPDATE tablename
SET priority = -1 * (priority + 1)
WHERE priority >= 2;
INSERT INTO tablename (priority, workitem)
VALUES (2, 'new task');
UPDATE tablename
SET priority = ABS(priority)
WHERE priority < 0;
$func$;
This is in the same vein as methods to maintain nested set-encoded
hierarchies, which are also order-dependent.
Michael Glaesemann
grzm seespotcode net