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);
> while (true)
> 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);
> System.out.println("error: unable to lookup foo_id");
> throw new Exception("internal error...");
> catch (SQLException e)
> System.out.println("bad things a-happenin");
> 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
> 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
> 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
> 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 T Mount peter(at)retep(dot)org(dot)uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf
In response to
pgsql-interfaces by date
|Next:||From: Peter Mount||Date: 2000-06-05 10:31:02|
|Subject: Re: Re: Simple bug in JDBC interface|
|Previous:||From: Peter Mount||Date: 2000-06-05 09:47:21|
|Subject: Re: PostgreSQL JDBC error: Missing or erroneous