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.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:

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.

2011/6/2 Alexey Klyukin <alexk@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).

On Thu, Jun 2, 2011 at 11:21 AM, Artiom Makarov
<artiom.makarov@gmail.com> wrote:

2011/6/2 Alexey Klyukin <alexk@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).

It's a fairly unusual case to have two inheritance children one of
which has a foreign key referencing the other, and to then try to
truncate the parent table, so I'm somewhat disinclined to put in the
time to fix this.

However, patches are welcome...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

2011/6/2 Alexey Klyukin <alexk@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.

2011/6/2 Robert Haas <robertmhaas@gmail.com>:

It's a fairly unusual case to have two inheritance children one of
which has a foreign key referencing the other, and to then try to

BTW, when the both tables refer to tr_test1 directly the same thing
happens on truncating:

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_test1(id1) 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,1);

truncate tr_test1; -- No message about tr_test3 !
__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.

truncate tr_test1 cascade;

__NOTICE: truncate cascades to table "tr_test2"
__NOTICE: truncate cascades to table "tr_test3"
__Query returned successfully with no result in 94 ms.

No message about tr_test3 !