Re: Analyse - max_locks_per_transaction - why?

Phil Endecott <spam_from_postgresql_general(at)chezphil(dot)org> writes:
> Thanks Tom. I will upgrade to 8.0 one day but not soon. In the
> meantime, is there a way to judge a suficient setting for
> max_locks_per_transaction so that a global ANALYZE will work? It
> doesn't seem to be one lock per table or anything as simple as that.

No. The shared lock table has room for max_locks_per_transaction *
max_connections entries (actually rather more, but that's the supported
limit), so as soon as this exceeds the number of tables in your DB
the ANALYZE will work ... at least as long as nothing else is going on.
Any other transactions you may be running will eat some lock entries,
and you have to allow for those.

The conservative answer is to set max_locks_per_transaction to
(tables in database)/max_connections more than you were using before.
This is still probably overkill, since for most purposes the default
value is plenty.

There was some discussion recently of renaming the
max_locks_per_transaction variable to make it clearer that it's not
a hard per-transaction limit but a global average. Nobody really
came up with a better name though.

regards, tom lane