Databases · Lesson 3.1
How a refused write reports back
So Postgres refused a second ada@simplified.org, because email is UNIQUE.
And it refused to delete Ben, because attempts 3 and 6 still point at his row.
Each time, Postgres stored nothing and deleted nothing.
Say Ada forgets she has an account, and signs up again with ada@simplified.org.
Simplified's server sends Postgres an INSERT with her email.
But she can't sign up again, because she already has an account.
So how does the server find out the sign-up was refused, so it can tell Ada why?
Postgres sends back an error.
An error is Postgres's message that it refused a write, and why.
So what does the error say?
It starts with ERROR, so the server knows the write was refused.
Then it says what went wrong: a duplicate key value, an email another row already holds.
Then the kind of rule that was broken: a unique constraint.
And last, in quotes, the constraint's name: users email key.
But where does that name come from?
Postgres names a constraint from its table and its column: users, then email.
Then it adds key, its word for a UNIQUE rule.
So the name points at one line of users' schema: email, with NOT NULL UNIQUE.
And what about an attempt for a learner who doesn't exist?
Say a bug sends an attempt with user ID 99, for lesson 2.
No row in users has id 99, so the foreign key refuses it.
The error names attempts user id fkey.
That's attempts, then user ID, then fkey, the ending Postgres gives a foreign key.
So it points at user ID's line in attempts' schema: REFERENCES users (id).
So is an error just like UPDATE 0?
No. Say the server adds 10 XP to the learner with id 9.
No row has id 9, so the UPDATE changes nothing, and sends back UPDATE 0.
That write worked: Postgres checked every row, and none passed the WHERE.
But Ada's second sign-up didn't work at all: Postgres refused it.
So 0 in a command tag means no row matched, and an error means a refused write.
So what does Simplified's server do with the error?
The error names users email key, so the server knows the email is taken.
So it might show Ada "That email already has an account" on the sign-up page.
Instagram might do the same when you pick a username someone has: "That username is taken".
So for a refused write, Postgres sends back an error that names the rule it broke.
Simplified's users table has email text NOT NULL UNIQUE in its schema. Ada's row, id 1, holds ada@simplified.org, and Ben's row, id 2, holds ben@simplified.org. Simplified's server sends Postgres UPDATE users SET email = 'ada@simplified.org' WHERE id = 2;.
What does Postgres send back?
Explanation
First, the UPDATE would give Ben's row ada@simplified.org, which Ada's row already holds, so UNIQUE refuses it.
Then, a refused write changes nothing, so Ben's email stays ben@simplified.org.
Therefore, Postgres sends back an error naming users_email_key.
Say YouTube keeps channels in a table called channels, with the columns id, handle and name. A write sends back ERROR: duplicate key value violates unique constraint "channels_handle_key". Postgres names a constraint from its table and its column, then adds key for a UNIQUE rule.
Which rule did the write break?
Databases · Lesson 3.2
What if only one row breaks a rule?
So when Ada signed up again, Postgres refused her INSERT.
It sent back an error naming users email key, and stored nothing.
And one INSERT can add several rows, with a list of values for each.
But what if only one row of a write breaks a rule?
Say one INSERT adds three learners: Hal, Jo, and then ada@simplified.org again.
Hal's and Jo's emails are new, so their rows break no rule.
But the third row's email is Ada's, so UNIQUE refuses it.
So Postgres refuses the whole INSERT, and sends back the error naming users email key.
It stores none of the three, not even Hal's or Jo's.
So users still holds six rows.
A refused write stores nothing, not even part of it.
So how could Hal and Jo get in?
Say the server sends the same three emails as three INSERTs, one each.
Hal's INSERT breaks no rule, so Postgres stores it, and sends back INSERT 0 1.
Jo's INSERT does the same.
Ada's email is already in row 1, so only the third INSERT is refused.
So users now holds eight rows, with Hal's and Jo's in it.
So each write is stored whole, or not at all.
Simplified's users table holds 6 rows, and its schema has email text NOT NULL UNIQUE. Simplified's server sends Postgres INSERT INTO users (email, name) VALUES ('kai@simplified.org', 'Kai'), (NULL, 'Lee');.
How many rows does users hold afterwards?
Explanation
First, Lee's list has a NULL email, and email is NOT NULL, so Postgres refuses the INSERT.
Then, a refused write stores nothing, not even part of it, so Kai's row isn't stored either.
Therefore, users still holds 6 rows.
Simplified's users table holds 6 rows, with the emails ada@simplified.org, ben@simplified.org, cy@simplified.org, dee@simplified.org, eli@simplified.org and fay@simplified.org. Its schema has email text NOT NULL UNIQUE. The server sends one INSERT with three lists of values: kim@simplified.org, then ben@simplified.org, then lee@simplified.org.
How many rows does users hold afterwards?
Simplified's attempts table holds 7 rows, and its schema has user_id integer NOT NULL REFERENCES users (id). The users table holds ids 1 to 6. The server sends one INSERT into attempts with three lists of values: user_id 99 for lesson 1, then user_id 2 for lesson 3, then user_id 6 for lesson 2.
How many rows does attempts hold afterwards?
Simplified's users table holds 6 rows, with the emails ada@simplified.org to fay@simplified.org, and its schema has email text NOT NULL UNIQUE. The server sends one INSERT with two lists of values: kim@simplified.org, then ada@simplified.org. It gets back an error. Then it sends a second INSERT with only kim@simplified.org.
How many rows does users hold afterwards?
Simplified's users table has email text NOT NULL UNIQUE, and Ben's row holds ben@simplified.org. The server sends one INSERT with three lists of values: hal@simplified.org, jo@simplified.org, then ben@simplified.org. It gets back ERROR: duplicate key value violates unique constraint "users_email_key".
Which of the three new rows are in users now?
Simplified's users table holds 6 rows, and its schema has email text NOT NULL UNIQUE. No row holds gia@simplified.org. The server sends one INSERT with two lists of values, and both hold gia@simplified.org.
How many rows does users hold afterwards?
Simplified's users table has email text NOT NULL UNIQUE, and Dee's row holds dee@simplified.org. The team must add three learners: kai@simplified.org, lee@simplified.org and dee@simplified.org. They want Kai and Lee stored, even though Dee's email is taken.
How should the server send them?
Databases · Lesson 3.3
Why not check in the server's code?
So when Ada signs up again, Postgres refuses it, with an error naming users email key.
And Simplified's server turns that error into "That email already has an account".
But Simplified's server could check the email itself.
Before the INSERT, it could select the row with that email.
If a row comes back, it would stop, and never send the INSERT.
So why put the rule in the schema too?
Because Simplified's server isn't the only program that writes to users.
Simplified runs two servers side by side to share the work: server A and server B.
Say the team also writes a script to import learners from a file.
And next year, someone writes a new feature that adds learners too.
All four programs send their writes to the same database.
So which writes does a check in server A's code protect?
Only the writes that run through server A's code.
The import script runs none of that code, so it never looks the email up.
But every write, from every program, goes through the same schema.
So every write meets the same rules, whichever program sends it.
We'll call this every program, every path.
Every program, every path means a rule in the schema applies to every write, from any program.
So what happens when the import script has a bug?
Say a bug makes the script send an attempt with user ID 99, for lesson 1.
The script never checks users, so it doesn't notice no learner has id 99.
But attempts' schema says user ID REFERENCES users (id).
So Postgres looks in users, finds no row with id 99, and refuses it.
It sends back the error naming attempts user id fkey, and attempts still holds 7 rows.
Simplified's server still looks the email up too, so it can show Ada a friendly message before sending.
But only the rule in the schema can't be skipped, by any program.
Simplified's attempts table has user_id integer NOT NULL REFERENCES users (id) in its schema, and users holds ids 1 to 6. Next year, the team writes a new feature whose code never checks users. It sends Postgres INSERT INTO attempts (user_id, lesson_id) VALUES (8, 2);.
What stops this attempt from being stored?
Explanation
First, the feature's code never checks users, so nothing in it stops user_id 8.
Then, the write still goes through attempts' schema, and no row in users has id 8.
Therefore, the foreign key on user_id refuses it, and nothing is stored.
Simplified's server looks up each email before it sends an INSERT, and users' schema has email text NOT NULL UNIQUE. The team writes a script that sends INSERTs straight to Postgres, without running the server's code. The file it imports holds ada@simplified.org, which Ada's row already holds.
What happens to that INSERT?
Simplified's attempts table holds 7 rows, its schema has user_id integer NOT NULL REFERENCES users (id), and users holds ids 1 to 6. A script with a bug sends four INSERTs, one after another, each with one attempt. Their user_ids are 2, 99, 4 and 100.
How many rows does attempts hold afterwards?
Simplified's team wants no two learners ever to share an email, even in code that someone writes next year.
Where should that rule live?
Simplified's users table has email text NOT NULL UNIQUE, so Postgres already refuses a second account with the same email.
Why does Simplified's server still look up a sign-up's email before it sends the INSERT?
Say Instagram's accounts table has a username column that is UNIQUE, and no account has the username nova.dev yet. Its iPhone app, Android app and website each send an INSERT with the username nova.dev, one after another, each through different code.
How many rows hold nova.dev afterwards?
Simplified's users table holds 6 rows, with emails from ada@simplified.org to fay@simplified.org, and its schema has email text NOT NULL UNIQUE. A new feature's code never looks emails up. It sends four INSERTs, one after another, each with one email: gia@simplified.org, ada@simplified.org, hal@simplified.org, then gia@simplified.org again.
How many rows does users hold afterwards?
Databases · Lesson 3.4
Tracing an error to its rule
So every write meets the schema's rules, whichever program sends it.
And each refused write sends back an error that names the rule it broke.
Say three writes reach Simplified's database today, and Postgres refuses all three.
Server A sends a sign-up with ada@simplified.org.
The import script sends an attempt for user 99.
And server B sends a delete for Ben's account, id 2.
So how can the team find the rule each write broke, and be sure nothing changed?
They read each error's name, find the schema line it points at, then check the table.
So which rule did server A's sign-up break?
At step 1, server A sends an INSERT with ada@simplified.org.
At step 2, Postgres refuses it, and the error names users email key.
That's users, then email, then key, for a UNIQUE rule.
At step 3, that points at email's line in users' schema: NOT NULL UNIQUE.
Because Ada's row, id 1, already holds that email.
At step 4, the team counts the rows with that email.
COUNT star sends back 1, so there is still just one Ada.
And which rule did the import script's attempt break?
At step 1, the script sends an INSERT into attempts, with user ID 99, for lesson 1.
At step 2, the error names attempts user id fkey.
That's attempts, then user ID, then fkey, for a foreign key.
At step 3, that points at user ID's line in attempts' schema: REFERENCES users (id).
Because no row in users has id 99.
At step 4, the team counts attempts' rows, and gets 7, just as before.
And which rule did server B's delete break?
At step 1, server B sends a DELETE for Ben's row, where the id is 2.
At step 2, the error says a delete on users broke a foreign key.
It names attempts user id fkey, on the table attempts.
So at step 3, the rule isn't in users' schema at all: it's on user ID's line in attempts'.
Because attempts 3 and 6 hold user ID 2, deleting Ben would leave them pointing at nobody.
At step 4, the team counts the attempts with user ID 2, and gets 2.
And Ben's row is still in users, so nothing was deleted.
So each error names a line of the schema, and a refused write changes nothing.
Simplified's attempts table has user_id integer NOT NULL REFERENCES users (id) and lesson_id integer NOT NULL REFERENCES lessons (id) in its schema. A write sends back ERROR: insert or update on table "attempts" violates foreign key constraint "attempts_user_id_fkey".
Which line of the schema does it name?
Explanation
First, the name in quotes is attempts_user_id_fkey: attempts, then user_id, then fkey.
Then, fkey is the ending Postgres gives a foreign key, and the column is user_id.
Therefore, it names user_id's line, REFERENCES users (id).
Simplified's attempts table has user_id integer NOT NULL REFERENCES users (id), with no ON DELETE rule, and users has id integer PRIMARY KEY and email text NOT NULL UNIQUE. DELETE FROM users WHERE id = 4; sends back ERROR: update or delete on table "users" violates foreign key constraint "attempts_user_id_fkey" on table "attempts".
Which line of which schema holds the rule it broke?
Simplified's attempts table has user_id integer NOT NULL REFERENCES users (id), with no ON DELETE rule. Ben, id 2, has 2 attempts. The server sends DELETE FROM users WHERE id = 2; and gets back ERROR: update or delete on table "users" violates foreign key constraint "attempts_user_id_fkey" on table "attempts". Then it sends SELECT COUNT(*) FROM attempts WHERE user_id = 2;.
What number comes back?
Simplified's users table has email text NOT NULL UNIQUE, and Dee's row holds dee@simplified.org. The server sends INSERT INTO users (email) VALUES ('dee@simplified.org'); and gets back ERROR: duplicate key value violates unique constraint "users_email_key". Then it sends SELECT COUNT(*) FROM users WHERE email = 'dee@simplified.org';.
What number comes back?
Simplified's users table has email text NOT NULL UNIQUE, and Eli's row, id 5, holds eli@simplified.org. The server sends UPDATE users SET email = NULL WHERE id = 5; and gets back ERROR: null value in column "email" of relation "users" violates not-null constraint.
What does Eli's email cell hold now?
Simplified's attempts table holds 7 rows, and its schema has user_id integer NOT NULL REFERENCES users (id). users holds ids 1 to 6. A script sends one INSERT into attempts with three lists of values, for user_ids 1, 4 and 99. It gets back ERROR: insert or update on table "attempts" violates foreign key constraint "attempts_user_id_fkey". Then it sends SELECT COUNT(*) FROM attempts;.
What number comes back?
Simplified's attempts table has user_id integer NOT NULL REFERENCES users (id), with no ON DELETE rule. Ada, id 1, has 3 attempts, and Fay, id 6, has none. The server sends DELETE FROM users WHERE id = 6;, then DELETE FROM users WHERE id = 1;.
What does each send back?