Re: Automatic transactions in SELECT - Mailing list pgsql-jdbc
From | Kevin Grittner |
---|---|
Subject | Re: Automatic transactions in SELECT |
Date | |
Msg-id | 1362077623.96612.YahooMailNeo@web162902.mail.bf1.yahoo.com Whole thread Raw |
In response to | Automatic transactions in SELECT (Joseph Pravato <joseph.pravato@nomagic.com>) |
Responses |
Re: Automatic transactions in SELECT
(Joseph Pravato <joseph.pravato@nomagic.com>)
|
List | pgsql-jdbc |
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
pgsql-jdbc by date: