Обсуждение: How to temporarily disable a table's FK constraints?
Hi, everyone. Is there a standard way to disable a table foreign-key constraint temporarily? I thought that this would be a fairly common thing to want to do, but I only found this snippet online: -- to disable UPDATE pg_class SET reltriggers=0 WHERE relname = 'your_table'; -- to re-enable UPDATE pg_class SET reltriggers = count( * ) FROM pg_trigger WHERE pg_class.oid=tgrelid AND relname = 'your_table'; and it appears that one needs to be root to execute these statements. Is there any other way for non-root users? TIA! kj
On Nov 5, 2007, at 10:50 AM, Kynn Jones wrote: > Hi, everyone. > > Is there a standard way to disable a table foreign-key constraint > temporarily? > > I thought that this would be a fairly common thing to want to do, but > I only found this snippet online: > > -- to disable > UPDATE pg_class SET reltriggers=0 WHERE relname = 'your_table'; > > -- to re-enable > UPDATE pg_class SET reltriggers = count( * ) > FROM pg_trigger WHERE pg_class.oid=tgrelid AND relname = > 'your_table'; > > and it appears that one needs to be root to execute these statements. > > Is there any other way for non-root users? Can you explain what it is you're actually trying to do? As in, what's your use case for needing to do this? While there isn't any specific 'disable foreign key' functionality, there are different solutions to what use cases where people think they need this. The one you listed is actually a total hack and should really be avoided unless your really know what you're doing as what you're doing is forcing an inconsistency in the catalogs and if you forget to restore them with that second query, well, good luck to the next guy trying to figure out what you did. You'd be better off just dropping the foreign key than going that route. I think a good addition to the pieces of advice that get tacked on to the end of the list messages would be something along the lines of: "Don't edit the catalogs unless you absolutely, positively know what you're doing and even then, think again." Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 11/05/07 10:50, Kynn Jones wrote: > Hi, everyone. > > Is there a standard way to disable a table foreign-key constraint temporarily? > > I thought that this would be a fairly common thing to want to do, but > I only found this snippet online: > > -- to disable > UPDATE pg_class SET reltriggers=0 WHERE relname = 'your_table'; > > -- to re-enable > UPDATE pg_class SET reltriggers = count( * ) > FROM pg_trigger WHERE pg_class.oid=tgrelid AND relname = 'your_table'; > > and it appears that one needs to be root to execute these statements. > > Is there any other way for non-root users? The whole idea of enforcing Relational Integrity in the database engine is to *not* allow "regular users" to bypass data integrity checks. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD4DBQFHL1PLS9HxQb37XmcRAm7zAKDbdYSymz3zIyKmfdU5wPjtpVTAlwCYoEA/ DI1Z2Fbgo62k6C2P8gsCQQ== =Np96 -----END PGP SIGNATURE-----
On 11/5/07, Erik Jones <erik@myemma.com> wrote: > On Nov 5, 2007, at 10:50 AM, Kynn Jones wrote: > > Is there a standard way to disable a table foreign-key constraint > > temporarily? > > > > I thought that this would be a fairly common thing to want to do... > Can you explain what it is you're actually trying to do? As in, > what's your use case for needing to do this? A Perl script that needs to update a referring table with many new entries before knowing the foreign keys for each new record. (I described a similar situation in a recent post, Subject: Populating large DB from Perl script.) Also, Ron, the *owner* of a table is not a "regular user" as far as that table is concern. That user has special privileges, including that of dropping constraints. What I seek to do is no greater a violation of the idea of enforcing relational integrity than is the ability to drop constraints altogether. BTW, I realize that I can just drop and reinstate constraints, but from the point of view of writing a Perl script to do all this, it would be much easier if I could just disable temporarily all the FK constraints on a table. kj
On 11/5/07, Kynn Jones <kynnjo@gmail.com> wrote: > On 11/5/07, Erik Jones <erik@myemma.com> wrote: > > > On Nov 5, 2007, at 10:50 AM, Kynn Jones wrote: > > > Is there a standard way to disable a table foreign-key constraint > > > temporarily? > > > > > > I thought that this would be a fairly common thing to want to do... > > > Can you explain what it is you're actually trying to do? As in, > > what's your use case for needing to do this? > > A Perl script that needs to update a referring table with many new > entries before knowing the foreign keys for each new record. (I > described a similar situation in a recent post, Subject: Populating > large DB from Perl script.) > > Also, Ron, the *owner* of a table is not a "regular user" as far as > that table is concern. That user has special privileges, including > that of dropping constraints. What I seek to do is no greater a > violation of the idea of enforcing relational integrity than is the > ability to drop constraints altogether. > > BTW, I realize that I can just drop and reinstate constraints, but > from the point of view of writing a Perl script to do all this, it > would be much easier if I could just disable temporarily all the FK > constraints on a table. But those aren't the same things. If userA has permission to add / drop FKs, and drops them, inserts data, and then reapplies the foreign key, userA will get an error if they've managed to dork out the data (i.e. data with no foreign key). OTOH, if userA just switches off FK enforcement, adds data and turns them back on, the data can now be incoherent. Things get even more interesting if other users are involved. If one and only one user ever uses the table, and that use is absolutely sure the data is coherent, then they are the same thing. Otherwise, they certainly are not.
On Nov 5, 2007, at 11:52 AM, Kynn Jones wrote: > On 11/5/07, Erik Jones <erik@myemma.com> wrote: > >> On Nov 5, 2007, at 10:50 AM, Kynn Jones wrote: >>> Is there a standard way to disable a table foreign-key constraint >>> temporarily? >>> >>> I thought that this would be a fairly common thing to want to do... > >> Can you explain what it is you're actually trying to do? As in, >> what's your use case for needing to do this? > > A Perl script that needs to update a referring table with many new > entries before knowing the foreign keys for each new record. (I > described a similar situation in a recent post, Subject: Populating > large DB from Perl script.) So, let me rephrase to see if I get you correctly: you want to insert a lot of data and then go back and update the inserted rows with the foreign key values? If that's the case and you can do all of that in one transaction then you should talk to your dba or whomever the table owner is to see about redefining the foreign key as being deferrable. That will make it so that the foreign key constraints aren't checked until transaction commit time rather than at statement execution time. Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
On Mon, Nov 05, 2007 at 01:52:54PM -0400, Kynn Jones wrote: > BTW, I realize that I can just drop and reinstate constraints, but > from the point of view of writing a Perl script to do all this, it > would be much easier if I could just disable temporarily all the FK > constraints on a table. Do you really want to disable the foreign key constraint, or just defer their checking till you commit the transaction? If you just want to defer checking, then [1] may help. Sam [1] http://www.postgresql.org/docs/current/static/sql-set-constraints.html
On 11/5/07, Erik Jones <erik@myemma.com> wrote: > ...see about redefining the foreign key > as being deferrable... Yep, that'll do it. Thanks! kj
Kynn Jones wrote: > Hi, everyone. > > Is there a standard way to disable a table foreign-key constraint temporarily? > > I thought that this would be a fairly common thing to want to do, but > I only found this snippet online: > > -- to disable > UPDATE pg_class SET reltriggers=0 WHERE relname = 'your_table'; > > -- to re-enable > UPDATE pg_class SET reltriggers = count( * ) > FROM pg_trigger WHERE pg_class.oid=tgrelid AND relname = 'your_table'; > > and it appears that one needs to be root to execute these statements. > > Is there any other way for non-root users? > > TIA! > > kj Hey, I was just thinking about this... instead of disabling the FK's, what about adding a temp table where you could COPY into, then fire off a bunch of update's to setup the id fields, etc, etc, then do an Insert into realtable select * from temptable? -Andy
On 11/5/07, andy <andy@squeakycode.net> wrote: > Hey, I was just thinking about this... instead of disabling the FK's, > what about adding a temp table where you could COPY into, then fire off > a bunch of update's to setup the id fields, etc, etc, then do an Insert > into realtable select * from temptable? I like this idea. It's very straightforward. Thanks! kj