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

From: Vik Fearing <vik(dot)fearing(at)dalibo(dot)com>
To: Vincent Langard <v(dot)langard(at)auriga(dot)fr>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #8630: Planner behavior change between PG 9.0.14 and 9.3.1
Date: 2013-11-25 22:07:20
Message-ID: 5293CA18.50105@dalibo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Patrick Lademan 2013-11-25 23:09:25 Concat truncates at 257 characters
Previous Message bricklen 2013-11-25 21:42:06 Re: BUG #8629: Strange resultset when using CTE or a subselect