Support schema other than public on PostgreSQL
Description
Environment
PostgreSQL 9.3.5
Activity
Nathan Voxland March 10, 2015 at 9:23 PM
Merged pull request https://github.com/liquibase/liquibase/pull/359
Piotr Findeisen February 17, 2015 at 12:27 PM
I created a pull request: https://github.com/liquibase/liquibase/pull/359
Piotr Findeisen February 16, 2015 at 4:45 PMEdited
The fix for DropPrimaryKeyGenerator on Postgresql would be
String schemaName = Objects.firstNonNull(statement.getSchemaName(), database.getDefaultSchemaName());
schemaName = database.correctObjectName(schemaName, Schema.class);
String tableName = database.correctObjectName(statement.getTableName(), Table.class);
String sql = String.format(""
+ "DO $$ DECLARE constraint_name varchar;\n"
+ "BEGIN\n"
+ " SELECT tc.CONSTRAINT_NAME into strict constraint_name\n"
+ " FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc\n"
+ " WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'\n"
+ " AND TABLE_NAME = '%2$s' AND TABLE_SCHEMA = '%1$s';\n"
+ " EXECUTE 'alter table %1$s.%2$s drop constraint ' || constraint_name;\n"
+ "END $$;"
, schemaName, tableName);+ no need to define temporary function __liquibase_drop_pk just to remove it afterwards.
(or https://liquibase.jira.com/browse/CORE-1901#icft=CORE-1901)
Piotr Findeisen February 16, 2015 at 4:05 PM
When this if fixed, second problem is DropPrimaryKeyGenerator, which searches for primary key without giving schema name. It will fail if similar table exists in some other schema.
Piotr Findeisen February 16, 2015 at 2:58 PMEdited
With Postgres JDBC driver 9.4 it is even possible to specify curent schema in JDBC url like this (https://jdbc.postgresql.org/documentation/94/connect.html#connection-parameters):
jdbc:postgresql://localhost/mydatabase?currentSchema=myschemathen the following would do the trick in PostgresDatabase.getConnectionSchemaName()
@Override
protected String getConnectionSchemaName() {
try {
String currentSchema = ExecutorService.getInstance().getExecutor(this)
.queryForObject(new RawCallStatement("select current_schema"), String.class);
return currentSchema;
} catch (Exception e) {
throw new RuntimeException("Failed to get current schema", e);
}
}
liquibase.database.core.PostgresDatabase.getConnectionSchemaName()hard-codes"public". It should return value ofcurrent_schema()PostgreSQL function (see http://www.postgresql.org/docs/9.3/static/functions-info.html)