Adrian Klaver <adrian.klaver@xxxxxxxxxxx> writes: > 3) Because of 1 & 2, this needs further analysis by someone or someones > more knowledgeable. This surprised me a bit too, because I thought we took a slightly-less-than-exclusive lock for FK additions or deletions. Tracing through it, I find that CloneFkReferencing opens the referenced relation with ShareRowExclusiveLock as I expected. But then we conclude that we can drop the existing FK enforcement triggers for the table being attached. That causes us to take AccessExclusiveLock on the trigger itself, which is fine because nobody's really paying attention to that. But then RemoveTriggerById takes AccessExclusiveLock on the trigger's table. We already had that on the table being attached, but not on the other table. This is quite bad, not just because the AccessExclusiveLock might block other stuff but because we are doing a lock upgrade on the referenced table, greatly increasing the risk of deadlock. I wonder whether it'd be all right for RemoveTriggerById to take only ShareRowExclusiveLock on the trigger's table. This seems OK in terms of basic semantics: that's enough to lock out anything that might want to fire triggers on the table. However, this comment for AlterTableGetLockLevel gives me pause: * Also note that pg_dump uses only an AccessShareLock, meaning that anything * that takes a lock less than AccessExclusiveLock can change object definitions * while pg_dump is running. Be careful to check that the appropriate data is * derived by pg_dump using an MVCC snapshot, rather than syscache lookups, * otherwise we might end up with an inconsistent dump that can't restore. I think pg_dump uses pg_get_triggerdef, which is probably not safe in these terms. An alternative answer might be what Alvaro was muttering about the other day: redesign FKs for partitioned tables so that we do not have to change the set of triggers when attaching/detaching. regards, tom lane
