Aws-sdk-js: RDS Data API weird behavior on Statement Timeout

[IhostVlad]

Confirm by changing [ ] to [x] below:

• [x] I've gone through Developer Guide and API reference
• [x] I've checked AWS Forums and StackOverflow for answers

Describe the question
We are using RDS Data API for communication with the Postgres Serverless cluster from AWS Lambdas. Since last time on corresponding post (https://www.reddit.com/r/aws/comments/f6ag4c/rds_data_api_inconsistency_and_weird_behavior/) RDS Data API had begun working well, but some new issues had been discovered.

Suppose long-running SQL query, which can seize about 40 seconds and more. RDS Data API has known limit 45 seconds for SQL query, which is fine, and if statement runs more than about ~40 seconds, query crashes with StatementTimeoutError.

Weird thing is two definitely different StatementTimeoutError-like errors on same case: Request timed out and canceling statement due to user request, but real problem is following.

1) Open transaction via BeginTransaction, save TransactionId identifier - OK
2) Run long SQL query within TransactionId SELECT pg_sleep(60) - it will fail with StatementTimeoutException: ERROR: canceling statement due to user request - OK
3) Run primitive SQL query within TransactionId SELECT 0 - it will fail with BadRequestException: ERROR: current transaction is aborted, commands ignored until end of transaction block - OK
4) Commit current transaction TransactionId with CommitTransaction - it will succeed with { transactionStatus: 'Transaction Committed' } - WTF?

In other words, current transaction had already been failed, and it cannot be successfully committed anyway.

Regular PosgresSQL server version 10.7 has definitely more expected behavior - when trying to commit failed transaction, it explicitly responds with ROLLBACK message, but RDS shows "Transaction Commited", which cannot be true, and therefore is defect.

More discussion at https://www.reddit.com/r/aws/comments/i3gnpz/rds_data_api_weird_behavior_on_statement_timeout/

[ajredniwja]

Hey @IhostVlad, thank-you for reaching out to us and also putting up all the efforts to address this.

I see you have 3 different issues, I can briefly comment on those too however I believe this not related to the JavaScript SDK but are very specific to the RDS DATA API.

I have seen the teams PM from Amazon comment on the issue, I have asked him for updates.

Moreover, I will be taking this up with the RDS DATA team to comment on it.

[IhostVlad]

Hi @ajredniwja! Thanks very much for your response! Unfortunately there is no way to contact RDS data team, thats why these issues had been created. Of course I understand, that there is no direct relation to JS SDK :) Waiting for updates from RDS data api team

[IhostVlad]

Hi @ajredniwja ! Are there some updates on this issue and related ones (https://github.com/aws/aws-sdk-js/issues/3387 and https://github.com/aws/aws-sdk-js/issues/3386) from RDS Data API team? Maybe is there way for contacting RDS Data API team and discussing described issues directly? Thanks in advance!

[ajredniwja]

Thanks for checking @IhostVlad, I haven't heard back from them yet but I can escalate the issue to them.

[IhostVlad]

Thanks @ajredniwja ! It will be great if you can escalate this issue and related ones (https://github.com/aws/aws-sdk-js/issues/3386 and https://github.com/aws/aws-sdk-js/issues/3387) to RDS Data API development team. Now there are tons of workaround tools for correct working with RDS data api, but RDS should work correctly itself :)

[IhostVlad]

CC @nitesmeh