Search Postgresql Archives

Dealing with ordered hierarchies

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I have read many articles about dealing with hierarchies in postgres including nested sets, ltree, materialized paths, using arrays as parentage,  CTEs etc but nobody talks about the following scenario.

Say I have a hierarchy like this

1
1.1
1.1.1
1.1.2
1.2
1.3
2
2.1

In this hierarchy the order is very important and I want to run frequent(ish) re-ordering of both subsets and entire trees and even more frequent inserts.

Scenario 1: I want to insert a child into the 1.1 subtree.  The next item should be 1.1.3 and I can't figure out any other way to do this other than to subquery the children and to figure out the max child ID, add one to it which is a race condition waiting to happen.

Scenario 2: I now decide the recently inserted item is the second most important so I reset the ID to 1.1.2 and then increment 1.1.2 (and possibly everything below).  Again this is both prone to race conditions and involves a heavy update.

Is there a better way to deal with this or is the complexity unavoidable?

I should state that like most database reads will be much more frequent than writes and inserts will be more frequent than updates (re-ordering)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux