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

On 11/25/2013 04:56 PM, v(dot)langard(at)auriga(dot)fr wrote:
> The following bug has been logged on the website:
>
> Bug reference: 8630
> Logged by: Vince
> Email address: v(dot)langard(at)auriga(dot)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(at)sss(dot)pgh(dot)pa(dot)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