cube-js / cube-js/cube

CubeJS Server crashes with "terminating connection due to conflict with recovery" (PG read replica)

Open
#3,904 5 comments 2 reactions 0 assignees View on GitHub
help wanted
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Describe the bug**
When using a PostgreSQL read replica as datasource, the Cube Server crashes when a running query is interrupted because there are pending WAL entries that conflict with the query for more than `max_standby_archive_delay` (or `max_standby_streaming_delay`) (both are 30s by default).

As per the documentation: "Note that `max_standby_archive_delay` is not the same as the maximum length of time a query can run before cancellation (...) if one query has resulted in significant delay, subsequent conflicting queries will have much less grace time until the standby server has caught up again."

**To Reproduce**
1. (optional) To make reproduction easier reduce `max_standby_archive_delay` and `max_standby_streaming_delay` to a few milliseconds instead of the default value of 30s.
2. Trigger a long running query against a PostgreSQL read replica.
3. CubeJS server crashes.

```
error: terminating connection due to conflict with recovery
at Parser.parseErrorMessage (/app/index.js:402934:98)
at Parser.handlePacket (/app/index.js:402773:29)
at Parser.parse (/app/index.js:402686:38)
(...)
```

**Expected behavior**
When a query is aborted by a PostgreSQL read replica due "to conflict with recovery", the query should be retried once or twice with backoff.

Our current CubeJS server configuration (below) already includes a query execution timeout smaller than the allowed replication delay, however if one or more previous queries already took a significant portion of the time, a relatively fast query can still get aborted.

```
orchestratorOptions: {
queryCacheOptions: {
(...)
queueOptions: {
executionTimeout: 25,
orphanedTimeout: 20,
heartBeatInterval: 5,
},
},
```

**Version:**
@cubejs-backend/server-core: "0.29.17"
@cubejs-backend/postgres-driver: "0.29.17"

**Additional context**
Also tried to increase the `max_standby_archive_delay` and `max_standby_streaming_delay` to 60s (double) but the problem still occurs frequently (twice a day or more).

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.