[SOLVED] "Cannot perform CREATE PROCEDURE. This session does not have a current schema" when creating procedure via Liquibase over Snowflake

"Cannot perform CREATE PROCEDURE. This session does not have a current schema" when creating procedure via Liquibase over Snowflake

I'm using Liquibase's sqlFile tag to create a stored procedure in a Snowflake DB:

<changeSet id="add my_proc" author="tzachz">
    <sqlFile path="my_proc.sql"/>
</changeSet>

The file contains a valid, tested stored procedure.

I'm running liquibase update while passing --defaultSchemaName=MY_SCHEMA (in additional to full Snowflake connection details). While other changesets work successfully (including once creating tables and other ones using sqlFile tag), this changeset fails with the following error:

liquibase.exception.DatabaseException: Cannot perform CREATE PROCEDURE. This session does not have a current schema. Call 'USE SCHEMA', or use a qualified name. [Failed SQL: (90106) [full procedure SQL]]

I've seen similar questions about creating TABLES (e.g. here), but the solutions there (which explain the issue is a permissions issue) do not seem to work for the stored procedure case.

Solution

The solution requires switching from sqlFile to createProcedure (you can still use the same SQL file via the path argument):

<changeSet id="add my_proc" author="tzachz">
    <createProcedure dbms="snowflake" path="my_proc.sql"/>
</changeSet>

Additionally you must pass the extra flag --alwaysOverrideStoredLogicSchema=true to the liquibase update command.

Either one of these on its own isn't sufficient - both changes are required.

See details about this flag here.