I would like to be able to truncate all of the tables in a schema without worrying about FK constraints. I tried issuing a "SET CONSTRAINTS ALL DEFERRED" before truncating, but I still get constraint errors. Is there a way todo something like:

1) disable all constraints
2) truncate all tables
3) re-enable all constraints

?

In the slony project there is a procedure "altertableforreplication" that appears to do 1), but since it is updating pg_catalog tables directly, I don't want to cut/paste before I understand what it's doing!

Is there any "standard" way of doing this?

- DAP
----------------------------------------------------------------------------------
David Parker Tazz Networks (401) 709-5130
*

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

DP> I would like to be able to truncate all of the tables in a schema
DP> without worrying about FK constraints. I tried issuing a "SET
DP> CONSTRAINTS ALL DEFERRED" before truncating, but I still get
DP> constraint errors. Is there a way to do something like:

Why don't you truncate your tables in an order that won't violate
FK's? Or do you have circular references?

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera (AT) kciLink (DOT) com Rockville, MD +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

khera (AT) kcilink (DOT) com (Vivek Khera) writes:

Because that doesn't work:

test=# create table able(id serial primary key, data text);
NOTICE: CREATE TABLE will create implicit sequence "able_id_seq" for "serial" column "able.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "able_pkey" for table "able"
CREATE TABLE
test=# create table baker(id int references able(id) deferrable, data text);
CREATE TABLE
test=# truncate able;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "baker" references "able" via foreign key constraint "$1".
test=# begin;
BEGIN
test=# set constraints all deferred;
SET CONSTRAINTS
test=# truncate able;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "baker" references "able" via foreign key constraint "$1".
test=# rollback;
ROLLBACK
test=#

--
Remove -42 for email

Vivek Khera <khera (AT) kcilink (DOT) com> writes:
The TRUNCATE command just says "no" if it sees any FKs pointing at the
target table; order doesn't matter.

It might be an interesting future extension to allow truncating multiple
tables in a single command, whereupon we could ignore FKs linking two
such tables.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Hi. Thanks for responding.

I don't *think* there are circular references, but the nature of the
problem is that the code doesn't know anything about the semantics of
the schema - it just gets the list of tables, and there are multiple
schemas it needs to handle, all of which may change over time. That's
why I was hoping for some kind of global "disable constraints" command.

I guess it should be possible to generate the proper table order based
on loading up all of the constraints from the catalog. It seems like
more trouble than I want to go to for this problem, but maybe it
wouldn't be too difficult...

- DAP

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org