Canvas Data 2 Postgres Error with Enum Types

[cdellapietra]

Hi All,

Our group noticed a few tables producing error messages with the updates to the enumeration types recently. Upgrading the DAP client to the 1.3.1 didn't seem to fix the problem with the enum type update, but I did notice a change in the syntax of the ALTER TYPE statement:

1.3.0:

2025-01-31 10:28:14,932 - ERROR - error executing query:
ALTER TYPE "canvas"."rubrics__workflow_state"
ADD VALUE 'archived',
ADD VALUE 'draft';

1.3.1:

2025-01-31 10:32:04,841 - ERROR - error executing query:
ALTER TYPE "canvas"."rubrics__workflow_state" ADD VALUE 'archived';
ALTER TYPE "canvas"."rubrics__workflow_state" ADD VALUE 'draft';

We were able to work around this issue by running the ALTER TYPE statements one at a time in Postgres (we are running version 10.23), but attempts to update more than one enum type are met with the error:

ERROR: ALTER TYPE ... ADD cannot be executed from a function or multi-command string SQL state: 25001

Any help would be greatly appreciated.

[stimme · Accepted solution]

@cdellapietra I think the issue you're encountering may be caused by incompatibility between the DAP client and the version of Postgres you are running. The error message you received suggests that the DAP client wraps multiple ALTER TYPE statements in a transaction block. That approach is supported by newer versions of Postgres, but not version 10. Comparing the ALTER TYPE document's Notes section shows that version 16 (the lowest version supported by DAP) allows it, but version 10 does not.

Updating to Postgres 16 seems advisable.