Hi All,

I would like to implement the 'DISABLE TRIGGER' functionality for
Postgres...

My proposal for a syntax are the following..... We could choose either or
all of the following....

a) ALTER TABLE <table_name> DISABLE|ENABLE ALL TRIGGERS
(This syntax is available in oracle to alter all triggers for any given
table. We could make the 'ALL' optional...)

b) ALTER TABLE <table_name> DISABLE|ENABLE TRIGGER <trigger_name>

c) ALTER TRIGGER <trigger_name> DISABLE|ENABLE
(This syntax is available in oracle to alter individual triggers)

Another important issue is how to handle deferred triggers..... Considering
a scenario where the status of a trigger is changed when it is already in a
deferred stack,

a) We can update the corresponding entry of that particular trigger, which
is in the deferred stack, in the system table and when it comes up for
execution we can check its status. Depending upon the status of the
'tgenabled' it will either be executed or ignored.
b) Oracle has a different approach, When it encounters a DDL statement like
'ALTER TRIGGER' it will execute the deferred DML statements before that and
will then run the DDL statement... So for the above scenario, the trigger
will be run with whatever status it came into the deferred stack.

And finally about the scope of the change of status of a trigger. Should this 
be local to the session or should be reflected globally? My humble opinion is 
it should be reflected globally(again, as in oracle ?)....

Rgds,
Arul
This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the ordinary user of the email address to which it was addressed and may contain copyright and/or legally privileged information. No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive this email in error, please return to sender. Thank you.

If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please email unsubscribe@fast.fujitsu.com.au



Fri, 6 Aug 2004 15:14:13 +1000
fastpgs <fastpgs@fast.fujitsu.com.au>
Proposal for Disable Triggers
On Fri, Aug 06, 2004 at 03:14:13PM +1000, fastpgs wrote:

> And finally about the scope of the change of status of a trigger.
> Should this be local to the session or should be reflected globally?
> My humble opinion is it should be reflected globally(again, as in
> oracle ?)....

If the change is global, what should happen on other sessions that have
a deferred event from that trigger concurrently with the one that
modifies it?  Should the answer be different depending on the isolation
mode of the transaction?

Also, should the change be permanent, or should it be undone when the
modifying backend exits (or the transaction ends)?

I don't think it makes a lot of sense to be changing triggers globally.
Usually you want to change it only to do a certain operation, without
worrying about concurrent transactions.  Following that rationale, the
command should not be ALTER, because that's used for permanent changes.
Also, make sure that when a backend crashes, the final state should be
the same as when the backend exits normally.

I'm not sure the Oracle behavior is the one we want to imitate here ...

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Jude: I wish humans laid eggs
Ringlord: Why would you want humans to lay eggs?
Jude: So I can eat them


Sat, 7 Aug 2004 18:19:37 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Proposal for Disable Triggers
On Sun, 8 Aug 2004 08:19, Alvaro Herrera wrote:

> If the change is global, what should happen on other sessions that have
> a deferred event from that trigger concurrently with the one that
> modifies it?  Should the answer be different depending on the isolation
> mode of the transaction?

This was my other question.... It depends on how we are going to treat the 
trigger already on the deferred stack... Are we going to execute the trigger 
as if its status wasn't changed or are we going to double check its status 
just before executing the trigger body ?? 

> Also, should the change be permanent, or should it be undone when the
> modifying backend exits (or the transaction ends)?
>
> I don't think it makes a lot of sense to be changing triggers globally.
> Usually you want to change it only to do a certain operation, without
> worrying about concurrent transactions.  

My preference is for the change being permanent... Sure in most cases the 
user would want the trigger disabled for certain operations.. But there would 
be scnearios where the user would want it not be executed for a longer period 
of time... In either cases the user is the best one to know when to enable 
the trigger back...  

Rgds,
Arul

This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the ordinary user of the email address to which it was addressed and may contain copyright and/or legally privileged information. No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive this email in error, please return to sender. Thank you.

If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please email unsubscribe@fast.fujitsu.com.au



Mon, 9 Aug 2004 11:56:17 +1000
fastpgs <fastpgs@fast.fujitsu.com.au>
Re: Proposal for Disable Triggers
On Mon, Aug 09, 2004 at 11:56:17AM +1000, fastpgs wrote:
> On Sun, 8 Aug 2004 08:19, Alvaro Herrera wrote:
> 
> > If the change is global, what should happen on other sessions that have
> > a deferred event from that trigger concurrently with the one that
> > modifies it?  Should the answer be different depending on the isolation
> > mode of the transaction?
> 
> This was my other question.... It depends on how we are going to treat the 
> trigger already on the deferred stack... Are we going to execute the trigger 
> as if its status wasn't changed or are we going to double check its status 
> just before executing the trigger body ?? 

No, I'm talking about a different issue.  Not what happens with a deferred
event in the queue of the backend executing the disabling operation, but
in another backend.


Think about referential integrity triggers.  Will you allow one backend,
that needs to do some strange processing bypassing the triggers, make all
other backends corrupt data by running with triggers disabled?  It doesn't
apply only to referential integrity triggers, of course.  The user would
need to lock the table to make sure no one uses it while she have the
trigger disabled.  That's no good.  I think permanent or global trigger
changes are recipes for disaster.

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
La web junta la gente porque no importa que clase de mutante sexual seas,
tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con
ciervos incendiï¿½nse", y el computador dirï¿½ "especifique el tipo de ciervo"
(Jason Alexander)

Sun, 8 Aug 2004 22:04:55 -0400
Alvaro Herrera Munoz <alvherre@dcc.uchile.cl>
Re: Proposal for Disable Triggers