Обсуждение: For SELECT statement (just a reading one, no 'FOR UPDATE'), is COMMITor ROLLBACK preferred?

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

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