Unable to ALTER table after SELECT data from table
Hi,
following I tried for some application logic based data migration
Statement stmt = dbCon.createStatement();
try {
ResultSet geo_columns_rs = stmt.
executeQuery("SELECT baseline_check_version from geodb limit 1");
source_version = Versions.R03_00;
source_version = Versions.R03_00;
}catch (Exception e) {}
to check if column already exists...
If I get an exception in Java I remember that and try to update my
database structure to new version using following:
dbCon.createStatement().execute("ALTER TABLE geodb ADD COLUMN
baseline_check_version VARCHAR(32)");
If I do so I get following error by Postgres:
org.postgresql.util.PSQLException: ERROR: current transaction is
aborted, commands ignored until end of transaction block
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:437)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:339)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:331)
at
de.airbus.omts.geodbgen.communication.dbms.Checker.execute(Checker.java:244)
at
de.airbus.omts.geodbgen.communication.dbms.Checker.migrateDB(Checker.java:206)
at
de.airbus.omts.geodbgen.communication.dbms.Checker.main(Checker.java:390)
To work around this I add an dbCon.rollBack() after select statement
above in good and in bad times. After that ALTER works.
Can someone explain me why I need this rollback ?
Using:
Postgres 8.1.4-1, JDBC postgresql-8.1-405.jdbc3, JDK 1.6
Same problem with JDK 1.5...
Thanks
thomas
--
Mit freundlichen Grüßen
Brunel GmbH
Dipl.-Inf. Thomas Carsten Franke
- Senior Software Designer -
Brunel GmbH
Bereich Communications
Daimlerring 9
D 31135 Hildesheim, Germany
Telefon: 05121 1760-820
Telefax: 05121 1760-999
E-Mail: Thomas-Carsten.Franke@brunel.de
Internet: www.brunel.de
Hauptsitz: Airport City, Hermann-Köhl-Str. 1 a, 28199 Bremen
Amtsgericht Bremen HRB 16935
General Manager: Carsten Siebeneich
on 12/10/07 12:15 PM, Thomas-Carsten.Franke@brunel.de purportedly said:
To work around this I add an dbCon.rollBack() after select statement
above in good and in bad times. After that ALTER works.
Can someone explain me why I need this rollback ?
You only need the rollback when the SELECT statement fails. When an error
occurs within a transaction, the transaction is aborted but not rolled back.
There are likely reasons why the rollback isn't automatic, but I don't know
them.
Your choice is either to run the commands in an "autocommit" mode or, as you
are doing, rollback (or commit, if there was no error) after the SELECT.
Best,
Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"
On Dec 10, 2007 2:48 PM, Keary Suska <hierophant@pcisys.net> wrote:
on 12/10/07 12:15 PM, Thomas-Carsten.Franke@brunel.de purportedly said:
To work around this I add an dbCon.rollBack() after select statement
above in good and in bad times. After that ALTER works.
Can someone explain me why I need this rollback ?You only need the rollback when the SELECT statement fails. When an error
occurs within a transaction, the transaction is aborted but not rolled back.
There are likely reasons why the rollback isn't automatic, but I don't know
them.
I think you confuse postgresql with some other database. Without
setting a savepoint, any error during a transaction and roll it back
upon connection close | rollback | commit.
I.e. the rollback IS automatic. Just not necessarily immediate.
Your choice is either to run the commands in an "autocommit" mode or, as you
are doing, rollback (or commit, if there was no error) after the SELECT.
You can commit with an error. It'll do the same thing as a rollback;
on 12/10/07 2:27 PM, scott.marlowe@gmail.com purportedly said:
To work around this I add an dbCon.rollBack() after select statement
above in good and in bad times. After that ALTER works.
Can someone explain me why I need this rollback ?You only need the rollback when the SELECT statement fails. When an error
occurs within a transaction, the transaction is aborted but not rolled back.
There are likely reasons why the rollback isn't automatic, but I don't know
them.I think you confuse postgresql with some other database. Without
setting a savepoint, any error during a transaction and roll it back
upon connection close | rollback | commit.I.e. the rollback IS automatic. Just not necessarily immediate.
I suppose this clarification is useful for posterity, but or the record, I
as using the term "automatic" to address the apparent expectations of the
OP, and not to the ultimate behavior of postgresql
Best,
Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"