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(dot)klaver(at)aklaver(dot)com