Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3E39D475E3A for ; Mon, 27 Jan 2003 04:08:29 -0500 (EST) Received: from lakemtao01.cox.net (lakemtao01.cox.net [68.1.17.244]) by postgresql.org (Postfix) with ESMTP id 8613B475425 for ; Mon, 27 Jan 2003 04:08:28 -0500 (EST) Received: from [192.168.1.1] ([68.11.66.83]) by lakemtao01.cox.net (InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with ESMTP id <20030127090829.IXJQ16369.lakemtao01.cox.net@[192.168.1.1]> for ; Mon, 27 Jan 2003 04:08:29 -0500 Subject: Re: LOCK TABLE & speeding up mass data loads From: Ron Johnson To: PgSQL Performance ML In-Reply-To: References: <1043521053.818.32.camel@haggis> <1043521665.58142.135.camel@jester> <1043522349.818.36.camel@haggis> Content-Type: text/plain Organization: Message-Id: <1043658500.818.398.camel@haggis> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 27 Jan 2003 03:08:20 -0600 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/342 X-Sequence-Number: 989 On Sun, 2003-01-26 at 17:10, Curt Sampson wrote: > On Sun, 25 Jan 2003, Ron Johnson wrote: > > > > Oh, and you're using COPY right? > > > > No. Too much data manipulation to do 1st. Also, by committing every > > X thousand rows, then if the process must be aborted, then there's > > no huge rollback, and the script can then skip to the last comitted > > row and pick up from there. > > I don't see how the amount of data manipulation makes a difference. > Where you now issue a BEGIN, issue a COPY instead. Where you now INSERT, > just print the data for the columns, separated by tabs. Where you now > issue a COMMIT, end the copy. Yes, create an input file for COPY. Great idea. However, If I understand you correctly, then if I want to be able to not have to roll-back and re-run and complete COPY (which may entail millions of rows), then I'd have to have thousands of seperate input files (which would get processed sequentially). Here's what I'd like to see: COPY table [ ( column [, ...] ) ] FROM { 'filename' | stdin } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ] ] [COMMIT EVERY ... ROWS WITH LOGGING] <<<<<<<<<<<<< [SKIP ... ROWS] <<<<<<<<<<<<< This way, if I'm loading 25M rows, I can have it commit every, say, 1000 rows, and if it pukes 1/2 way thru, then when I restart the COPY, it can SKIP past what's already been loaded, and proceed apace. -- +---------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "Fear the Penguin!!" | +---------------------------------------------------------------+