path: root/doc/TODO.detail/trigger

From pgsql-patches-owner+M4639@postgresql.org Wed Aug  7 12:11:04 2002
Return-path: <pgsql-patches-owner+M4639@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g77GB3Y20812
	for <pgman@candle.pha.pa.us>; Wed, 7 Aug 2002 12:11:03 -0400 (EDT)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 8C371475A2C; Wed,  7 Aug 2002 12:10:56 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id E46514759AD; Wed,  7 Aug 2002 12:10:54 -0400 (EDT)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id B54944759AD
	for <pgsql-patches@postgresql.org>; Wed,  7 Aug 2002 12:10:46 -0400 (EDT)
Received: from klamath.dyndns.org (CPE002078144ae0.cpe.net.cable.rogers.com [24.102.202.35])
	by postgresql.org (Postfix) with ESMTP id 37F70475813
	for <pgsql-patches@postgresql.org>; Wed,  7 Aug 2002 12:10:46 -0400 (EDT)
Received: from boston.klamath.dyndns.org (unknown [192.168.40.12])
	by klamath.dyndns.org (Postfix) with ESMTP
	id C76C17010; Wed,  7 Aug 2002 12:10:50 -0400 (EDT)
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Tom Lane <tgl@sss.pgh.pa.us>, Elliot Lee <sopwith@redhat.com>,
   pgsql-patches@postgresql.org
Subject: Re: [PATCHES] Fix disabled triggers with deferred constraints
References: <200206140507.g5E57om04338@candle.pha.pa.us>
From: Neil Conway <nconway@klamath.dyndns.org>
In-Reply-To: <200206140507.g5E57om04338@candle.pha.pa.us>
Date: 07 Aug 2002 12:10:10 -0400
Message-ID: <87it2mfy59.fsf@klamath.dyndns.org>
Lines: 24
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-patches-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
> > Elliot Lee <sopwith@redhat.com> writes:
> > > About as obscure a bug as you can get - without the patch, disabled
> > > triggers for deferred constraints get run anyways. The patch is simple and
> > > works, but the "right" (and more complicated) fix may involve not adding
> > > the trigger to event->dte_item to begin with.
> > 
> > I remember looking at this issue and not doing anything because I
> > couldn't decide whether the test for enabled status should occur when
> > the trigger is queued or when it is executed --- or, perhaps, both?
> > Is there anything in the standard about it?
> 
> Was there any agreement on this?

Any update on this?

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

