Re: ResultSet updates are not retained - 42.2.23

On Thu, 8 Jul 2021 at 14:13, Prasanth <dbadmin@pangburngroup.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@pangburngroup.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

@Test
public 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