GSA / GSA/data.gov

Close Idle Transaction for Harvest Jobs to prevent 'harvester-db-idling-transactions' alert

Open
#6,287 1 comment 0 reactions 1 assignee Claimed by @marcos-nieto-usds View on GitHub
bug Harvest-DB Harvester O&M
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

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.