Re: JDBC squirrely transaction behavior??

Tom Lane wrote:
>
> Mark Dzmura <mdz(at)digital-mission(dot)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.

In my case, the common case is way far and away (>90% of the time) tilted
toward insert (where if insert isn't needed, then an update is...), so a
question:

There seem to be two general approaches here (and in other related postings):

(a) Try an update and see if it works before trying an insert. Similar to
the first approach Tom suggests here.

(b) Attach a trigger to another table or view to catch the insert and
then query the real table to see if it really should be an insert or
an update.

Does anyone know which approach is likely to be faster? My guess is that
(b) has an advantage, being a server-side operation.

Of course, given my context, I would really like to try the insert first
and only fall back to the update for the exception, but the lack of a
statement level abort prevents that approach currently.

Incidently, I'm leaning towards inserting into a temporary table with
a trigger that catches inserts to the temp table and converts it (appropriately)
into an insert or update into the 'real' table, instead of using a view.
My (limited) understanding is that the view would have to be rebuilt after
every insertion to keep it consistent, whereas the temp table would stay
empty - it's really just used as a place to attach the trigger to an
insert without having to worry about recursion... Please correct me
if that's not correct!

Thanks!
--
Steve Wampler- SOLIS Project, National Solar Observatory
swampler(at)noao(dot)edu