From: | Tim Uckun <timuckun(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Dealing with ordered hierarchies |
Date: | 2017-07-24 07:02:57 |
Message-ID: | CAGuHJrPaz8brHExtw_5_dYKnAA7S66uAnT4-vFPNDXrqmZbZxg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleas Mantzios | 2017-07-24 07:18:18 | Re: Dealing with ordered hierarchies |
Previous Message | David G. Johnston | 2017-07-24 03:12:43 | Re: What is the problem with this query? |