Unable to ALTER table after SELECT data from table

Поиск
Список
Период
Сортировка
От Thomas Carsten Franke
Тема Unable to ALTER table after SELECT data from table
Дата
Msg-id 475D9059.4060803@brunel.de
обсуждение исходный текст
Ответы Re: Unable to ALTER table after SELECT data from table  (Keary Suska <hierophant@pcisys.net>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: rihad
Дата:
Сообщение: TIMESTAMP difference
Следующее
От: Vivek Khera
Дата:
Сообщение: Re: partitioned table query question