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

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

>=20
> The following bug has been logged online:
>=20
> Bug reference:      6048
> Logged by:          Artiom Makarov
> Email address:      artiom.makarov@gmail.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:=20
>=20
> 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);
>=20
> 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);
>=20
> truncate tr_test1;
>=20
> 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.
>=20
> 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?=20

One thing I find potentially surprising is that TRUNCATE CASCADE doesn't fo=
llow 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?


>=20
> NOTICE:  truncate cascades to table "tr_test2"
> NOTICE:  truncate cascades to table "tr_test3"
>=20
>=20
> While drop schema public; command list all affected objects:
>=20
> ERROR: cannot drop schema public because other objects depend on it
>=20
> 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.