Thread: BUG #6048: TRUNCATE vs TRUNCATE CASCADE: misleading message
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: 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.
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.
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 wou= ld truncate the dependent table even with ON DELETE RESTRICT foreign key. D= o we need a similar 'ON TRUNCATE' =A0FK 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 Jun 2, 2011, at 6:21 PM, Artiom Makarov wrote:
> 2011/6/2 Alexey Klyukin <alexk@commandprompt.com>:
>=20
>> What would you expect to happen for TRUNCATE .. CASCADE?
>>=20
>> 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 wo=
uld truncate the dependent table even with ON DELETE RESTRICT foreign key. =
Do we need a similar 'ON TRUNCATE' FK clause?
>>=20
>=20
> 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=20
--------+---------+-----------
id | integer | not null
name | text |=20
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "test2" CONSTRAINT "test2_id_fkey" FOREIGN KEY (id) REFERENCES te=
st(id) ON DELETE RESTRICT
TABLE "test3" CONSTRAINT "test3_id_fkey" FOREIGN KEY (id) REFERENCES te=
st(id) ON DELETE RESTRICT
postgres=3D# delete from test;
ERROR: update or delete on table "test" violates foreign key constraint "t=
est2_id_fkey" on table "test2"
DETAIL: Key (id)=3D(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.
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 wo= uld truncate the dependent table even with ON DELETE RESTRICT foreign key. = Do we need a similar 'ON TRUNCATE' =A0FK 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... --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
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 !