Potential bug -- script that drops postgres server

Started by Lance Thomasalmost 23 years ago3 messages
#1Lance Thomas
LThomas@DevIS.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Lance Thomas (#1)
plpython trigger code is wrong (Re: Potential bug -- script that drops postgres server)

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.

#3Bradley McLean
brad@bradm.net
In reply to: Tom Lane (#2)
Re: [HACKERS] plpython trigger code is wrong (Re: Potential bug -- script that drops postgres server)

* 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