Postgresql 9.6 instance starting failing with error

[matiasf9]

Version Information

Server Version: CLI Version (for CLI related issue):

Environment

What is the current behaviour?

When I open the DATA tab in my Hasura Cloud instance I get the following error, and it doesn't let me navigate the tables/schema:

{
    "arguments": [],
    "error": {
        "description": null,
        "exec_status": "FatalError",
        "hint": null,
        "message": "column a.attidentity 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='public'))\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      CASE \n        WHEN a.attidentity = 'd' THEN TRUE\n        WHEN a.attidentity = 'a' THEN TRUE\n        ELSE FALSE\n      END as is_identity\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 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 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        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 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='public'))\n    GROUP BY pgc.oid, pgn.nspname, pgc.relname, table_type, isv.*\n  ) AS info;"
}

What is the expected behaviour?

To not show the error and handle the database as it was done before.

How to reproduce the issue?

1. Create a Hasura cloud instance
2. Connect to a existing DB with Postgresql 9.6

Screenshots or Screencast

Any possible solutions/workarounds you're aware of?

With older versions it was not happening

Keywords

Postgresql 9.6 attidentity

[soupi]

Hi, Hasura GraphQL Engine supports all supported versions of PostgreSQL per the public documentation. At the time of writing, this means versions 12-16. Older versions that are not supported by PostgreSQL are not supported by Hasura and might not work.

Hasura uses the PostgreSQL catalog to fetch information about the database schema, including attributes about columns such as whether they are identity/generated columns or not. In this case, this particular attribute was added in PostgreSQL 10, and we use it to have sophisticated handling of mutations of tables with identity and generated columns.