What is the behavior of isReadOnly JDBC parameter while using PostgreSQL?

Question

The Java JDBC Connection class allows setting a parameter called readOnly, but what is it used for?

According to JDBC documentation:

readOnly is used to put the connection in read-only mode

According to HikariCP documentation:

This property controls whether Connections obtained from the pool are in read-only mode by default. Note some databases do not support the concept of read-only mode, while others provide query optimizations when the Connection is set to read-only. Whether you need this property or not will depend largely on your application and database. Default: false

But what is a connection in read-only mode? What is the concept? A connection that allows SELECT statements only?

Does a read-only connection provide any benefits with PostgreSQL JDBC driver?

Answer 1

You can browse PostgreSQL JDBC driver on github. Implementation of Connection.setReadOnly indicates that when you're calling that method, one of the following SQL statements sent to the database:

SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY

or

SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE

Now if we will read PostgreSQL reference, it specifies:

The transaction access mode determines whether the transaction is read/write or read-only. Read/write is the default. When a transaction is read-only, the following SQL commands are disallowed: INSERT, UPDATE, DELETE, and COPY FROM if the table they would write to is not a temporary table; all CREATE, ALTER, and DROP commands; COMMENT, GRANT, REVOKE, TRUNCATE; and EXPLAIN ANALYZE and EXECUTE if the command they would execute is among those listed. This is a high-level notion of read-only that does not prevent all writes to disk.

So basically the main benefit is that you won't be able to accidentally modify data. It's similar to using Collections.unmodifiableList and generally will lead to a more robust code, so if you're sure that a given transaction is expected to be read-only, use that property.