Postgres Parameter Missing from Parameter List.

Vishnu Agarwal 0 Reputation points

I am using Cosmos DB for Postgresql, with 2 worker node + 1 coordinator node. I want to set max_locks_per_transaction parameter for postgresql. I have a partioned table which I then sharded with Citus command, but I am unable to unshard, because I am getting error

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

I have looked into both Coordinator and Worker Node Parameter, and I am not finding it.

Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,496 questions
Azure Database for PostgreSQL
{count} votes

1 answer

Sort by: Most helpful
  1. ShaktiSingh-MSFT 14,186 Reputation points Microsoft Employee

    Hi Vishnu Agarwal •,

    Welcome to Microsoft Q&A forum.

    As I understand, you are unable to find parameter max_locks_per_transaction .

    max_locks_per_transaction (integer)

    The shared lock table has space for max_locks_per_transaction objects (e.g., tables) per server process or prepared transaction; hence, no more than this many distinct objects can be locked at any one time. This parameter limits the average number of object locks used by each transaction; individual transactions can lock more objects as long as the locks of all transactions fit in the lock table. This is not the number of rows that can be locked; that value is unlimited. The default, 64, has historically proven sufficient, but you might need to raise this value if you have queries that touch many different tables in a single transaction, e.g., query of a parent table with many children. This parameter can only be set at server start.

    When running a standby server, you must set this parameter to have the same or higher value as on the primary server. Otherwise, queries will not be allowed in the standby server.



    Clusters running older versions of the Citus extension might not offer all the parameters listed here

    As per the list here, it does not show up the parameter max_locks_per_transaction, but the underlying PostgreSQL data base also has configuration parameters.

    Let me check internally also and get back to you about it supportability.

    Thanks for your patience.

    0 comments No comments