From: Steve Wampler <swampler@noao.edu>
To: postgres-interfaces <pgsql-interfaces@postgresql.org>
Subject: Re: JDBC squirrely transaction behavior??
Date: Mon, 05 Jun 2000 08:37:44 -0700
Message-ID: <393BC948.43803086@noao.edu> (raw)
References: <Pine.LNX.4.10.10006051051080.5726-100000@maidast.retep.org.uk>
Peter Mount wrote:
>
....
>
> > This code will run for a number of insertions, then fail because the
> > select of the foo_id returns no records. This should be impossible,
> > because either (1) the foo_id was just added to the foos table, or (2)
> > it had previously been added. Either way, there should be a record
> > with a matching namefield.
>
> I can see a problem here. Your first insert inserts foo_id, and fails if
> foo_id already exists. In your code, you ignore this which is fine outside
> a Transaction. However, because you are using a transaction, the
> transaction is now in a failed state, so everything else done in that
> transaction will be ignored.
Hmmm, that may cause me a problem in a similar situation - I want to
do an update whenever an insert fails. Further, for performance reasons
the insertions are done in large transactions (thousands to tens of
thousands of inserts in each transaction). Does the above comment mean
that this ("update after failed insert") won't work as I'd like because
it's inside a transaction? Is there a way to force the state of the
transaction back to success after the failed insert, without losing the
insertions that have been made previously in the transaction [or those
that are made subsequently...]?
> The alternative is to but a connection.commit() after the insert, which
> may work in this case, but you have to think about the integrity of your
> tables.
This sounds like it would work in my case - does anyone see why it wouldn't
or have a more appropriate approach? (Through JDBC, of course.)
Thanks!
--
Steve Wampler- SOLIS Project, National Solar Observatory
swampler@noao.edu
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-interfaces@postgresql.org
Cc: swampler@noao.edu
Subject: Re: JDBC squirrely transaction behavior??
In-Reply-To: <393BC948.43803086@noao.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