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