Re: Check constraints and function volatility categories

On 02/01/2016 12:36 PM, David G. Johnston wrote:
> On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster <studdugie@gmail.com
> <mailto:studdugie@gmail.com>>wrote:
>
>
>     On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston
>     <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
>         On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver
>         <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.

As an example of where this leads see:

http://www.postgresql.org/message-id/7224.1452275604@sss.pgh.pa.us

>
> 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.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com