The following bug has been logged online: Bug reference: 1620 Logged by: Nahuel Greco Email address: [EMAIL PROTECTED] 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 [EMAIL PROTECTED])