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
