ResultSet should be updateable when using a Unique Key instead of a Primary Key (null columns)

[chalmagr]

I'm submitting a ...

• bug report
• feature request

Describe the issue
When attempting to update a table which has Unique Key (but not Primary Key due to nullable columns) using the ResultSet the Missing primary key exception is thrown. Unique keys should be accounted for as well as Primary Keys when marking a ResultSet as updateable or not.

Driver Version?
42.2.20

Java Version?
Adopt OpenJDK 11.0.10+9

OS Version?
MacOS / BigSur

PostgreSQL Version?
12.7 (Docker Image postgresql:12.7)

To Reproduce
Create a ResultSet from a table with only unique key and attempt to update the row when selecting all columns from the unique key

Expected behaviour
It should be allowed and it should work as if it were a Primary Key

Logs
NA

[davecramer]

This will introduce some performance issues, as we now have to fetch unique keys as well. Is there documentation in the API to support this ?

[chalmagr]

There is no restriction on the API of ResultSet that require the PK of the table to allow updateRow to be called (JDK 11 Reference

In other methods there are references to java.sql.RowId which would probably help removing such requirement.

We have been using Oracle Database and we had no such restrictions there to perform the changes to the Row. We have been always accessing a Table object and not any View or similar that would prevent the RowId from being truly available in the ResultSet - I would assume this restriction still holds true. Now while moving into Postgres, it is making the journey somewhat bumpy because of this - wouldn't want to re-write the whole data management framework if possible... :)

[davecramer]

Fair enough. I can see us extending it to Unique columns, however you should be aware that all the RowId functions are not implemented in PostgreSQL because the server does not provide a row id

[teetrenz]

Hi,

I updated to the latest JDBC driver 42.2.23 and I am getting now an exception "org.postgresql.util.PSQLException: No primary key found for table" although my table has a id column which is a primary key. That was working with previous versions of the JDBC driver. Testing now with 42.2.22.

[teetrenz]

Just for your information. I tried 42.2.22 and it is still working fine. 42.2.23 seems to be broken.

[davecramer]

Yes, it's not as simple as I first thought. I'll be pushing a fix shortly

[chalmagr]

Created PR #2228

[ionutursuleanu]

Just for your information. I tried 42.2.22 and it is still working fine. 42.2.23 seems to be broken.

I also noticed this. 42.2.23 is unusable for me

[davecramer]

fixed with #2228