From: | vz186002(at)teradata(dot)com |
---|---|
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. |
Date: | 2014-09-15 10:49:50 |
Message-ID: | 20140915104950.2485.64205@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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",)
> (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(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)
From | Date | Subject | |
---|---|---|---|
Next Message | Zhong, Van | 2014-09-15 10:55:36 | RE: BUG #11425: Trigger UDF doesn’t properly preserve tuple’s descriptors after "add/drop column" on target table. |
Previous Message | Stephen Frost | 2014-09-14 01:56:15 | Re: BUG #11350: ALTER SYSTEM is not DDL? |