JdbcDatabase / PostgresDatabase.setDefaultSchema() does not set schema for <sql> change
Description
Environment
PostgreSQL 9.3
testing discovered
Activity
Matt Smith January 14, 2016 at 2:24 AM
I have come across this issue also using Postgres 9.4 and Hibernate EM (4.2 and 5.0). I use Hibernate multi-tenant support and switch on the schema, so I'm doing the SET SCHEMA command before creating the Liquibase Database object. When I use the <sql> element in the change log the entire batch is run against the 'public' schema instead of the one it was set to run against: 'localhost'.
If I use the <createTable> elements then Liquibase prefixes the table names in the CREATE TABLE statements, but does not prefix table names referenced in foreign keys. So I end up with a table created but it's FK pointing to a table with the same name in a different schema. Here's an example of the SQL that Liquibase generated in this scenario:
CREATE TABLE localhost.airport (id VARCHAR(255) NOT NULL, airportname VARCHAR(255) NOT NULL, code CHAR(4) NOT NULL, country_id VARCHAR(255) NOT NULL, CONSTRAINT "PK_airport" PRIMARY KEY (id), CONSTRAINT "FK_airport_country" FOREIGN KEY (country_id) REFERENCES country(id))Details
Details
Reporter
Fix versions
Affects versions
Priority
Major
JdbcDatabase.setDefaultSchema()is provided but it only works for standard changes. It doesn't work for<sql>which requires workarounds like this: (https://github.com/soluvas/soluvas-framework/commit/60c52e436c44c6ceee588c00d910bf7d43e40957)/** * Migrates using Liquibase for the specified {@code tenantId}. * @param tenantId * @throws SQLException * @throws LiquibaseException */ public void migrate(String tenantId) throws SQLException, LiquibaseException { log.info("[{}] Migrating {}", tenantId, entityClass.getSimpleName()); final Contexts contexts = new Contexts(); final ClassLoaderResourceAccessor resourceAccessor = new ClassLoaderResourceAccessor(this.entityClass.getClassLoader()); try (final Connection conn = dataSource.getConnection()) { // TODO: SET SCHEMA is workaround for Liquibase's not setting schema for <sql> final Statement st = conn.createStatement(); st.executeUpdate("SET SCHEMA '" + tenantId + "'"); final JdbcConnection jdbc = new JdbcConnection(conn); final PostgresDatabase db = new PostgresDatabase(); db.setDefaultSchemaName(tenantId); try { db.setConnection(jdbc); final Liquibase liquibase = new Liquibase(liquibasePath, resourceAccessor, db); liquibase.update(contexts); } finally { st.executeUpdate("SET SCHEMA 'public'"); db.close(); } } }(Important: the order is important, for the workaround to work,
SET SCHEMAmust be executed beforenew JdbcConnection, otherwise Liquibase will still use thepublicschema).Please make
setDefaultSchema()also performsSET SCHEMAso that<sql>will use the proper schema.Note that hardcoding the schema name inside the
<sql>is not an option as thedefaultSchemaNameis dynamically provided by Java code.