From: Ross J. Reedstrom <reedstrm@rice.edu>
To: Cristi Petrescu-Prahova <cristipp@lasting.ro>
Cc: pgsql-sql@postgresql.org
Subject: Re: how to continue a transaction after an error?
Date: Mon, 13 Nov 2000 14:08:08 -0600
Message-ID: <20001113140808.A12500@rice.edu> (raw)
In-Reply-To: <002e01c04da9$a8f95c20$25efe6c1@lasting.ro>
References: <002e01c04da9$a8f95c20$25efe6c1@lasting.ro>
On Mon, Nov 13, 2000 at 09:41:04PM +0200, Cristi Petrescu-Prahova wrote:
> Hello,
>
> I would like to insert a bunch of rows in a table in a transaction. Some of
> the insertions will fail due to constraints violation. When this happens,
> Postgres automatically ends the transaction and rolls back all the previous
> inserts. I would like to continue the transaction and issue the
> commit/rollback command myself.
>
> How to do it?
> Is there any setting I am missing?
> Is it possible at all?
Patient: "Doctor, it hurts when I bend my arm behind my back like
this. Can you help me?"
Doctor: "Sure, don't do that."
But seriously, this comes up from time to time. PostgreSQL is a little
stricter than most DBMSs with regards to transactional semantics, but
there are good reasons for this, involving tradeoffs of locking, MVCC,
"autocommit" mode, etc.
Let's look at transactions in general. When you start a transaction,
you're telling the backend "treat all of these statements as one, big,
all or nothing event." Just the thing for, say, balance transfers in
a bookkeeping application, but not something you need for storing web
log URL hits. If the backend isn't strict, how would you want it to
distinguish between "I really mean it, this time" and "that's o.k.,
go ahead anyway?"
If you want (need, if you're using large objects) transactions, you
really need to think about your transaction boundries. Don't just wrap
your whole frontend in one big, long lived transaction: close and reopen
your transaction for those inserts that are allowed to fail. Or don't do
them inside a multistatement transaction at all: let each one run in
it's own transaction space (i.e. other databases "autocommit" mode)
Ross
--
Open source code is like a natural resource, it's the result of providing
food and sunshine to programmers, and then staying out of their way.
[...] [It] is not going away because it has utility for both the developers
and users independent of economic motivations. Jim Flynn, Sunnyvale, Calif.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: reedstrm@rice.edu, cristipp@lasting.ro
Subject: Re: how to continue a transaction after an error?
In-Reply-To: <20001113140808.A12500@rice.edu>
References: <002e01c04da9$a8f95c20$25efe6c1@lasting.ro> <20001113140808.A12500@rice.edu>
* 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