Issue 190 in flyway: Support for PostgreSQL 8.3
fly...@googlecode.com
Owner: ----
Labels: Type-Defect Priority-Medium
New issue 190 by claus...@gmail.com: Support for PostgreSQL 8.3
http://code.google.com/p/flyway/issues/detail?id=190
I would be very nice if PostgreSQL 8.3 could be supported. At the moment
this error occures:
[DEBUG] Starting to drop all database objects in schema 'public' ...
[ERROR] org.springframework.jdbc.BadSqlGrammarException:
PreparedStatementCallback; bad SQL grammar [select typname from
pg_catalog.pg_type where typcategory in ('P', 'U') and typnamespace in
(select oid from pg_catalog.pg_namespace where nspname = ?)]; nested
exception is org.postgresql.util.PSQLException: ERROR: column "typcategory"
does not exist
[ERROR] Caused by org.postgresql.util.PSQLException: ERROR:
column "typcategory" does not exist
[INFO]
------------------------------------------------------------------------
[ERROR] BUILD ERROR
[INFO]
------------------------------------------------------------------------
[INFO] Flyway Error: org.springframework.jdbc.BadSqlGrammarException:
PreparedStatementCallback; bad SQL grammar [select typname from
pg_catalog.pg_type where typcategory in ('P', 'U') and typnamespace in
(select oid from pg_catalog.pg_namespace where nspname = ?)]; nested
exception is org.postgresql.util.PSQLException: ERROR: column "typcategory"
does not exist
[INFO]
------------------------------------------------------------------------
[DEBUG] Trace
org.apache.maven.lifecycle.LifecycleExecutionException: Flyway Error:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;
bad SQL grammar [select typname from pg_catalog.pg_type where typcategory
in ('P', 'U') and typnamespace in (select oid from pg_catalog.pg_namespace
where nspname = ?)]; nested exception is org.postgresql.util.PSQLException:
ERROR: column "typcategory" does not exist
at
org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoals(DefaultLifecycleExecutor.java:719)
at
org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeStandaloneGoal(DefaultLifecycleExecutor.java:569)
at
org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoal(DefaultLifecycleExecutor.java:539)
at
org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoalAndHandleFailures(DefaultLifecycleExecutor.java:387)
at
org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeTaskSegments(DefaultLifecycleExecutor.java:348)
at
org.apache.maven.lifecycle.DefaultLifecycleExecutor.execute(DefaultLifecycleExecutor.java:180)
at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:328)
at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:138)
at org.apache.maven.cli.MavenCli.main(MavenCli.java:362)
at org.apache.maven.cli.compat.CompatibleMain.main(CompatibleMain.java:60)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.codehaus.classworlds.Launcher.launchEnhanced(Launcher.java:315)
at org.codehaus.classworlds.Launcher.launch(Launcher.java:255)
at org.codehaus.classworlds.Launcher.mainWithExitCode(Launcher.java:430)
at org.codehaus.classworlds.Launcher.main(Launcher.java:375)
Caused by: org.apache.maven.plugin.MojoExecutionException: Flyway Error:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;
bad SQL grammar [select typname from pg_catalog.pg_type where typcategory
in ('P', 'U') and typnamespace in (select oid from pg_catalog.pg_namespace
where nspname = ?)]; nested exception is org.postgresql.util.PSQLException:
ERROR: column "typcategory" does not exist
at
com.googlecode.flyway.maven.AbstractFlywayMojo.execute(AbstractFlywayMojo.java:174)
at
org.apache.maven.plugin.DefaultPluginManager.executeMojo(DefaultPluginManager.java:490)
at
org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoals(DefaultLifecycleExecutor.java:694)
... 17 more
Caused by: org.springframework.jdbc.BadSqlGrammarException:
PreparedStatementCallback; bad SQL grammar [select typname from
pg_catalog.pg_type where typcategory in ('P', 'U') and typnamespace in
(select oid from pg_catalog.pg_namespace where nspname = ?)]; nested
exception is org.postgresql.util.PSQLException: ERROR: column "typcategory"
does not exist
at
org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:641)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:670)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:678)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:710)
at
org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:776)
at
com.googlecode.flyway.core.dbsupport.postgresql.PostgreSQLDbSupport.generateDropStatementsForBaseTypes(PostgreSQLDbSupport.java:175)
at
com.googlecode.flyway.core.dbsupport.postgresql.PostgreSQLDbSupport.createCleanScript(PostgreSQLDbSupport.java:104)
at
com.googlecode.flyway.core.clean.DbCleaner.cleanSchema(DbCleaner.java:97)
at com.googlecode.flyway.core.clean.DbCleaner.clean(DbCleaner.java:82)
at com.googlecode.flyway.core.Flyway.clean(Flyway.java:672)
at com.googlecode.flyway.maven.CleanMojo.doExecute(CleanMojo.java:31)
at
com.googlecode.flyway.maven.AbstractFlywayMojo.execute(AbstractFlywayMojo.java:163)
... 19 more
Caused by: org.postgresql.util.PSQLException: ERROR: column "typcategory"
does not exist
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at
org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:648)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
... 31 more
fly...@googlecode.com
Labels: -Type-Defect Type-Enhancement
Comment #1 on issue 190 by axel.fontai...@gmail.com: Support for
PostgreSQL 8.3
http://code.google.com/p/flyway/issues/detail?id=190
Hi Claus,
Thanks for your suggestion. The priority will depend on the number of stars
it gets.
The errors you see are triggered by the hoops Flyway has to jump through to
completely clean a 9.0 schema. Let's hope some alternative techniques are
available that work with both the 8.3 and 9.0 series...
Cheers
Axel
fly...@googlecode.com
Comment #2 on issue 190 by claus...@gmail.com: Support for PostgreSQL 8.3
http://code.google.com/p/flyway/issues/detail?id=190
Hi Axel,
This will do the job :-)
CREATE OR REPLACE FUNCTION public.truncate_tables(XXXXX IN VARCHAR) RETURNS
void AS $$
DECLARE
stmt RECORD;
BEGIN
FOR stmt IN SELECT tablename FROM pg_tables
WHERE tableowner = XXXXX AND schemaname = 'public' LOOP
execute 'TRUNCATE TABLE public.' || quote_ident(stmt.tablename) |
|';';
END LOOP;
END;
$$ LANGUAGE plpgsql;
Replace XXXXX with the value from the user property in the pom
fly...@googlecode.com
Comment #3 on issue 190 by axel.fontai...@gmail.com: Support for
PostgreSQL 8.3
http://code.google.com/p/flyway/issues/detail?id=190
Hi Claus,
I wish... Unfortunately cleaning a schema takes much more effort than just
issuing truncate table statements. The tables are the easy part. UDTs and
functions are the messy bits, especially when interdependencies come into
play. Have a look at the source, you will see what I mean.
Cheers
Axel
fly...@googlecode.com
fly...@googlecode.com
Comment #5 on issue 190 by claus...@gmail.com: Support for PostgreSQL 8.3
http://code.google.com/p/flyway/issues/detail?id=190
Hi Axel,
What about this one:
drop schema XXX cascade
Cheers
Claus
fly...@googlecode.com
Comment #6 on issue 190 by axel.fontai...@gmail.com: Support for
PostgreSQL 8.3
http://code.google.com/p/flyway/issues/detail?id=190
Hi Claus,
one specific goal is to drop all the objects without dropping the schema
itself. The reason is that many teams have integration test servers with DB
instances managed by DBAs. The schema creation and drop rights are usually
something the developers don't have.
Cheers
Axel
fly...@googlecode.com
Comment #7 on issue 190 by roman.r...@gmail.com: Support for PostgreSQL 8.3
http://code.google.com/p/flyway/issues/detail?id=190
Pre-9.x is very large part of real world Postgres users. If not majority at
the moment. And this support is postponed because someone somewhere
possibly may not have schema create/drop rights?
fly...@googlecode.com
Comment #8 on issue 190 by pereszte...@tigra.hu: Support for PostgreSQL 8.3
http://code.google.com/p/flyway/issues/detail?id=190
Maybe implementing clean with drop schema and using the alternative
strategy as backup, if rights are not granted, can be a solution. I hope
that the day soon will come where DBA/sysadmin and developer can work
together on CI/CD process.
fly...@googlecode.com
Labels: Database-PostgreSQL Component-Core
Comment #9 on issue 190 by axel.fontai...@gmail.com: Support for
PostgreSQL 8.3
http://code.google.com/p/flyway/issues/detail?id=190
Schema Create/Drop will come as part of Issue 74 in 1.9. This could be a
good workaround for some of you.
fly...@googlecode.com
Status: Migrated
Comment #10 on issue 190 by axel.fontai...@gmail.com: Support for
PostgreSQL 8.3
http://code.google.com/p/flyway/issues/detail?id=190
Migrated to GitHub: https://github.com/flyway/flyway/issues/90
To everyone who starred this issue: please leave a +1 comment on the linked
GitHub issue
--
You received this message because this project is configured to send all
issue notifications to this address.
You may adjust your notification preferences at:
https://code.google.com/hosting/settings