Re: Monitoring for long running transactions

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,
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com