FatalError: cannot alter type of a column used by a view or rule (public.tx_metadata)

[zxpectre]

OS
Your OS:

Operating System: Ubuntu 24.04 LTS                
Kernel: Linux 6.8.0-40-generic
Architecture: x86-64
(hostnamectl)

Versions
The db-sync version (eg cardano-db-sync --version):
Docker image: ghcr.io/intersectmbo/cardano-db-sync:13.4.0.1

PostgreSQL version:
16.1-bullseye

Build/Install Method
The method you use to build or install cardano-db-sync:
docker-compose (podman)

Run method
The method you used to run cardano-db-sync (eg Nix/Docker/systemd/none):
docker-compose (podman)

Additional context
Add any other context about the problem here.

Cardano GraphQL, Koios and Dandelion's PostGREST (separate schema and PostGREST, like Koios) APIs are present on same docker compose stack and reading from db. Historically this worked without issues until this version.

Problem Report
Please do not include screenshots or images, but instead cut and paste any relevant log messages
or errors.

New Db Sync boot sequence SQL migrations or operations interfere with other API schemas.
I'm contributing with Dandelion PostGREST API, not just using it.

From all the tables that are using the exposure through views technique, Db Sync only have critical issues with public.tx_metadata

cardano-db-sync-1  | [db-sync-node:Warning:6] [2024-08-27 22:11:41.69 UTC] Adding jsonb datatypes back to the database. This can take time.
cardano-db-sync-1  | cardano-db-sync: DBRJsonbInSchemaSqlError {sqlState = "0A000", sqlExecStatus = FatalError, sqlErrorMsg = "cannot alter type of a column used by a view or rule", sqlErrorDetail = "rule _RETURN on view dandelion_postgrest.tx_metadata depends on column "json"", sqlErrorHint = ""}

Maybe I'm getting the error message wrong, but this should be allowed right?

How to reproduce?

Extract from full code:

		EXECUTE format('CREATE OR REPLACE VIEW %s.%s AS SELECT * FROM %s.%s ;',
				new_schema, -- "dandelion_postgrest" new schema where to create the view
				table_record.tablename, -- "tx_metadata" table name of table populated by dbsync 
				original_schema, -- "public" schema  populated by dbsync
				table_record.tablename ) ; "tx_metadata" view name 

[rdlrt]

Does your dbsync config contain removal of jsonb as documented?

If so, modify that as per your case

[zxpectre]

remove_jsonb_from_schema is not present on our configs. Should be ok this way.

[alekswithakayy]

I am getting this error as well on start up:

cardano-db-sync-1  | [db-sync-node:Warning:6] [2024-09-23 21:52:49.51 UTC] Adding jsonb datatypes back to the database. This can take time.
cardano-db-sync-1  | cardano-db-sync: DBRJsonbInSchemaSqlError {sqlState = "0A000", sqlExecStatus = FatalError, sqlErrorMsg = "cannot alter type of a column used by a view or rule", sqlErrorDetail = "rule _RETURN on view api.tx_metadata depends on column \"json\"", sqlErrorHint = ""}

I can resolve it by dropping my view api.tx_metadata and then starting up db-sync but this is not ideal.

@rdlrt I'm trying to figure out how to set remove_jsonb_from_schema: disable when using docker compose. Do you know where the default configuration file is in the container? I want to keep all the defaults and just add this one parameter

[rdlrt]

You can provide your own config file as per documentation here

[alekswithakayy]

Ok but I don't know what I'm overriding if I set that ie Overrides the db-sync-config.json provided by the network configuration.

What is the default network configuration?

[rdlrt]

When you specify NETWORK environment variable for starting up docker, you're provided with default dbsync config (whose contents would be something like here ). If you want to override with your config you'd be providing it a complete config file - with the insert_options stanza , for example - we use these flags in koios config. The existing tables/data in the DB and your config should match, if unsure - drop the existing DB and sync with parameters you want, reading the documentation at configuration.md

[alekswithakayy]

@rdlrt Even after configuring "remove_jsonb_from_schema": "disable" I am still getting the following error on db-sync start up. You can see the parameter was set properly as ioRemoveJsonbFromSchema = False

cardano-db-sync-1  | /run/secrets
cardano-db-sync-1  | Generating PGPASS file
cardano-db-sync-1  | Connecting to network: mainnet
cardano-db-sync-1  | [db-sync-node:Info:6] [2024-09-28 21:48:12.21 UTC] Version number: 13.5.0.2
cardano-db-sync-1  | [db-sync-node:Info:6] [2024-09-28 21:48:12.21 UTC] Git hash: fe05fc5f1f72b0b72b19b0f19b3b7811feb91edc
cardano-db-sync-1  | [db-sync-node:Info:6] [2024-09-28 21:48:12.21 UTC] Enviroment variable DbSyncAbortOnPanic: False
cardano-db-sync-1  | [db-sync-node:Info:6] [2024-09-28 21:48:12.21 UTC] SyncNodeParams {enpConfigFile = ConfigFile {unConfigFile = "/config/mainnet/cardano-db-sync/config.json"}, enpSocketPath = SocketPath {unSocketPath = "/node-ipc/node.socket"}, enpMaybeLedgerStateDir = Just (LedgerStateDir {unLedgerStateDir = "/var/lib/cexplorer"}), enpMigrationDir = MigrationDir "/nix/store/vxx1m24c8xifl84gq2517a1m3j80ba3r-schema", enpPGPassSource = PGPassDefaultEnv, enpEpochDisabled = False, enpHasCache = True, enpSkipFix = False, enpOnlyFix = False, enpForceIndexes = False, enpHasInOut = True, enpSnEveryFollowing = 500, enpSnEveryLagging = 10000, enpMaybeRollback = Nothing}
cardano-db-sync-1  | [db-sync-node:Info:6] [2024-09-28 21:48:12.21 UTC] SyncOptions {soptEpochAndCacheEnabled = True, soptAbortOnInvalid = False, soptCache = True, soptSkipFix = False, soptOnlyFix = False, soptPruneConsumeMigration = PruneConsumeMigration {pcmPruneTxOut = False, pcmConsumeOrPruneTxOut = False, pcmSkipTxIn = False}, soptInsertOptions = InsertOptions {ioTxCBOR = False, ioInOut = True, ioUseLedger = True, ioShelley = True, ioRewards = True, ioMultiAssets = True, ioMetadata = True, ioKeepMetadataNames = Nothing, ioPlutusExtra = True, ioOffChainPoolData = True, ioPoolStats = False, ioGov = True, ioRemoveJsonbFromSchema = False}, snapshotEveryFollowing = 500, snapshotEveryLagging = 10000}
cardano-db-sync-1  | [db-sync-node:Info:6] [2024-09-28 21:48:12.22 UTC] Schema migration files validated
cardano-db-sync-1  | [db-sync-node:Info:6] [2024-09-28 21:48:12.23 UTC] Running database migrations in mode Initial
cardano-db-sync-1  | [db-sync-node:Info:6] [2024-09-28 21:48:12.23 UTC] Found maintenance_work_mem=1GB, max_parallel_maintenance_workers=4
cardano-db-sync-1  | [db-sync-node:Info:6] [2024-09-28 21:48:13.73 UTC] All migrations were executed
cardano-db-sync-1  | [db-sync-node:Info:6] [2024-09-28 21:48:13.73 UTC] New user indexes were not created. They may be created later if necessary.
cardano-db-sync-1  | [db-sync-node:Info:6] [2024-09-28 21:48:13.73 UTC] Using byron genesis file from: "/config/mainnet/cardano-db-sync/../cardano-node/../genesis/byron.json"
cardano-db-sync-1  | [db-sync-node:Info:6] [2024-09-28 21:48:13.73 UTC] Using shelley genesis file from: "/config/mainnet/cardano-db-sync/../cardano-node/../genesis/shelley.json"
cardano-db-sync-1  | [db-sync-node:Info:6] [2024-09-28 21:48:13.73 UTC] Using alonzo genesis file from: "/config/mainnet/cardano-db-sync/../cardano-node/../genesis/alonzo.json"
cardano-db-sync-1  | [db-sync-node:Info:6] [2024-09-28 21:48:14.08 UTC] NetworkMagic: 764824073
cardano-db-sync-1  | [db-sync-node:Warning:6] [2024-09-28 21:48:14.39 UTC] Adding jsonb datatypes back to the database. This can take time.
cardano-db-sync-1  | cardano-db-sync: DBRJsonbInSchemaSqlError {sqlState = "0A000", sqlExecStatus = FatalError, sqlErrorMsg = "cannot alter type of a column used by a view or rule", sqlErrorDetail = "rule _RETURN on view api.tx_metadata depends on column \"json\"", sqlErrorHint = ""}
cardano-db-sync-1 exited with code 0