Re: Add 64-bit XIDs into PostgreSQL 15

On Tue, Nov 29, 2022 at 9:35 PM Chris Travers <chris@orioledata.com> wrote:
> My proposal would be to make the threshold configurable and start warning on every transaction after that.  There are
acouple 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
cheesemodel of safety doesn't go away.  Will it stop all problems?  No.  But the current warning strategy is effective,
givenhow 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.
Peoplecan then adjust as they see fit. 
>
> Warning text might be something like "XID Lag Threshold Exceeded.  Is autovacuum clearing space and keeping up?"

None of this seems unreasonable to me. If we want to allow more
configurability, we could also let you choose the threshold and the
frequency of warnings (every N transactions).

But, I think we might be getting down a little bit in the weeds. It's
not clear that everybody's on board with the proposed page format
changes. I'm not completely opposed, but I'm also not wild about the
approach. It's probably not a good idea to spend all of our energy
debating the details of how to reform xidWrapLimit without having some
consensus on those points. It is, in a word, bikeshedding: on-disk
page format changes are hard, but everyone understands warning
messages.

Lest we miss the forest for the trees, there is an aspect of this
patch that I find to be an extremely good idea and think we should try
to get committed even if the rest of the patch set ends up in the
rubbish bin. Specifically, there are a couple of patches in here that
have to do with making SLRUs indexed by 64-bit integers rather than by
32-bit integers. We've had repeated bugs in the area of handling SLRU
wraparound in the past, some of which have caused data loss. Just by
chance, I ran across a situation just yesterday where an SLRU wrapped
around on disk for reasons that I don't really understand yet and
chaos ensued. Switching to an indexing system for SLRUs that does not
ever wrap around would probably enable us to get rid of a whole bunch
of crufty code, and would also likely improve the general reliability
of the system in situations where wraparound is threatened. It seems
like a really, really good idea.

I haven't checked the patches to see whether they look correct, and
I'm concerned in particular about upgrade scenarios. But if there's a
way we can get that part committed, I think it would be a clear win.

--
Robert Haas
EDB: http://www.enterprisedb.com