Cockroach DB deleted columns

Version Information

Server Version: Docker Container - (hasura/graphql-engine:latest) [ v2.39.2 ] Docker Container - (cockroachdb/cockroach:latest) [ v23.2.5 ]

Environment

OSS

What is the current behaviour?

Tables with deleted columns trigger error on the dashboard when trying to view the table or create a relationship with the table.

What is the expected behaviour?

Not to show error and allow me to access the table when viewing it or creating foreign keys on another table

How to reproduce the issue?

Create a cockroach db docker container for testing.
Create 2 tables that are related by a foreign key.
Delete a column from one of the tables.

Screenshots or Screencast

Please provide any traces or logs that could help here.

{
    "arguments": [],
    "error": {
        "description": null,
        "exec_status": "FatalError",
        "hint": null,
        "message": "has_column_privilege(): column 10 of relation store_integration does not exist",
        "status_code": "42703"
    },
    "prepared": false,
    "statement": "SELECT\n    COALESCE(Json_agg(Row_to_json(info)), '[]' :: json) AS tables\n  FROM (\n    WITH partitions AS (\n      SELECT array(\n        WITH partitioned_tables AS (SELECT array(SELECT oid FROM pg_class WHERE relkind = 'p') AS parent_tables)\n        SELECT\n        child.relname       AS partition\n    FROM partitioned_tables, pg_inherits\n        JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid\n        JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace\n    where ((nmsp_child.nspname='private'))\n    AND pg_inherits.inhparent = ANY (partitioned_tables.parent_tables)\n      ) AS names\n    )\n    SELECT\n      pgn.nspname AS table_schema,\n      pgc.relname AS table_name,\n      CASE\n        WHEN pgc.relkind = 'r' THEN 'TABLE'\n        WHEN pgc.relkind = 'f' THEN 'FOREIGN TABLE'\n        WHEN pgc.relkind = 'v' THEN 'VIEW'\n        WHEN pgc.relkind = 'm' THEN 'MATERIALIZED VIEW'\n        WHEN pgc.relkind = 'p' THEN 'PARTITIONED TABLE'\n      END AS table_type,\n      obj_description(pgc.oid) AS comment,\n      COALESCE(json_agg(DISTINCT row_to_json(isc) :: jsonb || jsonb_build_object('comment', col_description(pga.attrelid, pga.attnum))) filter (WHERE isc.column_name IS NOT NULL), '[]' :: json) AS columns,\n      COALESCE(json_agg(DISTINCT row_to_json(ist) :: jsonb || jsonb_build_object('comment', obj_description(pgt.oid))) filter (WHERE ist.trigger_name IS NOT NULL), '[]' :: json) AS triggers,\n      row_to_json(isv) AS view_info\n      FROM partitions, pg_class as pgc\n      INNER JOIN pg_namespace as pgn\n        ON pgc.relnamespace = pgn.oid\n    /* columns */\n    /* This is a simplified version of how information_schema.columns was\n    ** implemented in postgres 9.5, but modified to support materialized\n    ** views.\n    */\n    LEFT OUTER JOIN pg_attribute AS pga\n      ON pga.attrelid = pgc.oid\n    LEFT OUTER JOIN (\n      SELECT\n        nc.nspname         AS table_schema,\n        c.relname          AS table_name,\n        a.attname          AS column_name,\n        a.attnum           AS ordinal_position,\n        pg_get_expr(ad.adbin, ad.adrelid) AS column_default,\n        CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END AS is_nullable,\n        CASE WHEN t.typtype = 'd' THEN\n          CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'\n               WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)\n               ELSE 'USER-DEFINED' END\n        ELSE\n          CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'\n               WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)\n               ELSE 'USER-DEFINED' END\n        END AS data_type,\n        coalesce(bt.typname, t.typname) AS data_type_name\n      FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)\n        JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid\n        JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid\n        LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))\n          ON (t.typtype = 'd' AND t.typbasetype = bt.oid)\n        LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))\n          ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')\n      WHERE -- (NOT pg_is_other_temp_schema(nc.oid))\n        -- AND\n        a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'm', 'f', 'p')\n        AND (-- pg_has_role(c.relowner, 'USAGE')\n              -- OR\n              has_column_privilege(c.oid, a.attnum,\n                                     'SELECT, INSERT, UPDATE, REFERENCES'))\n    ) AS isc\n      ON  isc.table_schema = pgn.nspname\n      AND isc.table_name   = pgc.relname\n      AND isc.column_name  = pga.attname\n    /* triggers */\n    LEFT OUTER JOIN pg_trigger AS pgt\n      ON pgt.tgrelid = pgc.oid\n    LEFT OUTER JOIN information_schema.triggers AS ist\n      ON  ist.event_object_schema = pgn.nspname\n      AND ist.event_object_table  = pgc.relname\n      AND ist.trigger_name        = pgt.tgname\n    /* This is a simplified version of how information_schema.views was\n    ** implemented in postgres 9.5, but modified to support materialized\n    ** views.\n    */\n    LEFT OUTER JOIN (\n      SELECT\n        nc.nspname         AS table_schema,\n        c.relname          AS table_name,\n        pg_get_viewdef(c.oid),\n        -- CASE WHEN pg_has_role(c.relowner, 'USAGE') THEN pg_get_viewdef(c.oid) ELSE null END AS view_definition,\n        CASE WHEN pg_relation_is_updatable(c.oid, false) & 20 = 20 THEN 'YES' ELSE 'NO' END AS is_updatable,\n        CASE WHEN pg_relation_is_updatable(c.oid, false) &  8 =  8 THEN 'YES' ELSE 'NO' END AS is_insertable_into,\n        CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81) THEN 'YES' ELSE 'NO' END AS is_trigger_updatable,\n        CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73) THEN 'YES' ELSE 'NO' END AS is_trigger_deletable,\n        CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69) THEN 'YES' ELSE 'NO' END AS is_trigger_insertable_into\n      FROM pg_namespace nc, pg_class c\n      WHERE c.relnamespace = nc.oid\n        AND c.relkind in ('v', 'm')\n        -- AND (NOT pg_is_other_temp_schema(nc.oid))\n        AND (-- pg_has_role(c.relowner, 'USAGE')\n             -- OR\n             has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')\n             OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'))\n    ) AS isv\n      ON  isv.table_schema = pgn.nspname\n      AND isv.table_name   = pgc.relname\n    WHERE\n      pgc.relkind IN ('r', 'v', 'f', 'm', 'p')\n      and ((pgn.nspname='private'))\n    GROUP BY pgc.oid, pgn.nspname, pgc.relname, table_type, isv.*\n  ) AS info;"
}

Any possible solutions/workarounds you're aware of?

The only workaround I have been managing is to re-create the table, but that is not a good workaround.

Keywords

has_column_privilege of relation does not exists

hasura / graphql-engine