
A lot of different things going on but my perl program (whose backend crashed)
was doing a lot of insert into table as select * from another table for a
lot of different tables. I see triggers referenced here and it should be
noted that for one of the tables the triggers were first disabled (update
pg_class) and re-enabled after the inserts are done (or it takes forever).

The pgsql log shows:
...
2002-10-08 15:48:38 [18033]  DEBUG:  recycled transaction log file
00000052000000B1
2002-10-08 15:49:24 [28612]  DEBUG:  server process (pid 16003) was
terminated by signal 11
2002-10-08 15:49:24 [28612]  DEBUG:  terminating any other active server
processes
2002-10-08 18:49:24 [28616]  NOTICE:  Message from PostgreSQL backend:
        The Postmaster has informed me that some other backend
        died abnormally and possibly corrupted shared memory.
        I have rolled back the current transaction and am
        going to terminate your database system connection and exit.
        Please reconnect to the database system and repeat your query.
...

A core file was found in <datadir>/base/326602604
and a backtrace shows:
(gdb) bt
#0  DeferredTriggerSaveEvent (relinfo=0x83335f0, event=0, oldtup=0x0,
    newtup=0x8348150) at trigger.c:2056
#1  0x080b9d0c in ExecARInsertTriggers (estate=0x8333778,
relinfo=0x83335f0,
    trigtuple=0x8348150) at trigger.c:952
#2  0x080c0f23 in ExecAppend (slot=0x8333660, tupleid=0x0,
estate=0x8333778)
    at execMain.c:1280
#3  0x080c0dcd in ExecutePlan (estate=0x8333778, plan=0x83336f0,
    operation=CMD_INSERT, numberTuples=0, direction=ForwardScanDirection,
    destfunc=0x8334278) at execMain.c:1119
#4  0x080c026c in ExecutorRun (queryDesc=0x826fd88, estate=0x8333778,
feature=3,
    count=0) at execMain.c:233
#5  0x0810b2d5 in ProcessQuery (parsetree=0x826c500, plan=0x83336f0,
dest=Remote,
    completionTag=0xbfffec10 "") at pquery.c:259
#6  0x08109c83 in pg_exec_query_string (
    query_string=0x826c168 "insert into jobsequences select * from
rev_000_jobsequences", dest=Remote, parse_context=0x8242cd8) at
postgres.c:811
#7  0x0810abee in PostgresMain (argc=4, argv=0xbfffee40,
    username=0x8202d59 "laurette") at postgres.c:1929
#8  0x080f24fe in DoBackend (port=0x8202c28) at postmaster.c:2243
#9  0x080f1e9a in BackendStartup (port=0x8202c28) at postmaster.c:1874
#10 0x080f10e9 in ServerLoop () at postmaster.c:995
#11 0x080f0c56 in PostmasterMain (argc=1, argv=0x81eb398) at
postmaster.c:771
#12 0x080d172b in main (argc=1, argv=0xbffff7d4) at main.c:206
#13 0x401e7177 in __libc_start_main (main=0x80d15a8 <main>, argc=1,
    ubp_av=0xbffff7d4, init=0x80676ac <_init>, fini=0x81554f0 <_fini>,
    rtld_fini=0x4000e184 <_dl_fini>, stack_end=0xbffff7cc)
    at ../sysdeps/generic/libc-start.c:129


Thanks,

-- 
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
------------------------------
It's 10 o'clock...
Do you know where your bus is?


Tue, 8 Oct 2002 16:34:02 -0700 (PDT)
Laurette Cisneros <laurette@nextbus.com>
pgsql 7.2.3 crash
Laurette Cisneros <laurette@nextbus.com> writes:
> A core file was found in <datadir>/base/326602604
> and a backtrace shows:
> (gdb) bt
> #0  DeferredTriggerSaveEvent (relinfo=0x83335f0, event=0, oldtup=0x0,
>     newtup=0x8348150) at trigger.c:2056

