Constraints: A Developer's Secret Weapon | pgDay Paris 2018 | Will Leinweber

Constraints: A Developer's Secret Weapon | pgDay Paris 2018 | Will Leinweber

Of all my regrets while building database-backed applications, not fully embracing database constraints sooner is one of the bigger ones. For far too long, I treated the db as a little more than a dumb key-value store with joins.

Strong constraints turn your database into the last line of defense against bad data. Applications change several orders of magnitude more often than db schemas, and each change is a chance for corruption.

Constraints in Postgres come in many forms, from literally using the CONSTRAINT keyword to a large choice of datatypes. You'll come away from this talk understanding the pros and cons of type of constraint, and be ready to transform your application into a safer and more resilient system.

024d6a0dd14fb31c804969a57a06dfbe?s=128

Citus Data

March 15, 2018
Tweet

Transcript

  1. 3.
  2. 4.

    Will Leinweber @leinweber CONSTRAINTS maybe not the most exciting topic

    just want DB to safely store&retrieve data stern parent saying "No!"
  3. 5.

    Will Leinweber @leinweber RAILS changed the landscape before: spaghetti or

    mountains of xml after: convention instead of configuration
  4. 14.

    Will Leinweber @leinweber MOTIVATION ~1 year old app 71 migrations

    1203 releases after ~2 years 90 migrations 1454 releases
  5. 16.
  6. 23.

    Will Leinweber @leinweber PARTIAL UNIQUE CREATE UNIQUE INDEX 
 ON

    users (email)
 WHERE deleted_at IS NULL;
  7. 24.

    Will Leinweber @leinweber ENFORCE ASSUMPTIONS "This property should always "Should

    never be…" be positive" be present" be between 0 and 1" be a fibonacci number"
  8. 25.
  9. 29.

    Will Leinweber @leinweber ENUMS CREATE TYPE state 
 AS ENUM

    ('creating', 'running'); 
 ALTER TYPE state 
 ADD VALUE 'deleting';
  10. 30.

    Will Leinweber @leinweber RANGES SELECT i, i <@ '[1,10)'::int4range included


    FROM (VALUES (1),(5),(10)) as v(i); i | included ----+---------- 1 | t 5 | t 10 | f
  11. 33.

    Will Leinweber @leinweber SMALL OPTIMIZATION CREATE TABLE somename (
 aws_id

    text COLLATE "C" NOT NULL
 ); 
 -- i-0598c7d356eba48d7
  12. 38.
  13. 40.
  14. 43.

    Will Leinweber @leinweber REFERENCE OTHER COLUMN CREATE TABLE products(
 name

    text,
 price int CHECK(price > 0),
 sale_p int CHECK(sale_p > 0),
 CHECK (price > sale_p) 
 );
  15. 46.

    Will Leinweber @leinweber USER DEFINED FUNCTIONS CREATE OR REPLACE FUNCTION

    is_fib(i int) RETURNS boolean AS $$
 DECLARE
 a integer := 5*i*i+4;
 b integer := 5*i*i-4;
 asq integer := sqrt(a)::int;
 bsq integer := sqrt(b)::int;
 BEGIN
 RETURN asq*asq=a OR bsq*bsq=b;
 end
 $$ LANGUAGE plpgsql IMMUTABLE STRICT;
  16. 47.

    Will Leinweber @leinweber USER DEFINED FUNCTIONS # CREATE TABLE onlyfib(

    i int CHECK (is_fib(i)) );
 CREATE TABLE # insert into onlyfib values (5), (8);
 INSERT 0 2 # insert into onlyfib values (6);
 ERROR: new row for relation "onlyfib" violates check constraint "onlyfib_i_check"
 DETAIL: Failing row contains (6).
  17. 48.

    Will Leinweber @leinweber DOMAINS # CREATE DOMAIN fib AS int

    CHECK (is_fib(VALUE));
 # CREATE TABLE onlyfib(i fib); # insert into onlyfib values (5),(8);
 INSERT 0 2 # insert into onlyfib values (6);
 ERROR: value for domain fib violates check constraint "fib_check"
  18. 50.
  19. 51.
  20. 52.

    Will Leinweber @leinweber EXCLUSION CREATE TABLE billings (
 formation_id uuid

    NOT NULL,
 validity_period tstzrange NOT NULL,
 price_per_month integer NOT NULL
 );
  21. 53.

    Will Leinweber @leinweber EXCLUSION ALTER TABLE billings ADD CONSTRAINT billings_excl

    EXCLUDE USING gist ( formation_id WITH =, validity_period WITH && );
  22. 54.

    Will Leinweber @leinweber ERROR MESSAGE ERROR: conflicting key value violates

    exclusion constraint "constraint name" DETAIL: Key (id, range)=(<new row>) conflicts with existing key (id, range)=(<existing row>).
  23. 55.
  24. 56.

    Will Leinweber @leinweber 3 TAKEAWAYS database is your last line

    of defense postgres has some really great constraints datatypes are constraints too