"column att.attgenerated does not exist" in pg_attribute after upgrade to 9.5.0

[3615alexis]

Preflight Checklist

• I have completed all Troubleshooting Steps.
• I'm on the latest version of Directus.
• There's no other issue that already describes my problem.

Describe the Bug

Hello,

After upgrading to 9.5.0, Directus broke, with following error: "column att.attgenerated does not exist" (see details below).

Indeed, this column "attgenerated" from pg_attribute does not exist in PostgreSQL prior to version 12 : https://pgpedia.info/p/pg_attribute.html

In other words, migrating to Directus 9.5.0 means a major/breaking change for a lot of people, versus the documentation still saying 10+ : https://docs.directus.io/getting-started/installation/cli/

Can you fix it to keep Directus compatible with Postgre >= 10 ? Or is the SQL instruction part below absolutely vital ?

Thanks for your help

Alexis

To Reproduce

Use 9.5.0 with postgre priori to version 12

Errors Shown

        SELECT
          att.attname AS name,
          rel.relname AS table,
          rel.relnamespace::regnamespace::text as schema,
          att.atttypid::regtype::text AS data_type,
          NOT att.attnotnull AS is_nullable,
          CASE WHEN att.attgenerated = 's' THEN pg_get_expr(ad.adbin, ad.adrelid) ELSE null END AS generation_expression,
          CASE WHEN att.attgenerated = '' THEN pg_get_expr(ad.adbin, ad.adrelid) ELSE null END AS default_value,
          att.attgenerated = 's' AS is_generated,
          CASE
            WHEN att.atttypid IN (1042, 1043) THEN (att.atttypmod - 4)::int4
            WHEN att.atttypid IN (1560, 1562) THEN (att.atttypmod)::int4
            ELSE NULL
          END AS max_length,
          des.description AS comment,
          CASE att.atttypid
            WHEN 21 THEN 16
            WHEN 23 THEN 32
            WHEN 20 THEN 64
            WHEN 1700 THEN
              CASE WHEN atttypmod = -1 THEN NULL
                ELSE (((atttypmod - 4) >> 16) & 65535)::int4
              END
            WHEN 700 THEN 24
            WHEN 701 THEN 53
            ELSE NULL
          END AS numeric_precision,
          CASE
            WHEN atttypid IN (21, 23, 20) THEN 0
            WHEN atttypid = 1700 THEN
              CASE
                WHEN atttypmod = -1 THEN NULL
                ELSE ((atttypmod - 4) & 65535)::int4
              END
            ELSE null
          END AS numeric_scale
        FROM
          pg_attribute att
          LEFT JOIN pg_class rel ON att.attrelid = rel.oid
          LEFT JOIN pg_attrdef ad ON (att.attrelid, att.attnum) = (ad.adrelid, ad.adnum)
          LEFT JOIN pg_description des ON (att.attrelid, att.attnum) = (des.objoid, des.objsubid)
        WHERE
          rel.relnamespace IN ('public'::regnamespace)


          AND rel.relkind = 'r'
          AND att.attnum > 0
          AND NOT att.attisdropped
        ORDER BY rel.relname, att.attnum;
       - column att.attgenerated does not exist
