I was working on trigger support for PL/R and ran across this bug in my 
own code related to STATEMENT level triggers. I decided to try it in PL/Tcl:

regression=# CREATE FUNCTION tcltrigfunc() RETURNS trigger AS '
regression'#    return OK
regression'# ' LANGUAGE pltcl;
CREATE FUNCTION
regression=#
regression=# CREATE TRIGGER tcltrig BEFORE INSERT OR UPDATE OR DELETE ON foo
regression-#     FOR EACH STATEMENT EXECUTE PROCEDURE tcltrigfunc();
CREATE TRIGGER
regression=# insert into foo values(11,'cat99',1.89);
The connection to the server was lost. Attempting reset: Failed.
connection pointer is NULL
!>

Hmm, same problem. Looks like PL/pgSQL does the right thing, but 
plpython will crash also. I don't think plperl supports triggers.

I'll try to submit a patch later tonight or tomorrow morning if no one 
beats me to it.

Joe



Sun, 03 Aug 2003 20:04:11 -0700
Joe Conway <mail@joeconway.com>
statement level trigger causes pltcl, plpython SIGSEGV
I thought that statement level triggers did not work yet.
Are they supposed to work in 7.4?

(But even if they don't work they shouldn't crash...)

elein

On Sun, Aug 03, 2003 at 08:04:11PM -0700, Joe Conway wrote:
> I was working on trigger support for PL/R and ran across this bug in my 
> own code related to STATEMENT level triggers. I decided to try it in PL/Tcl:
> 
> regression=# CREATE FUNCTION tcltrigfunc() RETURNS trigger AS '
> regression'#    return OK
> regression'# ' LANGUAGE pltcl;
> CREATE FUNCTION
> regression=#
> regression=# CREATE TRIGGER tcltrig BEFORE INSERT OR UPDATE OR DELETE ON foo
> regression-#     FOR EACH STATEMENT EXECUTE PROCEDURE tcltrigfunc();
> CREATE TRIGGER
> regression=# insert into foo values(11,'cat99',1.89);
> The connection to the server was lost. Attempting reset: Failed.
> connection pointer is NULL
> !>
> 
> Hmm, same problem. Looks like PL/pgSQL does the right thing, but 
> plpython will crash also. I don't think plperl supports triggers.
> 
> I'll try to submit a patch later tonight or tomorrow morning if no one 
> beats me to it.
> 
> Joe
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

Sun, 3 Aug 2003 21:59:38 -0700
elein <elein@varlena.com>
Re: statement level trigger causes pltcl, plpython SIGSEGV
Joe Conway wrote:
> I'll try to submit a patch later tonight or tomorrow morning if no one 
> beats me to it.
> 

Here's a patch for pltcl. It works for my test case:

CREATE OR REPLACE FUNCTION footrigfunc() RETURNS trigger AS 'return OK' 
LANGUAGE pltcl;
CREATE TRIGGER footrig BEFORE INSERT OR UPDATE OR DELETE ON foo FOR EACH 
STATEMENT EXECUTE PROCEDURE footrigfunc();
insert into foo values(11,'cat99',1.89);
delete from foo where f0 = 11;


I also ran the "runtest" script in pl/tcl/test -- it said:
**** Running test queries ****
     Tests passed O.K.


Note that I also changed behavior in that when trigdata->tg_event 
doesn't match anything known -- instead of pressing on with a value of 
"UNKNOWN" it now throws an "elog(ERROR...". The previous behavior made 
no sense to me, but you may not want to change existing behavior in this 
way (even though it seems to me that it is a "should not happen" kind of 
error).

If this patch is acceptable, I'll make similar changes to plpython. If 
not, let me know what to change.

Thanks,

Joe
Sun, 03 Aug 2003 22:45:06 -0700
Joe Conway <mail@joeconway.com>
Re: [HACKERS] statement level trigger causes pltcl, plpython SIGSEGV
Joe Conway said:
> Hmm, same problem. Looks like PL/pgSQL does the right thing, but
> plpython will crash also. I don't think plperl supports triggers.

