Message-ID: <fedd43e27a744a8d15dc574d1ba53d4c46b76cf8.camel@cybertec.at>
Subject: Re: txid_current vs xact_commit stats
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: senor <frio_cervesa@hotmail.com>, "pgsql-general@lists.postgresql.org"
	 <pgsql-general@lists.postgresql.org>
Date: Wed, 09 Oct 2024 08:39:35 +0200
In-Reply-To: <SJ0P221MB06894F3D6D38DE5C61E82B30F77F2@SJ0P221MB0689.NAMP221.PROD.OUTLOOK.COM>
References: 
	<SJ0P221MB06894F3D6D38DE5C61E82B30F77F2@SJ0P221MB0689.NAMP221.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/fedd43e27a744a8d15dc574d1ba53d4c46b76cf8.camel%40cybertec.at>
Precedence: bulk

On Wed, 2024-10-09 at 04:22 +0000, senor wrote:
> I was under the impression that all transactions must end with a commit o=
r a
> rollback but watching stats doesn't support this. Web searches tend to re=
turn
> info on what a transaction is or the ratio of commits to rollbacks. I fou=
nd
> nothing contradicting what I think I know.

The rollback can be implicit, for example when you terminate the connection=
 or
crash the server...

Also, PostgreSQL has autocommit, so every data modifying statement that's n=
ot
in an explicit transaction will implicitly commit at the end of the stateme=
nt.

> I've sampled pg_stat_database.xact_commit, pg_stat_database.xact_rollback=
 and
> txid_current() at intervals on a few independent clusters and see that co=
mmits
> increase anywhere from 50% to 300% of the rate of transaction increase. R=
ollback
> remains very near zero for all clusters. Each cluster tends to stay consi=
stently
> within a range (i.e. 120-130% or 50-70%).

Perhaps what I wrote above explains that.

> PG version 11 & 12 on Linux

That's too old.

Yours,
Laurenz Albe