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

Attach the reproducing script and its output

Zhou Regards!

-----Original Message-----
From: Zhong, Van
Sent: Monday, September 15, 2014 6:50 PM
To: pgsql-bugs(at)postgresql(dot)org
Subject: 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(at)teradata(dot)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(at)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",) <<<<<<<<<<<< Incorrect rec populated, but goes well after re-login
> (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 <<<<<<<<<<<<<<< error, but goes well after re-login
> postgres=#
> postgres=# \q
> beehive(at)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)