Re: Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster <studdugie(at)gmail(dot)com> wrote:

>
> 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.​
>
>
>
​http://www.postgresql.org/docs/9.5/interactive/sql-createtable.html​
​Second Paragraph​

​"""​
CHECK ( expression ) [ NO INHERIT ]
The CHECK clause specifies an expression producing a Boolean result which
new or updated rows must satisfy for an insert or update operation to
succeed. Expressions evaluating to TRUE or UNKNOWN succeed. Should any row
of an insert or update operation produce a FALSE result, an error exception
is raised and the insert or update does not alter the database. A check
constraint specified as a column constraint should reference that column's
value only, while an expression appearing in a table constraint can
reference multiple columns.

Currently, CHECK expressions cannot contain subqueries nor refer to
variables other than columns of the current row. The system column tableoid
may be referenced, but not any other system column.

A constraint marked with NO INHERIT will not propagate to child tables.

When a table has multiple CHECK constraints, they will be tested for each
row in alphabetical order by name, after checking NOT NULL constraints.
(PostgreSQL versions before 9.5 did not honor any particular firing order
for CHECK constraints.)
​"""

While you've managed to fool the system by wrapping your query into a
function you've violated the documented restrictions and so any breakage is
on you - not the system.

Also, consider that at the time you insert a row the check constraint
passes but then you alter the other table so that, if you tried to insert
the row again it would fail. Since check constraints are only evaluated
upon INSERT/UPDATE of the data on the same table you would have a violation.

So, while the documentation doesn't explicitly say that functions used in
CHECK must be IMMUTABLE that is what it all boils down to when you put all
of these things together.

David J.