深入理解max_locks_per_transaction

ERROR:  out of shared memory
HINT:  You might need to increase "max_locks_per_transaction".

[postgres@Nick ~]$ psql
psql (18beta2)
Type "help" for help.

postgres=# \l+
ERROR:  out of shared memory
HINT:  You might need to increase "max_locks_per_transaction".
postgres=#

SELECT
  d.datname as "Name",
  pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
  pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
  CASE d.datlocprovider WHEN 'b' THEN 'builtin' WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS "Locale Provider",
  d.datcollate as "Collate",
  d.datctype as "Ctype",
  d.datlocale as "Locale",
  d.daticurules as "ICU Rules",
  CASE WHEN pg_catalog.array_length(d.datacl, 1) = 0 THEN '(none)' ELSE pg_catalog.array_to_string(d.datacl, E'\n') END AS "Access privileges",
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
       THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
       ELSE 'No Access'
  END as "Size",
  t.spcname as "Tablespace",
  pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;

/*
 * Estimate shared-memory space used for lock tables
 */
Size
LockManagerShmemSize(void)
{
 Size  size = 0;
long  max_table_size;

/* lock hash table */
 max_table_size = NLOCKENTS();
 size = add_size(size, hash_estimate_size(max_table_size, sizeof(LOCK)));

/* proclock hash table */
 max_table_size *= 2;
 size = add_size(size, hash_estimate_size(max_table_size, sizeof(PROCLOCK)));

/*
  * Since NLOCKENTS is only an estimate, add 10% safety margin.
  */
 size = add_size(size, size / 10);

return size;
}

#define NLOCKENTS() \
 mul_size(max_locks_per_xact, add_size(MaxBackends, max_prepared_xacts))
 
MaxBackends = MaxConnections + autovacuum_worker_slots +
 max_worker_processes + max_wal_senders + NUM_SPECIAL_WORKER_PROCS;

void
LockManagerShmemInit(void)
{
 HASHCTL  info;
long  init_table_size,
    max_table_size;
bool  found;

/*
  * Compute init/max size to request for lock hashtables.  Note these
  * calculations must agree with LockManagerShmemSize!
  */
 max_table_size = NLOCKENTS();
 init_table_size = max_table_size / 2;

/*
  * Allocate hash table for LOCK structs.  This stores per-locked-object
  * information.
  */
 info.keysize = sizeof(LOCKTAG);
 info.entrysize = sizeof(LOCK);
 info.num_partitions = NUM_LOCK_PARTITIONS;

 LockMethodLockHash = ShmemInitHash("LOCK hash",
            init_table_size,
            max_table_size,
            &info,
            HASH_ELEM | HASH_BLOBS | HASH_PARTITION);

/* Assume an average of 2 holders per lock */
 max_table_size *= 2;
 init_table_size *= 2;

/*
  * Allocate hash table for PROCLOCK structs.  This stores
  * per-lock-per-holder information.
  */
 info.keysize = sizeof(PROCLOCKTAG);
 info.entrysize = sizeof(PROCLOCK);
 info.hash = proclock_hash;
 info.num_partitions = NUM_LOCK_PARTITIONS;

 LockMethodProcLockHash = ShmemInitHash("PROCLOCK hash",
             init_table_size,
             max_table_size,
             &info,
             HASH_ELEM | HASH_FUNCTION | HASH_PARTITION);

/*
  * Allocate fast-path structures.
  */
 FastPathStrongRelationLocks =
  ShmemInitStruct("Fast Path Strong Relation Lock Data",
      sizeof(FastPathStrongRelationLockData), &found);
if (!found)
  SpinLockInit(&FastPathStrongRelationLocks->mutex);
}

LockAcquireResult
LockAcquireExtended(const LOCKTAG *locktag,
     LOCKMODE lockmode,
     bool sessionLock,
     bool dontWait,
     bool reportMemoryError,
     LOCALLOCK **locallockp,
     bool logLockFailure)
{
/* 省略 */
/*
  * Find or create a LOCALLOCK entry for this lock and lockmode
  */
 MemSet(&localtag, 0, sizeof(localtag)); /* must clear padding */
 localtag.lock = *locktag;
 localtag.mode = lockmode;

 locallock = (LOCALLOCK *) hash_search(LockMethodLocalHash,
            &localtag,
            HASH_ENTER, &found);

/* 省略 */
/* 当past-path锁冲突或者耗尽，转向访问共享hash table，当不能create new entry时报错 out of shared memory */
if (ConflictsWithRelationFastPath(locktag, lockmode))
 {
  uint32  fasthashcode = FastPathStrongLockHashPartition(hashcode);

  BeginStrongLockAcquire(locallock, fasthashcode);
if (!FastPathTransferRelationLocks(lockMethodTable, locktag,
             hashcode))
  {
   AbortStrongLockAcquire();
   if (locallock->nLocks == 0)
    RemoveLocalLock(locallock);
   if (locallockp)
    *locallockp = NULL;
   if (reportMemoryError)
    ereport(ERROR,
      (errcode(ERRCODE_OUT_OF_MEMORY),
       errmsg("out of shared memory"),
       errhint("You might need to increase \"%s\".", "max_locks_per_transaction")));
   else
    return LOCKACQUIRE_NOT_AVAIL;
  }
 }

/*
  * We didn't find the lock in our LOCALLOCK table, and we didn't manage to
  * take it via the fast-path, either, so we've got to mess with the shared
  * lock table.
  */
 partitionLock = LockHashPartitionLock(hashcode);

 LWLockAcquire(partitionLock, LW_EXCLUSIVE);

/* 当记录进程-锁对应关系时，访问hash table，无法create new entry时，报错out of memory */
 proclock = SetupLockInTable(lockMethodTable, MyProc, locktag,
        hashcode, lockmode);
if (!proclock)
 {
  AbortStrongLockAcquire();
  LWLockRelease(partitionLock);
if (locallock->nLocks == 0)
   RemoveLocalLock(locallock);
if (locallockp)
   *locallockp = NULL;
if (reportMemoryError)
   ereport(ERROR,
     (errcode(ERRCODE_OUT_OF_MEMORY),
      errmsg("out of shared memory"),
      errhint("You might need to increase \"%s\".", "max_locks_per_transaction")));
else
   return LOCKACQUIRE_NOT_AVAIL;
 }
/* 省略 */

[postgres@Nick ~]$ psql
psql (18beta2)
Type "help"forhelp.

postgres=# show max_connections ;
 max_connections 
-----------------
 16
(1 row)

postgres=# show max_worker_processes ;
 max_worker_processes 
----------------------
 2
(1 row)

postgres=# show autovacuum_worker_slots ;
 autovacuum_worker_slots 
-------------------------
 2
(1 row)

postgres=# show max_wal_senders ;
 max_wal_senders 
-----------------
 1
(1 row)

postgres=# show max_prepared_transactions ;
 max_prepared_transactions 
---------------------------
 0
(1 row)

postgres=# show max_locks_per_transaction ;
 max_locks_per_transaction 
---------------------------
 10
(1 row)

postgres=# select 10 * (16+2+2+1+2+0);
 ?column? 
----------
      230
(1 row)

postgres=# 

[postgres@Nick ~]$ psql
psql (18beta2)
Type "help" for help.

postgres=# \l+
ERROR:  out of shared memory
HINT:  You might need to increase "max_locks_per_transaction".
postgres=# \errverbose
ERROR:  53200: out of shared memory
HINT:  You might need to increase "max_locks_per_transaction".
LOCATION:  LockAcquireExtended, lock.c:1077
postgres=# \q
[postgres@Nick ~]$