Search Postgresql Archives

Re: Check constraints and function volatility categories

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.$$;


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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux