On Mon, Feb 1, 2016 at 2:41 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.$$;
-- $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.klaver@xxxxxxxxxxx
