Quiz: PostgreSQL

Read the output

Yesterday's dump was restored straight into the working shop. Why did the new order fail?

$ pg_restore -d shop shop.dump
…
pg_restore: warning: errors ignored on restore: 9
$ psql -d shop -c 'SELECT count(*) FROM orders'
     6
$ psql -d shop -c 'INSERT INTO orders (customer_id, amount) VALUES (1, 10)'
ERROR:  duplicate key value violates unique constraint "orders_pkey"
DETAIL:  Key (id)=(6) already exists.

• A pg_restore recreated orders without a primary key.
• B The dump deleted today's order, and its id was freed with an error.
• C setval from the dump set the orders_id_seq counter back to yesterday's 5, and the database handed out the taken id 6.
• D After its errors pg_restore left the table locked.