Re: [GENERAL] Check constraints and function volatility categories

[Dane Foster]

On Mon, Feb 1, 2016 at 4:48 PM, Adrian Klaver 
wrote:

>
>
>>  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
>>
>>
>> I could see moving your constraint into a per row trigger.
>>
>>
>> You'd need to basically replicate the current FK constraint setup but
>> with custom queries...you need the insert/update trigger on the main
>> table and then a insert/update/delete trigger on the referenced table to
>> ensure that actions just rejected if the relevant detail on the main
>> table isn't changed.  Then decide whether you need something like "ON
>> UPDATE/DELETE CASCADE" instead of the default 'reject' behavior.
>>
>> I take it you would need to ensure that these triggers are disabled
>> during dump/restore but am not certain on that point.
>>
>
> Well this brings up another part to Danes post(that contained the function
> definition):
>
> "Unfortunately the "type" definition can't be expressed as a primary key
> so I can't use foreign keys to enforce consistency."
>
> Not sure what exactly is meant by "type", though I suspect it is this:
> "SELECT type FROM discount_codes WHERE code ..."
>
​Type in the context that I'm using it simply means the type of thing the
function is checking to see exists​ or not. The example I gave
unfortunately had a column named type that confuses the situation but in
actually that example is the only one that actually has a column named type
involved in evaluation.



> FYI, I know type is non-reserved word, but I would avoid using it as a
> column name. I went down that path and got myself confused in a hurry:)
>
> In any case it should be pointed out that FKs do not necessarily have to
> point to PKs:
>
> http://www.postgresql.org/docs/9.5/interactive/sql-createtable.html
>
> "The referenced columns must be the columns of a non-deferrable unique or
> primary key constraint in the referenced table"
>
​I am aware of the fact that FKs don't have to point to PKs but they do
have to point to something backed by a unique indexed and that is what
makes them unsuitable for my needs. Here is an example where foreign keys
can't play a role,  but this time as a description.

The system can send out surveys to solicit feedback. The admin can
customize the surveys and associate them w/ specific class events. The
constraint is this, once a survey has a respondent then the survey can no
longer be edited.

​I'm going to try out David's idea of using triggers to implement the more
complex constraints and inline the simpler ones in functions where
appropriate.

Regards,


Dane​

Re: [GENERAL] Check constraints and function volatility categories

[Adrian Klaver]

On 02/01/2016 01:23 PM, David G. Johnston wrote:

On Mon, Feb 1, 2016 at 2:11 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>>wrote:

On 02/01/2016 12:52 PM, Dane Foster wrote:

On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>
>> wrote:



 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


I could see moving your constraint into a per row trigger.


You'd need to basically replicate the current FK constraint setup but
with custom queries...you need the insert/update trigger on the main
table and then a insert/update/delete trigger on the referenced table to
ensure that actions just rejected if the relevant detail on the main
table isn't changed.  Then decide whether you need something like "ON
UPDATE/DELETE CASCADE" instead of the default 'reject' behavior.

I take it you would need to ensure that these triggers are disabled
during dump/restore but am not certain on that point.


Well this brings up another part to Danes post(that contained the 
function definition):


"Unfortunately the "type" definition can't be expressed as a primary key 
so I can't use foreign keys to enforce consistency."


Not sure what exactly is meant by "type", though I suspect it is this:
"SELECT type FROM discount_codes WHERE code ..."

FYI, I know type is non-reserved word, but I would avoid using it as a 
column name. I went down that path and got myself confused in a hurry:)


In any case it should be pointed out that FKs do not necessarily have to 
point to PKs:


http://www.postgresql.org/docs/9.5/interactive/sql-createtable.html

"The referenced columns must be the columns of a non-deferrable unique 
or primary key constraint in the referenced table"




David J.




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Check constraints and function volatility categories

[David G. Johnston]

On Mon, Feb 1, 2016 at 2:11 PM, Adrian Klaver 
wrote:

> On 02/01/2016 12:52 PM, Dane Foster wrote:
>
>> On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver > > wrote:
>>
>>
>
>> 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
>>
>
> I could see moving your constraint into a per row trigger.


You'd need to basically replicate the current FK constraint setup but with
custom queries...you need the insert/update trigger on the main table and
then a insert/update/delete trigger on the referenced table to ensure that
actions just rejected if the relevant detail on the main table isn't
changed.  Then decide whether you need something like "ON UPDATE/DELETE
CASCADE" instead of the default 'reject' behavior.

I take it you would need to ensure that these triggers are disabled during
dump/restore but am not certain on that point.

David J.

Re: [GENERAL] Check constraints and function volatility categories

[Adrian Klaver]

On 02/01/2016 12:52 PM, Dane Foster wrote:

On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote:





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


I could see moving your constraint into a per row trigger.


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




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Check constraints and function volatility categories

[Dane Foster]

On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver 
wrote:

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

Re: [GENERAL] Check constraints and function volatility categories

[Karsten Hilbert]

On Mon, Feb 01, 2016 at 12:41:30PM -0800, Adrian Klaver wrote:

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

For the record, with the help of this list I have managed to
work myself out of the corner I had painted myself into.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Check constraints and function volatility categories

[Dane Foster]

On Mon, Feb 1, 2016 at 3:36 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster  wrote:
>
>>
>> On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>> On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver <
>>> 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.
>
> 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.
>
> ​Though I understand the thinking you have applied to conclude that a
CHECK constraint is supposed to be IMMUTABLE I don't necessarily agree w/
it nor has the section you quoted made that expectation clear. Because when
I read it the first time and even again now it is not immediately apparent
that that assumption exists. But if it is true, as in, that is the intent
of the code then it should be made explicit in the documentation.

​Regards,​

​Dane​

Re: [GENERAL] Check constraints and function volatility categories

[Adrian Klaver]

On 02/01/2016 12:36 PM, David G. Johnston wrote:

On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster mailto:studdu...@gmail.com>>wrote:


On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston
mailto:david.g.johns...@gmail.com>> wrote:

On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver
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



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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Check constraints and function volatility categories

[David G. Johnston]

On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster  wrote:

>
> On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver > > 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.

Re: [GENERAL] Check constraints and function volatility categories

[Dane Foster]

On Mon, Feb 1, 2016 at 3:22 PM, Dane Foster  wrote:

>
> On Mon, Feb 1, 2016 at 2:41 PM, Adrian Klaver 
> 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.
>>>
>>> Essentially what I'm trying to figure out is if volatility categories
>>> increase or decrease the latency of executing check constraints. I've
>>> done some micro benchmarks but I have no experience benchmarking
>>> anything in PostgreSQL to trust that I'm measuring the right thing. So
>>> I'm asking the experts.
>>>
>>
>> The above is sort of backwards. You need to ask what the function does
>> and from that determine what is the most appropriate volatitity category.
>> For more detailed info see:
>>
>> http://www.postgresql.org/docs/9.5/interactive/xfunc-volatility.html
>>
>
> ​I did that already and all of the ones written so far would be STABLE.
> The gist of all of them is they check for the presence or absence of a
> particular type of thing to exist in some other table. Unfortunately the
> "type" definition can't be expressed as a primary key so I can't use
> foreign keys to enforce consistency.
> ​
>
>
>> It would help to see some samples of the actual functions.
>
> ​-- $1: The class event primary key
> -- $2: The discount code
> CREATE FUNCTION discounts_enabled_for(INT, CITXT70) RETURNS BOOLEAN AS $$
> SELECT NOT
>   CASE (SELECT type FROM discount_codes WHERE code = $2)
> WHEN 'giftcert'::DISC_CODE_TYPE THEN TRUE
> WHEN 'coupon'::DISC_CODE_TYPE
>   THEN (SELECT no_coupons FROM class_events WHERE cid = $1)
>   ELSE (SELECT no_vouchers FROM class_events WHERE cid = $1)
>   END;
> $$ LANGUAGE SQL STRICT LEAKPROOF;
> COMMENT ON FUNCTION discounts_enabled_for(INT, CITXT70) IS
> $$Determines if a class event accepts coupon or voucher discounts.$$;
>
> CREATE TABLE group_codes (
>   cid  INTEGER
>PRIMARY KEY
>REFERENCES class_events ON DELETE CASCADE ON UPDATE CASCADE,
>   code CITXT70
>NOT NULL
>REFERENCES discount_codes ON DELETE CASCADE ON UPDATE CASCADE,
>   CONSTRAINT discounts_enabled CHECK (discounts_enabled_for(cid, code))
> );
> CREATE INDEX ON group_codes USING GIN (code);
> COMMENT ON TABLE group_codes IS
> $$Discount codes that are exclusive to a set of class events.$$;
>

​I just realized there is little bug in the function body. So before anyone
gets distracted by it I wanted to let you know that I know it exists and
has been fixed internally.​

​
>

Re: [GENERAL] Check constraints and function volatility categories

[Dane Foster]

On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver 
> 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.​

Thanks,

Dane

Re: [GENERAL] Check constraints and function volatility categories

[Dane Foster]

On Mon, Feb 1, 2016 at 2:41 PM, Adrian Klaver 
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.
>>
>> Essentially what I'm trying to figure out is if volatility categories
>> increase or decrease the latency of executing check constraints. I've
>> done some micro benchmarks but I have no experience benchmarking
>> anything in PostgreSQL to trust that I'm measuring the right thing. So
>> I'm asking the experts.
>>
>
> The above is sort of backwards. You need to ask what the function does and
> from that determine what is the most appropriate volatitity category. For
> more detailed info see:
>
> http://www.postgresql.org/docs/9.5/interactive/xfunc-volatility.html
>

​I did that already and all of the ones written so far would be STABLE. The
gist of all of them is they check for the presence or absence of a
particular type of thing to exist in some other table. Unfortunately the
"type" definition can't be expressed as a primary key so I can't use
foreign keys to enforce consistency.
​


> It would help to see some samples of the actual functions.

​-- $1: The class event primary key
-- $2: The discount code
CREATE FUNCTION discounts_enabled_for(INT, CITXT70) RETURNS BOOLEAN AS $$
SELECT NOT
  CASE (SELECT type FROM discount_codes WHERE code = $2)
WHEN 'giftcert'::DISC_CODE_TYPE THEN TRUE
WHEN 'coupon'::DISC_CODE_TYPE
  THEN (SELECT no_coupons FROM class_events WHERE cid = $1)
  ELSE (SELECT no_vouchers FROM class_events WHERE cid = $1)
  END;
$$ LANGUAGE SQL STRICT LEAKPROOF;
COMMENT ON FUNCTION discounts_enabled_for(INT, CITXT70) IS
$$Determines if a class event accepts coupon or voucher discounts.$$;

CREATE TABLE group_codes (
  cid  INTEGER
   PRIMARY KEY
   REFERENCES class_events ON DELETE CASCADE ON UPDATE CASCADE,
  code CITXT70
   NOT NULL
   REFERENCES discount_codes ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT discounts_enabled CHECK (discounts_enabled_for(cid, code))
);
CREATE INDEX ON group_codes USING GIN (code);
COMMENT ON TABLE group_codes IS
$$Discount codes that are exclusive to a set of class events.$$;
​

>
>
>> I'm running PostgreSQL 9.5 on Ubuntu Linux 15.10. I don't know if this
>> matters but this is my workstation which is a pretty zippy AlienWare X51
>> w/ 16GB RAM on a Core i7-4770 processor.
>>
>> Thanks,
>>
>> Dane
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Check constraints and function volatility categories

[David G. Johnston]

On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver 
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.

Re: [GENERAL] Check constraints and function volatility categories

[Adrian Klaver]

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.

Essentially what I'm trying to figure out is if volatility categories
increase or decrease the latency of executing check constraints. I've
done some micro benchmarks but I have no experience benchmarking
anything in PostgreSQL to trust that I'm measuring the right thing. So
I'm asking the experts.


The above is sort of backwards. You need to ask what the function does 
and from that determine what is the most appropriate volatitity 
category. For more detailed info see:


http://www.postgresql.org/docs/9.5/interactive/xfunc-volatility.html

It would help to see some samples of the actual functions.



I'm running PostgreSQL 9.5 on Ubuntu Linux 15.10. I don't know if this
matters but this is my workstation which is a pretty zippy AlienWare X51
w/ 16GB RAM on a Core i7-4770 processor.

Thanks,

Dane



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Check constraints and function volatility categories

[Dane Foster]

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.

Essentially what I'm trying to figure out is if volatility categories
increase or decrease the latency of executing check constraints. I've done
some micro benchmarks but I have no experience benchmarking anything in
PostgreSQL to trust that I'm measuring the right thing. So I'm asking the
experts.

I'm running PostgreSQL 9.5 on Ubuntu Linux 15.10. I don't know if this
matters but this is my workstation which is a pretty zippy AlienWare X51 w/
16GB RAM on a Core i7-4770 processor.

Thanks,

Dane