Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1T6yLF-0003GD-JX for pgsql-performance@postgresql.org; Thu, 30 Aug 2012 06:35:01 +0000 Received: from nm3-vm0.bullet.mail.bf1.yahoo.com ([98.139.212.154]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1T6yLC-0000uS-12 for pgsql-performance@postgresql.org; Thu, 30 Aug 2012 06:35:00 +0000 Received: from [98.139.212.152] by nm3.bullet.mail.bf1.yahoo.com with NNFMP; 30 Aug 2012 06:34:56 -0000 Received: from [98.139.212.214] by tm9.bullet.mail.bf1.yahoo.com with NNFMP; 30 Aug 2012 06:34:56 -0000 Received: from [127.0.0.1] by omp1023.mail.bf1.yahoo.com with NNFMP; 30 Aug 2012 06:34:56 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 284783.18850.bm@omp1023.mail.bf1.yahoo.com Received: (qmail 98801 invoked by uid 60001); 30 Aug 2012 06:34:56 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1346308496; bh=T25v4R5Hhiear+N16I7LojOUhfBdC9SPknS2kNQ9ywA=; h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:Cc:MIME-Version:Content-Type; b=qD7g5/uaBEZxXXsJxtjqJWhwH3KMbwstL7G6lIRDKFasGxk9G1WYFfrT+QiZvSB9V+nzMIvp4uIyI6wz4M4s8T35HRheuRNrjFc0XKXhnN5PtF/knc+JoMEirCWIBqGJGjT0ZD4JXH4auTjZFItrlgkyd5eGH/XdTvlaGJS/RbI= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:Cc:MIME-Version:Content-Type; b=VGQPLYo7xEzQqudmWP8ZWIkKFe9ScywVrbg0yfIQt7ohv5Kw5u7cBQzAdlEC5zfCV7c0BACvulLVz6b7YQMWU5tcDYLzzI9M2I9KyTKQ7ELnragosQnmMbPm2RpD9GGunZS0tScSdw2XEt1RrOF7av8goUwrBU8y+xuLVBiWbYI=; X-YMail-OSG: ig04M60VM1lzT1WqhLcRSuZMiI._j5nS7.EwVH744EHRza1 COtHIS85iAqkU.mvkqC3ZAzwKQ9d3OZiNcg9JSEytgKWmNbuVuHKqZMqj91c 5rKXtxsGLGZjvVWpQc9dq.9eGZGRBpJ81UPE0tFnPN6Zw6pJxAA0Tpf.Tw4f lLlxux70nSVly3MDSTeu5YwWgRDMPZKKD6QrX6_P_jwljj2Z6ey1u1_7Qwzl 2w7XbpenRLjGj0rxRoQ94_gxKvo8mazjpk7PXVzNcoRItTiy1aIS86boj.ve oDVq9Mt1OVhbdGExdtpT9X9jyD7F6UXyaoNvMnSYay0dVpRLsAr16z7KNneG it.ICxuuIpRCL16W6JIkDQsazaDCZ2dhTs14jIwn0zc1McKy9BtAowjW4FXu vxpagboHPRSNBjtGqAb5v8bG2NTgynuGypwAtyt5.fdYiXuv3PDb_TtjYFhd jXIgdDAJLDU_NdNdqaYozfO5T.SFAMw3WR436 Received: from [68.5.79.97] by web141101.mail.bf1.yahoo.com via HTTP; Wed, 29 Aug 2012 23:34:56 PDT X-Mailer: YahooMailWebService/0.8.121.416 Message-ID: <1346308496.83000.YahooMailNeo@web141101.mail.bf1.yahoo.com> Date: Wed, 29 Aug 2012 23:34:56 -0700 (PDT) From: Eileen Reply-To: Eileen Subject: JDBC 5 million function insert returning Single Transaction Lock Access Exclusive Problem To: "pgsql-performance@postgresql.org" Cc: "hey_here@yahoo.com" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="844607989-429045681-1346308496=:83000" X-Pg-Spam-Score: -0.3 (/) X-Archive-Number: 201208/202 X-Sequence-Number: 47740 --844607989-429045681-1346308496=:83000 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi,=0A=0AI have written some Java code which builds a postgresql function.= =A0 That function calls approximately 6 INSERT statements with a RETURNING = clause.=A0 I recreate and re-run the function about 900,000 times.=A0 I use= JDBC to execute these functions on postgresql 8.3 on Windows.=A0 When I tr= ied running this on a single Connection of Postgresql, it failed (some kind= of memory error).=A0 So I split the JDBC connections up into chunks of 500= 0.=A0 I reran and everything was fine.=A0 It took about 1 hour to execute a= ll the updates.=0A=0ASince it took so long to perform the update, I wanted = to prevent other users from querying the data during that time.=A0 So I rea= d about the LOCK command.=A0 It seemed like I should LOCK all the tables in= the database with an ACCESS EXCLUSIVE mode.=A0 That would prevent anyone f= rom getting data while the database was making its updates.=0A=0ASince a LO= CK is only valid for 1 transaction, I set autocommit to FALSE.=A0 I also re= moved the code which chunked up the inserts.=A0 I had read that a single tr= ansaction ought to have better performance than committing after each inser= t, but that was clearly not what ended up happening in my case.=0A=0AIn my = case, a few problems occurred.=A0 Number 1, the process ran at least 8 hour= s and never finished.=A0 It did not finish because the hard drive was fille= d up.=A0 After running a manual vacuum (VACUUM FULL), no space was freed up= .=A0 I think this has cost me 20 GB of space.=A0 Is there any way to free t= his space up?=A0 I even dropped the database to no avail.=0A=0ASecondly, wh= y did this process take over 8 hours to run?=A0 While reading the performan= ce mailing list, it seems like recommendations are to run lots of INSERTS i= n a single commit.=A0 Is 5 million too many?=A0 Is redefining a function ov= er and over inside a transaction a problem?=A0 Does the RETURNING clause pr= esent a problem during a single transaction?=0A=0AIf anyone has any suggest= ions for me, I would really appreciate it.=0A=0ATina=0A --844607989-429045681-1346308496=:83000 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Hi,
I have written some Java code which builds a postgresql functi= on.  That function calls approximately 6 INSERT statements with a RETU= RNING clause.  I recreate and re-run the function about 900,000 times.=   I use JDBC to execute these functions on postgresql 8.3 on Windows.&= nbsp; When I tried running this on a single Connection of Postgresql, it fa= iled (some kind of memory error).  So I split the JDBC connections up = into chunks of 5000.  I reran and everything was fine.  It took a= bout 1 hour to execute all the updates.

Since= it took so long to perform the update, I wanted to prevent other users fro= m querying the data during that time.  So I read about the LOCK comman= d.  It seemed like I should LOCK all the tables in the database with a= n ACCESS EXCLUSIVE mode.  That would prevent anyone from getting data = while the database was making its updates.

Since= a LOCK is only valid for 1 transaction, I set autocommit to FALSE.  I also removed the code which chunked up the inserts.  I had read that = a single transaction ought to have better performance than committing after= each insert, but that was clearly not what ended up happening in my case.<= /div>

In my case, a few problems occurred.  Number = 1, the process ran at least 8 hours and never finished.  It did not fi= nish because the hard drive was filled up.  After running a manual vac= uum (VACUUM FULL), no space was freed up.  I think this has cost me 20= GB of space.  Is there any way to free this space up?  I even dr= opped the database to no avail.

Secondly, why = did this process take over 8 hours to run?  While reading the performa= nce mailing list, it seems like recommendations are to run lots of INSERTS = in a single commit.  Is 5 million too many?  Is redefining a func= tion over and over inside a transaction a problem?  Does the RETURNING= clause present a problem during a single transaction?

=
If anyone has any suggestions for me, I would really appreciate i= t.

Tina
--844607989-429045681-1346308496=:83000--