On Fri, Mar 14, 2008 at 7:12 PM, Tom Lane <
tgl@sss.pgh.pa.us> wrote:
"Kynn Jones" <
kynnjo@gmail.com> writes:
> Initially I didn't know what our max_locks_per_transaction was (nor even a
> typical value for it), but in light of the procedure's failure after 3500
> iterations, I figured that it was 3500 or so. In fact ours is only 64 (the
> default), so I'm now thoroughly confused.
max_locks_per_transaction times max_connections, and your procedure was
chewing them all. I suggest taking the hint's advice if you really need
to create 3500 tables in a single transaction. Actually, you'd better
do it if you want to have 3500 tables at all, because pg_dump will
certainly try to acquire AccessShare lock on all of them.
OK, in light of this, I'll have to either change my strategy (and schema) significantly or greatly increase max_locks_per_transaction.
I'm leaning towards the re-design option, primarily because I really don't really understand the consequences of cranking up max_locks_per_transaction. E.g. Why is its default value 2^6, instead of, say, 2^15? In fact, why is there a ceiling on the number of locks at all? I'm guessing that the fact that the default value is relatively small (i.e. a couple of orders of magnitude below the number of tables I have in mind) suggests that setting this value to a huge number would be a terrible idea. Is that so?
Thanks!
Kynn