[SQL] 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/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Nested Sets
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
Re: [SQL] Nested Sets
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 <[EMAIL PROTECTED]>: > 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/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Nested Sets
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 | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Nested Sets
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Nested Sets
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
