On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 02/01/2016 12:36 PM, David G. Johnston wrote:
On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster <studdugie@xxxxxxxxx
<mailto:studdugie@xxxxxxxxx>>wrote:
On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston
<david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx>> wrote:
On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>>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@xxxxxxxxxxxxx
Thanks for the heads up. The good news is all machine access to the data will be via functions and views so I can inline the constraint in the right places. In other news, this sucks! I have no idea what it would take to implement a more flexible constraint mechanism where these types of dependencies can be expressed declaratively but it would be great if someone w/ the know-how did. As is evident by the fact that I wasn't the only one to not realize the rabbit hole I was heading down, it would be a useful feature.
As always thanks for setting me straight,
Dane
