BUG #11283: PostgreSQL bug? function's variable type cached not flushed within session when the type altered.

From: digoal(at)126(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #11283: PostgreSQL bug? function's variable type cached not flushed within session when the type altered.
Date: 2014-08-28 08:39:29
Message-ID: 20140828083929.2534.84054@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: 11283
Logged by: digoal
Email address: digoal(at)126(dot)com
PostgreSQL version: 9.3.4
Operating system: CentOS 6.5 x64
Description:

PostgreSQL bug? function's variable type cached not flushed within session
when the type altered.
see my test :
create table public.t (
c1 int primary key,
c2 text,
crt_time timestamp
);

CREATE TABLE public.undo_t (
id serial8 primary key,
xid int8,
relid oid,
table_schema text,
table_name text,
when_tg text,
level text,
op text,
encoding name,
old_rec public.t,
new_rec public.t,
crt_time timestamp without time zone DEFAULT now(),
username text,
client_addr inet,
client_port int
);

CREATE OR REPLACE FUNCTION public.undo_t_trace()
RETURNS trigger
LANGUAGE plpgsql
AS $BODY$
DECLARE
v_new_rec public.t; -- type cached in session, don't modified when table
t altered. must reopen an session.
v_old_rec public.t; -- type cached in session, don't modified when table
t altered. must reopen an session.
v_username text := session_user;
v_client_addr inet := inet_client_addr();
v_client_port int := inet_client_port();
v_xid bigint := txid_current();
v_encoding name := pg_client_encoding();
BEGIN
case TG_OP
when 'DELETE' then
v_old_rec := OLD;
insert into public.undo_t (xid, relid, table_schema, table_name,
when_tg, level, op, encoding, old_rec, username, client_addr, client_port)
values (v_xid, tg_relid, tg_table_schema, tg_table_name, tg_when,
tg_level, tg_op, v_encoding, v_old_rec, v_username, v_client_addr,
v_client_port);
when 'INSERT' then
raise notice '%', NEW; -- OK
v_new_rec := NEW;
raise notice '%', v_new_rec; -- use cached type. not OK.
insert into public.undo_t (xid, relid, table_schema, table_name,
when_tg, level, op, encoding, new_rec, username, client_addr, client_port)
values (v_xid, tg_relid, tg_table_schema, tg_table_name, tg_when,
tg_level, tg_op, v_encoding, v_new_rec, v_username, v_client_addr,
v_client_port);
when 'UPDATE' then
v_old_rec := OLD;
v_new_rec := NEW;
insert into public.undo_t (xid, relid, table_schema, table_name,
when_tg, level, op, encoding, old_rec, new_rec, username, client_addr,
client_port)
values (v_xid, tg_relid, tg_table_schema, tg_table_name, tg_when,
tg_level, tg_op, v_encoding, v_old_rec, v_new_rec, v_username,
v_client_addr, v_client_port);
when 'TRUNCATE' then
insert into public.undo_t (xid, relid, table_schema, table_name,
when_tg, level, op, encoding, old_rec, username, client_addr, client_port)
select v_xid, tg_relid, tg_table_schema, tg_table_name, tg_when,
tg_level, tg_op, v_encoding, t, v_username, v_client_addr, v_client_port
from public.t;
else
return null;
end case;
RETURN null;
END;
$BODY$ strict volatile;

CREATE TRIGGER tg1 AFTER DELETE or INSERT or UPDATE ON public.t FOR EACH ROW
EXECUTE PROCEDURE undo_t_trace();
CREATE TRIGGER tg2 BEFORE TRUNCATE ON public.t FOR EACH STATEMENT EXECUTE
PROCEDURE undo_t_trace();

insert into public.t values (1,'test',now());
insert into public.t values (2,'test',now());

postgres=# select * from public.t;
c1 | c2 | crt_time
----+------+----------------------------
1 | test | 2014-08-28 16:14:34.548555
2 | test | 2014-08-28 16:14:35.194634
(2 rows)

postgres=# select new_rec from public.undo_t;
new_rec
---------------------------------------
(1,test,"2014-08-28 16:14:34.548555")
(2,test,"2014-08-28 16:14:35.194634")
(2 rows)

postgres=# alter table public.t add column c3 int;
ALTER TABLE

postgres=# insert into public.t values (3,'test',now(),1);
NOTICE: (3,test,"2014-08-28 16:15:58.394688",1)
NOTICE: (3,test,"2014-08-28 16:15:58.394688",)
INSERT 0 1
postgres=# select * from public.t;
c1 | c2 | crt_time | c3
----+------+----------------------------+----
1 | test | 2014-08-28 16:14:34.548555 |
2 | test | 2014-08-28 16:14:35.194634 |
3 | test | 2014-08-28 16:15:58.394688 | 1
(3 rows)

postgres=# select new_rec from public.undo_t;
new_rec
----------------------------------------
(1,test,"2014-08-28 16:14:34.548555",)
(2,test,"2014-08-28 16:14:35.194634",)
(3,test,"2014-08-28 16:15:58.394688",)
(3 rows)

postgres=# \q
postgres(at)192_168_173_203-> psql
psql (9.3.4)
Type "help" for help.
postgres=# select new_rec from public.undo_t;
new_rec
----------------------------------------
(1,test,"2014-08-28 16:14:34.548555",)
(2,test,"2014-08-28 16:14:35.194634",)
(3,test,"2014-08-28 16:15:58.394688",)
(3 rows)

postgres=# insert into public.t values (4,'test',now(),1);
NOTICE: (4,test,"2014-08-28 16:16:45.857712",1)
NOTICE: (4,test,"2014-08-28 16:16:45.857712",1)
INSERT 0 1
postgres=# select new_rec from public.undo_t;
new_rec
-----------------------------------------
(1,test,"2014-08-28 16:14:34.548555",)
(2,test,"2014-08-28 16:14:35.194634",)
(3,test,"2014-08-28 16:15:58.394688",)
(4,test,"2014-08-28 16:16:45.857712",1)
(4 rows)

postgres=# alter table t drop column c2;
ALTER TABLE
postgres=# \set VERBOSITY verbose

postgres=# insert into public.t values (5,now(),1);
NOTICE: 00000: (5,"2014-08-28 16:17:20.078688",1)
LOCATION: exec_stmt_raise, pl_exec.c:3041
ERROR: 22007: invalid input syntax for type timestamp: "1"
CONTEXT: PL/pgSQL function undo_t_trace() line 18 at assignment
LOCATION: DateTimeParseError, datetime.c:3567

Browse pgsql-bugs by date

  From Date Subject
Next Message m.woehling 2014-08-28 21:37:51 BUG #11304: UNION query with NULL values fails
Previous Message Noah Misch 2014-08-28 01:40:30 Re: [BUGS] Re: BUG #9555: pg_dump for tables with inheritance recreates the table with the wrong order of columns