Re: ResultSet updates are not retained - 42.2.23

On Thu, 8 Jul 2021 at 14:13, Prasanth <dbadmin(at)pangburngroup(dot)com> wrote:

> Thank you for the quick response. Created a new test table and tried the
> same on that and it is working as expected. So probably something unique
> about this table.
>

Can I get the entire table definition as in create table .... Something in
.23 broke it.

Thanks,

Dave

>
> Test on new table
> Starting Value: 2021-01-01
> After Update: 2020-01-01
> After Requery: 2020-01-01
>
> Test on plan_data
> Starting Value: 2021-01-01
> After Update: 2020-01-01
> After Requery: 2021-01-01
>
> Below are the details about the columns in question. The table has 100+
> columns.
>
> attrelid | attname | atttypid | attstattarget | attlen |
> attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage |
> attalign | attnotnull | atthasdef | atthasmissing | attidentity |
> attgenerated | attisdropped | attislocal | attinhcount | attcollation |
> attacl | attoptions | attfdwoptions | attmissingval
>
> ----------+--------------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+---------------+-------------+--------------+--------------+------------+-------------+--------------+--------+------------+---------------+---------------
> 10525600 | accounting_current_start | 1082 | -1 | 4
> | 26 | 0 | -1 | -1 | t | p |
> i | f | f | f | |
> | f | t | 0 | 0
> | | | |
> 10525600 | plan_id | 23 | -1 | 4
> | 1 | 0 | -1 | -1 | t | p |
> i | t | t | f | |
> | f | t | 0 | 0
> | | | |
> (2 rows)
>
> Thanks,
> Prasanth
>
> On 7/8/21 12:08 PM, Dave Cramer wrote:
>
>
>
> On Thu, 8 Jul 2021 at 12:00, Prasanth <dbadmin(at)pangburngroup(dot)com> wrote:
>
>> Hi,
>>
>> With the latest release 42.2.23 ResultSet updates are not propagated to
>> the database. Below is a sample code to verify the issue. In the below code
>> we are querying the record using the primary key in that table.
>>
>> import java.sql.Connection;
>> import java.sql.Date;
>> import java.sql.DriverManager;
>> import java.sql.ResultSet;
>> import java.sql.SQLException;
>>
>> public class RowSet {
>>
>> public void test () throws SQLException, ClassNotFoundException {
>> Class.forName("org.postgresql.Driver");
>> Connection connection =
>> DriverManager.getConnection("jdbc:postgresql://192.168.0.100:5432/testdb",
>> "postgres", "xxxxxxxxxxxxxxxx");
>> connection.setAutoCommit(false);
>> String sql = "SELECT * FROM plan_data where plan_id = 30756";
>> ResultSet rs =
>> connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
>> ResultSet.CONCUR_UPDATABLE ).executeQuery(sql);
>> rs.next();
>> System.out.println("Starting Value: " +
>> rs.getDate("accounting_current_start"));
>> rs.updateDate("accounting_current_start",
>> Date.valueOf("2020-01-01"));
>> rs.updateRow();
>> System.out.println("After Update: " +
>> rs.getDate("accounting_current_start"));
>> connection.commit();
>>
>> sql = "SELECT * FROM plan_data where plan_id = 30756";
>> rs =
>> connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
>> ResultSet.CONCUR_UPDATABLE ).executeQuery(sql);
>> rs.next();
>> System.out.println("After Requery: " +
>> rs.getDate("accounting_current_start"));
>>
>> connection.close();
>> }
>>
>> public static void main(String args[]) {
>> try {
>> new RowSet().test();
>> } catch (SQLException e) {
>> e.printStackTrace();
>> } catch (ClassNotFoundException e) {
>> e.printStackTrace();
>> }
>> }
>>
>> }
>>
>>
>>
>> ----------------OUTPUT with 42.2.23 -----------
>> Starting Value: 2021-01-01
>> After Update: 2020-01-01
>> After Requery: 2021-01-01
>>
>>
>> Update accounting_current_start to 2021-01-01 using a query directly on
>> the database and rerun with 42.2.22
>>
>> ----------------OUTPUT with 42.2.22 -----------
>> Starting Value: 2021-01-01
>> After Update: 2020-01-01
>> After Requery: 2020-01-01
>>
>> Thanks,
>> Prasanth
>>
>>
>> On 7/6/21 10:41 AM, Dave Cramer wrote:
>>
>> Branch: refs/tags/REL42.2.23
>> Home: https://github.com/pgjdbc/pgjdbc
>>
>>
>>
> Can you tell me what the schema of plan_data is ?
>
> I just tried and it worked fine
>
> @Testpublic void testUpdateDate() throws Exception{
> Date testDate = Date.valueOf("2021-01-01");
> TestUtil.execute( "insert into hasdate values (1,'2021-01-01'::date)", con);
> con.setAutoCommit(false);
> String sql = "SELECT * FROM hasdate where id=1";
> ResultSet rs = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE ).executeQuery(sql);
> assertTrue(rs.next());
> assertEquals(testDate, rs.getDate("dt"));
> rs.updateDate("dt", Date.valueOf("2020-01-01"));
> rs.updateRow();
> assertEquals(Date.valueOf("2020-01-01"), rs.getDate("dt"));
> System.out.println("After Update: " + rs.getDate("dt"));
> con.commit();
> rs = con.createStatement().executeQuery("select dt from hasdate where id=1");
> assertTrue(rs.next());
> assertEquals(Date.valueOf("2020-01-01"), rs.getDate("dt"));
> rs.close();
> }
>
>
> Dave
>
>
>