plpython bug in 7.3.x - Mailing list pgsql-bugs

============================================================================
                         POSTGRESQL BUG REPORT TEMPLATE
============================================================================




Your name       : Rob Fielding
Your email address  : rob@dsvr.net




System Configuration
---------------------
   Architecture (example: Intel Pentium)     : Intel P3 SMP


   Operating System (example: Linux 2.0.26 ELF)  : RH9


   PostgreSQL version (example: PostgreSQL-7.3.6):   PostgreSQL-7.3.6


   Compiler used (example:  gcc 2.95.2)      : gcc (GCC) 3.2.2 20030222
(Red Hat Linux 3.2.2-5)




Please enter a FULL description of your problem:
------------------------------------------------

plpython trigger function crashes postmaster when encountering integer
columns.






Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------


$ dropdb trigger_test
$ createdb trigger_test
$ psql trigger_test < ~/trigger_test2.sql
CREATE FUNCTION
NOTICE:  CREATE TABLE will create implicit sequence 'foobar_id_seq' for
SERIAL column 'foobar.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'foobar_pkey' for table 'foobar'
CREATE TABLE
NOTICE:  CREATE TABLE will create implicit sequence 'barfoo_id_seq' for
SERIAL column 'barfoo.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'barfoo_pkey' for table 'barfoo'
CREATE TABLE
NOTICE:  CREATE TABLE will create implicit sequence 'object_id_seq' for
SERIAL column 'object.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'object_pkey' for table 'object'
CREATE TABLE
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
NOTICE:  ('TD %', {'relid': '12082227', 'old': None, 'name': 'tr_trans',
'level': 'ROW', 'new': {'id': 1, 'desc': 'foo'}, 'args': None, 'when':
'BEFORE', 'event': 'INSERT'})
INSERT 12082255 1
NOTICE:  ('TD %', {'relid': '12082237', 'old': None, 'name': 'tr_trans',
'level': 'ROW', 'new': {'id': 1, 'desc': 'var'}, 'args': None, 'when':
'BEFORE', 'event': 'INSERT'})
INSERT 12082256 1
NOTICE:  ('TD %', {'relid': '12082227', 'old': None, 'name': 'tr_trans',
'level': 'ROW', 'new': {'id': 2, 'desc': 'foo'}, 'args': None, 'when':
'BEFORE', 'event': 'INSERT'})
INSERT 12082257 1
NOTICE:  ('TD %', {'relid': '12082237', 'old': None, 'name': 'tr_trans',
'level': 'ROW', 'new': {'id': 2, 'desc': 'var'}, 'args': None, 'when':
'BEFORE', 'event': 'INSERT'})
INSERT 12082258 1
server closed the connection unexpectedly
         This probably means the server terminated abnormally
         before or while processing the request.
connection to server was lost


$ cat ~/trigger_test2.sql (annotated)

/* simple function */
CREATE OR REPLACE FUNCTION "tr_trans_py"() RETURNS TRIGGER AS '
plpy.notice("TD %", TD)
' LANGUAGE 'plpythonu' ;        <--- either plpython or plpythonu

CREATE TABLE "foobar" (
  "id" SERIAL,
  "desc" text,
  PRIMARY KEY ("id")
);




CREATE TABLE "barfoo" (
  "id" SERIAL,
  "desc" text,
  PRIMARY KEY ("id")
);


CREATE TABLE "object" (
  "id" SERIAL,
  "anum" integer,
  PRIMARY KEY ("id")
);

                                                     CREATE TRIGGER
tr_trans BEFORE UPDATE OR INSERT ON foobar FOR EACH ROW
EXECUTE PROCEDURE tr_trans_py();
CREATE TRIGGER tr_trans BEFORE UPDATE OR INSERT ON barfoo FOR EACH ROW
EXECUTE PROCEDURE tr_trans_py();
CREATE TRIGGER tr_trans BEFORE UPDATE OR INSERT ON object FOR EACH ROW
EXECUTE PROCEDURE tr_trans_py();

insert into foobar ("desc") values ('foo');
insert into barfoo ("desc") values ('var');
insert into foobar ("desc") values ('foo');
insert into barfoo ("desc") values ('var');
insert into object ("anum") values ('1');  <------ dies here
insert into object ("anum") values ('2');

if you edit the schema for the table "object" and change "anum" to type
text, all is well.

If you change "anum" to timestamp and the values to now() I get
additionally weird results (after a dropdb/createdb)

ERROR:  Relation 303 does not exist
ERROR:  Relation 1086872352 does not exist



If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------


Neither these problems are encountered in 7.4.x ; ordinarily I'd just
upgrade the database server, however in this case it isn't quite time
yet. Fixing this in the 7.3.x branch IMO would be nice :))

Best regards,


--

Rob Fielding
rob@dsvr.net

www.dsvr.co.uk              Development             Designer Servers Ltd