Thread: disabling triggers

disabling triggers

From
Date:
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/




Re: disabling triggers

From
Josh Berkus
Date:
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



Re: disabling triggers

From
Date:
> 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/




Re: disabling triggers

From
Josh Berkus
Date:
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



Re: disabling triggers

From
Date:
> 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/




Re: disabling triggers

From
Josh Berkus
Date:
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



Re: disabling triggers

From
weigelt@metux.de
Date:
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/


Re: disabling triggers

From
chester c young
Date:
--- 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