Re: For SELECT statement (just a reading one, no 'FOR UPDATE'), is COMMIT or ROLLBACK preferred?
On 8/25/19 12:40 PM, Rob Sargent wrote On Aug 25, 2019, at 1:09 PM, David Wall 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.
For SELECT statement (just a reading one, no 'FOR UPDATE'), is COMMIT or 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
Transaction state on connection Idle/Open/Failed
In JDBC (latest version), I'm using the org.postgresql.jdbc.PgConnection.getTransactionState() that returns an enumeration of IDLE, OPEN or FAILED. I am familiar with IDLE, meaning the connection has a new transaction started, but isn't doing anything. We think of this as the "normal" state for our connections. How does OPEN compare to IDLE? If we stop using a connection that's in the OPEN state, is something wrong? That is, does it have pending updates that require a commit/rollback? Same for FAILED. If we no longer need a connection in the FAILED state, is something wrong? Does it have pending updates that require a commit/rollback (assuming in this state rollback makes more sense). I'm really trying to find out what it means for the connection to be in OPEN or FAILED states compared to IDLE. Thanks, David
Re: Code of Conduct plan
On 9/14/18 7:52 AM, James Keener wrote: I fail to see how that makes everyone here part of a community anymore than I'm part of the "community" of regulars at a bar I walk into for the first time. As I said, the rules can and should apply within the list, but applying them outside the list is odd and wreaks of authoritarianism. Jim In the 20 years I've been using PG, I've not noted any bizarre "list speech" except this discussion that suggests others should monitor people's behavior wherever they are, and report any "infraction" to PG, so PG can boot them. I'm with those who think that idea is diametrically opposed to open source's freedom. What next, monitor what apps people are using their DB for and decide if the "community" approves of its character or not? David
