This post is nearly a year old, but I’ve just revised and improved it. It now contains the most complete explanation of exclusion constraints (which are awesome!) that I can find.
I didn’t know about DEFERABLE. That’ll be useful in the future. At work, we have a script that moves data between different tables. It has an intermediate state in which some fkey constraints would be violated. Since we didn’t know about DEFERABLE at the time, the script drops the fkey constraints, does its job, and then re-adds the constraints, all in a single transaction.
The script one time caused a pretty serious production outage. One of the members of our support team, who had a basic understanding of transactions, copy and pasted the script into psql. He executed everything in the script except for the final COMMIT, and then double checked everything to make sure it was okay if he ran the script. Because modifying a constraint on a table grabs a lock that blocks reads to the table, queries to the table weren’t finishing. This caused our connection pooler to quickly become saturated with queries to the one table. The connection pool saturating then all queries to the db to hang waiting for a connection. Since the db was down down, the support person decided not to execute the final COMMIT. If he had executed the COMMIT, the db would have come back up. Since the outage, we now require all administrative commands to be slack commands. While the script does work, the right way to do it is with DEFERABLE.
Oh, and I hadn’t noticed that DEFERRABLE could be used on foreign key constraints. :) The docs confirm:
Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE constraints are affected by this setting.
Thanks for pointing that out!