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

On Jun 2, 2011, at 2:23 PM, Artiom Makarov wrote:

>
> The following bug has been logged online:
>
> Bug reference: 6048
> Logged by: Artiom Makarov
> Email address: artiom(dot)makarov(at)gmail(dot)com
> PostgreSQL version: 9.04
> Operating system: 2.6.32-30-server #59-Ubuntu SMP Tue Mar 1 22:46:09 UTC
> 2011 x86_64 GNU/Linux
> Description: TRUNCATE vs TRUNCATE CASCADE: misleading message
> Details:
>
> 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);
>
> insert into tr_test1(id1) values (1);
> insert into tr_test2(id2,id) values (1,1);
> insert into tr_test2(id2,id) values (2,1);
> insert into tr_test3(id3,id) values (1,2);
>
> truncate tr_test1;
>
> ERROR: cannot truncate a table referenced in a foreign key constraint
> SQL state: 0A000
> Detail: Table "tr_test2" references "tr_test1".
> Hint: Truncate table "tr_test2" at the same time, or use TRUNCATE ...
> CASCADE.
>
> This is definetly misleading message, because of when applying truncate
> tr_test1 CASCADE; all 3 tables truncated:

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?

>
> NOTICE: truncate cascades to table "tr_test2"
> NOTICE: truncate cascades to table "tr_test3"
>
>
> While drop schema public; command list all affected objects:
>
> ERROR: cannot drop schema public because other objects depend on it
>
> SQL state: 2BP01
> Detail: table tr_test1 depends on schema public
> table tr_test2 depends on schema public
> table tr_test3 depends on schema public
> Hint: Use DROP ... CASCADE to drop the dependent objects too.

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