Decibel! <decibel@decibel.org> writes:
> Actually, in the interest of stating the problem and not the
> solution, what we need is a way to add FKs that doesn't lock
> everything up to perform the key checks.
Ah, finally a useful comment. I think it might be possible to do an
"add FK concurrently" type of command that would take exclusive lock
for just long enough to add the triggers, then scan the tables with just
AccessShareLock to see if the existing rows meet the constraint, and
if so finally mark the constraint "valid". Meanwhile the constraint
would be enforced against newly-added rows by the triggers, so nothing
gets missed. You'd still get a small hiccup in system performance
from the transient exclusive lock, but nothing like as bad as it is
now. Would that solve your problem?
regards, tom lane