Re: Monitoring for long running transactions

On 6/4/20 2:29 PM, Adrian Klaver wrote:
> On 6/4/20 10:00 AM, Samuel Smith wrote:
>> We had a customer complaining of random data loss for the last 6 
>> months or so. We eventually tracked it down to a combination of bad 
>> coding and a couple of bugs with the ORM. Basically, 'BEGIN' was being 
>> emitted by the web app when viewing a certain page and 'COMMIT' was 
>> never emitted after that. So once the app would get restarted, all 
>> data changes would be lost. Definitely worst case scenario.
>>
>> So the question is, what is the best way to monitor for this scenario 
>> going forward? Are there any plugins or community recommended scripts 
>> already made?
> 
> https://www.postgresql.org/docs/12/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW 
> 
> 
> "
> state     text     Current overall state of this backend. Possible 
> values are:
> 
>      active: The backend is executing a query.
> 
>      idle: The backend is waiting for a new client command.
> 
>      idle in transaction: The backend is in a transaction, but is not 
> currently executing a query.
> 
>      idle in transaction (aborted): This state is similar to idle in 
> transaction, except one of the statements in the transaction caused an 
> error.
> 
>      fastpath function call: The backend is executing a fast-path function.
> 
>      disabled: This state is reported if track_activities is disabled in 
> this backend.
> "
> 
>>
>> Regards,
>>
>>
>>
>>
> 
> 


Sorry, I should have clarified that I was aware of the pg_stat_activity 
table. That is how we found the problem in the first place. And yes I 
could just write a bash script and run it in cron. I just didn't know if 
there was a more "official" way to go about this since it is probably a 
common monitoring point and/or if something like this was already made.

Regards,