Обсуждение: disabling triggers
Hi, Is setting reltriggers=0 on pg_class an accepatble way of disabling triggers on a table temporarily? secondly is it worth having commands like alter trigger <triggername> inactive; alter trigger <triggername> active ; i feel such commands exists on Oracle. sometime we need to bulk update tables from backdoor and we feel the trigger execution to be unneccessary. regds mallah. ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/
Mallah, > Is setting reltriggers=0 on pg_class an accepatble way of > disabling triggers on a table temporarily? Yes. pg_dump does this. > secondly is it worth having commands like > alter trigger <triggername> inactive; > alter trigger <triggername> active ; > i feel such commands exists on Oracle. No, since DROP TRIGGER ... CREATE TRIGGER does the same thing in Postgres. -- -Josh BerkusAglio Database SolutionsSan Francisco
> Mallah, > >> Is setting reltriggers=0 on pg_class an accepatble way of >> disabling triggers on a table temporarily? Ok , but someone on list was scary few months back. > > Yes. pg_dump does this. > >> secondly is it worth having commands like >> alter trigger <triggername> inactive; >> alter trigger <triggername> active ; >> i feel such commands exists on Oracle. > > No, since DROP TRIGGER ... CREATE TRIGGER does the same thing in Postgres. Agreed , but there is no easy way to view current trigger defination of existing triggers before dropping from psql . One has to go the long way of pg_dump -s -t tabname to view a list. regds mallah. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/
Mallah, > >> Is setting reltriggers=0 on pg_class an accepatble way of > >> disabling triggers on a table temporarily? > > Ok , but someone on list was scary few months back. I've done it many times without a problem. The trick is re-setting the triggers to the correct number when you're done. See the scripts that pg_dump -a creates for a good example. > Agreed , but there is no easy way to view current trigger defination of > existing triggers before dropping from psql . So? Create a perl script. It can: 1) query the system tables to find out the trigger definintion(you'll need pg_trigger, pg_proc, and pg_type) 2) generate a script to restore all the triggers to be used later; 3) drop all the triggers Of course, setting reltriggers=0 is probably a lot easier. -- -Josh BerkusAglio Database SolutionsSan Francisco
> Mallah, > >> >> Is setting reltriggers=0 on pg_class an accepatble way of >> >> disabling triggers on a table temporarily? >> >> Ok , but someone on list was scary few months back. > > I've done it many times without a problem. The trick is re-setting the triggers to the > correct number when you're done. See the scripts that pg_dump -a creates for a good example. > >> Agreed , but there is no easy way to view current trigger defination of existing triggers >> before dropping from psql . > > So? Create a perl script. It can: > 1) query the system tables to find out the trigger definintion > (you'll need pg_trigger, pg_proc, and pg_type) > 2) generate a script to restore all the triggers to be used later; 3) drop all the triggers > > Of course, setting reltriggers=0 is probably a lot easier. in past we tried creating a plpgsql function that takes relname and schema and try setting reltriggers=0 on disabling and = (select count(*) from pg_trigger for that relname and schema) on enabling . we got stuck somewhere and abandoned it , do u think above approach would work in principle ? i will retry it tommorw though and post the results. regds mallah. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---------------------------(end of broadcast)--------------------------- TIP 3: if > posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your message can get through > to the mailing list cleanly ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/
Mallah, > in past we tried creating a plpgsql function that takes relname and schema > and try setting reltriggers=0 on disabling and = (select count(*) from pg_trigger > for that relname and schema) on enabling . > > we got stuck somewhere and abandoned it , do u think above approach > would work in principle ? Yes. As I said, I've used it before. An additional safeguard you can use is enclosing everything in a transaction, that is: BEGIN disable triggers load data enable triggers END -- -Josh BerkusAglio Database SolutionsSan Francisco
On Tue, Jun 17, 2003 at 11:49:44AM -0700, Josh Berkus wrote: <big_snip> I also need something like that for my replication stuff: When data from another node comes it, it should be written directly to the tables, without any (unwanted) triggers firering. On the other hand, in "normal" mode, also some missing fields should be filled on create or update. Perhaps some more details: All tables have this layout: inode_id int4 attr char mtime timestamp ... On INSERT an missing (NULL) inode_id should be taken from sequence _inode_seq, attr can be set to an default value (currently not important) and an NULL mtime should be replaced by current_timestamp(). When UPDATE-ing, the mtime should also be set to current_timestamp() if not given. But in the "raw" mode (when data from neighbours come in), the mtime field may _not_ be touched (since it would produce an loop!). Currently this is done by the middleware, but I'd like to have it directly in the RDBMS. How could this be solved ? regards -- ---------------------------------------------------------------------Enrico Weigelt == metux IT services phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: contact@metux.decellphone:+49 174 7066481 ---------------------------------------------------------------------Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/
--- weigelt@metux.de wrote: > On Tue, Jun 17, 2003 at 11:49:44AM -0700, Josh Berkus wrote: > > <big_snip> > > I also need something like that for my replication stuff: > > When data from another node comes it, it should be written > directly to the tables, without any (unwanted) triggers firering. > > On the other hand, in "normal" mode, also some missing fields > should be filled on create or update. > I have done this three ways: 1. have all client DML go through views and at the view level do all checking etc. + very robust, very secure - lots of work 2. have replication updates use the "replicate" user - in triggers, do nothing if user is replicate, otherwise work as normal + very easy to do - triggers fire always, even when doing nothing 3. have replicate triggers write data file for import directly into the postgresql engine + transactions smaller, less rollback + can replicate to different databases, over net, etc - lots of work (although easy to write trigger generator) - difficult failure senarios __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com