Re: Automatic transactions in SELECT

Joseph Pravato <joseph.pravato@nomagic.com> wrote:

> We use SquirrelSQL to talk to our databases with the Postgres
> 9.2-1002 JDBC driver.

In my previous job the DBAs had an outright ban on using
SquirrelSQL against production or shared development or test
databases because of its bad transactional behavior.  We used
several other tools, none of which caused the same problems.

> What gets particularly confusing with this issue is when your
> query is wrong in any way, you are required to rollback the
> transaction.

This is viewed as a feature since otherwise, in the absence of
careful exception handling, you might commit a transaction after an
important step failed, possibly resulting in data loss.

> Worse yet, you have to re-run any prior statements in the
> transaction if you have any errors. The easiest solution is to
> turn auto-commit on and hope that you never seriously screw up
> data. But, we are not going to, nor allowed to do that. Did I
> miss anything, maybe a setting somewhere in the JDBC driver that
> can fix this issue?

I recommend using savepoints, or a tool which can automatically use
them:

http://www.postgresql.org/docs/9.2/interactive/sql-savepoint.html

FWIW, psql has an option to turn on automatic savepoint usage via
the ON_ERROR_ROLLBACK option.  Use that with care, though, because
if you use it with a script like this, you can regret it:

BEGIN;
CREATE TABLE new_copy [...] ;
INSERT INTO new_copy SELECT * FROM prod_dat;
DROP TABLE prod_data;
ALTER TABLE new_copy RENAME TO prod_data;
COMMIT;

Note the typo in INSERT/SELECT.  With the default behavior, the
script leaves you in the same state you started.  Ignoring errors,
you lose the contents of that table.  That tends not be a problem
with interactive commands, as long as copy/paste of multiple
commands is not used, but it can be a problem with scripts or
copy/paste.

> Please note, one of the recommendations was to switch sql query
> tools, turn on auto-commit, or to use save points.

When I had to do ad hoc data fix queries, I always used
transactions.  If I wasn't sure of my syntax, I tried it on a small
test database first, tried an EXPLAIN of the statement on a
different connection to validate syntax, or used a savepoint.

> We would rather find a solution with the JDBC driver instead of
> switching tools as we have been using SquirrelSQL for over a year
> with various databases. We are currently looking into the use of
> save points, but as of now, any assistance is appreciated.

Due to the desire of members of the community not to lose data due
to a script file typo, any suggestion that a transaction continue
to process subsequent statements after an error without a
subtransaction is a non-starter.  Maybe you can change the
SquirrelSQL source code to have a feature for this, like psql does?

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company