Upgrade from 13.1.1.3 to 13.2.0.1 ERROR: ALTER TYPE ... ADD cannot run inside a transaction block

[wutzebaer]

Any ideas why this errors occur its runnign with postgres:11.18-alpine?

Upgrade from 13.1.1.3 to 13.2.0.1 ERROR: ALTER TYPE ... ADD cannot run inside a transaction block

Running : migration-1-0000-20190730.sql
init

(1 row)

Running : migration-1-0001-20190730.sql
migrate

(1 row)

Running : migration-1-0002-20190912.sql
drop_cexplorer_views

(1 row)

Running : migration-1-0003-20200211.sql
migrate

(1 row)

Running : migration-1-0004-20201026.sql
migrate

(1 row)

Running : migration-1-0005-20210311.sql
migrate

(1 row)

Running : migration-1-0006-20210531.sql
migrate

(1 row)

Running : migration-1-0007-20210611.sql
migrate

(1 row)

Running : migration-1-0008-20210727.sql
migrate

(1 row)

Running : migration-1-0009-20210727.sql
migrate

(1 row)

Running : migration-1-0010-20230612.sql
psql:/nix/store/n7qvj8847bpsvzv018bi36c2wci1cgb0-schema/migration-1-0010-20230612.sql:21: ERROR: ALTER TYPE ... ADD cannot run inside a transaction block
CONTEXT: SQL statement "ALTER TYPE scripttype ADD VALUE 'plutusV3' AFTER 'plutusV2'"
PL/pgSQL function migrate() line 10 at SQL statement
ExitFailure 3

[rdlrt]

Version 13.1.1.3 -> 13.2.0.1 does not have stable path for upgrade in place (alongwith flag considerations, there are also table structure changes that are not backfilled), you'd want to sync from scratch (or if using consumed flag - can use db-sync-snapshot from here )

[wutzebaer]

Thanks i'll try with the snapshot

[sgillespie]

Which version of Postgresql are you using?

[wutzebaer]

postgres:11.18-alpine

[sgillespie]

I think this must be your problem. In the latest release we added some migrations that are only valid using Postgres 12+. I created some instructions here: https://github.com/IntersectMBO/cardano-db-sync/blob/13.2.0.1/doc/upgrading-postgresql.md.

I also gather that you're probably using docker. The instructions were written for a system-wide installation, but I can adapt them to Docker if necessary.

[rdlrt]

In the latest release we added some migrations that are only valid using Postgres 12+.

Are you sure these are supposed to work? We have done upgrade in place from 13.1.1.3 on various instances w/o docker and using postgres 15/16 (tho only with option with consumed_by_tx_id , but regardless) - there are many data integrity issues (eg: rewards table split into instant_rewards is not correctly handled) unless we do a resync

[sgillespie]

I'm pretty sure it's supposed to work, based on the doc here: https://github.com/IntersectMBO/cardano-db-sync/blob/master/doc/migrations.md#upgrading-to-1320x. I was able to upgrade from 13.1.1.3 -> 13.2.0.1 (although I have not verified the data integrity).

I can't guarantee there won't be any issues. The only thing I can say for sure is you won't be able to run the migrations on Postgres 11.