liquibase: Incorrect schema name in the search path is called when using the "--defaultSchemaName" on a non-default schema

Environment

Windows, Linux, PostgreSQL Liquibase Version: version 4.0.0-beta1 Liquibase Integration & Version: CLI

Description

When attempting to create a view to a table in a specific schema, Liquibase attempts to apply the “create view” changeSet to query to the table in the default schema instead.

Steps To Reproduce

Create a new postgreSQL database with schemas: public (default schema), lookup.
Create a changeLog with 2 changeSets, “create table” and “create view”. For example:

<changeSet author="SteveZ" id="1">
        <createTable tableName="execSales">
            <column name="id" type="INTEGER">
                <constraints nullable="false" primaryKey="true" primaryKeyName="id_pkey"/>
            </column>
            <column name="name" type="VARCHAR(255)"/>
        </createTable>
    </changeSet>
    <changeSet author="SteveZ" id="2">
        <createView fullDefinition="false" viewName="newView"> SELECT "execSales".id,
    "execSales".name
   FROM "execSales";
        </createView>
    </changeSet>

Run: liquibase --defaultSchemaName=lookup update

Actual Behavior

The will produce the following error: liquibase.exception.DatabaseException: ERROR: relation "execSales" does not exist It appears that the statement from changeSet “create view”:

SELECT "execSales".id,"execSales".name FROM "execSales";

is trying to query the table in public.execSales. Since there is no table “execSales” in the schema “public” the error occur.

Expected/Desired Behavior

The following SQL should be in the “lookup” schema search path.

SELECT "execSales".id, "execSales".name FROM "execSales";

Workaround

This can be worked around with adding ?currentSchema=lookup to the end of the jdbc connection string before running the update command. For example: url: jdbc:postgresql://localhost:5432/testDB?currentSchema=lookup However, this will not work well when using a multi-schema project (ie: using --schemas=public,users,lookup,accounts).

Acceptance Criteria

When specified by a user, --defaultSchemaName=<myschema> should behave as expected, guiding liquibase commands such as update, updateSQL, rollback, and more to work on the specified schema

About this issue

Original URL
State: closed
Created 4 years ago
Reactions: 3
Comments: 23 (9 by maintainers)

Most upvoted comments

Hi,

I updated my version from 3.10.0 to 3.10.3 and this new feature introduced a bug.

We use types declared in the ‘public’ schema (like the hstore and other types), specific schemas (one by domain and eventually migration) and optionally, the parameter in the ‘currentSchema’ connection string (if the current user does not fit the schema name). Thus, we can use the types declared in the ‘public’ schema.

The problem with this new feature is that the hstore type is no longer accessible. Is there a workaround?

In my opinion, adding the ‘defaultSchema’ in the ‘search-path’ is a good idea. But rather than replacing the existing ‘search_path’, it should be added to the existing value.

SELECT set_config( 'search_path', 'my_schema,' || current_setting('search_path'), true ) WHERE current_setting('search_path') !~ '(^|,)my_schema(,|$)';

ZigZag59 on Dec 10, 2020

Yes, this seems to be working with me in the code going out in 4.11.0

nvoxland on May 23, 2022

FYI: This SEARCH_PATH overriding is fixed in version 4.5.0 with #1864

piomar123 on Jan 14, 2022

I’m also hitting an issue related to this. The issue is caused by a recent change in version 3.10.2. Specifically, our migration scripts add geometry types from the PostGIS extension library. Our migration script (probably incorrectly) do not address the geometry data type correctly. It should probably be public.geometry

 <changeSet author="roryodonnell" id="20190121">

...
        <createTable schemaName="${schema.name}" tableName="tablename">
            ...
            <column name="DATA_POINT" type="geometry(Point,4326)"/>
            ...
        </createTable>

…fyi - I’m using Liquibase via Dropwizard 2.0.13+

roryodonnell on Jan 15, 2021

Hi @ZigZag59 Thanks for bringing this to our attention. We did make a change to search path recently and that may be why you are now seeing this issue. We’ll take a look at what was fixed and see if we can accommodate both requests for searchpath use cases. Or if there is a workaround.

molivasdat on Dec 11, 2020