Jeff Janes <jeff.janes@gmail.com> writes:
> For dumping entire databases, It looks like the biggest problem is
> going to be LockReassignCurrentOwner in the server. And that doesn't
> seem to be easy to fix, as any change to it to improve pg_dump will
> risk degrading normal use cases.
> If we want to be able to efficiently dump entire databases in a
> scalable way, it seems like there should be some way to obtain a
> data-base-wide AccessShare lock, which blocks AccessExclusive locks on
> any object in the database, and turns ordinary object-level
> AccessShare lock requests into no-ops.
I thought a little bit about that, but it seems fairly unworkable.
In the first place, pg_dump doesn't necessarily want lock on every table
in the database. In the second, such a lock mechanism would have
logical difficulties, notably whether it would be considered to apply to
tables created after the lock request occurs. If it does, then it would
effectively block all such creations (since creation takes exclusive
locks that ought to conflict). If it doesn't, how would you implement
that? In any case, we'd be adding significant cost and complexity to
lock acquisition operations, for something that only whole-database
pg_dump operations could conceivably make use of.
As far as the specific problem at hand goes, I think there might be a
less invasive solution. I poked into the behavior with gdb (and you're
right, LockReassignCurrentOwner does get called during portal drop)
and noted that although pg_dump is indeed holding thousands of locks,
any given statement that it issues touches only a few of them. So the
loop in LockReassignCurrentOwner iterates over the whole lock table but
does something useful at only a few entries.
We could fix things for this usage pattern with what seems to me to
be a pretty low-overhead method: add a fixed-size array to
ResourceOwners in which we can remember up to N LOCALLOCKs, for N around
10 or so. Add a LOCALLOCK to that array when we add the ResourceOwner to
that LOCALLOCK, so long as the array hasn't overflowed. (If the array
does overflow, we mark it as overflowed and stop adding entries.) Then,
in LockReassignCurrentOwner, we only iterate over the whole hash table
if the ResourceOwner's array has overflowed. If it hasn't, use the
array to visit just the LOCALLOCKs that need work.
Comments?
regards, tom lane