Intermittent introspection error on datasource-sql

Help me!
1

Feature(s) impacted

Introspection on datasource-sql

Observed behavior

Since I upgraded forest admin to latest version, I’m having intermittent issues when introspecting my database.

This happens like one out of 20 restarts (been coding for a few hours with the server restarting when I make changes, it happened twice).

Absolutely no idea of what could be causing that, but I never saw that issue before.

Just posting in case you upgraded that part of the code lately (can’t be sure on my side this is from the upgrade, as the issue is difficult to reproduce)

image
image981×169 36.5 KB

Expected behavior

Not to crash :slight_smile:

Failure Logs

Error in forest agent:

/home/eloims/Projects/freelance/roundtable/node_modules/.pnpm/sequelize@6.35.2_pg@8.8.0/node_modules/sequelize/src/dialects/postgres/query.js:76
    const errForStack = new Error();
                        ^
Error
    at Query.run (/home/eloims/Projects/freelance/roundtable/node_modules/.pnpm/sequelize@6.35.2_pg@8.8.0/node_modules/sequelize/src/dialects/postgres/query.js:76:25)
    at /home/eloims/Projects/freelance/roundtable/node_modules/.pnpm/sequelize@6.35.2_pg@8.8.0/node_modules/sequelize/src/sequelize.js:650:28
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PostgreSQLDialect.listColumns (/home/eloims/Projects/freelance/roundtable/node_modules/.pnpm/@forestadmin+datasource-sql@1.11.2_pg@8.8.0/node_modules/@forestadmin/datasource-sql/src/introspection/dialects/postgresql-dialect/index.ts:114:21)
    at async Function.getTables (/home/eloims/Projects/freelance/roundtable/node_modules/.pnpm/@forestadmin+datasource-sql@1.11.2_pg@8.8.0/node_modules/@forestadmin/datasource-sql/src/introspection/introspector.ts:122:27)
    at async Function.introspect (/home/eloims/Projects/freelance/roundtable/node_modules/.pnpm/@forestadmin+datasource-sql@1.11.2_pg@8.8.0/node_modules/@forestadmin/datasource-sql/src/introspection/introspector.ts:24:20)
    at async introspect (/home/eloims/Projects/freelance/roundtable/node_modules/.pnpm/@forestadmin+datasource-sql@1.11.2_pg@8.8.0/node_modules/@forestadmin/datasource-sql/src/index.ts:25:12)

Original error:

original: error: failed to find plan for subquery ss
      at Parser.parseErrorMessage (/home/eloims/Projects/freelance/roundtable/node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/src/parser.ts:369:69)
      at Parser.handlePacket (/home/eloims/Projects/freelance/roundtable/node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/src/parser.ts:188:21)
      at Parser.parse (/home/eloims/Projects/freelance/roundtable/node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/src/parser.ts:103:30)
      at Socket.<anonymous> (/home/eloims/Projects/freelance/roundtable/node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/src/index.ts:7:48)
      at Socket.emit (node:events:514:28)
      at Socket.emit (node:domain:488:12)
      at addChunk (node:internal/streams/readable:545:12)
      at readableAddChunkPushByteMode (node:internal/streams/readable:495:3)
      at Socket.Readable.push (node:internal/streams/readable:375:5)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 106,
    severity: 'ERROR',
    code: 'XX000',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'ruleutils.c',
    line: '7661',
    routine: 'get_name_for_var_field',
    sql: 'SELECT \n' +
      '      c.table_schema as "Schema",\n' +
      '      c.table_name as "Table",\n' +
      '      c.column_name as "Field", \n' +
      '      pk.constraint_type as "Constraint",\n' +
      '      c.column_default as "Default",\n' +
      '      c.is_nullable as "Null", \n' +
      '      c.identity_generation as "Identity",\n' +
      "      (CASE WHEN c.udt_name = 'hstore' THEN c.udt_name ELSE c.data_type END) \n" +
      '        || (CASE WHEN c.character_maximum_length IS NOT NULL \n' +
      "            THEN '(' || c.character_maximum_length || ')' \n" +
      `            ELSE '' END) as "Type", \n` +
      '      (SELECT array_agg(en.enumlabel) \n' +
      '        FROM pg_catalog.pg_type t JOIN pg_catalog.pg_enum en ON t.oid = en.enumtypid \n' +
      '        WHERE t.typname = c.udt_name OR t.typname = e.udt_name\n' +
      '      ) AS "Special",\n' +
      '      (SELECT pgd.description \n' +
      '        FROM pg_catalog.pg_statio_all_tables AS st\n' +
      '        INNER JOIN pg_catalog.pg_description pgd on (pgd.objoid=st.relid) \n' +
      '        WHERE c.ordinal_position=pgd.objsubid AND c.table_name=st.relname\n' +
      '      ) AS "Comment",\n' +
      '      e.data_type AS "ElementType",\n' +
      '      e.udt_name AS "TechnicalElementType"\n' +
      '      FROM \n' +
      '        information_schema.columns c \n' +
      '      LEFT JOIN (\n' +
      '        SELECT tc.table_schema, tc.table_name, \n' +
      '        cu.column_name, tc.constraint_type \n' +
      '        FROM information_schema.TABLE_CONSTRAINTS tc \n' +
      '        JOIN information_schema.KEY_COLUMN_USAGE  cu \n' +
      '        ON tc.table_schema=cu.table_schema \n' +
      '          AND tc.table_name=cu.table_name \n' +
      '          AND tc.constraint_name=cu.constraint_name \n' +
      "          AND tc.constraint_type='PRIMARY KEY'\n" +
      "          AND tc.constraint_catalog='roundtable'\n" +
      '      ) pk ON pk.table_schema=c.table_schema \n' +
      '        AND pk.table_name=c.table_name \n' +
      '        AND pk.column_name=c.column_name \n' +
      '      LEFT JOIN INFORMATION_SCHEMA.element_types e ON (\n' +
      '        c.table_catalog = e.object_catalog AND\n' +
      '        c.table_schema = e.object_schema AND\n' +
      '        c.table_name = e.object_name AND\n' +
      "        'TABLE' = e.object_type AND\n" +
      '        c.dtd_identifier = e.collection_type_identifier\n' +
      '      )\n' +
      "      WHERE c.table_catalog = 'roundtable' \n" +
      "        AND ((c.table_schema = 'public'\n" +
      "            AND c.table_name = '

... skipping this as it contains all tables in our db ...

      '            ))\n' +
      '      ORDER BY c.table_schema, c.table_name, c.ordinal_position;',

Context

  • Project name: Roundtable
  • Team name: Does not matter
  • Environment name: Dev
  • Agent (forest package) name & version: See screenshot for previous and after versions
  • Database type: postgres
  • Recent changes made on your end if any: upgraded all forest packages
2

I investigated a bit.

Some info that may help:

Good luck with that one!

1 Like
4

Hello @Romain_Gilliotte,

Thanks for the detailed case and pointers to possible solution. I’ll take a look and get back to you.

5

Could you please indicate which version of PG you’re using? It looks a lot like a bug from Postgresql itself. I’m not sure we’ll be able to use the solution described in the github issue, because we need udt_name and not only data_type.

6

Hello again @Romain_Gilliotte,

I created a PR to fix your issue. As I’m not able to reproduce the error, could you please test the 2 queries below to see if you’re able to reproduce with the first query and not with the second?

You’ll need to replace :database, :schemaName0 and :tableName0 by real values from your DB.

Original query with the issue

SELECT 
  c.table_schema as "Schema",
  c.table_name as "Table",
  c.column_name as "Field", 
  pk.constraint_type as "Constraint",
  c.column_default as "Default",
  c.is_nullable as "Null", 
  c.identity_generation as "Identity",
  (CASE WHEN c.udt_name = 'hstore' THEN c.udt_name ELSE c.data_type END) 
    || (CASE WHEN c.character_maximum_length IS NOT NULL 
        THEN '(' || c.character_maximum_length || ')' 
        ELSE '' END) as "Type", 
  (SELECT array_agg(en.enumlabel) 
    FROM pg_catalog.pg_type t JOIN pg_catalog.pg_enum en ON t.oid = en.enumtypid 
    WHERE t.typname = c.udt_name OR t.typname = e.udt_name
  ) AS "Special",
  (SELECT pgd.description 
    FROM pg_catalog.pg_statio_all_tables AS st
    INNER JOIN pg_catalog.pg_description pgd on (pgd.objoid=st.relid) 
    WHERE c.ordinal_position=pgd.objsubid AND c.table_name=st.relname
  ) AS "Comment",
  e.data_type AS "ElementType",
  e.udt_name AS "TechnicalElementType"
  FROM 
    information_schema.columns c 
  LEFT JOIN (
    SELECT tc.table_schema, tc.table_name, 
    cu.column_name, tc.constraint_type 
    FROM information_schema.TABLE_CONSTRAINTS tc 
    JOIN information_schema.KEY_COLUMN_USAGE  cu 
    ON tc.table_schema=cu.table_schema 
      AND tc.table_name=cu.table_name 
      AND tc.constraint_name=cu.constraint_name 
      AND tc.constraint_type='PRIMARY KEY'
      AND tc.constraint_catalog=:database
  ) pk ON pk.table_schema=c.table_schema 
    AND pk.table_name=c.table_name 
    AND pk.column_name=c.column_name 
  LEFT JOIN INFORMATION_SCHEMA.element_types e ON (
    c.table_catalog = e.object_catalog AND
    c.table_schema = e.object_schema AND
    c.table_name = e.object_name AND
    'TABLE' = e.object_type AND
    c.dtd_identifier = e.collection_type_identifier
  )
  WHERE c.table_catalog = :database 
    AND ((c.table_schema = :schemaName0
        AND c.table_name = :tableName0
        ))
  ORDER BY c.table_schema, c.table_name, c.ordinal_position;

Fixed query

SELECT
  "Schema",
  "Table",
  "Field",
  "Constraint",
  "Default",
  "Null",
  "Identity",
  "Type",
  "ElementType",
  "Comment",
  "TechnicalElementType",
  (SELECT array_agg(en.enumlabel) 
    FROM pg_catalog.pg_type t JOIN pg_catalog.pg_enum en ON t.oid = en.enumtypid 
    WHERE t.typname = udt_name OR t.typname = "TechnicalElementType"
  ) AS "Special"
  FROM (
    SELECT       
      *,
      (CASE 
        WHEN "ElementType" LIKE '"%"[]' 
          THEN SUBSTRING("ElementType", 2, LENGTH("ElementType") - 4)
        ELSE NULL 
      END) AS "TechnicalElementType"
    FROM (
      SELECT 
        columns.table_schema as "Schema",
        columns.table_name as "Table",
        columns.column_name as "Field", 
        pk.constraint_type as "Constraint",
        columns.column_default as "Default",
        columns.is_nullable as "Null", 
        columns.identity_generation as "Identity",
        columns.udt_name as "udt_name",
        (CASE 
          WHEN columns.udt_name = 'hstore' 
          THEN columns.udt_name 
          ELSE columns.data_type
        END)
        || 
        (CASE 
          WHEN columns.character_maximum_length IS NOT NULL 
          THEN '(' || columns.character_maximum_length || ')' 
          ELSE '' 
        END) as "Type", 
        pg_catalog.format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS "ElementType",
        (SELECT pgd.description 
          FROM pg_catalog.pg_statio_all_tables AS st
          INNER JOIN pg_catalog.pg_description pgd on (pgd.objoid=st.relid) 
          WHERE columns.ordinal_position=pgd.objsubid AND columns.table_name=st.relname
        ) AS "Comment"
      FROM 
        information_schema.columns 
      LEFT JOIN (
        SELECT tc.table_schema, tc.table_name, 
        cu.column_name, tc.constraint_type 
        FROM information_schema.TABLE_CONSTRAINTS tc 
        JOIN information_schema.KEY_COLUMN_USAGE  cu 
        ON tc.table_schema=cu.table_schema 
          AND tc.table_name=cu.table_name 
          AND tc.constraint_name=cu.constraint_name 
          AND tc.constraint_type='PRIMARY KEY'
          AND tc.constraint_catalog=:database
      ) pk ON pk.table_schema=columns.table_schema 
        AND pk.table_name=columns.table_name 
        AND pk.column_name=columns.column_name 
      INNER JOIN pg_catalog.pg_namespace ON (
        pg_namespace.nspname = columns.table_schema  
      )
      INNER JOIN pg_catalog.pg_class ON (
        pg_class.relname = columns.table_name
        AND pg_namespace.oid = pg_class.relnamespace
      )
      INNER JOIN pg_catalog.pg_attribute ON (
        pg_class.oid = pg_attribute.attrelid
        AND pg_attribute.attname = columns.column_name
      )
      WHERE columns.table_catalog = :database 
        AND ((columns.table_schema = :schemaName0
        AND columns.table_name = :tableName0
        ))
      ORDER BY columns.table_schema, columns.table_name, columns.ordinal_position
    ) as raw_info
  ) as with_technical_element_type;
7

Hi

Thanks!

I’ll try that tomorrow and keep you updated (and will tell you the exact versions i’m using to help you reproduce)

Romain

8

Hi Guillaume

Here is my postgres version

PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit

(will try tomorrow to reproduce on my end)

9

The first query did crash.
It’s quite strange, I run it a few times on DBeaver, and it crashed every time, then restarted DBeaver and tried again and it worked every time.

Then I changed it a bit (added a dummy where condition), then it crashed again.

image
image724×744 96.7 KB

The second one did not crash

10

We released a new version of @forestadmin/datasource-sql that should fix the issue with this particular query.

Could you please upgrade and check that it fixes the error?

Thanks

11

Hi Guillaume

I’ve been working on this project today and upgraded to the latest forestadmin version.
The issue seems to have gone away :slight_smile:

Thanks a lot!

2 Likes