JDBC squirrely transaction behavior??

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

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

Thanks and best regards,
Mark Dzmura

----------------------------------------------------

On a related note,

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

Regards,
Mark Dzmura

--
)))) This email routed via a wireless gateway!! ((((