Skip site navigation (1) Skip section navigation (2)

Re: JDBC squirrely transaction behavior??

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mark Dzmura <mdz(at)digital-mission(dot)com>
Cc: "pgsql-interfaces(at)postgreSQL(dot)org" <pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: JDBC squirrely transaction behavior??
Date: 2000-06-02 16:11:27
Message-ID: 15290.959962287@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-interfaces
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.

			regards, tom lane

In response to

Responses

pgsql-interfaces by date

Next:From: Roderick A. AndersonDate: 2000-06-02 23:05:14
Subject: Re: ODBC driver for Windows - Problems
Previous:From: Tom LaneDate: 2000-06-02 16:01:40
Subject: Re: Long atributes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group