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