40001 is not a query error

The PostgreSQL error code 40001 is often misinterpreted as a query error. However, it actually indicates that the entire transaction has been aborted. A common approach to handle this error is using a retry mechanism, which wraps the query and attempts to re-execute it a certain number of times. This can be implemented using a loop that keeps trying until the error is resolved or the maximum number of attempts is reached.

However, this approach has several drawbacks. First, it turns a clear error into an ambiguous one, making it difficult to understand what went wrong. Second, it adds unnecessary delay to the process, as the retry mechanism introduces a waiting period before each attempt.

Moreover, the error can occur at any point in the transaction, not just at the statement level. This means that even if the initial query succeeds, the error might still be raised later during the transaction. Therefore, retrying the entire transaction is the only reliable solution.

TypeORM, a popular ORM for Node.js, does not provide built-in support for retrying transactions. This leaves developers to implement their own retry logic, which can be error-prone and counterintuitive.

The author of the article built a custom solution to address this issue, but concluded that it is not feasible to implement whole-transaction retry correctly. The reason is that the failure point of the transaction is unpredictable and depends on various factors such as the version of PostgreSQL, the query plan, and the interleaving of statements. Therefore, it is not possible to reliably predict when the error will occur and where to retry the query.

The author emphasizes the importance of running assertions about database behavior against the versions of PostgreSQL that the application supports. This helps to identify any discrepancies between the mental model and the actual behavior of the system.

In conclusion, the PostgreSQL error code 40001 is not a simple query error that can be easily retried. It signals a failure at the transaction level, and the only viable solution is to rollback the entire transaction and start fresh. Developers should avoid implementing custom retry mechanisms and instead rely on the transaction boundary to handle such errors.