Re: Division by zero

2009/8/2 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote:
>> 2009/8/2 Sam Mason <sam(at)samason(dot)me(dot)uk>:
>> > On Sun, Aug 02, 2009 at 12:08:28PM +0100, Oliver Kohll - Mailing Lists wrote:
>> >> CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer
>> >> AS 'SELECT $1 / NULLIF($2,0);'
>> >> LANGUAGE SQL
>> >> IMMUTABLE
>> >> RETURNS NULL ON NULL INPUT;
>> >
>> > If I were you I'd remove the "RETURNS NULL ON NULL INPUT" flag. I used
>> > to think of it as just a "hint" to the planner as to its behavior,
>> > but it turns out that it's interpreted much more strongly by PG. The
>> > interpretation means that the function doesn't end up getting be inlined
>> > where I'd expect it to be and hence the optimizer doesn't get as much
>> > freedom to rewrite your queries as you may want.
>>
>> I thing, it's not true - RETURNS NULL ON NULL INPUT is equal to STRICT
>> flag, and it means, don't run function, when any param is null.
>
> Yes, this is how PG interprets it.
>
>> For
>> optimalisator it means only one - when any parameter is constant NULL,
>> then function evaluation should be replaced by NULL. But not too much
>> often optimalizer should detect this case, so this is shortcut for
>> evaluator.  This flag doesn't change inlining.
>
> No, not unless things have changed since this discussion:
>
>  http://archives.postgresql.org/message-id/20090604090045.GR5407@samason.me.uk
>
>> > Admittedly it's going to be less of an issue with division that other
>> > operators, but it's worth bearing in mind.  The "IMMUTABLE" options is a
>> > good one to specify though, keep that!
>>
>> There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug
>
> Not in any tests I've done.

I did it - and in this case immutable is wrong and strict not. It's an
new for me, because I used rules that are well only for plpgsql or C
language. What I see now, the rules for sql are totally different.

Pavel

>
> --
>  Sam  http://samason.me.uk/
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>