Constraints: a Developer's Secret Weapon | PostgreSQL Conference Europe 2018 | Will Leinweber

024d6a0dd14fb31c804969a57a06dfbe?s=47 Citus Data
October 25, 2018

Constraints: a Developer's Secret Weapon | PostgreSQL Conference Europe 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

October 25, 2018
Tweet

Transcript

  1. CONSTRAINTS A DEVELOPER'S SECRET WEAPON

  2. WILL LEINWEBER @LEINWEBER CITUSDATA.COM

  3. TINYURL.COM/ PGEUCONST

  4. INTRO

  5. Will Leinweber @leinweber CONSTRAINTS maybe not the most exciting topic

    just want DB to safely store&retrieve data stern parent saying "No!"
  6. Will Leinweber @leinweber RAILS changed the landscape before: spaghetti or

    mountains of xml after: convention instead of configuration
  7. Will Leinweber @leinweber RAILS embrace constraints separate code concerns pluralize

    table names primary key is called "id" …etc
  8. Will Leinweber @leinweber RAILS "big dumb hash in the sky"

  9. Will Leinweber @leinweber MEANWHILE… learning how to make web applications

  10. Will Leinweber @leinweber LEARNING 3NF examples hard to extrapolate to

    my problem
  11. Will Leinweber @leinweber

  12. Will Leinweber @leinweber TODAY think about database schema first work

    backwards to models and api
  13. Will Leinweber @leinweber MOTIVATION database is the last line of

    defense
  14. Will Leinweber @leinweber MOTIVATION code change frequency >>> schema change

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

    1203 releases after ~2 years 90 migrations 1454 releases
  16. Will Leinweber @leinweber MOTIVATION logical corruption is more likely to

    come from app bug
  17. Will Leinweber @leinweber BUGS bugs that can be caught by

    schema are particularly dangerous
  18. Will Leinweber @leinweber BUGS delayed problem hard to find cause

    can last for months
  19. Will Leinweber @leinweber CLEANUP

  20. Will Leinweber @leinweber MOTIVATION don't write bad data in the

    first place
  21. TRADITIONAL CONSTRAINTS

  22. Will Leinweber @leinweber TRADITIONAL CONSTRAINTS NOT NULL

  23. Will Leinweber @leinweber TRADITIONAL CONSTRAINTS CREATE UNIQUE INDEX 
 ON

    users (email);
  24. Will Leinweber @leinweber PARTIAL UNIQUE CREATE UNIQUE INDEX 
 ON

    users (email)
 WHERE deleted_at IS NULL;
  25. 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"
  26. DATATYPES

  27. Will Leinweber @leinweber DATATYPES not often thought of as constraints

    constrain what type of data gets in
  28. Will Leinweber @leinweber DATATYPES numbers are actually numbers booleans are

    actually booleans …etc
  29. Will Leinweber @leinweber DATATYPES …other databases

  30. Will Leinweber @leinweber ENUMS CREATE TYPE state 
 AS ENUM

    ('creating', 'running'); 
 ALTER TYPE state 
 ADD VALUE 'deleting';
  31. 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
  32. Will Leinweber @leinweber RANGES int4range, int8range, numrange tsrange, tstzrange daterange

  33. Will Leinweber @leinweber RANGES

  34. Will Leinweber @leinweber SMALL OPTIMIZATION CREATE TABLE somename (
 aws_id

    text COLLATE "C" NOT NULL
 ); 
 -- i-0598c7d356eba48d7
  35. Will Leinweber @leinweber OTHER TYPES uuid macaddr, inet, cidr array,

    hstore geometric
  36. Will Leinweber @leinweber DATATYPES downside of using JSONB

  37. FOREIGN KEYS

  38. Will Leinweber @leinweber FOREIGN KEYS CREATE TABLE posts(
 user_id int

    NOT NULL
 REFERENCES users(id),
 …
  39. Will Leinweber @leinweber OPTIONS REFERENCES foo ON DELETE/UPDATE NO ACTION

    / RESTRICT 
 CASCADE
 SET NULL / DEFAULT
  40. Will Leinweber @leinweber PROBLEMS WITH TESTING transaction vs. truncation/deleting SET

    CONSTRAINTS all DEFERRED;
  41. CHECK

  42. Will Leinweber @leinweber CHECK custom logic

  43. Will Leinweber @leinweber ENSURE POSITIVE NUMBER CREATE TABLE products(
 name

    text,
 price int CHECK(price > 0)
 );
  44. 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) 
 );
  45. Will Leinweber @leinweber PERCENTS scale float DEFAULT 1.0 NOT NULL,


    CHECK (scale >= 0 & scale <= 1)
  46. Will Leinweber @leinweber MORE CHECK ((json_col->>'i_prop')::int > 0)

  47. 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;
  48. 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).
  49. 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"
  50. Will Leinweber @leinweber

  51. EXCLUSION

  52. Will Leinweber @leinweber && OVERLAP select '[ 1,10)'::int4range
 && '[15,20)'::int4range;


    f select '[1,10)'::int4range
 && '[9,20)'::int4range;
 t
  53. Will Leinweber @leinweber EXCLUSION CREATE TABLE billings (
 formation_id uuid

    NOT NULL,
 validity_period tstzrange NOT NULL,
 price_per_month integer NOT NULL
 );
  54. Will Leinweber @leinweber EXCLUSION ALTER TABLE billings ADD CONSTRAINT billings_excl

    EXCLUDE USING gist ( formation_id WITH =, validity_period WITH && );
  55. 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>).
  56. RECAP

  57. Will Leinweber @leinweber 3 TAKEAWAYS database is your last line

    of defense postgres has some really great constraints datatypes are constraints too
  58. WILL LEINWEBER @LEINWEBER CITUSDATA.COM thank you