Liquibase Create View in Schema - Issue

General Discussion
1

I’m using liquibase 3.5.3.

I have a multi-tenant (Postgres, not sure that matters) environment where each tenant gets a schema created for them when they sign up.

When a user registers, I create a schema and run liquibase on that schema.  To do this, I do not specify the schema in the XML file.  I use:

<changeSet author=“xxyyzz (generated)” id=“148150949234281-2”>
<createView replaceIfExists=“true”
viewName=“v_feeding”>
SELECT
extract(YEAR FROM happened) AS “year”,
extract(MONTH FROM happened) “month”,
extract(DAY FROM happened) AS “day”,
milliliters
FROM feeding
</createView>
</changeSet>

Here is the SQL that is generated:

CREATE VIEW gud3ae2668f77344e2b73f55c317811d94.v_feeding AS SELECT
extract(YEAR FROM happened) AS “year”,
extract(MONTH FROM happened) “month”,
extract(DAY FROM happened) AS “day”,
milliliters
FROM feeding

The issue here (I’m pretty sure) is that I get an error that “feeding” does not exist, but it does exist in the schema ‘gud3ae2668f77344e2b73f55c317811d94’.  I think that since my connection is set to use the schema ‘PUBLIC’ I get that error.  Liquibase seems to add the schema name as a qualifier to the statement when programmatically setting the schema rather than switching the underlying connection’s schema.


What can I do to make this work?  I would like to use this method since it means I can run the Liquibase job as soon as the schema is provisioned, rather than scheduling a job or something.

2

Hey - did you get solve this? I’m having the same issue with the createView using adding the wrong schema to the sql in the view.

3

In our environment we always connect to the postgres using the schema, not public, so this is not an issue.

I’m not a postgres expert, but I think this is due to how the search_path is set on your database or role.