Re: Out of shared memory in postgres 8.4.2 and locks

[Vidhya Bondre <meetvbondre@xxxxxxxxx>]

On Tue, Mar 9, 2010 at 6:04 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:

On Tue, Mar 9, 2010 at 4:38 AM, Vidhya Bondre <meetvbondre@xxxxxxxxx> wrote:
> Hi All,
>
> We have installed postgres 8.4.2 on production.
>
> We have a parition table structure for one of the table.
>
> when i am drop the master table we get the following error.
>
> drop table table_name cascade;
> WARNING:  out of shared memory
> ERROR:  out of shared memory
> HINT:  You might need to increase max_locks_per_transaction.
>
> FYI : This sql was working fine on Postgres 8.3
>
> Can some one please let me know what is going wrong here.

are you using the same postgresql.conf?  have you created more
partitions?  using advisory locks?

Yes we are using same conf files. In a week we create around 5 partitions. We are not using advisory locks

In any event, increase the max_locks_per_transaction setting and
restart the database.

 

Currently the value of max_locks_per_transaction is 64 modifying it to 96 works.

Have a couple of questions

1] As and when we add partitions will we have to increase this parameter ?

2] will we have to consider any othe parameter twick while increasing this one?

 

merlin