Обсуждение: BUG #11425: Trigger UDF doesn’t properly preserve tuple’s descriptors after "add/drop column" on target table.
BUG #11425: Trigger UDF doesn’t properly preserve tuple’s descriptors after "add/drop column" on target table.
От
vz186002@teradata.com
Дата:
The following bug has been logged on the website: Bug reference: 11425 Logged by: Van Zhong Email address: vz186002@teradata.com PostgreSQL version: 9.3.4 Operating system: Ubento Description: Trigger function doesn’t properly preserve tuple’s metadata (rec type) when "add/drop column" on target table in same session. We may see incorrect rec type processed or invalid type check failure, but re-login a session everything goes well. Such issue could be found in PG9.0 9.1 9.2 9.3 and 8.4 > postgres=# insert into public.t values (1,'test',now()); > NOTICE: (1,test,"2014-09-15 14:15:09.365891") > NOTICE: (1,test,"2014-09-15 14:15:09.365891") > INSERT 0 1 > postgres=# insert into public.t values (2,'test',now()); > NOTICE: (2,test,"2014-09-15 14:15:09.386596") > NOTICE: (2,test,"2014-09-15 14:15:09.386596") > INSERT 0 1 > postgres=# > postgres=# alter table public.t add column c3 int; > ALTER TABLE > postgres=# > postgres=# insert into public.t values (3,'test',now(),1); > NOTICE: (3,test,"2014-09-15 14:15:09.405794",1) > NOTICE: (3,test,"2014-09-15 14:15:09.405794",) > INSERT 0 1 > postgres=# > postgres=# /* you will see new_rec is missing t.c3 for the new inserted */ > postgres-# select new_rec from public.undo_t; > new_rec > ---------------------------------------- > (1,test,"2014-09-15 14:15:09.365891",) > (2,test,"2014-09-15 14:15:09.386596",) > (3,test,"2014-09-15 14:15:09.405794",) > (3 rows) > > postgres=# > postgres=# \q > beehive@personal-vm-zhongzhou:~> psql postgres > psql (8.4.58) > Type "help" for help. > > /* exit session and reinsert a tuple, new_rec.c3 can be seen in new inserted tuple */ > insert into public.t values (4,'test',now(),1); > > postgres=# > postgres=# /* exit session and reinsert a tuple, new_rec.c3 can be seen in new inserted tuple */ > postgres-# insert into public.t values (4,'test',now(),1); > NOTICE: (4,test,"2014-09-15 14:15:09.493459",1) > NOTICE: (4,test,"2014-09-15 14:15:09.493459",1) > INSERT 0 1 > postgres=# > postgres=# select new_rec from public.undo_t; > new_rec > ----------------------------------------- > (1,test,"2014-09-15 14:15:09.365891",) > (2,test,"2014-09-15 14:15:09.386596",) > (3,test,"2014-09-15 14:15:09.405794",) > (4,test,"2014-09-15 14:15:09.493459",1) > (4 rows) > > postgres=# > postgres=# alter table t drop column c2; > ALTER TABLE > postgres=# > postgres=# /* you will see error here */ > postgres-# insert into public.t values (5,now(),1); > NOTICE: (5,"2014-09-15 14:15:09.555769",1) > ERROR: invalid input syntax for type timestamp: "1" > CONTEXT: PL/pgSQL function "undo_t_trace" line 17 at assignment > postgres=# > postgres=# \q > beehive@personal-vm-zhongzhou:~> psql postgres > psql (8.4.58) > Type "help" for help. > > > postgres=# > postgres=# /* without error after relogin a session */ > postgres-# insert into public.t values (5,now(),1); > NOTICE: (5,"2014-09-15 14:15:09.602766",1) > NOTICE: (5,"2014-09-15 14:15:09.602766",1) > INSERT 0 1 > postgres=# select new_rec from public.undo_t; > new_rec > ------------------------------------ > (1,"2014-09-15 14:15:09.365891",) > (2,"2014-09-15 14:15:09.386596",) > (3,"2014-09-15 14:15:09.405794",) > (4,"2014-09-15 14:15:09.493459",1) > (5,"2014-09-15 14:15:09.602766",1) > (5 rows)