Thread: BUG #3847: plpython trigger caches table structure - doesn't see new / changed columns

The following bug has been logged online:

Bug reference:      3847
Logged by:          Mark Reid
Email address:      reid.write@gmail.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.

"Mark Reid" <reid.write@gmail.com> writes:
> 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:

The cases you are saying work and don't work are exactly the same:

> -- 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;

Please be clearer.

            regards, tom lane

The trigger function does not recognize the "test4" column the second time
it is added - the update throws an error.

On Jan 1, 2008 11:17 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Mark Reid" <reid.write@gmail.com> writes:
> > 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:
>
> The cases you are saying work and don't work are exactly the same:
>
> > -- 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;
>
> Please be clearer.
>
>                        regards, tom lane
>

"Mark Reid" <reid.write@gmail.com> writes:
> The trigger function does not recognize the "test4" column the second time
> it is added - the update throws an error.

Try this patch:
http://archives.postgresql.org/pgsql-committers/2008-01/msg00016.php

            regards, tom lane

Works perfectly on my test case.  Thanks!

On Jan 1, 2008 8:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Mark Reid" <reid.write@gmail.com> writes:
> > The trigger function does not recognize the "test4" column the second
> time
> > it is added - the update throws an error.
>
> Try this patch:
> http://archives.postgresql.org/pgsql-committers/2008-01/msg00016.php
>
>                        regards, tom lane
>

We've run our real-life test cases and they've all worked properly using
this patch too.  Thanks again.
-Mark.

On Jan 1, 2008 8:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Mark Reid" <reid.write@gmail.com> writes:
> > The trigger function does not recognize the "test4" column the second
> time
> > it is added - the update throws an error.
>
> Try this patch:
> http://archives.postgresql.org/pgsql-committers/2008-01/msg00016.php
>
>                        regards, tom lane
>