Обсуждение: BUG #8046: PL/pgSQL plan caching regression
The following bug has been logged on the website: Bug reference: 8046 Logged by: Dmitriy Igrishin Email address: dmitigr@gmail.com PostgreSQL version: 9.2.4 Operating system: Linux Debian Wheezy x64 Description: = -- -*- sql -*- -- A bug test case. -- PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit BEGIN; CREATE OR REPLACE FUNCTION rec(type_name_ regclass, id_ bigint) RETURNS record LANGUAGE plpgsql STABLE AS $function$ DECLARE r_ record; BEGIN EXECUTE 'SELECT * FROM '||type_name_::text||' WHERE id =3D $1' INTO r_ USING id_; RAISE NOTICE '%', pg_typeof(r_.id); RETURN r_; END; $function$; CREATE TABLE t1 (id integer); CREATE TABLE t2 (id bigint); SELECT rec('t1', 1); -- NOTICE: integer SELECT rec('t2', 2); -- Should NOTICE: bigint, but RAISE ERROR: type of parameter 5 (bigint) does not match that when preparing the plan (integer) ROLLBACK;
dmitigr@gmail.com writes: > CREATE OR REPLACE FUNCTION rec(type_name_ regclass, id_ bigint) > RETURNS record > LANGUAGE plpgsql > STABLE > AS $function$ > DECLARE > r_ record; > BEGIN > EXECUTE 'SELECT * FROM '||type_name_::text||' WHERE id = $1' > INTO r_ USING id_; > RAISE NOTICE '%', pg_typeof(r_.id); > RETURN r_; > END; > $function$; > CREATE TABLE t1 (id integer); > CREATE TABLE t2 (id bigint); > SELECT rec('t1', 1); -- NOTICE: integer > SELECT rec('t2', 2); -- Should NOTICE: bigint, but RAISE ERROR: type of > parameter 5 (bigint) does not match that when preparing the plan (integer) What's your grounds for calling that a regression? It's always worked like that, or at least back to 8.4 which is as far as I checked (since pg_typeof didn't exist before that). The fine manual documents the problem thus: The mutable nature of record variables presents another problem in this connection. When fields of a record variable are used in expressions or statements, the data types of the fields must not change from one call of the function to the next, since each expression will be analyzed using the data type that is present when the expression is first reached. EXECUTE can be used to get around this problem when necessary. We might think of a nicer solution sooner or later, but don't hold your breath (and don't expect it to be back-patched into released branches). regards, tom lane
2013/4/9 Tom Lane <tgl@sss.pgh.pa.us> > dmitigr@gmail.com writes: > > CREATE OR REPLACE FUNCTION rec(type_name_ regclass, id_ bigint) > > RETURNS record > > LANGUAGE plpgsql > > STABLE > > AS $function$ > > DECLARE > > r_ record; > > BEGIN > > EXECUTE 'SELECT * FROM '||type_name_::text||' WHERE id = $1' > > INTO r_ USING id_; > > > RAISE NOTICE '%', pg_typeof(r_.id); > > > RETURN r_; > > END; > > $function$; > > > CREATE TABLE t1 (id integer); > > CREATE TABLE t2 (id bigint); > > > SELECT rec('t1', 1); -- NOTICE: integer > > SELECT rec('t2', 2); -- Should NOTICE: bigint, but RAISE ERROR: type of > > parameter 5 (bigint) does not match that when preparing the plan > (integer) > > What's your grounds for calling that a regression? It's always worked > like that, or at least back to 8.4 which is as far as I checked (since > pg_typeof didn't exist before that). The fine manual documents the > problem thus: > > The mutable nature of record variables presents another problem > in this connection. When fields of a record variable are used in > expressions or statements, the data types of the fields must not > change from one call of the function to the next, since each > expression will be analyzed using the data type that is present > when the expression is first reached. EXECUTE can be used to get > around this problem when necessary. > Oops, I am sorry, it's documented indeed. It was too late tomorrow and I was sure that variables (including record variables) are function-scoped, rather than session-scoped. (Which is natural.) So I was confused. -- // Dmitriy.