Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id E1DC047594D for ; Thu, 9 May 2002 07:49:01 -0400 (EDT) Received: from azuli.gotadsl.co.uk (azuli.gotadsl.co.uk [213.208.123.57]) by postgresql.org (Postfix) with ESMTP id BC593475944 for ; Thu, 9 May 2002 07:49:00 -0400 (EDT) Received: from localhost.localdomain ([127.0.0.1]) by azuli.gotadsl.co.uk with esmtp (Exim 3.22 #2) id 175mKe-0000ai-00; Thu, 09 May 2002 12:43:36 +0100 Subject: Re: Performance issues with compaq server From: John Gray To: holger@marzen.de Cc: Doug McNaught , Martijn van Oosterhout , "Samuel J. Sutjiono" , pgsql-general@postgresql.org In-Reply-To: References: Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.3 Date: 09 May 2002 12:43:33 +0100 Message-Id: <1020944616.1592.103.camel@adzuki> Mime-Version: 1.0 X-Archive-Number: 200205/318 X-Sequence-Number: 24298 On Thu, 2002-05-09 at 12:23, Holger Marzen wrote: > On 9 May 2002, Doug McNaught wrote: > > > Holger Marzen writes: > > > > > Is there a rule in SQL standards that describes what should happen if > > > some statemens in a transaction fail and the program issues a commit? > > > > I think PG's is standard behavior; that's kind of the whole point of > > having transactions. > > - rolling back the whole transaction if you do a COMMIT > or > - keeping the changes until the first failing statement and ignoring > everything that follows if you do a COMMIT > I can't speak to the phrasing of the standard, but PG behaviour is: Everything in the transaction is ignored. All the inserts made before the error, since the BEGIN, are rolled back. All subsequent inserts are ignored (generating an error that transaction is in ABORT state). The subsequent COMMIT will end the transaction (and thus clear the abort state) but *will not commit anything*. This means that scripts will work as intended -namely that all or none of the commands within a transaction block will succeed[*]. So if you split your insert into blocks, and one block fails, you will only need to correct that block and try to load it again. Regards John [*] This is handy for doing things like reindexing or drop/recreate table in a script. The drop table stands a good chance of succeeding, whereas the prospect of a syntax error in the create table makes it more likely to fail. A subsequent commit of the DROP TABLE would not produce the end result you intended.... -- John Gray Azuli IT www.azuli.co.uk