Re: A few questions about ltree

From: Teodor Sigaev <teodor(at)sigaev(dot)ru>
To: Alban Hertroys <alban(at)magproductions(dot)nl>
Cc: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Re: A few questions about ltree
Date: 2006-04-21 15:10:57
Message-ID: 4448F601.4080300@sigaev.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


> We've been experimenting with a table containing a branch 'a', 'a.b' and
> 'a.b.c', but deleting 'a.b' didn't cause a constraint violation.
>
> SQL> CREATE TABLE ltree_test (path ltree PRIMARY KEY REFERENCES
> ltree_test(path));
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "ltree_test_pkey" for table "ltree_test"
> CREATE TABLE
> SQL> INSERT INTO ltree_test VALUES ('a'::ltree);
> INSERT 84117368 1
> SQL> INSERT INTO ltree_test VALUES ('a.b'::ltree);
> INSERT 84117369 1
> SQL> INSERT INTO ltree_test VALUES ('a.b.c'::ltree);
> INSERT 84117370 1
> SQL> DELETE FROM ltree_test WHERE path = 'a.b'::ltree;
> DELETE 1
> SQL> select * from ltree_test;
> path
> -------
> a
> a.b.c
> (2 rows)
>
> Is there some obvious/easy way to prevent this?

Sorry, only by using triggers on insert/delete/update.

If it was a possible to use function in foreign key then it might looks as
create table foo (
path ltree not null
);

insert into foo values (''); -- root of tree, but it unremovable...

create unique index path_foo_idx on foo ( path ); -- BTree index for constraint

alter table foo add foreign key subpath( path, 0, -1) references foo( path )
deferrable initially deferred,;

But it's impossible...

--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Terry Lee Tucker 2006-04-21 15:13:59 Re: Triggers and Transactions
Previous Message Tom Lane 2006-04-21 15:09:20 Re: Odd transaction timestamp sequence issue