Обсуждение: Proposal for Disable Triggers
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 ordinaryuser 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 thisemail 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 emailunsubscribe@fast.fujitsu.com.au
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
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 ordinaryuser 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 thisemail 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 emailunsubscribe@fast.fujitsu.com.au
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)