Re: Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>
>> On 02/01/2016 11:17 AM, Dane Foster wrote:
>>
>>> Hello,
>>>
>>> I'm discovering that I need to write quite a few functions for use
>>> strictly w/ check constraints and I'm wondering if declaring the
>>> volatility category for said functions will affect their behavior when
>>> invoked by PostgreSQL's check constraint mechanism.
>>>
>>
> ​Adrian's point is spot-on but the important thing to consider in this
> situation is that check constraints are assumed to be immutable and if you
> implement a check function that is not you don't get to complain what you
> see something broken. The nature and use of an immutable check constraint
> only has a single dynamic - execute the function using the given values
> once for every record INSERT or UPDATE. There is no reason, and I suspect
> there is no actual, attempt to even look at the volatility category of said
> function before performing those actions. It is possible that two records
> inserted or updated in the same query could make use of the caching
> possibilities afforded by immutable functions but if so assume it is being
> done unconditionally.
>
> David J.
>
> ​Your point about ".. check ​constraints are assumed to be immutable ..",
is that in the manual? Because I don't remember reading it in the
constraints section, nor in the volatility categories section, nor in the
server programming sections. Granted, I haven't read the whole manual yet
nor do I have what I've read so far memorized, but I think that little fact
would have struck a cord in my gray matter. So if you can point me to the
spot in the manual where this is covered I would appreciate it.​

Thanks,

Dane