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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
