On 8/25/19 12:40 PM, Rob Sargent wrote
On Aug 25, 2019, at 1:09 PM, David Wall <d.wall@xxxxxxxxxxxx> wrote:
Using the latest PostgreSQL, does it matter if my code does a ROLLBACK or a COMMIT on an non-modifying SELECT statement? My impression is they'd be the same as nothing is changed and therefore there's nothing to commit or rollback, but wondered if there was any difference in how they are processed by Postgres?
Thanks,
David
In interactive psql, both issue a warning that there is no current transaction. What is your auto-commit setting and how is your code sent to the server?
We are accessing it via JDBC, and so we SQL via PreparedStatements
against a Connection, and the connection is not auto-commit. By
default, the connection has a BEGIN TRANSACTION in place, so after all
requests we do, we need to commit/rollback. The main issue is that if
we do a SELECT and get a ResultSet that has no rows, if we do a commit
or a rollback, it seems reasonable that these are identical as no
changes were made. My inclination is to do a Connection.commit() on the
connection because it wasn't in error or anything even if no rows were
found, but wondered if a Connection.rollback() has any difference
(positive/negative) in such a scenario. We have SELECT sql statements
that sometimes do a rollback after such queries because even though no
rows was found is fine for SQL, it may be an issue in the application
that expects there to be at least one row. So we're trying to determine
if there's actually any difference between commit/rollback after SELECT
statements (with rows returned or not), a bit like if there's any
difference for an UPDATE statement that returns zero rows were updated.
