Обсуждение: 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