search

IntersectMBO / cardano-db-sync

A component that follows the Cardano chain and stores blocks and transactions in PostgreSQL
Apache License 2.0
289 stars 162 forks source link

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

Open zxpectre opened 2 weeks ago

zxpectre commented 2 weeks ago

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 commented 2 weeks ago

Does your dbsync config contain removal of jsonb as documented?

If so, modify that as per your case

zxpectre commented 2 weeks ago

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