Thread: Turning off triggers ?
Is there a way that all the triggers associated with a table can be turned off ? I would like to be able to bulk load a table ( I have to use inserts not copy ) and I don't want the triggers to fire. Once loaded I want to turn them back on again. This process will be repeated quite often ( during the development phase). I could delete then add them again but I am hoping these is an easier way. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 5301 Ph/Fax: +64 6 357 8168, Mob: +64 21 424 015
On 25 Nov 2002, Glen Eustace wrote: > Is there a way that all the triggers associated with a table can be > turned off ? I would like to be able to bulk load a table ( I have to > use inserts not copy ) and I don't want the triggers to fire. Once > loaded I want to turn them back on again. This process will be repeated > quite often ( during the development phase). If you don't expect to be adding triggers during the process and you're a superuser, you can set reltriggers to 0 in the appropriate pg_class row and then set it back at the end. Pg_dump's data only dump does something similar.
Never tried this myself but there is an entry in the cookbook pages that, on the face of it seems to do what you are asking... http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_ id=5925 > On 25 Nov 2002, Glen Eustace wrote: > >> Is there a way that all the triggers associated with a table can be >> turned off ? I would like to be able to bulk load a table ( I have to >> use inserts not copy ) and I don't want the triggers to fire. Once >> loaded I want to turn them back on again. This process will be repeated >> quite often ( during the development phase). -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
If you look at a pg_dump file, you will find: UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = '<table name>'; UPDATE pg_class SET reltriggers = ( SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid) WHERE relname = '<table name>'; The first statemant turn off trigers, the second turns them back on. JLL Adam Witney wrote: > > Never tried this myself but there is an entry in the cookbook pages that, on > the face of it seems to do what you are asking... > > http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_ > id=5925 > > > On 25 Nov 2002, Glen Eustace wrote: > > > >> Is there a way that all the triggers associated with a table can be > >> turned off ? I would like to be able to bulk load a table ( I have to > >> use inserts not copy ) and I don't want the triggers to fire. Once > >> loaded I want to turn them back on again. This process will be repeated > >> quite often ( during the development phase). > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
There seems to be a TON of functionality accessable by querying and updating the system catalogs directly... Has anyone written a practical guide to the proper (ab)use and function of the underlying system tables similar to the postgresql cookbook? They ARE documented, but there aren't that many cookbook entries for nifty tweaks like disabling triggers by telling postgresql there are none... (well, there IS a cookbook recipie for that... :) I'm not sure how such a document would be organized, or what would constitute a "nifty" feature. The SQL for some of the cooler "\" commands in psql can be gleaned by using the -E command. Some other "nifties" can also be found by digging through the code. Is this a skill reserved only for the eXtreme postgresql hackers, or can (should!) mere mortals be given the understanding to push postgresql to its limits? CG --- Jean-Luc Lachance <jllachan@nsd.ca> wrote: > If you look at a pg_dump file, you will find: > > UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = '<table > name>'; > > UPDATE pg_class SET reltriggers = ( > SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid) > WHERE relname = '<table name>'; > > The first statemant turn off trigers, the second turns them back on. > > JLL > > > Adam Witney wrote: > > > > Never tried this myself but there is an entry in the cookbook pages that, > on > > the face of it seems to do what you are asking... > > > > > http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_ > > id=5925 > > > > > On 25 Nov 2002, Glen Eustace wrote: > > > > > >> Is there a way that all the triggers associated with a table can be > > >> turned off ? I would like to be able to bulk load a table ( I have to > > >> use inserts not copy ) and I don't want the triggers to fire. Once > > >> loaded I want to turn them back on again. This process will be repeated > > >> quite often ( during the development phase). > > > > -- > > This message has been scanned for viruses and > > dangerous content by MailScanner, and is > > believed to be clean. > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
We try to make any _meaningful_ system catalog changes into actual SQL commands. There is also little guarantee that these queries will be valid from release to release, so we don't encourage their use. --------------------------------------------------------------------------- Chris Gamache wrote: > > There seems to be a TON of functionality accessable by querying and updating > the system catalogs directly... Has anyone written a practical guide to the > proper (ab)use and function of the underlying system tables similar to the > postgresql cookbook? They ARE documented, but there aren't that many cookbook > entries for nifty tweaks like disabling triggers by telling postgresql there > are none... (well, there IS a cookbook recipie for that... :) I'm not sure how > such a document would be organized, or what would constitute a "nifty" feature. > The SQL for some of the cooler "\" commands in psql can be gleaned by using the > -E command. Some other "nifties" can also be found by digging through the code. > Is this a skill reserved only for the eXtreme postgresql hackers, or can > (should!) mere mortals be given the understanding to push postgresql to its > limits? > > CG > > --- Jean-Luc Lachance <jllachan@nsd.ca> wrote: > > If you look at a pg_dump file, you will find: > > > > UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = '<table > > name>'; > > > > UPDATE pg_class SET reltriggers = ( > > SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid) > > WHERE relname = '<table name>'; > > > > The first statemant turn off trigers, the second turns them back on. > > > > JLL > > > > > > Adam Witney wrote: > > > > > > Never tried this myself but there is an entry in the cookbook pages that, > > on > > > the face of it seems to do what you are asking... > > > > > > > > http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_ > > > id=5925 > > > > > > > On 25 Nov 2002, Glen Eustace wrote: > > > > > > > >> Is there a way that all the triggers associated with a table can be > > > >> turned off ? I would like to be able to bulk load a table ( I have to > > > >> use inserts not copy ) and I don't want the triggers to fire. Once > > > >> loaded I want to turn them back on again. This process will be repeated > > > >> quite often ( during the development phase). > > > > > > -- > > > This message has been scanned for viruses and > > > dangerous content by MailScanner, and is > > > believed to be clean. > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 4: Don't 'kill -9' the postmaster > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > __________________________________________________ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Chris Gamache <cgg007@yahoo.com> writes:
> There seems to be a TON of functionality accessable by querying and updating
> the system catalogs directly...
Querying, sure ... updating, well, it's pretty easy to shoot yourself in
the foot. I have been seen to recommend solutions that involve manual
catalog updates, but I always ask what version you're running first ;-)
The catalogs get revised for every version, to a greater or lesser
extent.
> Is this a skill reserved only for the eXtreme postgresql hackers, or can
> (should!) mere mortals be given the understanding to push postgresql to its
> limits?
If you're willing to commit to keeping the document updated across multiple
PG versions, go for it.
regards, tom lane