Обсуждение: disabling constraints

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

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_catalogtables 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
 

>>>>> "DP" == David Parker <dparker@tazznetworks.com> writes:

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@kciLink.com       Rockville, MD  +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

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

>-----Original Message-----
>From: pgsql-general-owner@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Vivek Khera
>Sent: Thursday, November 04, 2004 3:05 PM
>To: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] disabling constraints
>
>>>>>> "DP" == David Parker <dparker@tazznetworks.com> writes:
>
>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@kciLink.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
>

Vivek Khera <khera@kcilink.com> writes:
> "DP" == David Parker <dparker@tazznetworks.com> writes:
> 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?

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

khera@kcilink.com (Vivek Khera) writes:

> >>>>> "DP" == David Parker <dparker@tazznetworks.com> writes:
>
> 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?
>
> --

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