Migration dying with Postgres max_locks_per_transaction

1

I'm attempting to migrate from Plone Classic running on RelStorage with Postgres.
During the migration I get locking errors, it seems I'll need to increase max_locks_per_transaction in the postgres db.

Traceback (innermost last):
  Module ZPublisher.WSGIPublisher, line 187, in transaction_pubevents
  Module transaction._manager, line 254, in commit
  Module transaction._manager, line 133, in commit
  Module transaction._transaction, line 282, in commit
  Module transaction._transaction, line 273, in commit
  Module transaction._transaction, line 456, in _commitResources
  Module transaction._transaction, line 433, in _commitResources
  Module ZODB.Connection, line 688, in tpc_vote
  Module perfmetrics._metric, line 72, in perfmetrics._metric._AbstractMetricImpl.__call__
  Module relstorage.storage, line 498, in tpc_vote
  Module relstorage.storage.tpc.begin, line 101, in tpc_vote
  Module relstorage.storage.tpc.vote, line 158, in enter
  Module relstorage.storage.tpc.vote, line 235, in _vote
   - __traceback_info__: (<StoreConnection at 0xffff702c5f50 active=False description={'backend_pid': 146} conn=<connection object at 0xffff74e678b0; dsn: 'user=plone password=xxx dbname=plone host=db application_name='RS: Store'', closed: 0> cur=<cursor object at 0xffff70df9300; closed: 0>>, <cursor object at 0xffff70df9300; closed: 0>)
  Module perfmetrics._metric, line 66, in perfmetrics._metric._AbstractMetricImpl.__call__
  Module relstorage.adapters.adapter, line 206, in lock_objects_and_detect_conflicts
  Module relstorage.adapters.locker, line 280, in reraise_commit_lock_error
   - __traceback_info__: ('lock_objects_and_detect_conflicts(%s)', 'Transaction failed; no lock info available')
  Module six, line 718, in reraise
  Module relstorage.adapters.adapter, line 196, in lock_objects_and_detect_conflicts
  Module relstorage.adapters.postgresql.adapter, line 315, in _best_lock_objects_and_detect_conflicts
relstorage.adapters.interfaces.UnableToLockRowsToModifyError: Acquiring a lock during commit failed: out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
CONTEXT:  SQL statement "SELECT pg_advisory_xact_lock(current_object.zoid)
  FROM current_object
  INNER JOIN temp_store USING(zoid)
  WHERE temp_store.prev_tid <> 0
  ORDER BY current_object.zoid"
PL/pgSQL function lock_objects_and_detect_conflicts(bigint[],bigint[]) line 130 at PERFORM

Transaction failed; no lock info available
Stored Objects
<TPCTemporaryStorage at 0xffff70e7ea40 count=12425 bytes=30122921>
2

I'm conducting this migration on a dev machine with all the configuration in docker compose. Adding the following to my docker-compose file resolved the issue for me.

# Use postgres/example user/password credentials
version: '3.1'

services:

  db:
    image: postgres
    restart: always
    command:  -c 'max_locks_per_transaction=8192' #  <=============
    environment:
      POSTGRES_USER: ${DB_USER}
      PGUSER: ${DB_USER}
....

The command: entry is the important part. It increases the number of locks allowed per transaction. For me, 8192 was enough.

2 Likes