From: | Will Glynn <will(at)willglynn(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #11637: SQL function volatility is ignored on index creation |
Date: | 2014-10-10 18:35:10 |
Message-ID: | BB05E0E5-EA46-4C08-8FF7-F59AAB52D4AA@willglynn.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Oct 10, 2014, at 12:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> will(at)willglynn(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2014-10-10 19:14:01 | Re: BUG #11638: Transaction safety fails when constraints are dropped and analyze is done |
Previous Message | Alon | 2014-10-10 18:32:48 | Re: Re: [BUGS] Re: [BUGS] Re: BUG #11431: Failing to backup and restore a Windows postgres database, with Norwegian Bokmål locale. |