pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNested Sets
6+ messages / 4 participants
[nested] [flat]
* Nested Sets
@ 2005-04-16 12:31 A. Kulikov <a.kulikov@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: A. Kulikov @ 2005-04-16 12:31 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Nested Sets
@ 2005-04-16 13:04 Troels Arvin <troels@arvin.dk>
parent: A. Kulikov <a.kulikov@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Troels Arvin @ 2005-04-16 13:04 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Nested Sets
@ 2005-04-16 14:28 A. Kulikov <a.kulikov@gmail.com>
parent: Troels Arvin <troels@arvin.dk>
0 siblings, 1 reply; 6+ messages in thread
From: A. Kulikov @ 2005-04-16 14:28 UTC (permalink / raw)
To: pgsql-sql
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@arvin.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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Nested Sets
@ 2005-04-16 18:14 Andrew Sullivan <ajs@crankycanuck.ca>
parent: A. Kulikov <a.kulikov@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andrew Sullivan @ 2005-04-16 18:14 UTC (permalink / raw)
To: pgsql-sql
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@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Nested Sets
@ 2005-04-16 20:02 Troels Arvin <troels@arvin.dk>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Troels Arvin @ 2005-04-16 20:02 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Nested Sets
@ 2005-04-16 21:34 Christopher Browne <cbbrowne@acm.org>
parent: Troels Arvin <troels@arvin.dk>
0 siblings, 0 replies; 6+ messages in thread
From: Christopher Browne @ 2005-04-16 21:34 UTC (permalink / raw)
To: pgsql-sql
After takin a swig o' Arrakan spice grog, troels@arvin.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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-04-16 21:34 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-16 12:31 Nested Sets A. Kulikov <a.kulikov@gmail.com>
2005-04-16 13:04 ` Troels Arvin <troels@arvin.dk>
2005-04-16 14:28 ` A. Kulikov <a.kulikov@gmail.com>
2005-04-16 18:14 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-04-16 20:02 ` Troels Arvin <troels@arvin.dk>
2005-04-16 21:34 ` Christopher Browne <cbbrowne@acm.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox