From: | Thomas Carsten Franke <Thomas-Carsten(dot)Franke(at)brunel(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Unable to ALTER table after SELECT data from table |
Date: | 2007-12-10 19:15:37 |
Message-ID: | 475D9059.4060803@brunel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)Franke(at)brunel(dot)de
Internet: www.brunel.de
Hauptsitz: Airport City, Hermann-Köhl-Str. 1 a, 28199 Bremen
Amtsgericht Bremen HRB 16935
General Manager: Carsten Siebeneich
From | Date | Subject | |
---|---|---|---|
Next Message | Vivek Khera | 2007-12-10 19:17:00 | Re: partitioned table query question |
Previous Message | rihad | 2007-12-10 19:13:42 | TIMESTAMP difference |