Re: ResultSet updates are not retained - 42.2.23

This table is more than 20 years old I don't have the original create table DDL. I can pull the DDL from DBeaver would that help?

Thanks,
Prasanth

On 7/8/21 1:18 PM, Dave Cramer wrote:
>
>
> On Thu, 8 Jul 2021 at 14:13, Prasanth <dbadmin(at)pangburngroup(dot)com <mailto: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 <mailto: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 <http://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 
>