Thread: 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.