From: | "Mark Reid" <reid(dot)write(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #3847: plpython trigger caches table structure - doesn't see new / changed columns |
Date: | 2007-12-31 23:42:19 |
Message-ID: | 200712312342.lBVNgJcn061127@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 3847
Logged by: Mark Reid
Email address: reid(dot)write(at)gmail(dot)com
PostgreSQL version: 8.2.5
Operating system: Debian Linux
Description: plpython trigger caches table structure - doesn't see
new / changed columns
Details:
If a column is added, dropped, then re-added (all within a transaction), a
plpython trigger function loses track of the column and throws an error when
trying to access it. Here is the best minimal test case I could come up
with:
----------------- TEST 1 ----------------
BEGIN;
CREATE TABLE clarence (pick_id int, foo varchar, bar varchar, baz varchar);
INSERT INTO clarence (pick_id) values (1454);
INSERT INTO clarence (pick_id) values (1453);
CREATE OR REPLACE FUNCTION debug_columns()
RETURNS "trigger" AS
$BODY$
if TD["event"].upper() == "UPDATE":
plpy.notice('test4: %s' % (TD["new"]["test4"]))
return "OK"
$BODY$
LANGUAGE 'plpythonu' VOLATILE;
CREATE TRIGGER clarence_debug_trg
AFTER UPDATE
ON clarence
FOR EACH ROW
EXECUTE PROCEDURE debug_columns();
-- This works
alter table clarence add column test4 varchar;
update clarence set test4=12 where pick_id=1454;
alter table clarence drop column test4;
-- This does not work
alter table clarence add column test4 varchar;
update clarence set test4=12 where pick_id=1454; -- this creates a
problem... plpgsql seems to work fine.
alter table clarence drop column test4;
ROLLBACK;
------------------ END TEST 1 --------------
Here is another test case that may come in handy (it enumerates the names of
all the columns in the "new" record):
-------------------- TEST 2 -------------------
BEGIN;
CREATE TABLE clarence (pick_id int, foo varchar, bar varchar, baz varchar);
INSERT INTO clarence (pick_id) values (1454);
INSERT INTO clarence (pick_id) values (1453);
CREATE OR REPLACE FUNCTION debug_columns()
RETURNS "trigger" AS
$BODY$
if TD["event"].upper() == "UPDATE":
for key, val in TD["new"].iteritems():
plpy.notice('%s = [%s]' % (key, val))
return "OK"
$BODY$
LANGUAGE 'plpythonu' VOLATILE;
CREATE TRIGGER clarence_debug_trg
AFTER UPDATE
ON clarence
FOR EACH ROW
EXECUTE PROCEDURE debug_columns();
-- This works
alter table clarence add column test4 varchar;
update clarence set test4=12 where pick_id=1454;
alter table clarence drop column test4;
-- This does not work
--alter table clarence add column test4 varchar;
--update clarence set test4=12 where pick_id=1454; -- this creates a
problem... plpgsql seems to work fine.
--alter table clarence drop column test4;
-- This works
alter table clarence add column test5 varchar;
update clarence set test5=12 where pick_id=1454;
alter table clarence drop column test5;
ROLLBACK;
---------------- END TEST 2 -------------------
I would be willing to take a stab at fixing this, but would need someone
more experienced to give me some pointers as to how to go about it (i've
never looked at the PG source).
-Mark.
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2008-01-01 01:40:11 | Re: Duplicate values found when reindexing unique index |
Previous Message | Tom Lane | 2007-12-31 23:35:26 | Re: Duplicate values found when reindexing unique index |