ResultSet.updateRow fails if updated field was part of where clause

[sfritz-aurea]

I'm submitting a ...

• [x ] bug report
• feature request

Describe the issue
Trying to update the value of a se

lect resultset fails.
(the same approach works on Oracle and MSSQL)

Driver Version?
PostgreSQL JDBC Driver 42.2.18

Java Version?
OpenJDK 8 u275

OS Version?
Windows 10

PostgreSQL Version?
12.5

To Reproduce
Steps to reproduce the behaviour:

1. Create test table with one record:
   drop table testtable;
   CREATE TABLE testtable (
   public_id VARCHAR(255),
   deletion_date TIMESTAMP
   );
   INSERT INTO testtable (public_id) VALUES ('abc');
2. run the this java code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

public class Query {
	public static void main(String[] args) throws Exception {
		
		//TimeZone.setDefault(TimeZone.getTimeZone("UTC"));		 		 
		final String className = "org.postgresql.Driver";
		final String connString = "jdbc:postgresql://localhost/postgres?loggerLevel=TRACE";
		Class.forName(className);
		Connection conn;
		final String user = "postgres";
		final String password = "postgres";

		conn = DriverManager.getConnection(connString, user, password);
		System.out.println("Connection successful");

		String sql = "SELECT public_id,deletion_date FROM testtable WHERE (deletion_date IS NULL) ";		
		
		PreparedStatement preparedStatement = conn.prepareStatement(sql,
				ResultSet.TYPE_FORWARD_ONLY,
				ResultSet.CONCUR_UPDATABLE);		
        
		conn.setAutoCommit(false);
		
		preparedStatement.setQueryTimeout(30);
		System.out.println("Query: " + preparedStatement);
		
		ResultSet result = preparedStatement.executeQuery();
			if (!result.isBeforeFirst()) {
				System.out.println("SQL Result returned no data");
			} else {
				
				result.next();
				result.updateTimestamp(2,new Timestamp(System.currentTimeMillis()));				
				result.updateRow();				
				
				System.out.println("SQL Result available");				
			}

		// Clean up
		conn.close();
	}
}

Expected behaviour
deletion_date column should be updated to the assigned value, following select will then not fetch it as it already has a value (!=null)

Logs
Server:
2021-02-01 15:45:44.320 CET [1480] ERROR: syntax error at end of input at character 50
2021-02-01 15:45:44.320 CET [1480] STATEMENT: UPDATE testtable SET "deletion_date" = $1 WHERE
2021-02-01 15:45:44.329 CET [1480] LOG: could not receive data from client: An existing connection was forcibly closed by the remote host.

Client: log attached

client.log

[davecramer]

Honestly I would have expected it to fail because there is no primary key. Let me look at this. Thanks for the report

[sfritz-aurea]

@davecramer thanks for the swift reply. Indeed if I add a primary key then it works.
In my real application, I had to remove it earlier today as I got errors that the unique constraint is violated but once I removed the constraint I still had no duplicate entries. Guess I have to debug the cause of this further.

[davecramer]

There is no way to do an updatable result set in pg without a primary key. We don't have a hidden one we can use behind the scenes.

[davecramer]

duplicate of #1975