For SELECT statement (just a reading one, no 'FOR UPDATE'), is COMMIT or ROLLBACK preferred?

On Aug 25, 2019, at 1:09 PM, David Wall <d.wall@computer.org> 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?

On 8/25/19 12:40 PM, Rob Sargent wrote

On Aug 25, 2019, at 1:09 PM, David Wall <d.wall@computer.org> 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.

David Wall <d.wall@computer.org> writes:

... 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.

They're different code paths, but I'd expect any performance difference
to be at the noise level; if nothing happened in the transaction then
no WAL traffic will be emitted in either case.

A more useful thing to think about, IMO, is this: if your app thinks
that the statement had no side-effects but actually it did (maybe it
called a volatile function that did something), would you want those
effects to be persisted or not?

regards, tom lane

On Sun, Aug 25, 2019 at 10:12 PM David Wall <d.wall@computer.org> wrote:

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.

Quite frankly I would redesign your application workflow. Sounds like
you are building a framework to issue queries, and I suggest you to
clearly mark transactions only when needed because, disregarding
performances, it does not make much sense to commit/rollback on a
"data quantity" discrimintation. At least, as far as you described it.

Moreover, as Tom pointed out, there could be a SELECT against a
function (that could return nothing at all) with side effects. How are
you going to discriminate such case?

Luca