Re: BUG #6048: TRUNCATE vs TRUNCATE CASCADE: misleading message

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

> 2011/6/2 Alexey Klyukin <alexk(at)commandprompt(dot)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.