Re: Add 64-bit XIDs into PostgreSQL 15

On Tue, Nov 29, 2022 at 5:57 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Tue, Nov 29, 2022 at 11:41:07AM -0500, Robert Haas wrote:
> > My argument is that removing xidStopLimit is totally fine, because it
> > only serves to stop the database. What to do about xidWarnLimit is a
> > slightly more complex question. Certainly it can't be left untouched,
> > because warning that we're about to shut down the database for lack of
> > allocatable XIDs is not sensible if there is no such lack and we
> > aren't going to shut it down. But I'm also not sure if the model is
> > right. Doing nothing for a long time and then warning in every
> > transaction when some threshold is crossed is an extreme behavior
> > change. Right now that's somewhat justified because we're about to hit
> > a brick wall at full speed, but if we remove the brick wall and
> > replace it with a gentle pelting with rotten eggs, it's unclear that a
> > similarly strenuous reaction is the right model. But that's also not
> > to say that we should do nothing at all.
>
> Yeah, we would probably need to warn on every 1 million transactions or
> something.
>
>
My proposal would be to make the threshold configurable and start warning
on every transaction after that. There are a couple reasons to do that.

The first is that noisy warnings are extremely easy to see. You get them
in cron emails, from psql, in the db logs etc. Having them every million
makes them harder to catch.

The point here is not to ensure there are no problems, but to make sure
that an existing layer in the current swiss cheese model of safety doesn't
go away. Will it stop all problems? No. But the current warning strategy
is effective, given how many times we hear of cases of people having to
take drastic action to avoid impending xid wraparound.

If someone has an insert only database and maye doesn't want to ever
freeze, they can set the threshold to -1 or something. I would suggest
keeping the default as at 2 billion to be in line with existing limitations
and practices. People can then adjust as they see fit.

Warning text might be something like "XID Lag Threshold Exceeded. Is
autovacuum clearing space and keeping up?"

> --
> Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
> EDB https://enterprisedb.com
>
> Embrace your flaws. They make you human, rather than perfect,
> which you will never be.
>