Re: Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed

Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> 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