From: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: Re: Tree structure table normalization problem (do I need a trigger?)
Date: Tue, 19 Dec 2000 08:43:41 -0800
Message-ID: <web-1167250@davinci.ethosmedia.com> (raw)
In-Reply-To: <3A3EA494.3682000B@joerdens.de>
Frank,
> However, I have
> a problem now
> which seems non-trivial: I am at some point in the tree,
> say 3 nodes
> down from the root, but I don't know where I am exactly
> (across which
> nodes would I travel along the shortest path to the top?)
> and would like
> to find out. This is, again, not really difficult if I
> know how deep
> into the tree I am, in which case I can simply do (I know
> that I am 3
> nodes from the root and that my current node number is
> x):
This is exactly why my model includes a "Level" column. It
was more important to me to have the easy queriability of
the "redundant" level info than to have the fluid
flexibility of a tree without it. The choice sorta depends
on what you're storing in the tree.
> (This is probably very expensive if the tree gets really
> deep, but I
> don't expect that to happen in my database anytime soon.)
Not really. You're querying (hopefully) two indexed fields
within the same table, refrenced to itself. Once you've run
it a few times, even the elaborate UNION query I posted will
run very quickly - on my table (~300 items) it runs <2
seconds.
> This means
> you need a loop control structure which means you have to
> write a
> PL/pgSQL procedure (or some other procedure) that is run
> by a trigger to
> update the level column on insert or update, as in
> This seems to feasible but not really as straightforward
> as one might
> hope. Is there an easier way?
Hmmm. I don't know, Frank. That strikes me as a really
good, straightforward workaround to your problem. I'm not
sure what you could do that would be simpler. This is
practically a textbook example of why triggers are necessary
to retain relational integrity.
-Josh Berkus
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: Tree structure table normalization problem (do I need a trigger?)
In-Reply-To: <web-1167250@davinci.ethosmedia.com>
References: <3A3EA494.3682000B@joerdens.de> <web-1167250@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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