none
Any way to increase max_locks_per_transaction?

    Question

  • We're trying to host a PGS solution in Azure SQL for PGS. Running into immediate issues with the pre-configured limit for max_locks_per_transaction. It seems this server parameter is not modifiable. Can it be increased with a support call?

    Tuesday, February 5, 2019 9:22 PM

All replies

  • Hi Leonardo,

    You might find ../data/postgresql.conf file, then edit with notepad, set max_locks_per_transaction = 1024 if it looks like # max_locks_per_transaction... you must remove #. It must look like that: max_locks_per_transaction = 1024 # min 10, than save it and restart postgresql.

    Ref StackOverFLow link : StackOverFlow thread

    Hope it helps.

    Wednesday, February 6, 2019 7:04 AM
    Moderator
  • Thanks. Yes, that would be the path with a self-hosted PGS. But we are using Azure Database for PostgreSQL, the hosted solution. In the Azure hosted version, there is no access to PostgreSQL.conf file. For many server parameters, the Azure UI and CLI allows access. But not this particular setting.

    It looks like Amazon AWS' version of hosted PGS allows one to update max_locks_per_transaction. But not Azure's?

    Wednesday, February 6, 2019 3:45 PM
  • Hi Len,

    This is the case, the hosted solution does not expose the database engine runtime configuration through a .conf file. These are exposed through the Azure Portal for the deployed instance, or through client statements. 

    Can you please create an entry on the Azure Database for PostgreSQL user voice to request exposing this parameter via the Azure Portal.

    Thank you,

    Mike

    Wednesday, February 6, 2019 10:33 PM
    Moderator
  • So, just to be clear, that configuration option is NOT available in EITHER the Portal nor CLI. At least for the Azure PGS instance I created last August, which is PGS version 9.6. It looks like Azure database now supports PGS v10.x. Do you see it exposed through the CLI for a 10.x instance? 

    Wednesday, February 6, 2019 10:46 PM
  • So, I just deployed a v.10PostgreSQL instance and here are the server properties that are exposed, listed alphabetically to capture items start with L.

    If this value is something you really need, I can have a service engineer make a change to your instance. This is possible if you have a concrete idea what you want it set to. 

    Wednesday, February 6, 2019 11:51 PM
    Moderator
  • That's a great offer, Mike. Let me talk with my customer tomorrow and we can decide. Being in development mode, we may be creating new clean instances (also to get the 10.x version), and it might be unreasonable to expect the Azure team to set this param every time. I'll let you know.

    Thursday, February 7, 2019 12:09 AM
  • I marked Mike's answer above as the "answer". Even though we have not taken advantage of the offer for the one-time change. At this point, getting MS support to evaluate one's requirement for max_locks_per_transaction appears to be the only route for getting this config option increased.
    Tuesday, February 12, 2019 6:16 PM
  • Given that the persistence of a given property value can not be guaranteed after a reboot or version upgrade (as an example), the best channel to request additional property values is through the product uservoice forum.
    Thursday, May 2, 2019 12:25 AM
    Moderator