RE: Postgres is not able to handle more than 4k tables!?

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
> Looks like it is not true (at lest for PG9.6):
>
> #0 0x00007fa6d30da087 in semop () from /lib64/libc.so.6
> #1 0x0000000000682241 in PGSemaphoreLock
> (sema=sema(at)entry=0x7fa66f5655d8) at pg_sema.c:387
> #2 0x00000000006ec6eb in LWLockAcquire
> (lock=lock(at)entry=0x7f23b544f800,
> mode=mode(at)entry=LW_EXCLUSIVE) at lwlock.c:1338
> #3 0x00000000006e5560 in LockAcquireExtended
> (locktag=locktag(at)entry=0x7ffd94883fa0, lockmode=lockmode(at)entry=1,
> sessionLock=sessionLock(at)entry=0 '\000', dontWait=dontWait(at)entry=0
> '\000', reportMemoryError=reportMemoryError(at)entry=1 '\001',
> locallockp=locallockp(at)entry=0x7ffd94883f98) at lock.c:962
> #4 0x00000000006e29f6 in LockRelationOid (relid=87103837, lockmode=1)
> at lmgr.c:113
> #5 0x00000000004a9f55 in relation_open (relationId=87103837,
> lockmode=lockmode(at)entry=1) at heapam.c:1131
> #6 0x00000000004bdc66 in index_open (relationId=<optimized out>,
> lockmode=lockmode(at)entry=1) at indexam.c:151
> #7 0x000000000067be58 in get_relation_info (root=root(at)entry=0x3a1a758,
> relationObjectId=72079078, inhparent=<optimized out>,
> rel=rel(at)entry=0x3a2d460) at plancat.c:183
> #8 0x000000000067ef45 in build_simple_rel (root=root(at)entry=0x3a1a758,
> relid=2, reloptkind=reloptkind(at)entry=RELOPT_BASEREL) at relnode.c:148
>
> Please notice lockmode=1 (AccessShareLock)

Ouch, there exists another sad hardcoded value: the number of maximum locks that can be acquired by the fast-path mechanism.

[LockAcquireExtended]
/*
* Attempt to take lock via fast path, if eligible. But if we remember
* having filled up the fast path array, we don't attempt to make any
* further use of it until we release some locks. It's possible that some
* other backend has transferred some of those locks to the shared hash
* table, leaving space free, but it's not worth acquiring the LWLock just
* to check. It's also possible that we're acquiring a second or third
* lock type on a relation we have already locked using the fast-path, but
* for now we don't worry about that case either.
*/
if (EligibleForRelationFastPath(locktag, lockmode) &&
FastPathLocalUseCount < FP_LOCK_SLOTS_PER_BACKEND)
{

/*
* We allow a small number of "weak" relation locks (AccessShareLock,
* RowShareLock, RowExclusiveLock) to be recorded in the PGPROC structure
* rather than the main lock table. This eases contention on the lock
* manager LWLocks. See storage/lmgr/README for additional details.
*/
#define FP_LOCK_SLOTS_PER_BACKEND 16

16 looks easily exceeded even in a not-long OLTP transaction... especially the table is partitioned. I wonder if we're caught in the hell of lock manager partition lock contention without knowing it. I'm afraid other pitfalls are lurking when there are many relations.

Regards
Takayuki Tsunakawa