Re: Check constraints and function volatility categories

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.