Thread: 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!! ((((
Attachment
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) { insertinto foos (foo_name) values ('foo test value #1'); rs = select foo_id from foos where foo_name='foo testvalue #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
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
Peter Mount wrote: > .... > > > 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. Hmmm, that may cause me a problem in a similar situation - I want to do an update whenever an insert fails. Further, for performance reasons the insertions are done in large transactions (thousands to tens of thousands of inserts in each transaction). Does the above comment mean that this ("update after failed insert") won't work as I'd like because it's inside a transaction? Is there a way to force the state of the transaction back to success after the failed insert, without losing the insertions that have been made previously in the transaction [or those that are made subsequently...]? > 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. This sounds like it would work in my case - does anyone see why it wouldn't or have a more appropriate approach? (Through JDBC, of course.) Thanks! -- Steve Wampler- SOLIS Project, National Solar Observatory swampler@noao.edu
Today, in a message to postgres-interfaces, Steve Wampler wrote: > > Hmmm, that may cause me a problem in a similar situation - I want to > do an update whenever an insert fails. There was a discussion about this in several Postgresql mailing lists a few weeks ago. Postgresql currently doesn't support statement-level abort and instead aborts the whole transaction. Apparently, there is a consensus among the developers that statement-level abort is a good thing to have. Perhaps we will see it some day... Even now you can do this trick the other way around: do an update first, and when the row count is zero insert the row instead. This works because updating zero rows is not an error in SQL. Not sure whether this helps in your situation. Joachim -- work: joachima@realtimeint.com (http://www.realtimeint.com) private: joachim@kraut.bc.ca (http://www.kraut.bc.ca)
Tom Lane wrote: > > 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. In my case, the common case is way far and away (>90% of the time) tilted toward insert (where if insert isn't needed, then an update is...), so a question: There seem to be two general approaches here (and in other related postings): (a) Try an update and see if it works before trying an insert. Similar to the first approach Tom suggests here. (b) Attach a trigger to another table or view to catch the insert and then query the real table to see if it reallyshould be an insert or an update. Does anyone know which approach is likely to be faster? My guess is that (b) has an advantage, being a server-side operation. Of course, given my context, I would really like to try the insert first and only fall back to the update for the exception, but the lack of a statement level abort prevents that approach currently. Incidently, I'm leaning towards inserting into a temporary table with a trigger that catches inserts to the temp table and converts it (appropriately) into an insert or update into the 'real' table, instead of using a view. My (limited) understanding is that the view would have to be rebuilt after every insertion to keep it consistent, whereas the temp table would stay empty - it's really just used as a place to attach the trigger to an insert without having to worry about recursion... Please correct me if that's not correct! Thanks! -- Steve Wampler- SOLIS Project, National Solar Observatory swampler@noao.edu
Today, in a message to postgres-interfaces, Steve Wampler wrote: > > My (limited) understanding is that the view would have to be rebuilt > after every insertion to keep it consistent, In postgresql a view is in fact a relation just like a table is a relation, except that the view has an "on select do instead .." trigger attached to it (don't take the syntax literally, I'm writing this from memory). > whereas the temp table would stay empty - it's really just used as a > place to attach the trigger to an insert without having to worry about > recursion... Same effect as using a view, except with a view you would also have a trigger associated with select so you can select and insert into the same relation. Joachim -- work: joachima@realtimeint.com (http://www.realtimeint.com) private: joachim@kraut.bc.ca (http://www.kraut.bc.ca)