pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (9+ messages)  latest in thread

Message-ID: <web-1167250@davinci.ethosmedia.com>
Permalink:  ../web-1167250@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-1167250@davinci.ethosmedia.com

 · 

reply

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