Обсуждение: 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"