Обсуждение: Unable to ALTER table after SELECT data from table

Поиск
Список
Период
Сортировка

Unable to ALTER table after SELECT data from table

От
Thomas Carsten Franke
Дата:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iQEVAwUBR12PxZp7rRUHwFuZAQKnfggAvc8pYllA4RwLJqiiIuxAzF0zq4YcSAAv
Xl9dvKATWEkk1I22YfylAQP4JDzAUx630+0qe3Z6DTOC5hgQMC2yEFftebO/I8cK
9HcGyIJtAq75uwKuKMvoZVGYh0c91xZlRGhtGLUADITqn6L+r9JvnaoaKvZfOZGl
IcubUzw5JoWVANuD+PiePvSWiudYG966UD9eT6xPvYWaybITiof/xd/nlYV0zkdL
ZIHxZKEfS8KngGlqesqJDYM/XedVYvLXGe8vH6QTeNKWAr8l+rrBBseBNUrco6Jz
1w15bxe1k2n6KZbkCn6KhJARo3aw4axKgq0I9HDytFEfmt703U+rZQ==
=3VjN
-----END PGP SIGNATURE-----

Re: Unable to ALTER table after SELECT data from table

От
Martijn van Oosterhout
Дата:
On Mon, Dec 10, 2007 at 08:13:09PM +0100, Thomas Carsten Franke wrote:
> 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

It means exactly what it says. You (or Java for you) started a
transaction block and everything in a transaction block is either
committed or aborted. Once an error has been raised, everything after
that is ignore till the end of the transaction.

> 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 ?

Rollback/commit either will do. You just need ot start a new
transaction. What people usually do is to do stuff like what you're
doing outside any transactions, thus avoiding the whole problem.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy
Вложения