On Mon, Feb 1, 2016 at 2: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. >> >> 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 >