Right -- I only had time to implement support for statement-level
triggers in the backend itself and in PL/PgSQL. Thanks for taking the
time to update PL/TCL -- you're welcome to go ahead and fix PL/Python
as well, if you're so inclined.

-Neil

Mon, 4 Aug 2003 01:09:47 -0700 (PDT)
"Neil Conway" <neilc@samurai.com>
Re: statement level trigger causes pltcl,
Joe Conway <mail@joeconway.com> writes:
> Note that I also changed behavior in that when trigdata->tg_event 
> doesn't match anything known -- instead of pressing on with a value of 
> "UNKNOWN" it now throws an "elog(ERROR...". The previous behavior made 
> no sense to me, but you may not want to change existing behavior in this 
> way (even though it seems to me that it is a "should not happen" kind of 
> error).

Seems reasonable to me.  As a matter of style, the "unrecognized" elogs
should print the offending tg_event value; otherwise the patch looks fine.

> If this patch is acceptable, I'll make similar changes to plpython.

Please do.

			regards, tom lane

Mon, 04 Aug 2003 09:49:41 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] statement level trigger causes pltcl, plpython SIGSEGV 
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
> 
>>Note that I also changed behavior in that when trigdata->tg_event 
>>doesn't match anything known -- instead of pressing on with a value of 
>>"UNKNOWN" it now throws an "elog(ERROR...". The previous behavior made 
>>no sense to me, but you may not want to change existing behavior in this 
>>way (even though it seems to me that it is a "should not happen" kind of 
>>error).
> 
> Seems reasonable to me.  As a matter of style, the "unrecognized" elogs
> should print the offending tg_event value; otherwise the patch looks fine.
> 
>>If this patch is acceptable, I'll make similar changes to plpython.
> 
> Please do.
> 

This patch includes pltcl and plpython, with the mentioned style issue 
fixed. Both PLs pass their scripted tests and my simple statement level 
trigger test.

Joe
Mon, 04 Aug 2003 10:11:06 -0700
Joe Conway <mail@joeconway.com>
Re: [HACKERS] statement level trigger causes pltcl, plpython
Joe Conway <mail@joeconway.com> writes:
> This patch includes pltcl and plpython, with the mentioned style issue 
> fixed. Both PLs pass their scripted tests and my simple statement level 
> trigger test.

Applied, thanks.

BTW, one other stylistic nit: I don't think comments like

		/* internal error */
		elog(ERROR, "unrecognized OP tg_event: %u", tdata->tg_event);

are really necessary.  In the brave new ereport world, any elog(ERROR)
call is an internal error by definition --- if it isn't, you should be
using ereport.  So the use of elog is sufficient documentation.  IMHO
anyway.

			regards, tom lane

Mon, 04 Aug 2003 14:43:23 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] statement level trigger causes pltcl, plpython SIGSEGV 
Tom Lane wrote:
> BTW, one other stylistic nit: I don't think comments like
> 
> 		/* internal error */
> 		elog(ERROR, "unrecognized OP tg_event: %u", tdata->tg_event);
> 
> are really necessary.  In the brave new ereport world, any elog(ERROR)
> call is an internal error by definition --- if it isn't, you should be
> using ereport.  So the use of elog is sufficient documentation.  IMHO
> anyway.

Yeah, I agree. During the conversion, I did that mostly to show that it 
was a conscious decision. Going forward, every new elog (versus ereport) 
*is* a conscious decision, or at least should be.

Joe



Mon, 04 Aug 2003 12:46:43 -0700
Joe Conway <mail@joeconway.com>
Re: [HACKERS] statement level trigger causes pltcl, plpython
elein wrote:
> I thought that statement level triggers did not work yet.
> Are they supposed to work in 7.4?
> 
> (But even if they don't work they shouldn't crash...)
> 

Yeah, they work - not as everyone would like, but they work. All fixed 
now anyway.

Joe



Tue, 05 Aug 2003 18:28:03 -0700
Joe Conway <mail@joeconway.com>
Re: statement level trigger causes pltcl, plpython SIGSEGV