Re: Check constraints and function volatility categories

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'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(dot)klaver(at)aklaver(dot)com
>