Thread: Operators based on non-IMMUTABLE functions

I've recently run into a problem with a datatype whose operators are
based on functions not marked IMMUTABLE. Although there might be good
reasons to have such a thing, it seems like it might be a valuable
warning message if you create an operator based on an non-IMMUTABLE
function. Comments?

- Josh / eggyknap

On Thu, 2009-03-05 at 11:27 -0700, Joshua Tolley wrote:
> I've recently run into a problem with a datatype whose operators are
> based on functions not marked IMMUTABLE. Although there might be good
> reasons to have such a thing, it seems like it might be a valuable
> warning message if you create an operator based on an non-IMMUTABLE
> function. Comments?
> 

When I do:

select oprname, oprcode, provolatile from pg_operator , pg_proc where
pg_proc.oid::regclass = oprcode and provolatile <> 'i';

There are a bunch of operators related to TIMESTAMPTZ and full text
search that are marked as STABLE.

I don't know what the guidelines are for using a WARNING, but the
examples that come to mind are generally things that can be fixed. For
instance, if you get a WARNING for using non-standard backslash escapes,
you can fix it by using E''.

However, I agree that forgetting to mark functions correctly is a pretty
significant problem.

Regards,Jeff Davis

Joshua Tolley <eggyknap@gmail.com> writes:
> I've recently run into a problem with a datatype whose operators are
> based on functions not marked IMMUTABLE. Although there might be good
> reasons to have such a thing, it seems like it might be a valuable
> warning message if you create an operator based on an non-IMMUTABLE
> function. Comments?

No, it wouldn't be a good idea.  There are plenty of such operators.
        regards, tom lane