Обсуждение: self ordering list

Hello to all.  I'm new to the list and have only been engineering on postgresql for about a year.  I have solved some neat problems in that time and will gladly give  back to the community.  I'll try to contribute as much as startup time permits.

My first question concerns self ordering lists.  Basically, a numeric column that automatically maintains an order but allows arbitrary reordering by the user.  For example, a media playlist or the priority of workitems within a parent container ;)     This seems like a common pattern.  

priority |  workitem
---------+-----------
1        | task 1
2        | task 2
3        | task 3
4        | task 4
5        | task 5

Insert a new task with priority==2 and...

   update tablename set priority=priority+1 where priority >= 2

delete task with priority==2 and...
  
   update tablename set priority=priority-1 where priority > 2

reorder task with priority==2, set its priority=4

   update tablename set priority=priority+1 where priority >= 4
   update tablename set priority=priority-1 where priority > 2 and priority < 4

etc....

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. 

Instead of trying to update all the rows affected you could instead just update the successive row and let a trigger chain reaction take care of updating all the rows.  I've implemented this it also has issues that I'm not sure are entirely mine. 

I'll break out and upload a sample case of the issues but before I do can any point me to any publication on implementing this basic pattern with postgres and plpgsql.  

Thanks,

-bryan

Bryan Wilkerson wrote:
>
> My first question concerns self ordering lists.  Basically, a numeric
> column that automatically maintains an order but allows arbitrary
> reordering by the user.  For example, a media playlist or the
> priority of workitems within a parent container ;)     This seems
> like a common pattern.
>
> priority |  workitem
> ---------+-----------
> 1        | task 1
> 2        | task 2
> 3        | task 3
> 4        | task 4
> 5        | task 5
>
> Insert a new task with priority==2 and...
>
>  update tablename set priority=priority+1 where priority >= 2
>
> delete task with priority==2 and...
>
>  update tablename set priority=priority-1 where priority > 2
>
> reorder task with priority==2, set its priority=4
>
>  update tablename set priority=priority+1 where priority >= 4
 >  update tablename set priority=priority-1 where priority > 2
 >   and priority < 4
>
> etc....
>
> 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.
>

Why not update everything into a temp table first, then update the
original with the new values from that? Or maybe a view is better suited
to this.

brian

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