BUG #11425: Trigger UDF doesn’t properly preserve tuple’s descriptors after "add/drop column" on target table.

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)

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs