Re: Division by zero

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.

--
Sam http://samason.me.uk/