Re: JDBC squirrely transaction behavior?? - Mailing list pgsql-interfaces

On Wed, 31 May 2000, Mark Dzmura wrote:

> dumb JDBC/SQL question:
> 
> I have just moved some code over from pg 6.5.2 to pg 7.0 and am starting to modify an existing code base
> to use transactions.
> 
> I need to add some records to masterfile tables in a particular order (due to foreign key dependencies) and
> want to use a transaction to allow aborting the whole shebang in the event any of the inserts fails.
> 
> Below is a simple sample of code which shows the problem.
> 
> ---------------------------------------------
> 
> create table foos (foo_id serial primary key, foo_name varchar(64) unique);
> 
> create table bars(bar_id serial primary key, foo_id int4 references foos, bar_len int4, bar_width int4);
> 
> ...
> 
> connection.setAutoCommit(false);
> 
> while (true)
>     {
>     try
>         {
>         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.");
>             }
>         // get the foo_id for this foo;
>         rs = select foo_id from foos where foo_name='foo test value #1');
>         if (rs.next()
>             {
>             int foo_id = rs.getInt(1);
>             insert into bars (foo_id, bar_len, bar_width) values (foo_id, 25, 99);
>             connection.Commit();
>             }
>         else
>             {
>             connection.Rollback();
>             System.out.println("error: unable to lookup foo_id");
>             throw new Exception("internal error...");
>             }
>         }
>     catch (SQLException e)
>         {
>         System.out.println("bad things a-happenin");
>         e.printStackTrace();
>         }
>     }
> 

> 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.

So, I'd add in the catch clause of that block a connection.rollback()
call. That should then start a new transaction, which will then work for
the rest of the code.

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.

> I then commented out the transaction stuff, and verified that the code ran properly.
> 
> My first thought was that statement #2 was unable to see changes effected by
> statement #1, but because they are within the scope of the same transaction,
> they should be able to....strangely, this works for some records, but not others.
> 
> Is the pg or jdbc support for transactions broken??

No, this looks like normal behaviour. Because foo_id already exists, the
first insert fails, and marks that transaction as void. PG will ignore
everything until the transaction ends, rolls back, or autocommit is
enabled.

> Finally, the API does not seem to offer support for nested transactions, which
> sometime come in handy...  But even the JavaSoft-approved JDBC book
> written by the authors of JDBC does a lame job of covering transactions
> (and many other things...)

This is because Postgres doesn't support nested transactions. There's a
lot of JDBC internals that could be implemented easier if we had nested
transactions.

> Any guesses as to when the postgresql jdbc driver will support the JDBC 2.x extensions
> for record insert/update/delete via the special record in a result set ??

Possibly 7.1. It depends on if I get time between now and then. Time may
be tight for the next couple of months, as I may be moving home shortly,
and obviously I'll need to give that some priority ;-)

Peter

--      Peter T Mount peter@retep.org.uk     Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgresJava PDF Generator: http://www.retep.org.uk/pdf