Re: BUG #11637: SQL function volatility is ignored on index creation

On Oct 10, 2014, at 12:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> will@willglynn.com writes:
>> The CREATE INDEX documentation and the wording of that error message
>> strongly imply that it's a function's volatility *marking* that is
>> important, but in at least some situations, PostgreSQL ignores
>> pg_proc.provolatile='v' and allows index creation anyway if the function is
>> in fact immutable. I suspect this qualifier is ignored because the SQL
>> function call is being inlined prior to the CheckMutability() in
>> ComputeIndexAttrs(); there isn't a volatile function call, just an immutable
>> expression.
>
> That's correct, and it's intentional behavior, not a bug (cf commit
> 5a86e5e1930d95f495a134000512d6ca22064338).  Refusing the CREATE would
> just be pedantry AFAICS.

Since this is a feature, my proposed solution #2 -- updating the
documentation to reflect this behavior -- seems appropriate.

The docs and my previous exposure to this error message made me think that
the IMMUTABLE marking was the key requirement, so when I saw a VOLATILE
function getting used in an index, it surprised me enough that I went digging
into the source to find out how that can happen.

Also, FWIW I've come to value that PostgreSQL errs on the side of pedantry.
I genuinely didn't expect CREATE INDEX to peer into a VOLATILE function and
silently deduce that it's actually okay to use anyway. I'm not arguing that
this is wrong, it's just... more clever than I thought.

Hmm... could CREATE FUNCTION do a similar analysis and notify you if you're
creating a VOLATILE function that doesn't actually need to be VOLATILE?

--Will Glynn