Re: table partitioning & max_locks_per_transaction

Tom --

Thanks for the pointers and advice. We've started by doubling
max_locks and halving shared_buffers, we'll see how it goes.

Brian

On Oct 10, 2009, at 7:56 PM, Tom Lane wrote:

> Brian Karlak <zenkat(at)metaweb(dot)com> writes:
>> "out of shared memory HINT: You might need to increase
>> max_locks_per_transaction"
>
> You want to do what it says ...
>
>> 1) We've already tuned postgres to use ~2BG of shared memory -- which
>> is SHMAX for our kernel. If I try to increase
>> max_locks_per_transaction, postgres will not start because our shared
>> memory is exceeding SHMAX. How can I increase
>> max_locks_per_transaction without having my shared memory
>> requirements
>> increase?
>
> Back off shared_buffers a bit? 2GB is certainly more than enough
> to run Postgres in.
>
>> 2) Why do I need locks for all of my subtables, anyways? I have
>> constraint_exclusion on. The query planner tells me that I am only
>> using three tables for the queries that are failing. Why are all of
>> the locks getting allocated?
>
> Because the planner has to look at all the subtables and make sure
> that they in fact don't match the query. So it takes AccessShareLock
> on each one, which is the minimum strength lock needed to be sure that
> the table definition isn't changing underneath you. Without *some*
> lock
> it's not really safe to examine the table at all.
>
> regards, tom lane