From pgsql-patches-owner+M4641@postgresql.org Wed Aug  7 12:46:40 2002
Return-path: <pgsql-patches-owner+M4641@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g77GkeY23704
	for <pgman@candle.pha.pa.us>; Wed, 7 Aug 2002 12:46:40 -0400 (EDT)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 2162C475C7A; Wed,  7 Aug 2002 12:46:35 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id E9A4B475C29; Wed,  7 Aug 2002 12:46:32 -0400 (EDT)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 9CACC475C29
	for <pgsql-patches@postgresql.org>; Wed,  7 Aug 2002 12:46:22 -0400 (EDT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id 09669475BB7
	for <pgsql-patches@postgresql.org>; Wed,  7 Aug 2002 12:46:22 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g77GkNVk016878;
	Wed, 7 Aug 2002 12:46:23 -0400 (EDT)
To: Neil Conway <nconway@klamath.dyndns.org>
cc: Bruce Momjian <pgman@candle.pha.pa.us>, Elliot Lee <sopwith@redhat.com>,
   pgsql-patches@postgresql.org
Subject: Re: [PATCHES] Fix disabled triggers with deferred constraints 
In-Reply-To: <87it2mfy59.fsf@klamath.dyndns.org> 
References: <200206140507.g5E57om04338@candle.pha.pa.us> <87it2mfy59.fsf@klamath.dyndns.org>
Comments: In-reply-to Neil Conway <nconway@klamath.dyndns.org>
	message dated "07 Aug 2002 12:10:10 -0400"
Date: Wed, 07 Aug 2002 12:46:23 -0400
Message-ID: <16877.1028738783@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-patches-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

Neil Conway <nconway@klamath.dyndns.org> writes:
> Elliot Lee <sopwith@redhat.com> writes:
> About as obscure a bug as you can get - without the patch, disabled
> triggers for deferred constraints get run anyways. The patch is simple and
> works, but the "right" (and more complicated) fix may involve not adding
> the trigger to event->dte_item to begin with.
> 
> I remember looking at this issue and not doing anything because I
> couldn't decide whether the test for enabled status should occur when
> the trigger is queued or when it is executed --- or, perhaps, both?
> Is there anything in the standard about it?
>> 
>> Was there any agreement on this?

> Any update on this?

I think we're still waiting for someone to figure out what the behavior
should be per spec.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

From nconway@klamath.dyndns.org Wed Aug  7 14:10:27 2002
Return-path: <nconway@klamath.dyndns.org>
Received: from klamath.dyndns.org (identsucks@CPE002078144ae0.cpe.net.cable.rogers.com [24.102.202.35])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g77IAQY29959
	for <pgman@candle.pha.pa.us>; Wed, 7 Aug 2002 14:10:26 -0400 (EDT)
Received: from boston.klamath.dyndns.org (unknown [192.168.40.12])
	by klamath.dyndns.org (Postfix) with ESMTP
	id 284B87010; Wed,  7 Aug 2002 14:10:21 -0400 (EDT)
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: Bruce Momjian <pgman@candle.pha.pa.us>, Elliot Lee <sopwith@redhat.com>,
   pgsql-patches@postgresql.org
Subject: Re: [PATCHES] Fix disabled triggers with deferred constraints
References: <200206140507.g5E57om04338@candle.pha.pa.us>
	<87it2mfy59.fsf@klamath.dyndns.org> <16877.1028738783@sss.pgh.pa.us>
From: Neil Conway <nconway@klamath.dyndns.org>
In-Reply-To: <16877.1028738783@sss.pgh.pa.us>
Date: 07 Aug 2002 14:09:40 -0400
Message-ID: <874re6fsm3.fsf@klamath.dyndns.org>
Lines: 39
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Status: OR

Tom Lane <tgl@sss.pgh.pa.us> writes:
> Neil Conway <nconway@klamath.dyndns.org> writes:
> > Elliot Lee <sopwith@redhat.com> writes:
> > I remember looking at this issue and not doing anything because I
> > couldn't decide whether the test for enabled status should occur when
> > the trigger is queued or when it is executed --- or, perhaps, both?
> > Is there anything in the standard about it?

[...]

> I think we're still waiting for someone to figure out what the behavior
> should be per spec.

I took a brief look at SQL99, but I couldn't find anything regarding
this issue (AFAICS it doesn't mention "disabled triggers" at all). But
given my prior track record for divining information from the
standards, perhaps someone should double-check :-)

I did notice some behavior which we don't implement AFAIK:

        If the constraint mode is /deferred/, then the constraint is
        effectively checked when the constraint mode is changed to
        /immediate/ either explicitely by execution of a <set
        constraints mode statement>, or implicitely at the end of the
        current SQL-transaction.

(SQL99, Section 4.17.1, paragraph 3)

We don't recheck any outstanding deferred constraints when the
constraint mode is explicitly switched back to IMMEDIATE, as the
standard says we should.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC


From pgsql-patches-owner+M4751@postgresql.org Tue Aug 13 00:10:31 2002
Return-path: <pgsql-patches-owner+M4751@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g7D4AVk26779
	for <pgman@candle.pha.pa.us>; Tue, 13 Aug 2002 00:10:31 -0400 (EDT)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 321AE475A20; Tue, 13 Aug 2002 00:10:26 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 7BB7E475F42; Tue, 13 Aug 2002 00:10:20 -0400 (EDT)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id A0519476087
	for <pgsql-patches@postgresql.org>; Tue, 13 Aug 2002 00:10:10 -0400 (EDT)
Received: from linuxworld.com.au (www.linuxworld.com.au [203.34.46.50])
	by postgresql.org (Postfix) with ESMTP id 70806475806
	for <pgsql-patches@postgresql.org>; Tue, 13 Aug 2002 00:09:57 -0400 (EDT)
Received: from localhost (swm@localhost)
	by linuxworld.com.au (8.11.4/8.11.4) with ESMTP id g7D4CGI23801;
	Tue, 13 Aug 2002 14:12:16 +1000
Date: Tue, 13 Aug 2002 14:12:15 +1000 (EST)
From: Gavin Sherry <swm@linuxworld.com.au>
To: Neil Conway <nconway@klamath.dyndns.org>
cc: pgsql-patches@postgresql.org
Subject: Re: [PATCHES] Fix disabled triggers with deferred constraints
In-Reply-To: <874re6fsm3.fsf@klamath.dyndns.org>
Message-ID: <Pine.LNX.4.21.0208131342460.22771-100000@linuxworld.com.au>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-patches-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

On 7 Aug 2002, Neil Conway wrote:

> Tom Lane <tgl@sss.pgh.pa.us> writes:
> > Neil Conway <nconway@klamath.dyndns.org> writes:
> > > Elliot Lee <sopwith@redhat.com> writes:
> > > I remember looking at this issue and not doing anything because I
> > > couldn't decide whether the test for enabled status should occur when
> > > the trigger is queued or when it is executed --- or, perhaps, both?
> > > Is there anything in the standard about it?
> 
> [...]
> 
> > I think we're still waiting for someone to figure out what the behavior
> > should be per spec.
> 
> I took a brief look at SQL99, but I couldn't find anything regarding
> this issue (AFAICS it doesn't mention "disabled triggers" at all). But
> given my prior track record for divining information from the
> standards, perhaps someone should double-check :-)

I had a pretty hard look around SQL99. It does not appear to say anything
explicit about disabling triggers. This should be clear from page 90: 4.35
Triggers. This specifies the trigger descriptor. Those familiar with SQL99
know that it just about mandates that all state information about any
object in the system is recorded in its descriptor. The fact that
enabled/disabled state information is not recorded in the trigger
descriptor suggests that it is only ever enabled.

More over there is no case when a trigger is not executed, according to
10.12 'Execution of triggers'.

I dug deeper, wondering if it may be implicitly disabled given the
disabling of its 'dependencies', shall we call them. Namely: the base
table or the procedure used in the trigger action. Tables cannot be
disabled or made in active. As for the procedure, <SQL procedure
statement>, this expands to SQL which, itself, cannot be 'disabled'.

The spec is a large one and I didn't look at all references to triggers --
since there are hundreds -- but I don't believe that there is any
precedent for an implementation of DISABLE TRIGGER.

FWIW, i think that in the case of deferred triggers they should all be
added to the queue and whether they are executed or not should be
evaluated inside DeferredTriggerExecute() with:

    if(LocTriggerData.tg_trigger->tgenabled == false)
        return;

Gavin




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From pgsql-patches-owner+M4752@postgresql.org Tue Aug 13 00:28:42 2002
Return-path: <pgsql-patches-owner+M4752@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g7D4Sgk27162
	for <pgman@candle.pha.pa.us>; Tue, 13 Aug 2002 00:28:42 -0400 (EDT)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 94BDB475EFD; Tue, 13 Aug 2002 00:28:37 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id C912A475EDF; Tue, 13 Aug 2002 00:28:36 -0400 (EDT)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 0026B475924
	for <pgsql-patches@postgresql.org>; Tue, 13 Aug 2002 00:28:30 -0400 (EDT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id 5263B47582B
	for <pgsql-patches@postgresql.org>; Tue, 13 Aug 2002 00:28:30 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g7D4SJVk022192;
	Tue, 13 Aug 2002 00:28:19 -0400 (EDT)
To: Gavin Sherry <swm@linuxworld.com.au>
cc: Neil Conway <nconway@klamath.dyndns.org>, pgsql-patches@postgresql.org
Subject: Re: [PATCHES] Fix disabled triggers with deferred constraints 
In-Reply-To: <Pine.LNX.4.21.0208131342460.22771-100000@linuxworld.com.au> 
References: <Pine.LNX.4.21.0208131342460.22771-100000@linuxworld.com.au>
Comments: In-reply-to Gavin Sherry <swm@linuxworld.com.au>
	message dated "Tue, 13 Aug 2002 14:12:15 +1000"
Date: Tue, 13 Aug 2002 00:28:19 -0400
Message-ID: <22191.1029212899@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-patches-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

Gavin Sherry <swm@linuxworld.com.au> writes:
> ...The spec is a large one and I didn't look at all references to triggers --
> since there are hundreds -- but I don't believe that there is any
> precedent for an implementation of DISABLE TRIGGER.

Thanks for the dig.  I was hoping we could get some guidance from the
spec, but it looks like not.  How about other implementations --- does
Oracle support disabled triggers?  DB2?  etc?

> FWIW, i think that in the case of deferred triggers they should all be
> added to the queue and whether they are executed or not should be
> evaluated inside DeferredTriggerExecute() with:
>     if(LocTriggerData.tg_trigger->tgenabled == false)
>         return;

So check the state at execution, not when the triggering event occurs.
I don't have any strong reason to object to that, but I have a gut
feeling that it still needs to be thought about...

Let's see, I guess there are several possible changes of state for a
deferred trigger between the triggering event and the end of
transaction:

* Trigger deleted.  Surely the trigger shouldn't be executed, but should
we raise an error or just silently ignore it?  (I suspect right now we
crash :-()

* Trigger created.  In some ideal world we might think that such a
trigger should be fired, but in reality that ain't gonna happen; we're
not going to record every possible event on the speculation that some
trigger for it might be created later in the transaction.

* Trigger disabled.  Your proposal is to not fire it.  Okay, comports
with the deleted case, if we make that behavior be silently-ignore.

* Trigger enabled.  Your proposal is to fire it.  Seems not to comport
with the creation case --- does that bother anyone?

* Trigger changed from not-deferred to deferred.  If we already fired it
for the event, we surely shouldn't fire it again.  I believe the code
gets this case right.

* Trigger changed from deferred to not-deferred.  As Neil was pointing
out recently, this really should cause the trigger to be fired for the
pending event immediately, but we don't get that right at the moment.
(I suppose a stricter interpretation would be to raise an error because
we can't do anything that really comports with the intended behavior
of either case.)

How do these various cases relate?  Can we come up with a unified
rationale?

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

From pgsql-patches-owner+M4753@postgresql.org Tue Aug 13 00:56:55 2002
Return-path: <pgsql-patches-owner+M4753@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g7D4usk27855
	for <pgman@candle.pha.pa.us>; Tue, 13 Aug 2002 00:56:54 -0400 (EDT)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id F09AE475EFD; Tue, 13 Aug 2002 00:56:49 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 94E4B475DC0; Tue, 13 Aug 2002 00:56:47 -0400 (EDT)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 4EB5D475751
	for <pgsql-patches@postgresql.org>; Tue, 13 Aug 2002 00:56:42 -0400 (EDT)
Received: from joeconway.com (unknown [63.210.180.150])
	by postgresql.org (Postfix) with ESMTP id A5F35475531
	for <pgsql-patches@postgresql.org>; Tue, 13 Aug 2002 00:56:41 -0400 (EDT)
Received: from [192.168.5.2] (account jconway HELO joeconway.com)
  by joeconway.com (CommuniGate Pro SMTP 3.5.9)
  with ESMTP-TLS id 1246425; Mon, 12 Aug 2002 21:46:29 -0700
Message-ID: <3D589161.8020903@joeconway.com>
Date: Mon, 12 Aug 2002 21:56:01 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.0.0) Gecko/20020530
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: Gavin Sherry <swm@linuxworld.com.au>,
   Neil Conway <nconway@klamath.dyndns.org>, pgsql-patches@postgresql.org
Subject: Re: [PATCHES] Fix disabled triggers with deferred constraints
References: <Pine.LNX.4.21.0208131342460.22771-100000@linuxworld.com.au> <22191.1029212899@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-patches-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

Tom Lane wrote:
> Gavin Sherry <swm@linuxworld.com.au> writes:
>>...The spec is a large one and I didn't look at all references to triggers --
>>since there are hundreds -- but I don't believe that there is any
>>precedent for an implementation of DISABLE TRIGGER.
> 
> Thanks for the dig.  I was hoping we could get some guidance from the
> spec, but it looks like not.  How about other implementations --- does
> Oracle support disabled triggers?  DB2?  etc?

Oracle does for sure. With a complex app (i.e. Oracle Applications) 
being able to disable triggers from time-to-time is *indispensable*. Not 
sure about DB2. My knowledge of MSSQL is getting dated, but as of MSSQL7 
I don't *think* you can disable a trigger.

Joe


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

From pgsql-patches-owner+M4755@postgresql.org Tue Aug 13 01:36:42 2002
Return-path: <pgsql-patches-owner+M4755@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g7D5afk29468
	for <pgman@candle.pha.pa.us>; Tue, 13 Aug 2002 01:36:41 -0400 (EDT)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id C40B3476088; Tue, 13 Aug 2002 01:36:42 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id AAB02476037; Tue, 13 Aug 2002 01:36:41 -0400 (EDT)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 15911475751
	for <pgsql-patches@postgresql.org>; Tue, 13 Aug 2002 01:36:37 -0400 (EDT)
Received: from linuxworld.com.au (www.linuxworld.com.au [203.34.46.50])
	by postgresql.org (Postfix) with ESMTP id BC813475531
	for <pgsql-patches@postgresql.org>; Tue, 13 Aug 2002 01:36:35 -0400 (EDT)
Received: from localhost (swm@localhost)
	by linuxworld.com.au (8.11.4/8.11.4) with ESMTP id g7D5coN26796;
	Tue, 13 Aug 2002 15:38:50 +1000
Date: Tue, 13 Aug 2002 15:38:50 +1000 (EST)
From: Gavin Sherry <swm@linuxworld.com.au>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: Neil Conway <nconway@klamath.dyndns.org>, pgsql-patches@postgresql.org
Subject: Re: [PATCHES] Fix disabled triggers with deferred constraints 
In-Reply-To: <22191.1029212899@sss.pgh.pa.us>
Message-ID: <Pine.LNX.4.21.0208131530160.25873-100000@linuxworld.com.au>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-patches-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: ORr

On Tue, 13 Aug 2002, Tom Lane wrote:

> Gavin Sherry <swm@linuxworld.com.au> writes:
> > ...The spec is a large one and I didn't look at all references to triggers --
> > since there are hundreds -- but I don't believe that there is any
> > precedent for an implementation of DISABLE TRIGGER.
> 
> Thanks for the dig.  I was hoping we could get some guidance from the
> spec, but it looks like not.  How about other implementations --- does
> Oracle support disabled triggers?  DB2?  etc?

Oracle 8 (and I presume 9) allows you to disable/enable triggers through
alter table and alter trigger. My 8.1.7 documentation is silent on the
cases you mention below and I do not have an oracle installation handy to
test. Anyone?

> 
> > FWIW, i think that in the case of deferred triggers they should all be
> > added to the queue and whether they are executed or not should be
> > evaluated inside DeferredTriggerExecute() with:
> >     if(LocTriggerData.tg_trigger->tgenabled == false)
> >         return;
> 
> So check the state at execution, not when the triggering event occurs.
> I don't have any strong reason to object to that, but I have a gut
> feeling that it still needs to be thought about...
> 
> Let's see, I guess there are several possible changes of state for a
> deferred trigger between the triggering event and the end of
> transaction:
> 
> * Trigger deleted.  Surely the trigger shouldn't be executed, but should
> we raise an error or just silently ignore it?  (I suspect right now we
> crash :-()
> 
> * Trigger created.  In some ideal world we might think that such a
> trigger should be fired, but in reality that ain't gonna happen; we're
> not going to record every possible event on the speculation that some
> trigger for it might be created later in the transaction.

It doesn't need to be an ideal world. We're only talking about deferred
triggers after all. Why couldn't CreateTrgger() just have a look through
deftrig_events, check for its relid and if its in there, call
deferredTriggerAddEvent().

> * Trigger disabled.  Your proposal is to not fire it.  Okay, comports
> with the deleted case, if we make that behavior be silently-ignore.
> 
> * Trigger enabled.  Your proposal is to fire it.  Seems not to comport
> with the creation case --- does that bother anyone?
> 
> * Trigger changed from not-deferred to deferred.  If we already fired it
> for the event, we surely shouldn't fire it again.  I believe the code
> gets this case right.

Agreed.

> * Trigger changed from deferred to not-deferred.  As Neil was pointing
> out recently, this really should cause the trigger to be fired for the
> pending event immediately, but we don't get that right at the moment.
> (I suppose a stricter interpretation would be to raise an error because
> we can't do anything that really comports with the intended behavior
> of either case.)

I think this should generate an error as it doesn't sit well with the
spec IMHO.

Gavin


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org