DATA api issue with appsync after upgrade to serverless v2

Question

We have upgraded our Postgresql DB cluster from serverless v1 to serverless v2.
We use the DB as a Data Source for our graphQl api in appSync, and after the upgrade we are now receiving lots of errors like:

`RDSHttp: The result contains the unsupported data type tsvector.`

that we never saw when using serverless v1.

We do have some tsvector columns, and for the API we use most of the times `SELECT *`

do we really have to refactor the api to adjust all the SELECT, by specifying the list of columns, except the tsvector one?

Another option could be to use a lambda as a datasource, that would execute the query for us, (without the DATA api), but that's also quite a refactoring.

Any other viable solution?

re:Post Agent · Answer

This issue is occurring because Aurora Serverless v2 has different Data API behavior compared to v1, specifically regarding supported data types. Unlike Aurora Serverless v1, the v2 Data API doesn't support certain PostgreSQL data types, including geometric and monetary types, as well as tsvector in your case.

You have several options to address this:

1. Cast the unsupported data types to TEXT in your queries. Instead of using `SELECT *`, you could modify your queries to cast the tsvector columns:
```
SELECT column1, column2, tsvector_column::TEXT FROM my_table;
```
or
```
SELECT CAST(tsvector_column AS TEXT) FROM my_table;
```

2. Create views that handle the casting for you, then query from those views instead of directly from the tables.

3. Modify your queries to explicitly select only the columns you need, excluding the tsvector columns.

4. As you mentioned, use a Lambda function as a data source instead of the Data API. This would allow you to connect directly to the database without the Data API limitations.

5. If you're experiencing persistent issues, try disabling and then re-enabling the RDS Data API, as this has resolved similar issues for some users.

The most straightforward approach with minimal refactoring would be to cast the tsvector columns to TEXT in your queries or create views that handle this casting for you.  
**Sources**  
[Comparing Amazon RDS Data API behaviors for Aurora Serverless v2 and provisioned clusters with Aurora Serverless v1 clusters - Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.differences.html)  
[Limitations for the Amazon RDS Data API - Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.limitations.html)  
[Aurora Serverless v2 - The request was throttled because of insufficient resources on the database | AWS re:Post](https://repost.aws/questions/QUudolCeyiSDq_ZLmARgkIug/aurora-serverless-v2-the-request-was-throttled-because-of-insufficient-resources-on-the-database)

DATA api issue with appsync after upgrade to serverless v2

Add your answer

Relevant content