Close Idle Transaction for Harvest Jobs to prevent 'harvester-db-idling-transactions' alert
- Dominant language
- Python
- Stars
- 1.1k
- Forks
- 200
- Avg merge
- 4h 56m
- Merged PRs (30d)
- 5
Description
There was an alert open https://one.newrelic.com/alerts/issue?account=1601367&duration=259200000&state=5cb0a915-ad03-7bd0-7472-b371227ee7a7 which states datagov-harvest-prod query result is >= 1.0 on 'harvester-db-idling-transactions'.
I found that this was fixed here
- https://github.com/GSA/datagov-harvester/pull/847
but mainly for start_new_jobs. The alert itself shows that this issue resolved itself, but the query displayed only goes up to September 2nd, when the alert was resolved. Therefore, the query below confirms that there are currently no idle transactions as of the writing of this ticket, (September 4.) This fix is just to prevent future alerts of this type from occurring.
The query:
```shell
SELECT
count(*)
FROM
PostgresActivitySample
WHERE
appName = 'datagov-harvest-prod'
AND over60seconds > 0 SINCE 1 hour ago TIMESERIES 1 minute
```
```shell
"eventTypes": [
"PostgresActivitySample"
],
"eventType": "PostgresActivitySample",
"openEnded": true,
"messages": [],
"beginTimeMillis": 1788528401493,
"endTimeMillis": 1788532001493,
"beginTime": "2026-09-04T13:26:41Z",
"endTime": "2026-09-04T14:26:41Z",
"guid": "c2d7301e-712a-981d-3532-9a1a926c8b79",
"routerGuid": "c2d7301e-712a-981d-3532-9a1a926c8b79",
"rawSince": "1 HOURS AGO",
"rawUntil": "NOW",
"rawCompareWith": "",
"bucketSizeMillis": 60000,
"accounts": [
1601367
]
},
"total": {
"results": [
{
"count": 0
}
],
"beginTimeSeconds": 1788528401,
"endTimeSeconds": 1788532001,
"inspectedCount": 0
},
"performanceStats": {
"inspectedCount": 263,
"exceedsRetentionWindow": false,
"responseTime": 98
},
"queryUsageRaw": {
"scannedEvents": 263,
"ccu": 2.17238e-7,
"billableMetric": "ccu"
}
```
## How to reproduce
1. The simplest way to reproduce is to spin up a local DB instance where you simulate a hanging start_task.
## Expected behavior
The query provided here can be used to validate the fix:
- https://github.com/GSA/data.gov/issues/6223#issuecomment-5329486299
## Actual behavior
Confirm that start_task such as in a manual harvest job, can leave a state = idle in transaction, prior to the fix being implemented here.
## Sketch
I see that start_job is also called in trigger_manual_job. We can add a similar try/finally block where we close the session with an interface.close() command so that hanging transactions are also not caused by manual jobs.
Contributor guide
Assessment
This issue has not been assessed yet.