what causes xact_rollback for a database in pg_stat_database to increment?

Hi Hu

Log_statement = all can miss some statements sent to Postgresql from the
manual https://www.postgresql.org/docs/current/runtime-config-logging.html
*Note*

*Statements that contain simple syntax errors are not logged even by the
log_statement = all setting, because the log message is emitted only after
basic parsing has been done to determine the statement type. In the case of
extended query protocol, this setting likewise does not log statements that
fail before the Execute phase (i.e., during parse analysis or planning).
Set log_min_error_statement to ERROR (or lower) to log such statements.*

Trying cranking up Log_min_error_Statement

Also you can monitor the number of transactions being created from PG10+
there is

txid_status()

https://www.2ndquadrant.com/en/blog/postgresql-10-transaction-traceability/

On Wed, Dec 4, 2019 at 3:49 PM Hu, Patricia <Patricia.Hu@finra.org> wrote:

"Hu, Patricia" <Patricia.Hu@finra.org> writes:

We’ve been seeing over 200k rollbacks being recorded for a database
at certain hour overnight but can’t see the corresponding ROLLBACK
statements being recorded in postgresql db logs, even though our
log_statement is set to ALL.

I did some tests and confirmed that xact_rollback count increments in
scenarios below and am looking further at those.

1. a ROLLBACK is issued explicitly or implicitly(if a
transaction or pl/pgsql block/function raises an exception or was
aborted)

2. it doesn’t reflect the number of records being rolled back

3. even if ROLLBACK statement results in WARNING like “there is
no transaction in progress”, it increments

Does anyone know on top of his/her head any (other) explanation for
such unaccounted for rollbacks?

Dangling session...

begin;
select this;
select that...
<drop connection>

HTH

Thanks,

Patricia

Confidentiality Notice:: This email, including attachments, may
include non-public, proprietary, confidential or legally privileged
information. If you are not an intended recipient or an authorized
agent of an intended recipient, you are hereby notified that any
dissemination, distribution or copying of the information contained
in or transmitted with this e-mail is unauthorized and strictly
prohibited. If you have received this email in error, please notify
the sender by replying to this message and permanently delete this
e-mail, its attachments, and any copies of it immediately. You should
not retain, copy or use this e-mail or any attachment for any
purpose, nor disclose all or any part of the contents to any other
person. Thank you.

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net