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)
