Thread: Potential bug -- script that drops postgres server

Postgres General:

First, I would like to thank you for your contribution with Postgres, a
wonderful database server that I use extensively.

Below is something that may be of interest -- a short, 7-statement script
that seems to drop my postgres server. Here's the following version
information for my system:

OS: Debian 3.0, Linux dev 2.2.19-reiserfs #1 SMP i686
Postgres: 7.2.1
Python: 2.1.1

Run the following inside of a blank database with plpython enabled:

CREATE TABLE "empcert" (
            "empcertid" integer,
            "employeeid" integer,
            "certid" integer,
            "empcertachieveddt" date,
            "createuser" integer,
            "createaccess" timestamp without time zone
);
 
CREATE TABLE "requirementswaiver" (
            "requirementswaiverid" integer,
            "requirementswaiveremployee" integer,
            "requirementswaiveragency" character varying(20),
            "requirementswaivergrade" character varying(20),
            "requirementswaivertype" text,
            "createuser" integer,
            "createaccess" timestamp without time zone
);
 
CREATE FUNCTION "log_insert" () RETURNS opaque AS '
    return None
' LANGUAGE 'plpython';
 
CREATE TRIGGER "empcert_log_insert_trigger" AFTER INSERT ON "empcert"  FOR
EACH ROW EXECUTE PROCEDURE "log_insert" ('empcert');
CREATE TRIGGER "requirementswaiver_log_insert_t" AFTER INSERT ON
"requirementswaiver"  FOR EACH ROW EXECUTE PROCEDURE "log_insert"
('requirementswaiver');
 
insert into empcert
(employeeid,certid,empcertachieveddt,createuser,createaccess) values
(2000000001,11,'2002-02-03',2000000001,now());
insert into requirementswaiver
(requirementswaiveremployee,requirementswaivertype,createuser,createaccess)
values (2000000001,'Training',2000000001,now());


You receive the following:


server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.


This behavior occurs on three out of four systems that we tested. The one
that worked was running an unstable version of debian linux. If you have any
information, feel free to let me know.

Thanks,

Lance Thomas

Lance Thomas <LThomas@DevIS.com> writes:
> Below is something that may be of interest -- a short, 7-statement script
> that seems to drop my postgres server.

It appears that the plpython trigger implementation assumes that any
given procedure will be used as a trigger for only one relation.  The
reason it crashes is it's trying to use the rowtype info of the relation
it was first compiled for with the other relation.

Probably the easiest fix is to include the relation OID as part of the
Python name of a trigger procedure, so that a separate copy is compiled
for each relation the procedure is used with.

Any plpython users want to step forward and fix this?  I have other
things on my plate ...

            regards, tom lane

PS: I haven't tested, but I wonder whether any of our other PLs have the
same bug.

* Tom Lane (tgl@sss.pgh.pa.us) [030214 19:35]:
> Lance Thomas <LThomas@DevIS.com> writes:
> > Below is something that may be of interest -- a short, 7-statement script
> > that seems to drop my postgres server.
>
> It appears that the plpython trigger implementation assumes that any
> given procedure will be used as a trigger for only one relation.  The
> reason it crashes is it's trying to use the rowtype info of the relation
> it was first compiled for with the other relation.
>
> Probably the easiest fix is to include the relation OID as part of the
> Python name of a trigger procedure, so that a separate copy is compiled
> for each relation the procedure is used with.

Interesting idea.  I had been taking the approach of applying the cache
to just the python compilation, and not the rowtype info.  This has a
substantial performance penalty, which I'd been addressing by eliminating
some unneeded parameter processing that doesn't apply in the trigger
case, and considering a separate cache for each rowtype.

> Any plpython users want to step forward and fix this?  I have other
> things on my plate ...

I'm looking at the bug right now.  Patch in a day or so.

-Brad