On Thu, Feb 3, 2022 at 4:50 PM Andres Freund <and...@anarazel.de> wrote: > I wonder if we shouldn't add some exceptions to the xid allocation > prevention. It makes sense that we don't allow random DML. But it's e.g. often > more realistic to drop / truncate a few tables with unimportant content, > rather than spend the time vacuuming those. We could e.g. allow xid > consumption within VACUUM, TRUNCATE, DROP TABLE / INDEX when run at the top > level for longer than we allow it for anything else.
True, although we currently don't start refusing XID allocation altogether until only 1 million remain, IIRC. And that's cutting it really close if we need to start consuming 1 XID per table we need to drop. We might need to push out some of the thresholds a bit. For the most part, I think that there's no reason why autovacuum shouldn't be able to recover from this situation automatically, as long as old replication slots and prepared transactions are cleaned up and any old transactions are killed off. I don't think we're very far from that Just Working, but we are not all there yet either. Manual intervention to drop tables etc. is reasonable to allow a bit more than we do now, but the big problem IMO is that the behavior when we run short of XIDs has had very little testing and bug fixing, so things that don't really need to break just do anyway. > Indeed. Single user is the worst response to this (and just about anything > else, really). Even just getting into the single user mode takes a while > (shutdown checkpoint). The user interface is completely different (and > awful). The buffer cache is completely cold. The system is slower because > there's no wal writer / checkpointer running. Which basically is a list of > things one absolutely do not wants when confronted with a wraparound > situation. +1. -- Robert Haas EDB: http://www.enterprisedb.com