From: | Dane Foster <studdugie(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Check constraints and function volatility categories |
Date: | 2016-02-01 20:52:12 |
Message-ID: | CA+WxinKcyVGdQ0MwFSccXpH-wjTWWSjWa2abAu6iLo3Ydyrqvg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
> On 02/01/2016 12:36 PM, David G. Johnston wrote:
>
>> On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster <studdugie(at)gmail(dot)com
>> <mailto: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 <mailto: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 <mailto: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.
>>
>
> As an example of where this leads see:
>
> http://www.postgresql.org/message-id/7224.1452275604@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
From | Date | Subject | |
---|---|---|---|
Next Message | Yu Nie | 2016-02-01 21:03:31 | Re: strange sql behavior |
Previous Message | Karsten Hilbert | 2016-02-01 20:50:10 | Re: Check constraints and function volatility categories |