From: | Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Dealing with ordered hierarchies |
Date: | 2017-07-24 07:18:18 |
Message-ID: | 3843ccee-1341-edca-f430-49a0518f62e9@matrix.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 24/07/2017 10:02, Tim Uckun wrote:
> 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?
Maybe you could try a hybrid approach with genealogical paths, represented by arrays, and a (possible bidirectional) linked list storing the siblings of the same parent.
Basically what you'd normally want is to convert your problem into something that can be represented in such a way that it can run fast on postgresql.
>
> 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)
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitry Lazurkin | 2017-07-24 10:17:56 | Re: Perfomance of IN-clause with many elements and possible solutions |
Previous Message | Tim Uckun | 2017-07-24 07:02:57 | Dealing with ordered hierarchies |