On Jun 2, 2011, at 6:21 PM, Artiom Makarov wrote:

> 2011/6/2 Alexey Klyukin <al...@commandprompt.com>:
> 
>> What would you expect to happen for TRUNCATE .. CASCADE?
>> 
>> One thing I find potentially surprising is that TRUNCATE CASCADE doesn't 
>> follow the semantics of 'ON DELETE' clause for the foreign key, i.e. it 
>> would truncate the dependent table even with ON DELETE RESTRICT foreign key. 
>> Do we need a similar 'ON TRUNCATE'  FK clause?
>> 
> 
> Yes, cascade truncating taked place without ON DELETE RESTRICT
> checking. No matter.
> Either TRUNCATE must show message with full objects tree - correct
> behavior like DROP, or TRUNCATE CASCADE should not delete anything
> (strict constraint checking).

Well, in your example it actually shows all the direct dependencies:

> create table tr_test1(id1 int, primary key(id1));
> create table tr_test2(id2 int, id int, primary key(id2), foreign key (id)
> references tr_test1(id1) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE
> RESTRICT);
> create table tr_test3(id3 int, id int, primary key(id3), foreign key (id)
> references tr_test2(id2) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE
> RESTRICT);

tr_test3 here depends on tr_test2, and not directly on tr_test1.

Still, even if tr_test3.id would reference tr_test.id in your example, only the
first dependency is shown (for truncate, delete and probably update):

    Table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 name   | text    | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "test2" CONSTRAINT "test2_id_fkey" FOREIGN KEY (id) REFERENCES 
test(id) ON DELETE RESTRICT
    TABLE "test3" CONSTRAINT "test3_id_fkey" FOREIGN KEY (id) REFERENCES 
test(id) ON DELETE RESTRICT

postgres=# delete from test;
ERROR:  update or delete on table "test" violates foreign key constraint 
"test2_id_fkey" on table "test2"
DETAIL:  Key (id)=(1) is still referenced from table "test2".


I wonder whether this behavior is intentional, to avoid bloat in the logs. To
view all the dependencies you can just do \d tablename after receiving the
error.


--
Alexey Klyukin
The PostgreSQL Company - Command Prompt, Inc.





-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Reply via email to