The following bug has been logged online:

Bug reference: 1620
Logged by: Nahuel Greco
Email address: ngreco (AT) gmail (DOT) com
PostgreSQL version: 7.4.7
Operating system: Debian unstable i386
Description: triggers breaks with alter table (at least with
plpythonu)
Details:

If I create a trigger for a table, the trigger works
ok, but if I add a new column to the table, then
the trigger receives a NEW record with the old
structure! I can fix that only if I remove the trigger
from the table _and_ the trigger function. This was
only tested with plpythonu, see the following psql transcription:

NAH_TEST=# -- First we create an empty table
NAH_TEST=# CREATE TABLE bug_test (col1 text);
CREATE TABLE

NAH_TEST=# -- A plpythonu trigger function that
NAH_TEST=# -- prints the columns in the NEW record
NAH_TEST=# CREATE FUNCTION trigger_function() RETURNS trigger AS '
NAH_TEST'# plpy.notice("columns in new: %r" % TD[''new''].keys())
NAH_TEST'# 'LANGUAGE 'plpythonu';
CREATE FUNCTION

NAH_TEST=# -- Add it as a insert trigger of the table
NAH_TEST=# CREATE TRIGGER bug_test_trigger_ins AFTER INSERT ON bug_test
NAH_TEST-# FOR EACH ROW EXECUTE PROCEDURE trigger_function();
CREATE TRIGGER

NAH_TEST=# -- Insert a value to the table
NAH_TEST=# INSERT INTO bug_test (col1) VALUES ('hi');
NOTICE: ("columns in new: ['col1']",)
INSERT 2753690 1

NAH_TEST=# -- Ok, this worked

NAH_TEST=# -- Alter the table adding a column
NAH_TEST=# ALTER TABLE bug_test ADD COLUMN col2 text;
ALTER TABLE

NAH_TEST=# -- Try the trigger again
NAH_TEST=# INSERT INTO bug_test (col1,col2) VALUES ('hi','ho');
NOTICE: ("columns in new: ['col1']",)
INSERT 2753691 1

NAH_TEST=# -- Oops! only col1 is printed, not col2!

NAH_TEST=# -- Drop the trigger from the table
NAH_TEST=# DROP TRIGGER bug_test_trigger_ins ON bug_test;
DROP TRIGGER

NAH_TEST=# -- Create it again
NAH_TEST=# CREATE TRIGGER bug_test_trigger_ins AFTER INSERT ON bug_test
NAH_TEST-# FOR EACH ROW EXECUTE PROCEDURE trigger_function();
CREATE TRIGGER

NAH_TEST=# -- Try it again
NAH_TEST=# INSERT INTO bug_test (col1,col2) VALUES ('hi','ho');
NOTICE: ("columns in new: ['col1']",)
INSERT 2753693 1

NAH_TEST=# -- Stills not work! col2 isn't printed!

NAH_TEST=# -- Drop the trigger _AND_ the function
NAH_TEST=# DROP TRIGGER bug_test_trigger_ins ON bug_test;
DROP TRIGGER
NAH_TEST=# DROP FUNCTION trigger_function();
DROP FUNCTION

NAH_TEST=# -- Create the function and the trigger again
NAH_TEST=# CREATE FUNCTION trigger_function() RETURNS trigger AS '
NAH_TEST'# plpy.notice("columns in new: %r" % TD[''new''].keys())
NAH_TEST'# 'LANGUAGE 'plpythonu';
CREATE FUNCTION
NAH_TEST=# CREATE TRIGGER bug_test_trigger_ins AFTER INSERT ON bug_test
NAH_TEST-# FOR EACH ROW EXECUTE PROCEDURE trigger_function();
CREATE TRIGGER


NAH_TEST=# -- Try again...
NAH_TEST=# INSERT INTO bug_test (col1,col2) VALUES ('hi','ho');
NOTICE: ("columns in new: ['col2', 'col1']",)
INSERT 2753696 1

NAH_TEST=# -- Now col2 is printed!!

Note, in another test I checked and the NEW values are
ok (but when only 'col1' is printed, you only have
the col1 value! (but the correct one)).

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

"Nahuel Greco" <ngreco (AT) gmail (DOT) com> writes:
Starting a fresh backend session would be sufficient. I believe the
issue is that plpython is caching tuple structure information.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend