Skip site navigation (1) Skip section navigation (2)

Re: max_locks_per_transaction and partitioned tables

From: Michael Holt <MHolt(at)terapeak(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: max_locks_per_transaction and partitioned tables
Date: 2012-09-19 21:17:45
Message-ID: E7907DC29186A94988A077445AFD1DC014975405@EXCHANGEVIC.ad.terapeak.com (view raw or flat)
Thread:
Lists: pgsql-admin
Thanks Tom. In the original plan a query of this sort was never supposed to happen, but it looks like some coding issues may have allowed it.


-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us] 
Sent: September-19-12 2:04 PM
To: Michael Holt
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] max_locks_per_transaction and partitioned tables

Michael Holt <MHolt(at)terapeak(dot)com> writes:
> We've had a system in operation for a few years that makes use of a substantial amount of partitioning. The parent table now has over 4,000 children tables. Within the last couple of days the server started giving "out of shared memory" errors with the suggestion to increase the max_locks_per_transaction.
> If the parent table is queried will it require a lock for each one of the child tables? I'm guessing it will.

Yup, it will.  I'm a bit astonished that you've gotten this far without horrid performance problems.  The underlying mechanisms for inheritance aren't really designed to scale past perhaps a hundred child tables.

			regards, tom lane


In response to

pgsql-admin by date

Next:From: Rubén Crespo FloresDate: 2012-09-19 21:21:32
Subject: Problem creating temporary tables . . .
Previous:From: Tom LaneDate: 2012-09-19 21:03:39
Subject: Re: max_locks_per_transaction and partitioned tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group