The following bug has been logged online:
Bug reference: 1620
Logged by: Nahuel Greco
Email address: ngreco@gmail.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)).