error:
        SELECT
                att.attname AS name,
          rel.relname AS table,
          rel.relnamespace::regnamespace::text as schema,
          att.atttypid::regtype::text AS data_type,
          NOT att.attnotnull AS is_nullable,
          CASE WHEN att.attgenerated = 's' THEN pg_get_expr(ad.adbin, ad.adrelid) ELSE null END AS generation_expression,
          CASE WHEN att.attgenerated = '' THEN pg_get_expr(ad.adbin, ad.adrelid) ELSE null END AS default_value,
          att.attgenerated = 's' AS is_generated,
          CASE
            WHEN att.atttypid IN (1042, 1043) THEN (att.atttypmod - 4)::int4
            WHEN att.atttypid IN (1560, 1562) THEN (att.atttypmod)::int4
            ELSE NULL
          END AS max_length,
          des.description AS comment,
          CASE att.atttypid
            WHEN 21 THEN 16
            WHEN 23 THEN 32
            WHEN 20 THEN 64
            WHEN 1700 THEN
              CASE WHEN atttypmod = -1 THEN NULL
                ELSE (((atttypmod - 4) >> 16) & 65535)::int4
              END
            WHEN 700 THEN 24
            WHEN 701 THEN 53
            ELSE NULL
          END AS numeric_precision,
          CASE
            WHEN atttypid IN (21, 23, 20) THEN 0
            WHEN atttypid = 1700 THEN
              CASE
                WHEN atttypmod = -1 THEN NULL
                ELSE ((atttypmod - 4) & 65535)::int4
              END
            ELSE null
          END AS numeric_scale
        FROM
          pg_attribute att
          LEFT JOIN pg_class rel ON att.attrelid = rel.oid
          LEFT JOIN pg_attrdef ad ON (att.attrelid, att.attnum) = (ad.adrelid, ad.adnum)
          LEFT JOIN pg_description des ON (att.attrelid, att.attnum) = (des.objoid, des.objsubid)
        WHERE
          rel.relnamespace IN ('public'::regnamespace)


          AND rel.relkind = 'r'
          AND att.attnum > 0
          AND NOT att.attisdropped
        ORDER BY rel.relname, att.attnum;
       - column att.attgenerated does not exist
    at Parser.parseErrorMessage (/home/pi/NodeApps/xdm/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/home/pi/NodeApps/xdm/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/home/pi/NodeApps/xdm/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/home/pi/NodeApps/xdm/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:293:12)
    at readableAddChunk (internal/streams/readable.js:267:9)
    at Socket.Readable.push (internal/streams/readable.js:206:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)

What version of Directus are you using?

9.5.0

What version of Node.js are you using?

14.18.3

What database are you using?

Postgre 11

What browser are you using?

Chrome

What operating system are you using?

Debian 10

How are you deploying Directus?

Raspberry Pi

[azrikahar]

Can confirm this happens when tested on Postgres 10, and it showed up after logging in and greeted with the error message both in the App and terminal.

Seems to be a change over at knex/knex-schema-inspector - https://github.com/knex/knex-schema-inspector/blob/edd5e6cb9bffa5890796c65f3cbd817ed3fb112d/lib/dialects/postgres.ts#L232-L233, particularly added in this PR knex/knex-schema-inspector#93 to resolve the issue #11209.

[rijkvanzanten]

Cc @nickrum

[rijkvanzanten]

Can you fix it to keep Directus compatible with Postgre >= 10 ? Or is the SQL instruction part below absolutely vital ?

Yes to both. We aim to support Postgres back to 10.14 (as that's the current available version for the popular AWS Aurora service). The SQL instruction is important, but there must be a way we can work around that 👍🏻

[3615alexis]

Can you fix it to keep Directus compatible with Postgre >= 10 ? Or is the SQL instruction part below absolutely vital ?

Yes to both. We aim to support Postgres back to 10.14 (as that's the current available version for the popular AWS Aurora service). The SQL instruction is important, but there must be a way we can work around that 👍🏻

Ok noted ! Regarding the "is_generated" info that the query is looking for, it seems to me that the only other place where it exists before pgsql 12 is in information_schema.columns :
https://www.postgresql.org/docs/10/infoschema-columns.html

[rijkvanzanten]

Fixed in #11268

[rijkvanzanten]

@3615alexis Postgres < 12 didn't have support for generated columns, so simply not retrieving it for postgres < 12 is the simplest solution 🙂

[dataexcess]

I assume this fix is not included in @latest version? 9.5.0 ?
How can I fix this bug? should I build directus from source? and from what branch?

Thank you :)

[rijkvanzanten]

@dataexcess This bug was reported when 9.5 was released. It's marked in the v9-next (next release) milestone 👍🏻

How can I fix this bug?

Either stick on the last v9.4 release, or wait for the next scheduled release 👍🏻

[dataexcess]

@dataexcess This bug was reported when 9.5 was released. It's marked in the v9-next (next release) milestone 👍🏻

How can I fix this bug?

Either stick on the last v9.4 release, or wait for the next scheduled release 👍🏻

Ok thank you.
When is the following release scheduled?

[rijkvanzanten]

When is the following release scheduled?

Today! The milestones have deadlines on them 🙂

[dataexcess]

Cool! In that case I will wait it out :)

Thank you!

[dataexcess]

I tested and the issue is fixed!
Thank you 🙏