UnsupportedResultException with Aurora Serverless V2

[ryanmargono]

I'm getting UnsupportedResultException: The result contains the unsupported data type regtype about every other request. I'm using the same postgres engine, version, and models as my Aurora Serverless V1.

Also seeing that migrations are not running with sync enabled.

Any suggestions?

[ArsenyYankovsky]

Please read the list of supported column types in the official document here. The UnsupportedResultException is thrown from the AWS SDK itself.

[ryanmargono]

Hey @ArsenyYankovsky, I read through that prior to migration and I confirmed through the RDS query editor that there are no unsupported types in my schema.

Furthermore, RDS query editor (which uses Data API under the hood) works flawlessly. This leads me to believe there could be something wrong with the lib.

[vin1627]

the same issue that I've encountered. Im using typeorm then connect to Data API. at first run of the database it was successfully update/ migrate my schema after that it will show the error on 2nd run and so on

[ficzusistvan]

Hi! I'm experiencing the same issue. I tried registering a type parser: const registerCustomTypeParsers = () => { types.setTypeParser(2206, (val) => { console.log({ val }) return val }) // 2206 is the OID for regtype } , but with no luck. Any suggestions?

[vin1627]

I've only encountered this when I used dataAPI. I tried to use the host from RDS then I was able to avoid this error

[ficzusistvan]

Thanks @vin1627 ! Directly accessing the underlying postgres db solved the issue. I was trying to generate/run migrations when I encountered this error. The ugly solution for me is to use a DataSource without Data API for migrations, the problem in this case is that the db must be publicly accessible..

[trendafil-gechev]

Unfortunately, this issue is still present and reproducible directly through SDKs and AWS CLI. The SQL statement in question is shown below. I am reproducing it by simply running npx ts-node ./node_modules/typeorm/cli schema:sync -d ./typeorm-config.js, stack trace looks like this:

Error during schema synchronization:
UnsupportedResultException: The result contains the unsupported data type regtype.
    at de_UnsupportedResultExceptionRes (/home/tgechev/tg/packages-layer/nodejs/node18/node_modules/typeorm-aurora-data-api-driver/node_modules/@aws-sdk/client-rds-data/dist-es/protocols/Aws_restJson1.js:699:23)
    at de_ExecuteStatementCommandError (/home/tgechev/tg/packages-layer/nodejs/node18/node_modules/typeorm-aurora-data-api-driver/node_modules/@aws-sdk/client-rds-data/dist-es/protocols/Aws_restJson1.js:432:25)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async /home/tgechev/tg/packages-layer/nodejs/node18/node_modules/typeorm-aurora-data-api-driver/node_modules/@smithy/middleware-serde/dist-es/deserializerMiddleware.js:4:24
    at async /home/tgechev/tg/packages-layer/nodejs/node18/node_modules/typeorm-aurora-data-api-driver/node_modules/@aws-sdk/middleware-signing/dist-es/awsAuthMiddleware.js:26:20
    at async /home/tgechev/tg/packages-layer/nodejs/node18/node_modules/typeorm-aurora-data-api-driver/node_modules/@smithy/middleware-retry/dist-es/retryMiddleware.js:27:46
    at async /home/tgechev/tg/packages-layer/nodejs/node18/node_modules/typeorm-aurora-data-api-driver/node_modules/@aws-sdk/middleware-logger/dist-es/loggerMiddleware.js:3:26

SQL statement in question looks like this:

SELECT columns.*,
       pg_catalog.col_description(('"' || table_catalog || '"."' || table_schema || '"."' || table_name || '"')::regclass::oid, ordinal_position) AS description,
       ('"' || "udt_schema" || '"."' || "udt_name" || '"')::"regtype" AS "regtype",
       pg_catalog.format_type("col_attr"."atttypid", "col_attr"."atttypmod") AS "format_type"
FROM "information_schema"."columns"
LEFT JOIN "pg_catalog"."pg_attribute" AS "col_attr" ON "col_attr"."attname" = "columns"."column_name" AND 
                                                       "col_attr"."attrelid" = ( SELECT "cls"."oid"
									         FROM "pg_catalog"."pg_class" AS "cls"
										 LEFT JOIN "pg_catalog"."pg_namespace" AS "ns" ON "ns"."oid" = "cls"."relnamespace"
										 WHERE "cls"."relname" = "columns"."table_name" AND "ns"."nspname" = "columns"."table_schema"
                                                                               )
WHERE ("table_schema" = 'public' AND "table_name" = '<table-name-1>') OR 
      ("table_schema" = 'public' AND "table_name" = '<table-name-2>') OR
      ("table_schema" = 'public' AND "table_name" = '<table-name-...>') OR
      ("table_schema" = 'public' AND "table_name" = '<table-name-N>')

Tested on Aurora Serverless V2 with PostgreSQL and the following package versions:

• @aws-sdk/client-rds-data: 3.614.0
• typeorm: 0.3.20
• typeorm-aurora-data-api-driver: 3.0.1

Having no migrations like @ficzusistvan has pointed out and not being able to synchronize the database schema renders this driver unusable. This issue deserves more attention, @ArsenyYankovsky I am willing to help to further diagnose it if you need more input.

EDIT: After more debugging it became clear that we were looking at the wrong SQL statement. The correct (faulty) statement, generated by the ORM is provided now. Where the regtype is clearly seen: ('"' || "udt_schema" || '"."' || "udt_name" || '"')::"regtype" AS "regtype"

[marcofs]

I solved it by patching typeorm.

If you really need this to work, here's how you do it:

1. Clone typeorm
   • cd your-project
   • git clone https://github.com/typeorm/typeorm.git
2. Patch typeorm by changing "regtype" to text and package it
   • Find your-project/typeorm/src/driver/postgres/PostgresQueryRunner.ts
   • Change ('"' || "udt_schema" || '"."' || "udt_name" || '"')::"regtype" AS "regtype" to ('"' || "udt_schema" || '"."' || "udt_name" || '"')::text AS "regtype"
   • npm install
   • npm run package
3. Now your project will need to use this patched typeorm
   • Go to your-project/package.json
   • Set your typeorm dependency to read from the patched version: "typeorm": "file:./typeorm/build/package
   • npm install