X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9EDCB32A773 for ; Thu, 21 Oct 2004 23:00:32 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 80254-09 for ; Thu, 21 Oct 2004 22:00:23 +0000 (GMT) Received: from pop.sanbrunocable.com (pop.sanbrunocable.com [65.172.230.120]) by svr1.postgresql.org (Postfix) with ESMTP id 4A27532A71A for ; Thu, 21 Oct 2004 23:00:23 +0100 (BST) Received: from oss4u.com (cpe-65-172-234-54.sanbrunocable.com [65.172.234.54]) by pop.sanbrunocable.com (8.11.6/8.11.6) with SMTP id i9LLwvG26117 for ; Thu, 21 Oct 2004 14:58:57 -0700 Received: (qmail 1762 invoked from network); 21 Oct 2004 21:56:43 -0000 Received: from tiburon.oss4u.com (192.168.1.4) by gw.oss4u.com (192.168.1.1) with ESMTP; 21 Oct 2004 21:56:43 -0000 From: "Uwe C. Schroeder" Organization: OSS4U, LLC To: Tom Lane Subject: Re: psql enhancement idea Date: Thu, 21 Oct 2004 15:00:20 -0700 User-Agent: KMail/1.5.3 Cc: pgsql-admin@postgresql.org References: <200410211205.09350.uwe@oss4u.com> <22591.1098387614@sss.pgh.pa.us> In-Reply-To: <22591.1098387614@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Description: clearsigned data Content-Disposition: inline Message-Id: <200410211500.20625.uwe@oss4u.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=DOMAIN_4U2 X-Spam-Level: X-Archive-Number: 200410/289 X-Sequence-Number: 15282 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thursday 21 October 2004 12:40 pm, Tom Lane wrote: > "Uwe C. Schroeder" writes: > > I'm just doing a load of database transfers using pg_dump. Because I > > changed the schema of quite some tables I have to dump full column > > inserts. > > I don't think that conclusion follows from that premise. In recent > pg_dump versions (any that use a column list with COPY, which I think is > 7.3 or later) there is no fundamental disadvantage to using COPY; it > should be semantically equivalent to INSERT-with-column-list commands. > > The only reason I can see for using the INSERT option anymore is if you > think that some of the row insertions might fail, and you don't want > that to stop the load altogether. If that's what you want, then you'd > certainly not want to wrap the load in BEGIN/END, so there doesn't seem > to be much point in adding an option to do it. > > regards, tom lane The reason is that I made changes to the schema, i.e. changed the ordinal= =20 position of columns and added some columns. Since the positioning of the=20 columns isn't the same anymore a copy will fail. Column Inserts will leave= =20 the new columns untouched (set them to NULL or whatever the default) but ca= n=20 handle the change in order of the columns (ok, the order thing is purely=20 cosmetic...).=20 I see your point that you don't want to fail the whole import when using fu= ll=20 inserts - which would happen in a transaction block. My point was basically= =20 that I assume all inserts will succeed, however I can't use copy because of= =20 the changes but doing it in a transaction will increase the insert speed by= =20 at least 10 (chewing up memory certainly). As a remark: normally I make pure data backups as copy and as full column= =20 inserts. In case of a recovery I then can easily switch to single inserts i= f=20 the copy fails - eats a bit more diskspace but increases security (besides= =20 the slony replication I'm running on top of that...) Uwe - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax: +1 650 872 2417 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBeDF0jqGXBvRToM4RAmrkAKCoRXnlmnRpp0DI5/cFeCyx97nm1QCfWJ/A 8zSHT4NXzG8LG7GFqpPGSbE=3D =3Dc99W -----END PGP SIGNATURE-----