electric-sql / electric-sql/electric
Feature request: if Electric can't connect to Postgres due to long-running transactions, query and log what the transactions are
- Dominant language
- TypeScript
- Stars
- 10.4k
- Forks
- 375
- Avg merge
- 3d 1h
- Merged PRs (30d)
- 18
Description
**Is your feature request related to a problem? Please describe.**
An Electric Cloud customer ran into an issue where Electric couldn't create a publication slot due to a long-running transaction in PG. They (and we) didn't know what the problem was for a while so they had downtime while diagnosing the problem.
**Describe the solution you'd like**
In addition to Electric logging it can't connect, it should query for what the long-running txs are to help the user resolve things e.g. put a shorter limit on `idle_in_transaction_session_timeout`.
**Additional context**
Some possible queries:
Shows active transactions sorted by tx length:
```sql
SELECT
pid,
usename,
application_name,
state,
age(now(), xact_start) AS xact_age,
query
FROM
pg_stat_activity
WHERE
xact_start IS NOT NULL
ORDER BY
xact_age DESC;
```
Shows idle transactions:
```sql
SELECT
pid,
usename,
application_name,
client_addr,
state,
query_start,
xact_start,
age(now(), xact_start) AS xact_age,
query
FROM
pg_stat_activity
WHERE
state = 'idle in transaction'
ORDER BY
xact_age DESC;
```
Contributor guide
Assessment
This issue has not been assessed yet.