Redshift Deployments Fail with "relation does not exist" When Using --default-schema-name (Liquibase Secure 5.2.0)

Summary

On Liquibase Secure 5.2.0, deployments to Amazon Redshift can fail with an error like:

liquibase.exception.DatabaseException: ERROR: relation "table_name" does not exist
[Failed SQL: (0) alter table table_name add column column_name numeric(14,0) default null]

This occurs when a deployment relies on the --default-schema-name property (or the liquibase.command.defaultSchemaName property file setting) to target a schema, and the SQL in the changeset does not schema-qualify the object name (e.g., alter table orders ... instead of alter table my_schema.orders ...).

This issue is fixed in Liquibase Secure 5.2.1 (listed in the release notes as INT-2125).

Symptoms

• Deployments that worked on earlier versions begin failing after upgrading to Liquibase Secure 5.2.0.
• The error typically appears on ALTER TABLE statements (or other DML/DDL referencing existing objects), but can affect any unqualified SQL statement.
• The referenced table or object does exist in the database, just not in the schema Liquibase is checking.
• The same changeset succeeds when run against a plain PostgreSQL database, which can make the issue look Redshift-specific or environment-specific.
• The same changeset succeeds on Liquibase Secure 5.0.3 and earlier.

Cause

Liquibase Secure 5.2.0 changed how the --default-schema-name property is applied on Postgres-family databases: it moved from issuing a session-level SET SEARCH_PATH to a transaction-scoped SET LOCAL SEARCH_PATH.

SET LOCAL only takes effect inside an open transaction block. Redshift, however, is treated as an autocommit-only database by Liquibase, so there is no open transaction for SET LOCAL to apply to. As a result, the statement silently does nothing, --default-schema-name is never actually applied, and any unqualified SQL resolves against the database's default search path (typically "$user", public) instead of the intended schema. If no matching object exists in that default path, Liquibase reports it as missing.

Because plain PostgreSQL does not force autocommit the way Redshift does, SET LOCAL works as expected there, so this issue is specific to Redshift (and any other database where Liquibase forces autocommit and DDL cannot run inside a transaction).

Important caution: this same defect means an unqualified CREATE TABLE statement on 5.2.0 could silently succeed in the wrong schema (the default search path) rather than raising an error. If you deployed changesets that create new objects while on 5.2.0 with --default-schema-name set, verify that those objects were created in the intended schema.

Affected Versions

• Liquibase Secure 5.2.0
• Databases affected: Amazon Redshift
• Not affected: Liquibase Secure version 5.1.1 and earlier; standard PostgreSQL connections (autocommit is not forced, so SET LOCAL behaves correctly)

Resolution

Upgrade to Liquibase Secure 5.2.1 or later. 

This fix ensures Liquibase falls back to a session-level SET SEARCH_PATH when the connection does not support DDL inside a transaction (as with Redshift), so --default-schema-name is correctly applied.

Workarounds (if you cannot upgrade immediately)

If you're on Liquibase Secure 5.2.0 and cannot move to 5.2.1 right away, use one of the following until you can upgrade:

1. Roll back to Liquibase Secure 5.1.1 (or another version prior to 5.2.0).
2. Schema-qualify object names directly in your SQL (e.g., my_schema.table_name instead of table_name), rather than relying solely on --default-schema-name. Note this requires updating your changesets and may not be practical if your changelogs are designed to be schema-agnostic.

If you took either of these workarounds, no further action is needed once you upgrade to 5.2.1, other than confirming objects were created in the correct schema as noted in the caution above.