BUG #8046: PL/pgSQL plan caching regression

[d***@gmail.com]

The following bug has been logged on the website:

Bug reference: 8046
Logged by: Dmitriy Igrishin
Email address: ***@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 = $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;

[Tom Lane]

Post by d***@gmail.com
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

[Dmitriy Igrishin]

Post by d***@gmail.com

Post by d***@gmail.com
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
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.