liquibase: Default schema is not recognized on latest version

Hello, I have this changeset:

--changeset nicola:003 runAlways:true
--comment: call function which enable auditing for every table 
SELECT audit_trigger_install();

--rollback -- nothing to do here

This functions exists inside the schema application

CREATE OR REPLACE FUNCTION application.audit_trigger_install()
...

Since about 2 years ago I started this project, I always used the changelog as you can see, without a specific declaration of which schema is the function installed. The command I used has always been:

liquibase \
   --driver=org.postgresql.Driver \
   --classpath=postgresql-42.2.20.jar \
   --url=jdbc:postgresql://127.0.0.1/postgres \
   --username=xxxxxx \
   --password=yyyyyyyy \
   --changeLogFile=databaseChangelog.xml \
   --liquibaseSchemaName=liquibase \
   --defaultSchemaName=application \
   --contexts=dev\
   update

You can see that there is a specific notation “defaultSchemaName” to allow Liquibase work on a specific schema, and that has always worked since always using the version 4.2.2 I installed at first

Liquibase Community 4.2.2 by Datical
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 17:54:49 (version 4.2.2 #36 built at 2020-12-09 20:07+0000)
Liquibase: Update has been successful.

Today I updated the version of Liquibase up to the latest one, 4.9.0 (no other changes, same changelog, same postgres driver, same liquibase command, …) and here is the result

####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 18:06:46 (version 4.9.0 #1885 built at 2022-03-16 16:58+0000)
Liquibase Version: 4.9.0
Liquibase Community 4.9.0 by Liquibase
Running Changeset: databaseChangeLog_BUILD/changelog_1000_001_setup auto_trigger[audit_tables].sql::1000_001-003::nicola

Unexpected error running Liquibase: ERROR: function audit_trigger_install() does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 8 [Failed SQL: (0) SELECT audit_trigger_install()]

The only way I found to be working (with this new release) is forcing the schema as prefix of the function inside the changelog;

SELECT application.audit_trigger_install();

But, while this seems to be working, has a wide impact on my changelogs because I have many cases like this. I then reverted the version back to previous 4.2.2 and it is working again.

What is going on? Something changed during one of the last releases? Thanks

About this issue

Original URL
State: closed
Created 2 years ago
Reactions: 1
Comments: 21 (10 by maintainers)

Most upvoted comments

@SambitNanda It is in our queue to resolve. Sorry we haven’t been able to get to it yet.

kataggart on Sep 21, 2022

@filipelautert That is interesting. It wasn’t obvious in the documentation to me, but I will let you know the result if my testing. Thanks

SambitNanda on Oct 21, 2022

@filipelautert Invoking the Liquibase update() from a java class directly. Our DB upgrader is neither maven or springboot. It starts an init-container and upgrades the DB before the main service comes up.

We have an Oracle system type user with DBA permissions that creates the databse objects. It needs to create them in application/service schema.

Database getDatabase(String schema, Connection connection) throws DatabaseException {
   Database database = DatabaseFactory.getInstance() .findCorrectDatabaseImplementation(new 
                                                                                                                                        JdbcConnection(connection));
    if (schema != null && !schema.isBlank()) {
        database.setDefaultSchemaName(schema);
    }

    return database;
}

 void executeChangeLog(Contexts contexts, String schemaToConnect, final String resourcePath)
        throws LiquibaseException, SQLException, IOException {
    try (Connection connection = getConnection()) {
        Database database = getDatabase(schemaToConnect, connection);

    // Now execute service's changelogs
        Liquibase liquibase =
                new liquibase.Liquibase(resourcePath, new ClassLoaderResourceAccessor(), database);
        liquibase.update(contexts, new LabelExpression());
    }
}

Current workaround Get this executed first before other objects are created.

<changeSet id="oracle-alter-session" author="me">
  <sql dbms="oracle">ALTER SESSION SET CURRENT_SCHEMA=${database.defaultSchemaName}</sql>
</changeSet>

Hope that helps.

SambitNanda on Oct 20, 2022

Hi @kataggart , I think I found an additional information about this.

Using the working version 4.2.2 I tried the updateSQL command in order to find some hint on where is the issue. This is the resulting code

-- Changeset databaseChangeLog_BUILD/changelog_1000_001_setup auto_trigger[audit_tables].sql::1000_001-003::nicola
-- call function which enable auditing for every table 
SET SEARCH_PATH TO application;

SELECT audit_trigger_install();

You can see that there is a notation here SET SEARCH_PATH TO application;

While using the latest version 4.9.0, the generated code is different

-- Changeset databaseChangeLog_BUILD/changelog_1000_001_setup auto_trigger[audit_tables].sql::1000_001-003::nicola
-- call function which enable auditing for every table 
SELECT audit_trigger_install();

Without the schema overidde with the SEARCH_PATH it is understandable why it is not able to find the function. It seems that somehow we lost the defaultSchemaName definition with latest releases.

NicolaSpreafico on Mar 23, 2022