Hm.  Line 2056 is this:

	for (i = 0; i < ntriggers; i++)
	{
		Trigger    *trigger = &trigdesc->triggers[tgindx[i]];

->		new_event->dte_item[i].dti_tgoid = trigger->tgoid;

It seems there must be something wrong with the trigdesc data structure
for that table, but what?  Can you poke around in the corefile with gdb
print commands and determine what's wrong with the trigdesc?

> I see triggers referenced here and it should be
> noted that for one of the tables the triggers were first disabled (update
> pg_class) and re-enabled after the inserts are done (or it takes
> forever).

Did that happen while this backend was running?

			regards, tom lane

Wed, 09 Oct 2002 10:14:54 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pgsql 7.2.3 crash 
On Wed, 9 Oct 2002, Tom Lane wrote:

> Laurette Cisneros <laurette@nextbus.com> writes:
> > A core file was found in <datadir>/base/326602604
> > and a backtrace shows:
> > (gdb) bt
> > #0  DeferredTriggerSaveEvent (relinfo=0x83335f0, event=0, oldtup=0x0,
> >     newtup=0x8348150) at trigger.c:2056
> 
> Hm.  Line 2056 is this:
> 
> 	for (i = 0; i < ntriggers; i++)
> 	{
> 		Trigger    *trigger = &trigdesc->triggers[tgindx[i]];
> 
> ->		new_event->dte_item[i].dti_tgoid = trigger->tgoid;
> 
> It seems there must be something wrong with the trigdesc data structure
> for that table, but what?  Can you poke around in the corefile with gdb
> print commands and determine what's wrong with the trigdesc?

Here's my poking/printing around with gdb:
(gdb) print trigger
$1 = (Trigger *) 0x1272c9a0
(gdb) print *trigger
Cannot access memory at address 0x1272c9a0
(gdb) print trigger->tgoid
Cannot access memory at address 0x1272c9a0
(gdb) print trigdesc
$2 = (TriggerDesc *) 0x4c86b2d8
(gdb) print &trigdesc
$3 = (TriggerDesc **) 0xbfffea18 
(gdb) print *trigdesc 
$4 = {n_before_statement = {5378, 22310, 37184, 2085}, n_before_row =
{51128, 19585,
    62320, 19589}, n_after_row = {45784, 19590, 52748, 2084},
n_after_statement = {
    0, 0, 0, 0}, tg_before_statement = {0x4c86b2d8, 0x8259910, 0x0, 0x0},
  tg_before_row = {0xa0, 0x350000, 0x1f, 0x4006}, tg_after_row =
{0x8242920,
    0x4c860cb0, 0x4c86eec0, 0x0}, tg_after_statement = {0x0, 0x0, 0x0,
0x0},
  triggers = 0x4c86e7a0, numtriggers = 8}

> 
> > I see triggers referenced here and it should be
> > noted that for one of the tables the triggers were first disabled (update
> > pg_class) and re-enabled after the inserts are done (or it takes
> > forever).
> 
> Did that happen while this backend was running?

Yes.  I had run this perl program about 4-5 times in a row (which includes
the sequence, disable triggers, insert rows, enable triggers) and then it
crashed on one of the runs.

Thanks,

-- 
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
------------------------------
It's 10 o'clock...
Do you know where your bus is?


Wed, 9 Oct 2002 13:18:51 -0700 (PDT)
Laurette Cisneros <laurette@nextbus.com>
Re: pgsql 7.2.3 crash 
Laurette Cisneros <laurette@nextbus.com> writes:
> I see triggers referenced here and it should be
> noted that for one of the tables the triggers were first disabled (update
> pg_class) and re-enabled after the inserts are done (or it takes
> forever).
>> 
>> Did that happen while this backend was running?

> Yes.  I had run this perl program about 4-5 times in a row (which includes
> the sequence, disable triggers, insert rows, enable triggers) and then it
> crashed on one of the runs.

Hm.  The stack trace shows that this backend crashed while executing the
command
	insert into jobsequences select * from rev_000_jobsequences
Is it possible that you disabled and re-enabled triggers on jobsequences
*while this command was running* ?

The gdb info makes it look like the triggers code is using a stale
trigger description structure.  The pointer that's being used is cached
in the ResultRelInfo struct (ri_TrigDesc) during query startup.  If
some external operation changed the trigger state while the query is
running, trouble would ensue.

This looks like something we ought to fix in any case, but I'm unsure
whether it explains your crash.  Do you think that that's what could
have happened?


Hackers: we might reasonably fix this by doing a deep copy of the
relcache's trigger info during initResultRelInfo(); or we could fix it
by getting rid of ri_TrigDesc and re-fetching from the relcache every
time.  The former would imply that trigger state would remain unchanged
throughout a query, the latter would try to track currently-committed
trigger behavior.  Either way has got pitfalls I think.

The fact that there's a problem at all is because people are using
direct poking of the system catalogs instead of some kind of ALTER TABLE
command to disable/enable triggers; an ALTER command would presumably
gain exclusive lock on the table and thereby delay until active queries
finish.  But that technique is out there (even in pg_dump files :-() and
so we'd best try to make the system proof against it.

Any thoughts on which way to go?

			regards, tom lane

Sat, 12 Oct 2002 21:13:34 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pgsql 7.2.3 crash 
Tom Lane wrote:
> Hackers: we might reasonably fix this by doing a deep copy of the
> relcache's trigger info during initResultRelInfo(); or we could fix it
> by getting rid of ri_TrigDesc and re-fetching from the relcache every
> time.  The former would imply that trigger state would remain unchanged
> throughout a query, the latter would try to track currently-committed
> trigger behavior.  Either way has got pitfalls I think.
> 
> The fact that there's a problem at all is because people are using
> direct poking of the system catalogs instead of some kind of ALTER TABLE
> command to disable/enable triggers; an ALTER command would presumably
> gain exclusive lock on the table and thereby delay until active queries
> finish.  But that technique is out there (even in pg_dump files :-() and
> so we'd best try to make the system proof against it.
> 
> Any thoughts on which way to go?

I'd say:

1. go with the former
2. we definitely should also have an ALTER command to allow disable/enable of
    triggers
3. along with the ALTER, document that directly messing with the system
    catalogs is highly discouraged

Joe



Sat, 12 Oct 2002 22:31:31 -0700
Joe Conway <mail@joeconway.com>
Re: pgsql 7.2.3 crash
On Sat, 12 Oct 2002, Joe Conway wrote:

> Tom Lane wrote:
> > Hackers: we might reasonably fix this by doing a deep copy of the
> > relcache's trigger info during initResultRelInfo(); or we could fix it
> > by getting rid of ri_TrigDesc and re-fetching from the relcache every
> > time.  The former would imply that trigger state would remain unchanged
> > throughout a query, the latter would try to track currently-committed
> > trigger behavior.  Either way has got pitfalls I think.
> > 
> > The fact that there's a problem at all is because people are using
> > direct poking of the system catalogs instead of some kind of ALTER TABLE
> > command to disable/enable triggers; an ALTER command would presumably
> > gain exclusive lock on the table and thereby delay until active queries
> > finish.  But that technique is out there (even in pg_dump files :-() and
> > so we'd best try to make the system proof against it.
> > 
> > Any thoughts on which way to go?
> 
> I'd say:
> 
> 1. go with the former

I agree.

> 2. we definitely should also have an ALTER command to allow disable/enable of
>     triggers

I thought this was worked on for 7.3? I remember speaking to someone
(?) at OSCON because I had been working on 'ENABLE TRIGGER <trigname>' and
is compliment on the plane. Much of the work seemed to have been in CVS
already.

Gavin


Sun, 13 Oct 2002 15:52:30 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: pgsql 7.2.3 crash
Gavin Sherry wrote:
> On Sat, 12 Oct 2002, Joe Conway wrote:
> 
> > Tom Lane wrote:
> > > Hackers: we might reasonably fix this by doing a deep copy of the
> > > relcache's trigger info during initResultRelInfo(); or we could fix it
> > > by getting rid of ri_TrigDesc and re-fetching from the relcache every
> > > time.  The former would imply that trigger state would remain unchanged
> > > throughout a query, the latter would try to track currently-committed
> > > trigger behavior.  Either way has got pitfalls I think.
> > > 
> > > The fact that there's a problem at all is because people are using
> > > direct poking of the system catalogs instead of some kind of ALTER TABLE
> > > command to disable/enable triggers; an ALTER command would presumably
> > > gain exclusive lock on the table and thereby delay until active queries
> > > finish.  But that technique is out there (even in pg_dump files :-() and
> > > so we'd best try to make the system proof against it.
> > > 
> > > Any thoughts on which way to go?
> > 
> > I'd say:
> > 
> > 1. go with the former
> 
> I agree.
> 
> > 2. we definitely should also have an ALTER command to allow disable/enable of
> >     triggers
> 
> I thought this was worked on for 7.3? I remember speaking to someone
> (?) at OSCON because I had been working on 'ENABLE TRIGGER <trigname>' and
> is compliment on the plane. Much of the work seemed to have been in CVS
> already.

It is in TODO:

	* Allow triggers to be disabled [trigger]

 From the TODO.detail archives, it seems it got stuck on an
implementation issue:

	http://candle.pha.pa.us/mhonarc/todo.detail/trigger/msg00001.html

The patch didn't prevent deferred contraint triggers from being fired.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sun, 13 Oct 2002 21:39:05 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pgsql 7.2.3 crash
Gavin Sherry <swm@linuxworld.com.au> writes:
> On Sat, 12 Oct 2002, Joe Conway wrote:
>> Tom Lane wrote:
>>> Hackers: we might reasonably fix this by doing a deep copy of the
>>> relcache's trigger info during initResultRelInfo(); or we could fix it
>>> by getting rid of ri_TrigDesc and re-fetching from the relcache every
>>> time.  The former would imply that trigger state would remain unchanged
>>> throughout a query, the latter would try to track currently-committed
>>> trigger behavior.  Either way has got pitfalls I think.

>>> Any thoughts on which way to go?

>> I'd say:
>> 1. go with the former

> I agree.

That's my leaning too, after further reflection.  Will make it so.

>> 2. we definitely should also have an ALTER command to allow
>> disable/enable of triggers

> I thought this was worked on for 7.3?

Unless I missed it, it's not in current sources.

I was wondering whether an ALTER TABLE command is really the right way
to approach this.  If we had an ALTER-type command, presumably the
implication is that its effects would be global to all backends.  But
the uses that I've seen for suspending trigger invocations would be
happier with a local, temporary setting that only affects the current
backend.  Any thoughts about that?

			regards, tom lane

Mon, 14 Oct 2002 00:04:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Disabling triggers (was Re: pgsql 7.2.3 crash)
On Mon, 14 Oct 2002, Tom Lane wrote:

> I was wondering whether an ALTER TABLE command is really the right way
> to approach this.  If we had an ALTER-type command, presumably the
> implication is that its effects would be global to all backends.  But
> the uses that I've seen for suspending trigger invocations would be
> happier with a local, temporary setting that only affects the current
> backend.  Any thoughts about that?

I may be missing something here, but the only circumstance where i could
see such being useful would be a load of a database ... other then that,
how would overriding triggers be considered a good thing?



Mon, 14 Oct 2002 01:08:31 -0300 (ADT)
"Marc G. Fournier" <scrappy@hub.org>
Re: Disabling triggers (was Re: pgsql 7.2.3 crash)
Tom Lane wrote:
> I was wondering whether an ALTER TABLE command is really the right way
> to approach this.  If we had an ALTER-type command, presumably the
> implication is that its effects would be global to all backends.  But
> the uses that I've seen for suspending trigger invocations would be
> happier with a local, temporary setting that only affects the current
> backend.  Any thoughts about that?

I think SET would be the proper place, but I don't see how to make it
table-specific.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 14 Oct 2002 00:13:39 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Disabling triggers (was Re: pgsql 7.2.3 crash)
Tom Lane wrote:
> I was wondering whether an ALTER TABLE command is really the right way
> to approach this.  If we had an ALTER-type command, presumably the
> implication is that its effects would be global to all backends.  But
> the uses that I've seen for suspending trigger invocations would be
> happier with a local, temporary setting that only affects the current
> backend.  Any thoughts about that?
> 

Hmmm. Well the most common uses I've run across for disabling triggers in the 
  Oracle Apps world are:

1) bulk loading of data
2) temporarily turning off "workflow" procedures

The first case would benefit from being able to disable the trigger locally, 
without affecting other backends. Of course, I don't know how common it is to 
bulk load data while others are hitting the same table.

The second case is usually something like an insert into the employee table 
fires off an email to IT to create a login and security to make a badge. 
Commonly we turn off workflows (by disabling their related triggers) in our 
development and test databases so someone doesn't disable the CEO's login when 
we fire him as part of our testing! I think in this scenario it is better to 
be able to disable the trigger globally ;-)

Joe





Sun, 13 Oct 2002 21:15:08 -0700
Joe Conway <mail@joeconway.com>
Re: Disabling triggers (was Re: pgsql 7.2.3 crash)
"Marc G. Fournier" <scrappy@hub.org> writes:
> On Mon, 14 Oct 2002, Tom Lane wrote:
>> I was wondering whether an ALTER TABLE command is really the right way
>> to approach this.  If we had an ALTER-type command, presumably the
>> implication is that its effects would be global to all backends.  But
>> the uses that I've seen for suspending trigger invocations would be
>> happier with a local, temporary setting that only affects the current
>> backend.  Any thoughts about that?

> I may be missing something here, but the only circumstance where i could
> see such being useful would be a load of a database ... other then that,
> how would overriding triggers be considered a good thing?

Well, exactly: it seems like something you'd want to constrain as
tightly as possible.  So some kind of local, SET-like operation seems
safer to me than a global, ALTER-TABLE-like operation.

			regards, tom lane

Mon, 14 Oct 2002 00:20:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disabling triggers (was Re: pgsql 7.2.3 crash) 
Yeah I think that could have happened since I was running it several times and had
cancelled it (ctrl-c) it a couple of those times.  Could be the backend of one 
cancelled run hadn't finished what it was doing and if that was renabling
triggers it could have walked on it.

Thanks.

L.
On Sat, 12 Oct 2002, Tom Lane wrote:

> Laurette Cisneros <laurette@nextbus.com> writes:
> > I see triggers referenced here and it should be
> > noted that for one of the tables the triggers were first disabled (update
> > pg_class) and re-enabled after the inserts are done (or it takes
> > forever).
> >> 
> >> Did that happen while this backend was running?
> 
> > Yes.  I had run this perl program about 4-5 times in a row (which includes
> > the sequence, disable triggers, insert rows, enable triggers) and then it
> > crashed on one of the runs.
> 
> Hm.  The stack trace shows that this backend crashed while executing the
> command
> 	insert into jobsequences select * from rev_000_jobsequences
> Is it possible that you disabled and re-enabled triggers on jobsequences
> *while this command was running* ?
> 
> The gdb info makes it look like the triggers code is using a stale
> trigger description structure.  The pointer that's being used is cached
> in the ResultRelInfo struct (ri_TrigDesc) during query startup.  If
> some external operation changed the trigger state while the query is
> running, trouble would ensue.
> 
> This looks like something we ought to fix in any case, but I'm unsure
> whether it explains your crash.  Do you think that that's what could
> have happened?
> 
> 
> Hackers: we might reasonably fix this by doing a deep copy of the
> relcache's trigger info during initResultRelInfo(); or we could fix it
> by getting rid of ri_TrigDesc and re-fetching from the relcache every
> time.  The former would imply that trigger state would remain unchanged
> throughout a query, the latter would try to track currently-committed
> trigger behavior.  Either way has got pitfalls I think.
> 
> The fact that there's a problem at all is because people are using
> direct poking of the system catalogs instead of some kind of ALTER TABLE
> command to disable/enable triggers; an ALTER command would presumably
> gain exclusive lock on the table and thereby delay until active queries
> finish.  But that technique is out there (even in pg_dump files :-() and
> so we'd best try to make the system proof against it.
> 
> Any thoughts on which way to go?
> 
> 			regards, tom lane
> 

-- 
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
------------------------------
It's 10 o'clock...
Do you know where your bus is?


Mon, 14 Oct 2002 09:35:10 -0700 (PDT)
Laurette Cisneros <laurette@nextbus.com>
Re: pgsql 7.2.3 crash 
Laurette Cisneros <laurette@nextbus.com> writes:
> Yeah I think that could have happened since I was running it several times and had
> cancelled it (ctrl-c) it a couple of those times.  Could be the backend of one 
> cancelled run hadn't finished what it was doing and if that was renabling
> triggers it could have walked on it.

Sounds like we've got the explanation, then.

I've just committed fixes into 7.3 to prevent this scenario in future.
The patch is probably too large to risk back-patching into 7.2.* though.

			regards, tom lane

Mon, 14 Oct 2002 12:52:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pgsql 7.2.3 crash 
Great.  I am working my way toward 7.3 anyway...

Thanks!

L.
On Mon, 14 Oct 2002, Tom Lane wrote:

> Laurette Cisneros <laurette@nextbus.com> writes:
> > Yeah I think that could have happened since I was running it several times and had
> > cancelled it (ctrl-c) it a couple of those times.  Could be the backend of one 
> > cancelled run hadn't finished what it was doing and if that was renabling
> > triggers it could have walked on it.
> 
> Sounds like we've got the explanation, then.
> 
> I've just committed fixes into 7.3 to prevent this scenario in future.
> The patch is probably too large to risk back-patching into 7.2.* though.
> 
> 			regards, tom lane
> 

-- 
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
------------------------------
It's 10 o'clock...
Do you know where your bus is?


Mon, 14 Oct 2002 09:57:14 -0700 (PDT)
Laurette Cisneros <laurette@nextbus.com>
Re: pgsql 7.2.3 crash 
On Mon, 14 Oct 2002, Tom Lane wrote:

> Gavin Sherry <swm@linuxworld.com.au> writes:
> > On Sat, 12 Oct 2002, Joe Conway wrote:
> >> Tom Lane wrote:
> >>> Hackers: we might reasonably fix this by doing a deep copy of the
> >>> relcache's trigger info during initResultRelInfo(); or we could fix it
> >>> by getting rid of ri_TrigDesc and re-fetching from the relcache every
> >>> time.  The former would imply that trigger state would remain unchanged
> >>> throughout a query, the latter would try to track currently-committed
> >>> trigger behavior.  Either way has got pitfalls I think.
> 
> >>> Any thoughts on which way to go?
> 
> >> I'd say:
> >> 1. go with the former
> 
> > I agree.
> 
> That's my leaning too, after further reflection.  Will make it so.
> 
> >> 2. we definitely should also have an ALTER command to allow
> >> disable/enable of triggers
> 
> > I thought this was worked on for 7.3?
> 
> Unless I missed it, it's not in current sources.

Here is an email I sent to pgsql-patches.

--- BEGIN

---------- Forwarded message ----------
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

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...
>
> > 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.

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

This is why I thought ALTER TABLE was being worked on.

> 
> I was wondering whether an ALTER TABLE command is really the right way
> to approach this.  If we had an ALTER-type command, presumably the
> implication is that its effects would be global to all backends.  But
> the uses that I've seen for suspending trigger invocations would be
> happier with a local, temporary setting that only affects the current
> backend.  Any thoughts about that?

Oracle supports DISABLE TRIGGER and ALTER TABLE DISABLE ALL TRIGGERS. I
cannot find anything in my version 9 manual about whether the effect is
local or global. As you say, I think the syntax suggests global. (I do not
have an oracle system to test on).

There is no trigger disablement in DB2 7.2, which is the most recent
documentation I have.

Personally, I think we should one up oracle. How about:

DISABLE TRIGGER <name> [LOCALLY|GLOBALLY];
ALTER TABLE DISABLE [ALL] TRIGGERS [LOCALLY|GLOBALLY];

and their respective complements.

This should be able to be implemented through the current invalidation
system. We simply skill registering inval messages which are local.

Gavin







Wed, 16 Oct 2002 00:44:43 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: Disabling triggers (was Re: pgsql 7.2.3 crash)
On Mon, Oct 14, 2002 at 12:04:14AM -0400, Tom Lane wrote:

> implication is that its effects would be global to all backends.  But
> the uses that I've seen for suspending trigger invocations would be
> happier with a local, temporary setting that only affects the current
> backend.  Any thoughts about that?

None except that it would indeed be a big help.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Tue, 22 Oct 2002 10:51:48 -0400
Andrew Sullivan <andrew@libertyrms.info>
Re: Disabling triggers (was Re: pgsql 7.2.3 crash)
Hi,

On Mon, 2002-12-09 at 10:24, SEGUERRA FRANCIS TED ARANAS wrote:

> how do i port from mysql to postgresql?..

http://techdocs.postgresql.org/#convertfrom

Best regards,
.

-- 
Devrim GUNDUZ 
TR.NET Sistem Destek Uzmani

Tel : (312) 295 93 18  Fax : (312) 295 94 94 Tel : (216) 542 90 00


09 Dec 2002 10:21:24 +0200
Devrim GUNDUZ <devrim@tr.net>
Re: Porting from MySQL to PostgreSQL (was: pgsql 7.2.3
how do i port from mysql to postgresql?...

thanks bruce,
francis

-- 
ov3rr|d3r


Mon, 9 Dec 2002 16:24:14 +0800 (PHT)
SEGUERRA FRANCIS TED ARANAS <u98-0995@hindang.msuiit.edu.ph>
Re: pgsql 7.2.3 crash
Joe Conway wrote:
> The second case is usually something like an insert into the employee table 
> fires off an email to IT to create a login and security to make a badge. 
> Commonly we turn off workflows (by disabling their related triggers) in our 
> development and test databases so someone doesn't disable the CEO's login 
> when we fire him as part of our testing! I think in this scenario it is 
> better to be able to disable the trigger globally ;-)

I think in this scenario it's probably better to not fire the CEO,
gratifying as it may be!  :-)



-- 
Kevin Brown					      kevin@sysexperts.com

Mon, 9 Dec 2002 01:57:04 -0800
Kevin Brown <kevin@sysexperts.com>
Re: Disabling triggers (was Re: pgsql 7.2.3 crash)
thanks






On 9 Dec 2002, Devrim GUNDUZ wrote:

> Hi,
> 
> On Mon, 2002-12-09 at 10:24, SEGUERRA FRANCIS TED ARANAS wrote:
> 
> > how do i port from mysql to postgresql?..
> 
> http://techdocs.postgresql.org/#convertfrom
> 
> Best regards,
> .
> 
> 

-- 
ov3rr|d3r


Tue, 10 Dec 2002 02:03:36 +0800 (PHT)
SEGUERRA FRANCIS TED ARANAS <u98-0995@hindang.msuiit.edu.ph>
Re: Porting from MySQL to PostgreSQL (was: pgsql 7.2.3