Apr 13
launchbylunch.com/posts/2014/Feb/16/sql-naming-conventions
Note the observation that the lifetime of a naming schema may be decades, and the recommendation that tables be named in the singular.
Homework 3 #4
How does Postgres know?
Homework 4
Review basic theory, and Postgres MVCC. Then Python examples
conflict-serializability algorithm
debit-credit
isolation rules
Postgres and MVCC
select txid_current();
select * from pg_locks where transactionid = X;
Postgres serializable
Locking
and deadlocks
From the Postgres manual:
PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete. (Exactly which transaction will be aborted is difficult to predict and should not be relied upon.)
The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.
Deadlock detection via wait-for graph loops
How Postgres detects deadlock
Wait-for graphs versus transaction-conflict graphs (Ti→Tj if Ti accesses X and later Tj does and at least one of the accesses is a write)
Three-way deadlock:
T1:
begin;
update part set quan_in_stock = quan_in_stock -1 where partnum = 101;
update part set quan_in_stock = quan_in_stock -1 where partnum = 102;
T2:
update part set quan_in_stock = quan_in_stock -1 where partnum = 102;
update part set quan_in_stock = quan_in_stock -1 where partnum = 103;
T3:
update part set quan_in_stock = quan_in_stock -1 where partnum = 103;
update part set quan_in_stock = quan_in_stock -1 where partnum = 101;
Facebook timeline and denormalization:
https://www.facebook.com/note.php?note_id=10150468255628920
Tuning postgres to handle SSDs vs Spinning Iron: see postgresql.conf, Planner Cost Constants
Documentation at postgresql.org/docs/9.5/static/runtime-config-query.html.
How would you store a database of user posts? The post itself would be of type text (or a long varchar).
How would you search texts for a given keyword?