Re: [GENERAL] disabling constraints
[EMAIL PROTECTED] (Vivek Khera) writes: DP == David Parker [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] disabling constraints
DP == David Parker [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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
Re: [GENERAL] disabling constraints
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Vivek Khera Sent: Thursday, November 04, 2004 3:05 PM To: [EMAIL PROTECTED] Subject: Re: [GENERAL] disabling constraints DP == David Parker [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] disabling constraints
Vivek Khera [EMAIL PROTECTED] writes: DP == David Parker [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] disabling constraints
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 to do 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 ParkerTazz Networks(401) 709-5130 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
