Re: how to continue a transaction after an error?

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.