Postgres Parameter Missing from Parameter List.

Vishnu Agarwal 0 Reputation points
2024-04-18T10:33:49.4066667+00:00

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
    2024-04-19T02:32:24.8433333+00:00

    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.

    Refer: https://www.postgresql.org/docs/current/runtime-config-locks.html

    Note

    Clusters running older versions of the Citus extension might not offer all the parameters listed here https://learn.microsoft.com/en-us/azure/cosmos-db/postgresql/reference-parameters

    As per the list https://learn.microsoft.com/en-us/azure/cosmos-db/postgresql/reference-parameters#postgresql-parameters 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