Surprising infinite retrying of SQL statement on a replica causes replication lag

[steve-chavez]

Problem

On a read replica, this happens:

GET /rpc/xx..

Jul 22 23:21:04 [redacted] postgrest[115960]: 22/Jul/2024:23:21:04 +0000: 
{"code":"40001",
"details":"User query might have needed to see row versions that must be removed.",
"hint":null,
"message":"canceling statement due to conflict with recovery"}

On the postgres logs, this error is repeated infinitely:

..,ERROR,40001,"canceling statement due to conflict with recovery","User query might have needed to see row versions that must be removed.",,
PL/pgSQL function xx() line 3 at RETURN QUERY","WITH pgrst_source AS (SELECT ""pgrst_call"".
...

For some reason this causes noticeable replication lag, I'm assuming this error somehow affects the replication process. It can only be solved if postgrest is stopped.

The problem root cause can be found on hasql-transaction:

• https://github.com/nikita-volkov/hasql-transaction/blob/master/CHANGELOG.md
• https://github.com/nikita-volkov/hasql-transaction/blob/2a9a710bd6230cb0abae84f14f852ab14fc3fa85/library/Hasql/Transaction/Private/Sessions.hs#L48

Solution

Don't retry on 40001. We would need to patch hasql-transaction to make the retrying behavior configurable and then disable it.

Notes

This is hard to reproduce. But I think we shouldn't do any retrying by default.

[wolfgangwalther]

The problem root cause can be found on hasql-transaction:

Doesn't seem like a PostgREST bug, but a hasql bug, then?