Nested Sets

Hi all,

I have just moved to PostgreSQL from MySQL and am willing to employ
all the capabilities PostgreSQL has to offer. In MySQL I have had
implemented Joe Celko's Nested Sets quite a while ago, extending them
with some additional fields to cache data such as the level of the
node and a reference to the parent node for better data manipulation.
I don't feel like reinventing the wheel again in PostgreSQL by simply
porting all the SQL from MySQL and not giving priority to stuff like
stored procedures and referential integrity in postgres.

A search over google and these mailing lists have yielded little
results, perharps I am missing something. Has anyone implemented a
pretty Nested Sets solution in PostgreSQL? Or is this considered black
voodoo and noone wants to show their solutions to the world?

with regards,

A. Kulikov
--
The mind is essential -- http://essentialmind.com/

On Sat, 16 Apr 2005 16:31:43 +0400, A. Kulikov wrote:

> Has anyone implemented a pretty Nested Sets solution in
> PostgreSQL?

Maybe this is useful? :
http://threebit.net/tutorials/nestedset/tutorial1.html

--
Greetings from Troels Arvin, Copenhagen, Denmark

Seen this before. The solution is rather lame, as most of the logic is
shifted out to the code, whereas it could have been implemented in the
database directly. I have managed to get a move_tree algorythm working
rather fast and glitch free by now, yet I am wondering if there are
other directions to follow. What I can see as actual are:

* deleting trees
* swapping nodes places on one level (including the subtrees)

btw, do stored procedures (user functions in other words) have to
implement table locking or be carried out inside a transaction such
that the nested set indexes remain intact?

regards,

alex

2005/4/16, Troels Arvin <troels(at)arvin(dot)dk>:
> On Sat, 16 Apr 2005 16:31:43 +0400, A. Kulikov wrote:
>
> > Has anyone implemented a pretty Nested Sets solution in
> > PostgreSQL?
>
> Maybe this is useful? :
> http://threebit.net/tutorials/nestedset/tutorial1.html
>
> --
> Greetings from Troels Arvin, Copenhagen, Denmark
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
The mind is essential -- http://essentialmind.com/

On Sat, Apr 16, 2005 at 06:28:27PM +0400, A. Kulikov wrote:
> btw, do stored procedures (user functions in other words) have to
> implement table locking or be carried out inside a transaction such
> that the nested set indexes remain intact?

They're always in a transaction. Everything in Postgres is always
inside a transaction (although it might be a transaction of a single
statement). You shouldn't need to do anything special around table
locking.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

On Sat, 16 Apr 2005 14:14:57 -0400, Andrew Sullivan wrote:
> You shouldn't need to do anything special around table
> locking.

- Except of one wants "True Serializability" (see chapter 12.2 in the
manual). But I don't know if it's possible to handle table locking from
within a user defined function.

--
Greetings from Troels Arvin, Copenhagen, Denmark

After takin a swig o' Arrakan spice grog, troels(at)arvin(dot)dk (Troels Arvin) belched out:
> On Sat, 16 Apr 2005 14:14:57 -0400, Andrew Sullivan wrote:
>> You shouldn't need to do anything special around table
>> locking.
>
> - Except of one wants "True Serializability" (see chapter 12.2 in the
> manual). But I don't know if it's possible to handle table locking from
> within a user defined function.

"Inside stored functions" is one of the ultimate examples of places
where you are certain to be honest-to-goodness inside a transaction.

You can't change transactions while inside a function; all the
in-the-function processing is sure to take place in one transaction's
context.

In theory, savepoints may ultimately change that a little bit, in that
you might have portions of processing in different subtransactions.

But nonetheless Andrew's point remains valid: There is no need to do
any special locking surrounding processing that goes on inside a
stored procedure because it is all suitably embedded in a transaction.
--
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://linuxdatabases.info/info/slony.html
"The problem with the current Lisp Machine system is that nothing ever
calls anything anymore." -- KMP