Re: plpgsql versus domains

2015-03-03 20:32 GMT+01:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> I wrote:
> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >> On Thu, Feb 26, 2015 at 1:53 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >>> To some extent this is a workaround for the fact that plpgsql does type
> >>> conversions the way it does (ie, by I/O rather than by using the
> parser's
> >>> cast mechanisms). We've talked about changing that, but people seem to
> >>> be afraid of the compatibility consequences, and I'm not planning to
> fight
> >>> two compatibility battles concurrently ;-)
>
> >> A sensible plan, but since we're here:
>
> >> - I do agree that changing the way PL/pgsql does those type
> >> conversions is scary. I can't predict what will break, and there's no
> >> getting around the scariness of that.
>
> >> - On the other hand, I do think it would be good to change it, because
> >> this sucks:
>
> >> rhaas=# do $$ declare x int; begin x := (3.0::numeric)/(1.0::numeric);
> end $$;
> >> ERROR: invalid input syntax for integer: "3.0000000000000000"
> >> CONTEXT: PL/pgSQL function inline_code_block line 1 at assignment
>
> > If we did want to open that can of worms, my proposal would be to make
> > plpgsql type conversions work like so:
> > * If there is a cast pathway known to the core SQL parser, use that
> > mechanism.
> > * Otherwise, attempt to convert via I/O as we do today.
> > This seems to minimize the risk of breaking things, although there would
> > probably be corner cases that work differently (for instance I bet
> boolean
> > to text might turn out differently). In the very long run we could
> perhaps
> > deprecate and remove the second phase.
>
> Since people didn't seem to be running away screaming, here is a patch to
> do that. I've gone through the list of existing casts, and as far as I
> can tell the only change in behavior in cases that would have worked
> before is the aforementioned boolean->string case. Before, if you made
> plpgsql convert a bool to text, you got 't' or 'f', eg
>
> regression=# do $$declare t text; begin t := true; raise notice 't = %',
> t; end $$;
> NOTICE: t = t
> DO
>
> Now you get 'true' or 'false', because that's what the cast does, eg
>
> regression=# do $$declare t text; begin t := true; raise notice 't = %',
> t; end $$;
> NOTICE: t = true
> DO
>
> This seems to me to be a narrow enough behavioral change that we could
> tolerate it in a major release. (Of course, maybe somebody out there
> thinks that failures like the one Robert exhibits are a feature, in
> which case they'd have a rather longer list of compatibility issues.)
>
> The performance gain is fairly nifty. I tested int->bigint coercions
> like this:
>
> do $$
> declare b bigint;
> begin
> for i in 1 .. 1000000 loop
> b := i;
> end loop;
> end$$;
>
> On my machine, in non-cassert builds, this takes around 750 ms in 9.4,
> 610 ms in HEAD (so we already did something good, I'm not quite sure
> what), and 420 ms with this patch. Another example is a no-op domain
> conversion:
>
> create domain di int;
>
> do $$
> declare b di;
> begin
> for i in 1 .. 1000000 loop
> b := i;
> end loop;
> end$$;
>
> This takes about 760 ms in 9.4, 660 ms in HEAD, 380 ms with patch.
>
> Comments?
>

it is perfect,

thank you very much for this

Regards

Pavel Stehule

>
> regards, tom lane
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>