Hi, On 2019-05-03 16:26:46 -0400, Robert Haas wrote: > 2. Once you get to the point where you start to emit errors when > attempting to assign an XID, you can still run plain old VACUUM > because it doesn't consume an XID ... except that if it tries to > truncate the relation, then it will take AccessExclusiveLock, which > has to be logged, which forces an XID assignment, which makes VACUUM > fail. So if you burn through XIDs until the system gets to this > point, and then you roll back the prepared transaction that caused the > problem in the first place, autovacuum sits there trying to vacuum > tables in a tight loop and fails over and over again as soon as hits a > table that it thinks needs to be truncated. This seems really lame, > and also easily fixed. > > Attached is a patch that disables vacuum truncation if xidWarnLimit > has been reached. With this patch, in my testing, autovacuum is able > to recover the system once the prepared transaction has been rolled > back. Without this patch, not only does that not happen, but if you > had a database with enough relations that need truncation, you could > conceivably cause XID wraparound just from running a database-wide > VACUUM, the one tool you have available to avoid XID wraparound. I > think that this amounts to a back-patchable bug fix. > > (One could argue that truncation should be disabled sooner than this, > like when we've exceed autovacuum_freeze_max_age, or later than this, > like when we hit xidStopLimit, but I think xidWarnLimit is probably > the best compromise.)
I'd actually say the proper fix would be to instead move the truncation to *after* finishing updating relfrozenxid etc. If we truncate, the additional cost of another in-place pg_class update, to update relpages, is basically insignificant. And the risk of errors, or being cancelled, during truncation is much higher than before (due to the AEL). > Also, I think that old prepared transactions and stale replication > slots should be emphasized more prominently. Maybe something like: > > HINT: Commit or roll back old prepared transactions, drop stale > replication slots, or kill long-running sessions. > Ensure that autovacuum is progressing, or run a manual database-wide VACUUM. I think it'd be good to instead compute what the actual problem is. It'd not be particularly hard to show some these in the errdetail: 1) the xid horizon (xid + age) of the problematic database; potentially, if connected to that database, additionally compute what the oldest xid is (although that's computationally potentially too expensive) 2) the xid horizon (xid + age) due to prepared transactions, and the oldest transaction's name 3) the xid horizon (xid + age) due to replication slot, and the "oldest" slot's name 4) the xid horizon (xid + age) and pid for the connection with the oldest snapshot. I think that'd allow users much much easier to pinpoint what's going on. In fact, I think we probably should additionally add a function that can display the above. That'd make it much easier to write monitoring queries. IMO we also ought to compute the *actual* relfrozenxid/relminmxid for a table. I.e. the oldest xid actually present. It's pretty common for most tables to have effective horizons that are much newer than what GetOldestXmin()/vacuum_set_xid_limits() can return. Obviously we can do so only when scanning all non-frozen pages. But being able to record "more aggressive" horizons would often save unnecessary work. And it ought to not be hard. I think especially for regular non-freeze, non-wraparound vacuums that'll often result in a much newer relfrozenxid (as we'll otherwise just have GetOldestXmin() - vacuum_freeze_min_age). Greetings, Andres Freund
