CONSTRAINTS A DEVELOPER'S SECRET WEAPON PG Day Paris 2018-03-15 - - PowerPoint PPT Presentation

▶
constraints
SMART_READER_LITE
LIVE PREVIEW

CONSTRAINTS A DEVELOPER'S SECRET WEAPON PG Day Paris 2018-03-15 - - PowerPoint PPT Presentation

Apr 03, 2023 136 likes •720 views

CONSTRAINTS A DEVELOPER'S SECRET WEAPON PG Day Paris 2018-03-15 WILL LEINWEBER @LEINWEBER CITUSDATA.COM INTRO Will Leinweber @leinweber CONSTRAINTS maybe not the most exciting topic just want DB to safely store&retrieve data stern

slide-1
SLIDE 1

CONSTRAINTS

A DEVELOPER'S SECRET WEAPON

PG Day Paris 2018-03-15

slide-2
SLIDE 2

WILL LEINWEBER @LEINWEBER CITUSDATA.COM

slide-3
SLIDE 3

INTRO

slide-4
SLIDE 4

Will Leinweber @leinweber

CONSTRAINTS

maybe not the most exciting topic just want DB to safely store&retrieve data stern parent saying "No!"

slide-5
SLIDE 5

Will Leinweber @leinweber

RAILS

changed the landscape before: spaghetti or mountains of xml after: convention instead of configuration

slide-6
SLIDE 6

Will Leinweber @leinweber

RAILS

embrace constraints separate code concerns pluralize table names primary key is called "id" …etc

slide-7
SLIDE 7

Will Leinweber @leinweber

RAILS

"big dumb hash in the sky"

slide-8
SLIDE 8

Will Leinweber @leinweber

MEANWHILE…

learning how to make web applications

slide-9
SLIDE 9

Will Leinweber @leinweber

LEARNING 3NF

examples hard to extrapolate to my problem

slide-10
SLIDE 10

Will Leinweber @leinweber

slide-11
SLIDE 11

Will Leinweber @leinweber

TODAY

think about database schema first work backwards to models and api

slide-12
SLIDE 12

Will Leinweber @leinweber

MOTIVATION

database is the last line of defense

slide-13
SLIDE 13

Will Leinweber @leinweber

MOTIVATION

code change frequency >>> schema change frequency

slide-14
SLIDE 14

Will Leinweber @leinweber

MOTIVATION

~1 year old app 71 migrations 1203 releases after ~2 years 90 migrations 1454 releases

slide-15
SLIDE 15

Will Leinweber @leinweber

MOTIVATION

logical corruption is more likely to come from app bug

slide-16
SLIDE 16

Will Leinweber @leinweber

BUGS

bugs that can be caught by schema are particularly dangerous

slide-17
SLIDE 17

Will Leinweber @leinweber

BUGS

delayed problem hard to find cause can last for months

slide-18
SLIDE 18

Will Leinweber @leinweber

CLEANUP

slide-19
SLIDE 19

Will Leinweber @leinweber

MOTIVATION

don't write bad data in the first place

slide-20
SLIDE 20

TRADITIONAL CONSTRAINTS

slide-21
SLIDE 21

Will Leinweber @leinweber

TRADITIONAL CONSTRAINTS

NOT NULL

slide-22
SLIDE 22

Will Leinweber @leinweber

TRADITIONAL CONSTRAINTS

CREATE UNIQUE INDEX 
 ON users (email);

slide-23
SLIDE 23

Will Leinweber @leinweber

PARTIAL UNIQUE

CREATE UNIQUE INDEX 
 ON users (email)
 WHERE deleted_at IS NULL;

slide-24
SLIDE 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"

slide-25
SLIDE 25

DATATYPES

slide-26
SLIDE 26

Will Leinweber @leinweber

DATATYPES

not often thought of as constraints constrain what type of data gets in

slide-27
SLIDE 27

Will Leinweber @leinweber

DATATYPES

numbers are actually numbers booleans are actually booleans …etc

slide-28
SLIDE 28

Will Leinweber @leinweber

DATATYPES

…other databases

slide-29
SLIDE 29

Will Leinweber @leinweber

ENUMS

CREATE TYPE state 
 AS ENUM ('creating', 'running'); 
 ALTER TYPE state 
 ADD VALUE 'deleting';

slide-30
SLIDE 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

slide-31
SLIDE 31

Will Leinweber @leinweber

RANGES

int4range, int8range, numrange tsrange, tstzrange daterange

slide-32
SLIDE 32

Will Leinweber @leinweber

RANGES

slide-33
SLIDE 33

Will Leinweber @leinweber

SMALL OPTIMIZATION

CREATE TABLE somename (
 aws_id text COLLATE "C" NOT NULL
 ); 


  • - i-0598c7d356eba48d7
slide-34
SLIDE 34

Will Leinweber @leinweber

OTHER TYPES

uuid macaddr, inet, cidr array, hstore geometric

slide-35
SLIDE 35

Will Leinweber @leinweber

DATATYPES

downside of using JSONB

slide-36
SLIDE 36

FOREIGN KEYS

slide-37
SLIDE 37

Will Leinweber @leinweber

FOREIGN KEYS

CREATE TABLE posts(
 user_id int NOT NULL
 REFERENCES users(id),
 …

slide-38
SLIDE 38

Will Leinweber @leinweber

OPTIONS

REFERENCES foo ON DELETE/UPDATE NO ACTION / RESTRICT 
 CASCADE
 SET NULL / DEFAULT

slide-39
SLIDE 39

Will Leinweber @leinweber

PROBLEMS WITH TESTING

transaction vs. truncation/deleting

SET CONSTRAINTS all DEFERRED;

slide-40
SLIDE 40

CHECK

slide-41
SLIDE 41

Will Leinweber @leinweber

CHECK

custom logic

slide-42
SLIDE 42

Will Leinweber @leinweber

ENSURE POSITIVE NUMBER

CREATE TABLE products(
 name text,
 price int CHECK(price > 0)
 );

slide-43
SLIDE 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) 
 );

slide-44
SLIDE 44

Will Leinweber @leinweber

PERCENTS

scale float DEFAULT 1.0 NOT NULL,
 CHECK (scale >= 0 && scale <= 1)

slide-45
SLIDE 45

Will Leinweber @leinweber

MORE

CHECK ((json_col->>'i_prop')::int > 0)

slide-46
SLIDE 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;

slide-47
SLIDE 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).

slide-48
SLIDE 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"

slide-49
SLIDE 49

Will Leinweber @leinweber

slide-50
SLIDE 50

EXCLUSION

slide-51
SLIDE 51

Will Leinweber @leinweber

&& OVERLAP

select '[ 1,10)'::int4range
 && '[15,20)'::int4range;
 f select '[1,10)'::int4range
 && '[9,20)'::int4range;
 t

slide-52
SLIDE 52

Will Leinweber @leinweber

EXCLUSION

CREATE TABLE billings (
 formation_id uuid NOT NULL,
 validity_period tstzrange NOT NULL,
 price_per_month integer NOT NULL
 );

slide-53
SLIDE 53

Will Leinweber @leinweber

EXCLUSION

ALTER TABLE billings ADD CONSTRAINT billings_excl EXCLUDE USING gist ( formation_id WITH =, validity_period WITH && );

slide-54
SLIDE 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>).

slide-55
SLIDE 55

RECAP

slide-56
SLIDE 56

Will Leinweber @leinweber

3 TAKEAWAYS

database is your last line of defense postgres has some really great constraints datatypes are constraints too

slide-57
SLIDE 57

WILL LEINWEBER @LEINWEBER CITUSDATA.COM

thank you