From: John Gray <jgray@azuli.co.uk>
To: holger@marzen.de
Cc: Doug McNaught <doug@wireboard.com>
Cc: Martijn van Oosterhout <kleptog@svana.org>
Cc: Samuel J. Sutjiono <ssutjiono@wc-group.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Performance issues with compaq server
Date: 09 May 2002 12:43:33 +0100
Message-ID: <1020944616.1592.103.camel@adzuki> (raw)
In-Reply-To: <Pine.LNX.4.44.0205091321110.4909-100000@bluebell.marzen.de>
References: <Pine.LNX.4.44.0205091321110.4909-100000@bluebell.marzen.de>
On Thu, 2002-05-09 at 12:23, Holger Marzen wrote:
> On 9 May 2002, Doug McNaught wrote:
>
> > Holger Marzen <holger@marzen.de> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jgray@azuli.co.uk, holger@marzen.de, doug@wireboard.com, kleptog@svana.org, ssutjiono@wc-group.com, pgsql-general@postgresql.org
Subject: Re: Performance issues with compaq server
In-Reply-To: <1020944616.1592.103.camel@adzuki>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox