To: Mark Dzmura <mdz@digital-mission.com>
cc: "pgsql-interfaces@postgreSQL.org" <pgsql-interfaces@postgresql.org>
Subject: Re: JDBC squirrely transaction behavior?? 
In-reply-to: <3935A4BB.C9EF9337@digital-mission.com> 
References: <3935A4BB.C9EF9337@digital-mission.com>
Comments: In-reply-to Mark Dzmura <mdz@digital-mission.com>
	message dated "Wed, 31 May 2000 23:48:11 -0000"
Date: Fri, 02 Jun 2000 12:11:27 -0400
Message-ID: <15290.959962287@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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