Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 87E56476099 for ; Mon, 27 Jan 2003 04:54:28 -0500 (EST) Received: from lakemtao01.cox.net (lakemtao01.cox.net [68.1.17.244]) by postgresql.org (Postfix) with ESMTP id 64849475E3A for ; Mon, 27 Jan 2003 04:54:27 -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 <20030127095428.JDDD16369.lakemtao01.cox.net@[192.168.1.1]> for ; Mon, 27 Jan 2003 04:54:28 -0500 Subject: Re: LOCK TABLE & speeding up mass data loads From: Ron Johnson To: PgSQL Performance ML In-Reply-To: <3E354CFB.32732.A38120C@localhost> References: <3E354CFB.32732.A38120C@localhost> Content-Type: text/plain Organization: Message-Id: <1043661264.9231.8.camel@haggis> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 27 Jan 2003 03:54:25 -0600 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/345 X-Sequence-Number: 992 On Mon, 2003-01-27 at 03:45, Shridhar Daithankar wrote: > On 27 Jan 2003 at 3:08, Ron Johnson wrote: > > > 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. > > IIRc, there is a hook to \copy, not the postgreSQL command copy for how many I'll have to look into that. > transactions you would like to see. I remember to have benchmarked that and > concluded that doing copy in one transaction is the fastest way of doing it. Boy Scout motto: Be prepared!! (Serves me well as a DBA.) So it takes a little longer. In case of failure, the time would be more than made up. Also, wouldn't the WAL grow hugely if many millions of rows were inserted in one txn? -- +---------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "Fear the Penguin!!" | +---------------------------------------------------------------+