On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 02/01/2016 12:36 PM, David G. Johnston wrote: > >> On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster <studdu...@gmail.com >> <mailto:studdu...@gmail.com>>wrote: >> >> >> On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston >> <david.g.johns...@gmail.com <mailto:david.g.johns...@gmail.com>> >> wrote: >> >> On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver >> <adrian.kla...@aklaver.com <mailto:adrian.kla...@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.1452275...@sss.pgh.pa.us > > 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
