X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 39FA8D1EC0F for ; Wed, 24 Mar 2004 18:15:30 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 38602-06 for ; Wed, 24 Mar 2004 14:15:28 -0400 (AST) Received: from candle.pha.pa.us (candle.pha.pa.us [207.106.42.251]) by svr1.postgresql.org (Postfix) with ESMTP id B06C0D1E9B9 for ; Wed, 24 Mar 2004 14:15:25 -0400 (AST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id i2OIFJS02678; Wed, 24 Mar 2004 13:15:19 -0500 (EST) From: Bruce Momjian Message-Id: <200403241815.i2OIFJS02678@candle.pha.pa.us> Subject: Re: [Retrieved]RE: backup and recovery In-Reply-To: To: Tsirkin Evgeny Date: Wed, 24 Mar 2004 13:15:19 -0500 (EST) Cc: Naomi Walker , "Mark M. Huber" , "pgsql-admin@postgresql.org" X-Mailer: ELM [version 2.4ME+ PL108 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/316 X-Sequence-Number: 12885 Tsirkin Evgeny wrote: > > Uh, you just ask and we discuss it on the list. > > > > Are you using INSERTs from pg_dump? I assume so because COPY uses a > > single transaction per command. Right now with pg_dump -d I see: > > > > -- > > -- Data for Name: has_oids; Type: TABLE DATA; Schema: public; Owner: > > postgres > > -- > > > > INSERT INTO has_oids VALUES (1); > > INSERT INTO has_oids VALUES (1); > > INSERT INTO has_oids VALUES (1); > > INSERT INTO has_oids VALUES (1); > > > > Seems that should be inside a BEGIN/COMMIT for performance reasons, and > > to have the same behavior as COPY (fail if any row fails). Commands? > > > > As far as skipping on errors, I am unsure on that one, and if we put the > > INSERTs in a transaction, we will have no way of rolling back only the > > few inserts that fail. > > > That is right but there are sutuation when you prefer at least some > data to be inserted and not all changes to be ralled back because > of errors. Added to TODO: * Have pg_dump use multi-statement transactions for INSERT dumps For simple performance reasons, it would be good. I am not sure about allowing errors to continue loading. Anyone else? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073