Unable to upgrade PostgreSQL schema version to 1.9

Expected Behavior

temporal_visibility schema upgraded to v1.9 with no errors

Actual Behavior

We get the following error:

ERROR   Unable to update SQL schema.    {"error": "error executing statement:pq: relation \"queue\" does not exist", "logging-call-at": "handler.go:78"}

Steps to Reproduce the Problem and Background

We are currently in the process of upgrading from v1.18.4 to v1.19.0. I was able to upgrade the temporal db schema by running the following command with no errors: ./temporal-sql-tool --ep DB_ENDPOINT -p 5432 -u USER -pw PASSWORD --plugin postgres --db temporal update-schema -d /etc/temporal/schema/postgresql/v96/temporal/versioned

However, when running the above command with the temporal_visibility DB, it fails with the following error:

2023-03-13T02:57:44.512Z        INFO    UpdateSchemeTask started        {"config": {"DBName":"","TargetVersion":"","SchemaDir":"/etc/temporal/schema/postgresql/v96/temporal/versioned","IsDryRun":false}, "logging-call-at": "updatetask.go:97"}
2023-03-13T02:57:44.514Z        DEBUG   Schema Dirs: [] {"logging-call-at": "updatetask.go:186"}
2023-03-13T02:57:44.514Z        DEBUG   found zero updates from current version 1.8     {"logging-call-at": "updatetask.go:127"}
2023-03-13T02:57:44.514Z        INFO    UpdateSchemeTask done   {"logging-call-at": "updatetask.go:120"}
bash-5.1# ./temporal-sql-tool --ep temporal-aurora-pgsql-cluster.cluster-cuniohm9tqjh.ap-northeast-1.rds.amazonaws.com -p 5432 -u dbadmin -pw 0b270c2d --plugin postgres --db temporal_visibility update-schema -d /etc/temporal/schema/postgresql/v96/temporal/versioned
2023-03-13T02:58:24.460Z        INFO    UpdateSchemeTask started        {"config": {"DBName":"","TargetVersion":"","SchemaDir":"/etc/temporal/schema/postgresql/v96/temporal/versioned","IsDryRun":false}, "logging-call-at": "updatetask.go:97"}
2023-03-13T02:58:24.462Z        DEBUG   Schema Dirs: [v1.2 v1.3 v1.4 v1.5 v1.6 v1.7 v1.8]       {"logging-call-at": "updatetask.go:186"}
2023-03-13T02:58:24.462Z        INFO    Processing schema file: /etc/temporal/schema/postgresql/v96/temporal/versioned/v1.2/queue.sql   {"logging-call-at": "updatetask.go:232"}
2023-03-13T02:58:24.462Z        INFO    Processing schema file: /etc/temporal/schema/postgresql/v96/temporal/versioned/v1.3/visibility_tasks.sql        {"logging-call-at": "updatetask.go:232"}
2023-03-13T02:58:24.462Z        INFO    Processing schema file: /etc/temporal/schema/postgresql/v96/temporal/versioned/v1.4/cluster_metadata.sql        {"logging-call-at": "updatetask.go:232"}
2023-03-13T02:58:24.462Z        INFO    Processing schema file: /etc/temporal/schema/postgresql/v96/temporal/versioned/v1.5/event.sql   {"logging-call-at": "updatetask.go:232"}
2023-03-13T02:58:24.462Z        INFO    Processing schema file: /etc/temporal/schema/postgresql/v96/temporal/versioned/v1.5/executions.sql      {"logging-call-at": "updatetask.go:232"}
2023-03-13T02:58:24.462Z        INFO    Processing schema file: /etc/temporal/schema/postgresql/v96/temporal/versioned/v1.5/cluster_membership.sql      {"logging-call-at": "updatetask.go:232"}
2023-03-13T02:58:24.462Z        INFO    Processing schema file: /etc/temporal/schema/postgresql/v96/temporal/versioned/v1.6/queue_metadata.sql  {"logging-call-at": "updatetask.go:232"}
2023-03-13T02:58:24.462Z        INFO    Processing schema file: /etc/temporal/schema/postgresql/v96/temporal/versioned/v1.7/cluster_metadata_info.sql   {"logging-call-at": "updatetask.go:232"}
2023-03-13T02:58:24.462Z        INFO    Processing schema file: /etc/temporal/schema/postgresql/v96/temporal/versioned/v1.7/no_start_version.sql        {"logging-call-at": "updatetask.go:232"}
2023-03-13T02:58:24.462Z        INFO    Processing schema file: /etc/temporal/schema/postgresql/v96/temporal/versioned/v1.7/tiered_storage_tasks.sql    {"logging-call-at": "updatetask.go:232"}
2023-03-13T02:58:24.462Z        INFO    Processing schema file: /etc/temporal/schema/postgresql/v96/temporal/versioned/v1.8/drop_unused_tasks_table.sql {"logging-call-at": "updatetask.go:232"}
2023-03-13T02:58:24.462Z        INFO    Processing schema file: /etc/temporal/schema/postgresql/v96/temporal/versioned/v1.8/alter_columns.sql   {"logging-call-at": "updatetask.go:232"}
2023-03-13T02:58:24.462Z        DEBUG   ---- Executing updates for version 1.2 ----     {"logging-call-at": "updatetask.go:150"}
2023-03-13T02:58:24.463Z        DEBUG   ALTER TABLE queue ADD message_encoding VARCHAR(16) NOT NULL DEFAULT 'Json';     {"logging-call-at": "updatetask.go:152"}
2023-03-13T02:58:24.463Z        ERROR   Unable to update SQL schema.    {"error": "error executing statement:pq: relation \"queue\" does not exist", "logging-call-at": "handler.go:78"}

Is this a known issue?

dhiaayachi / temporal