Re: plpgsql: numeric assignment to an integer variable errors out

Hello

I tested patch v2.0, and I thing, so this patch should be used as bug
fix. It has same or little bit better speed than current and solve
some problems with numeric's implicit casting in plpgsql. But this is
only an half solution.

The core of problem is in lazy casting of plpgsql. We need to modify
execution plan for pl expression when result is different than
destination type. For almost expression we know destination type.

there is some sample of effectiveness:
postgres=# create or replace function test1()
returns int as $$
declare s int := 0;
begin
for i in 1..100000 loop
s := 4e3; -- numeric
end loop;
return s;
end;
$$ language plpgsql immutable;
CREATE FUNCTION
Time: 5,140 ms
postgres=# create or replace function test2()
returns int as $$
declare s int := 0;
begin
for i in 1..100000 loop
s := 4e3::int;
end loop;
return s;
end;
$$ language plpgsql immutable;
CREATE FUNCTION
Time: 416,240 ms
postgres=# select test1();
test1
-------
4000
(1 row)

Time: 161,048 ms
postgres=# select test2();
test2
-------
4000
(1 row)

Time: 68,110 ms
postgres=# select test1();
test1
-------
4000
(1 row)

Time: 171,020 ms
postgres=# select test2();
test2
-------
4000
(1 row)

Time: 61,771 ms
postgres=#

Regards
Pavel Stehule

2009/1/22 Bruce Momjian <bruce(at)momjian(dot)us>:
> Nikhil Sontakke wrote:
>> > > PFA, patch which uses find_coercion_pathway to find a direct
>> > > COERCION_PATH_FUNC function and uses that if it is available. Or is there
>> > a
>> > > better approach? Seems to handle the above issue with this patch.
>> >
>> > +1
>> >
>> > I thing, so some values should by cached, current patch could by slow.
>>
>>
>> Agreed, it can slow things down a bit especially since we are only
>> interested in the COERCION_PATH_FUNC case. What we need is a much simpler
>> pathway function which searches in the SysCache and returns back with the
>> valid/invalid castfunc immediately.
>>
>> PFA, version 2.0 of this patch with these changes in place. I could have
>> added a generic function in parse_coerce.c, but thought the use case was
>> restricted to plpgsql and hence I have kept it within pl_exec.c for now.
>
> Where are we on this? 8.5?
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>