BUG #8630: Planner behavior change between PG 9.0.14 and 9.3.1

On 11/25/2013 04:56 PM, v.langard@auriga.fr wrote:

The following bug has been logged on the website:

Bug reference: 8630
Logged by: Vince
Email address: v.langard@auriga.fr
PostgreSQL version: 9.3.1
Operating system: Windows 8
Description:

Using PG 9.0.14, this code returns no error:

create table t_dummy as (
select
null::integer as int_value,
null::timestamp as ts_value
);

create or replace function test(in_text character varying)
returns void as
$$
begin
update t_dummy set
ts_value = in_text::timestamp, -- OK
int_value = in_text::integer -- ERROR
where false;
end;
$$
language plpgsql volatile security definer;

select test('dummy');

Using PG 9.3.1, the code returns error: invalid input syntax for integer:
"dummy"

It seems that the planner try to cast the dummy value, although update
should never be executed. "analyse" only raise error too, and there's no
error for timestamp casting.

The change in behavior was caused by the following commit during the 9.2
cycle two years ago. Not sure what to do about it.

commit e6ed34f70d57d102da8383919e0046c577d317e7
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri Sep 16 12:31:23 2011 -0400

Ensure generic plan gets used for a plpgsql expression with no
parameters.

Now that a NULL ParamListInfo pointer causes significantly different
behavior in plancache.c, be sure to pass it that way when the expression
is known not to reference any plpgsql variables. Saves a few setup
cycles anyway.

--
Vik

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs