Re: Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 3:22 PM, Dane Foster <studdugie(at)gmail(dot)com> wrote:

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

​
>