1

I'm using an updatable ResultSet, where I want to update the columns directly in the ResultSet and commit (for performance and avoiding complex rollback logic). For this, I do the following -

  • Create PreparedStatement for query SELECT <pk_col>, <col2>,...<coln> FROM <my_table_name> FOR UPDATE with TYPE_SCROLL_INSENSITIVE and CONCUR_UPDATABLE as resultSetType and resultSetConcurrency respectively.
  • Invoke PreparedStatement.executeQuery and collect the ResultSet.
  • Loop through the ResultSet, perform business logic, and invoke the updateXXX methods, and invoke updateRow.
  • Continue to the next entry in the ResultSet.
  • After the loop is done, commit.

I have noticed that every invocation of the updateRow results in a (pretty complex) select query executed by the driver to retrieve the metadata. I understand that this query is executed to find out the primary key that must be used in the where clause of the update query executed inside updateRow. But this leads to a significant performance hit. In fact, this query to retrieve the metadata takes the most amount of time.

Is there any way that the execution of this query can be avoided or minimized (by telling the driver to cache the results) while using updatable ResultSets? Here is the query executed. I'm using PostgreSQL Driver version 42.3.1.

Let me know if more information is required. Thank you in advance for your help.

SELECT
   result.TABLE_CAT,
   result.TABLE_SCHEM,
   result.TABLE_NAME,
   result.COLUMN_NAME,
   result.KEY_SEQ,
   result.PK_NAME 
FROM
   (
      SELECT
         null AS TABLE_CAT,
         n.nspname AS TABLE_SCHEM,
         ct.relname AS TABLE_NAME,
         a.attname AS COLUMN_NAME,
         (
            information_schema._pg_expandarray(i.indkey)
         )
         .n AS KEY_SEQ,
         ci.relname AS PK_NAME,
         information_schema._pg_expandarray(i.indkey) AS KEYS,
         a.attnum AS A_ATTNUM 
      FROM
         pg_catalog.pg_class ct 
         JOIN
            pg_catalog.pg_attribute a 
            ON (ct.oid = a.attrelid) 
         JOIN
            pg_catalog.pg_namespace n 
            ON (ct.relnamespace = n.oid) 
         JOIN
            pg_catalog.pg_index i 
            ON ( a.attrelid = i.indrelid) 
         JOIN
            pg_catalog.pg_class ci 
            ON (ci.oid = i.indexrelid) 
      WHERE
         true 
         AND ct.relname = <my_table_name> 
         AND i.indisprimary 
   )
   result 
where
   result.A_ATTNUM = 
   (
      result.KEYS
   )
   .x 
ORDER BY
   result.table_name,
   result.pk_name,
   result.key_seq
1
  • You should report that to the pgsql-jdbc mailing list. I think that the primaryKeys attribute of PgResultSet could be cached between calls to updateRow. Currently it is recomputed whenever checkUpdateable is called. Dec 2 '21 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.