It appears that the PG driver returns the original rather than the
updated data values for an updatable ResultSet. This is where the
updates are applied to a single table ResultSet in a single transaction.
The following scenario illustrates:
1. Set Auto Commit off and start a new transaction
2. Create a ResultSet based upon a query on a single table. The target
table has a primary key, and the ResultSet is created as type
ResultSet.TYPE_SCROLL_SENSITIVE and ResultSet.CONCUR_UPDATABLE
3. Position to a specific row via ResultSet.absolute(n) (where n = row
ordinal)
4. Update a column value via ResultSet.updateX(colName,value)
5. "Flush" the update via ResultSet.updateRow()
6. Position to a different row
7. Position back to the same row as updated in steps 3-5
8. Read in the column value for the updated column
Observe that with the Postgres driver you always get the original data
value back and never the new value. This appears to conflict with the
JDBC Specification which states:-
========================
JDBC4 Specification 15.2.4.1:
"The method DatabaseMetaData.ownUpdatesAreVisible(int type) returns true
if a ResultSet object of the specified type is able to see its own
updates and returns false otherwise."
========================
I've tried the above scenario out on a variety of other database
platforms (MS SQLServer, IBM DB2) and these definitely return you the
updated data values on a subsequent re-read. The behavior also
conflicts with the Database connection meta-data returned as follows:-
INFO - Own updates visible for TYPE_SCROLL_INSENSITIVE = true
INFO - Other updates visible for TYPE_SCROLL_INSENSITIVE = false
INFO - Own updates visible for TYPE_SCROLL_SENSITIVE = true
INFO - Other updates visible for TYPE_SCROLL_SENSITIVE = false
INFO - Updates detected for TYPE_SCROLL_INSENSITIVE = false
INFO - Updates detected for TYPE_SCROLL_SENSITIVE = false
If however you applied the row update using an SQL UPDATE on a separate
Statement as opposed to using the ResultSet.updateX() methods within the
same transaction, then you DO see the new value at step 8. I suggest
both techniques should yield the same results.
This is a real pain as I don't see how you can keep track of changes
made whilst scrolling up and down a table if you want to use the
ResultSet.updateX() methods. This linked with the restriction with the
Postgres driver that a table MUST have a Primary Key in order for this
technique to work, makes life difficult.
Behaviour observed with:-
JDBC Driver Version: PostgreSQL 9.0devel JDBC4 (build 800)
Server version: 8.4.7