Re: self ordering list

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