12

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
  • 2
    PostgreSQL version from SELECT version()? Interesting problem... – Craig Ringer Sep 29 '14 at 11:47
  • 2
    "I suspect max_locks_per_transaction won't tune nothing." -- uh, why would you suspect that? Have you tried actually following the hint's suggestion? – Josh Kupershmidt Sep 29 '14 at 13:00
  • Have you tried actually following the hint's suggestion? I have uncommented max_locks_per_transaction = 64 # min 10 in /etc/postgresql/9.3/main/postgresql.conf so far. – uprego Sep 29 '14 at 13:03
  • 1
    The default max_locks_per_transaction is 64 to begin with - uncommenting that line didn't effectively change it. – yieldsfalsehood Sep 29 '14 at 13:12
  • 1
    OK an effective increase to 128 solved the issue, actually allowed the operation to continue. – uprego Sep 29 '14 at 13:19
9

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.

  • My workflow includes (1) dumping a schema X, (2) dropping another schema Y, and (3) restoring X on the schema name Y. As I told, until today I've been over several weeks performing these activities, and today the step (2) is failing. Step (2) mainly consists on DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public, these are the sentences throwing the WARNING, ERROR and HINT. – uprego Sep 29 '14 at 13:07
  • Doubling the max locks from 64 to 128 allowed the workflow to continue. Ivn't got all the internals yet, but I guess committing between the DROP SCHEMA and the CREATE SCHEMA sentences will have a similar relieving effect. – uprego Sep 29 '14 at 13:18
  • Now I realize many days I get a small schema increase, and this issue perfectly matches one of those small schema increases. As a general strategy I will have greater consideration with HINTs from now on. – uprego Sep 29 '14 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.