From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Dzmura <mdz@digital-mission.com>
Cc: pgsql-interfaces@postgreSQL.org <pgsql-interfaces@postgresql.org>
Subject: Re: JDBC squirrely transaction behavior??
Date: Fri, 02 Jun 2000 12:11:27 -0400
Message-ID: <15290.959962287@sss.pgh.pa.us> (raw)
In-Reply-To: <3935A4BB.C9EF9337@digital-mission.com>
References: <3935A4BB.C9EF9337@digital-mission.com>
Mark Dzmura <mdz@digital-mission.com> writes:
> try
> {
> insert into foos (foo_name) values ('foo test value #1');
> }
> catch (SQLException e)
> {
> system.out.println("attempt to insert duplicate foo ... not a problem.");
> }
Can't do it that way inside a transaction: the insert failure forces
the whole transaction to be aborted. The fact that you caught the
exception doesn't affect the fact that the server believes the
transaction must now be aborted; it won't process any more statements
until COMMIT or ROLLBACK.
I'd suggest something like
rs = select foo_id from foos where foo_name='foo test value #1';
if (rs is empty)
{
insert into foos (foo_name) values ('foo test value #1');
rs = select foo_id from foos where foo_name='foo test value #1';
Assert(rs is not empty);
}
This will be a little slower if the common case is that an insert is
needed, but faster if the common case is that no insert is needed.
Another possibility is to automate the sequence with a rule or trigger
on the database side. For example I think you could define a view
"foos_magic" with a rule such that you can just unconditionally do an
"select from foos_magic where ..." and an insert will be done for you
iff there's no matching record.
regards, tom lane
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: tgl@sss.pgh.pa.us, mdz@digital-mission.com
Subject: Re: JDBC squirrely transaction behavior??
In-Reply-To: <15290.959962287@sss.pgh.pa.us>
* 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