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@spamfence.net> 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
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@postgresql.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