Thread: ERROR: out of shared memory - But the table is empty
Hi there, not being an expert for Postgres…. I have a Postgis table with the countries of the world. Now, I would like to drop it.I got the error message: ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. and thought that it would be easier to drop first all rows, and then drop the empty table. But it seems that it has a problem with dropping the index: DROP INDEX gis.countries_uid; actually generates that error message. So, do I need to dig now into the postgres config file? It seems strange to me that Postgres has a problem deleting an emptytable, though. Thanks for any hints.
On Thu, Jul 12, 2012 at 8:23 PM, Stefan Schwarzer <stefan.schwarzer@unep.org> wrote:
Hi there,
not being an expert for Postgres…. I have a Postgis table with the countries of the world. Now, I would like to drop it. I got the error message:
ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
and thought that it would be easier to drop first all rows, and then drop the empty table.
But it seems that it has a problem with dropping the index:
DROP INDEX gis.countries_uid;
actually generates that error message.
So, do I need to dig now into the postgres config file? It seems strange to me that Postgres has a problem deleting an empty table, though.
The shared lock table has room for max_locks_per_transaction * max_connections entries,so as soon as it exceeds,you will get this error message. For fixing this issue,please set max_locks_per_transaction to (tables in database)/max_connections.
Thanks & Regards,
Raghu Ram
EnterpriseDB Corporation
Blog:http://raghurc.blogspot.in/
Thanks for that information. And yes, I have seen that in the documentation. Although I have to admit that I don't understand a word. I mean, what are max_locks_per_transaction? And the max_connections? Is the the number of users being connected to the database? In any case, I wonder why an empty table with a single index can generate that kind of message. I don't have a huge database, less than a thousand tables.not being an expert for Postgres…. I have a Postgis table with the countries of the world. Now, I would like to drop it. I got the error message:
ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
and thought that it would be easier to drop first all rows, and then drop the empty table.
But it seems that it has a problem with dropping the index:
DROP INDEX gis.countries_uid;
actually generates that error message.
So, do I need to dig now into the postgres config file? It seems strange to me that Postgres has a problem deleting an empty table, though.The shared lock table has room for max_locks_per_transaction * max_connections entries,so as soon as it exceeds,you will get this error message. For fixing this issue,please set max_locks_per_transaction to (tables in database)/max_connections.
Thanks a lot for any explanations and help.