PostgreSQL – Shared Memory Issues Despite Seeming OK

memorypostgresqlschema

I've been performing kind of intensive schema dropping and creating over a PostgreSQL server, but now complains..:

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

But the problem remains if PostgreSQL is just restarted with service postgresql restart, I suspect max_locks_per_transaction won't tune nothing.

I am a bit estranged because the troubleshooting lists for this error is not working for me.

MORE INFO 1409291350: Some details missing but I keep the core SQL result.

postgres=# SELECT version();
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2,
 64-bit

And:

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 14.04.1 LTS
Release:        14.04
Codename:       trusty

Best Answer

Your comment about intensive dropping and creating and the notice you received regarding increasing max_locks_per_transaction hint that you're dropping and creating many objects in the same transaction. Each of those results in a lock, which each requires a small amount of shared memory. Because of this, max_locks_per_transaction limits the number of locks you can hold within a transaction (to prevent any one transaction from using all of shared memory).

You could either increase that limit a bit (I would recommend against setting it arbitrarily large or you will run in to a separate situation of actually running out of total shared memory) or do your drops and creates either in batches of transactions or as one drop/create per transaction.

Edit: Apparently I was wrong about how max_locks_per_transaction works. From the documentation, the total number of locks available is max_locks_per_transaction * (max_connections + max_prepared_transactions) - any one transaction may hold more than max_locks_per_transaction, as long as the number of locks held everywhere is less than this total value.

Related Question