Thread: Re-read of updated row using scrollable ResultSet returns old data

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

On 1 April 2011 03:20, Andrew Hastie <andrew@ahastie.net> wrote:
> 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.

That's not meant to happen. Do you have a testcase showing the problem?

> 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

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

That's also not meant to happen - the driver should behave according
to what the metadata claims (notably, "other updates" are not
visible).
I think we need to see your code here.

Oliver

My mistake :-(

Appears my test program had both of its tests the wrong way around, so
after fixing and re-testing I can confirm that the behavior IS
consistent with what the meta-data states for both "own" updates and
"other" updates.

It also means my tests against MS SQLServer and IBM DB2 for the
"commercial" driver I'm using reveal that they don't support "own
updates visible", so congrats to the PG project for getting this support
in the driver unlike the competition!

Apologies to all.
Andrew

On 03/31/2011 11:20 PM, Oliver Jowett wrote:
> On 1 April 2011 03:20, Andrew Hastie<andrew@ahastie.net>  wrote:
>> 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.
> That's not meant to happen. Do you have a testcase showing the problem?
>
>> 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
>> 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.
> That's also not meant to happen - the driver should behave according
> to what the metadata claims (notably, "other updates" are not
> visible).
> I think we need to see your code here.
>
> Oliver