From: | "Zhong, Van" <Van(dot)Zhong(at)Teradata(dot)com> |
---|---|
To: | "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | RE: BUG #11425: Trigger UDF doesn’t properly preserve tuple’s descriptors after "add/drop column" on target table. |
Date: | 2014-09-15 10:55:36 |
Message-ID: | 2E65C78C2537F84088A3E82A10E744711620F576@SUSHDC8002.TD.TERADATA.COM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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)
Attachment | Content-Type | Size |
---|---|---|
reproducing_script.output | application/octet-stream | 7.4 KB |
reproducing_script.sql | application/octet-stream | 3.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | asimantov | 2014-09-15 21:56:46 | BUG #11430: Failing to backup and restore a Windows postgres database, with Norwegian Bokmål locale. |
Previous Message | vz186002 | 2014-09-15 10:49:50 | BUG #11425: Trigger UDF doesn’t properly preserve tuple’s descriptors after "add/drop column" on target table. |