Re: Self-referential records

If you do not use null to represent a root node, when you go to unwind the
data from the table to generate a hierarchy tree, you could end up with an
infinite loop. The query will always be looking for the next parent in the
hierarchy. Meaning, you will want to find the parent of a node using the
given parent_id, the query will find the parent of 1 to be 1, then it will
look again for the parent of 1 it will find 1, etc. etc. ad nauseum. I enjoy
using recursion as much as the next guy, but this could cause some serious
issues with the PostgreSQL query engine eating up system resources.

Wayne E. Pfeffer

On Sun, Jan 24, 2010 at 9:13 AM, Andreas Kretschmer <
akretschmer(at)spamfence(dot)net> wrote:

> Xi Shen <davidshen84(at)googlemail(dot)com> wrote:
>
> > what if you insert other values like '1', '999'? will the insertion
> > successful? if so, what's the difference between a deferred reference
> > and no reference at all?
>
> Nice question ;-)
>
> Okay, recreate the table but without NOT NULL:
>
> test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT
> NULL, parent_id INTEGER , FOREIGN KEY (parent_id) REFERENCES refers(id)
> deferrable initially deferred);
> NOTICE: CREATE TABLE will create implicit sequence "refers_id_seq" for
> serial column "refers.id"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "refers_pkey" for table "refers"
> CREATE TABLE
> Zeit: 63,477 ms
> test=*# insert into refers (name, parent_id) values ('xxx',null);
> INSERT 0 1
> Zeit: 0,686 ms
>
> I think, NULL is more sensible than inserting a 'random' value like 0.
>
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Red Leader 1 -- Out
URL: http://apps.facebook.com/faceblogged/?uid=674333666