ERROR: out of shared memory

PostgreSQL
1

ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
I’ve increased it from 64 to 1024 but that hasn’t made a difference.

2

Hi,

could you please provide us with a few more details? Which distribution are you on? Which software are you exactly using? You did upgrade from which version to which? What are you general specs of your hardware? Is there anything in the logs?

With more details, we would be able to better support and to look into it.

Thanks,

Kai

3

Hi Kai,
Standard distribution. 16.4. Hasn’t been upgraded.
The software that is triggering the error is HVR.
Hyper-V VM with 24 cores, 64 GB memory, Disks local to Hyper-V host.

2024-11-14 10:45:41.118 GMT [36886]: u= db= app= c= s=[6735d27d.9016:3] tx=[:0] LOG: checkpoint starting: time
2024-11-14 10:47:13.102 GMT [37031]: u=[hvragent] db=[equiptest] app=[hvr_integrate] c=[::1] s=[6735d293.90a7:6] tx=[3/11:85848253] ERROR: out of shared memory
2024-11-14 10:47:13.102 GMT [37031]: u=[hvragent] db=[equiptest] app=[hvr_integrate] c=[::1] s=[6735d293.90a7:7] tx=[3/11:85848253] HINT: You might need to increase max_locks_per_transaction.
2024-11-14 10:47:13.102 GMT [37031]: u=[hvragent] db=[equiptest] app=[hvr_integrate] c=[::1] s=[6735d293.90a7:8] tx=[3/11:85848253] STATEMENT: insert into “ramsnapshotqtyasset” (
“period_no”,
“company_no”,
“stock_no”,
“stock_code”,
“ramqtyasset_ramno”,
“ramsnapshotqtyasset_purchase”,
“ramsnapshotqtyasset_nbv”
)
values (
$1,
$2,
$3,
$4,
$5,
$6,
$7
)

I have increased max_connections to 100. I have set max_locks_per_transaction to 4096.

Regards
Jeremy

4

Hi,

Thanks for the additional information. As this is a third-party tool, it’s hard to give the perfect advice, but something I’m wondering about is the actual memory values in your postgresql.conf.

The issue is triggered by the hvr-agent so maybe the agent even provides more options to “tune” it?

Kai

5

Yes, I’ve made a change to HVR to limit the size of transactions and it appears to be working now, at least it hasn’t errored yet.

What puzzles me is the size of shared memory. ipcs is saying 56 bytes where I would have expected something more like 12GB.