Re: ResultSet updates are not retained - 42.2.23

pg_dump -t plan_data -s

Dave Cramer
www.postgres.rocks

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

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