Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 37AF19FBD08; Fri, 13 Apr 2007 11:13:37 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 10433-02; Fri, 13 Apr 2007 11:13:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 816079FBDB3; Fri, 13 Apr 2007 11:09:03 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l3DE8TKe018738; Fri, 13 Apr 2007 10:08:31 -0400 (EDT) To: "Sorin N. Ciolofan" cc: "'Shoaib Mir'" , pgsql-general@postgresql.org, pgsql-admin@postgresql.org, "'Dimitris Kotzinos'" Subject: Re: [GENERAL] Increasing the shared memory In-reply-to: <20070413081234.B59698E40FC@mailhost.ics.forth.gr> References: <20070413081234.B59698E40FC@mailhost.ics.forth.gr> Comments: In-reply-to "Sorin N. Ciolofan" message dated "Fri, 13 Apr 2007 11:13:24 +0300" Date: Fri, 13 Apr 2007 10:08:28 -0400 Message-ID: <18737.1176473308@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/130 X-Sequence-Number: 25194 "Sorin N. Ciolofan" writes: > I will simplify the things in order to describe when the error occurred: > The input of the application is some data which is read from files on disk, > processed and then inserted in the database in one transaction. This total > quantity of data represents an integer number of data files, n*q, where q is > a file which has always 60kb and n is the positive integer. > For n=23 and shared_buffers=1000 and max_locks_per_transaction=64 the > Postgres throws the following exception: > org.postgresql.util.PSQLException: ERROR: out of shared memory > For n=23 I estimated that we create and manipulate about 8000 tables. Okay, as far as I know the only user-causable way to get that message is to run out of lock-table space, and a transaction does take a lock for each table it touches, so I concur that raising max_locks_per_transaction is an appropriate response. If you didn't see any change in the maximum N you could handle then I wonder whether you actually did raise it --- does "show max_locks_per_transaction" reflect the intended new value? Another possibility is that there's something about your code that makes the number of locks involved very nonlinear in N. You could try checking the number of rows in pg_locks immediately before commit at some smaller values of N to confirm what the scaling is really like. regards, tom lane