This patch actually does not discard any prepared transactions and only throws a warning for each orphaned one. So, there is no behaviour change except for getting some warnings in the log or emitting some warning to a client executing a vacuum command.
I hear all the criticism which I don't disagree with. Obviously, scripts and other solutions could provide a lot more flexibility.
Also, I believe most of us agree that vacuum needs to be smarter.
src/backend/commands/vacuum.c does throw warnings for upcoming wraparound issues with one warning in particular mentioning prepared transactions and stale replication slots. So, throwing warnings is not unprecedented. There are 3 warnings in this file which I believe can also be handled by external tools. I'm not debating the merit of these warnings, nor am I trying to justify the addition of new warnings based on these.
My real question is whether vacuum should be preemptively complaining about prepared transactions or stale replication slots rather than waiting for transaction id to exceed the safe limit. I presume by the time safe limit is exceeded, vacuum's work would already have been significantly impacted.
AFAICT, my patch actually doesn't break anything and doesn't add any significant overhead to the vacuum process. It does supplement the current warnings though which might be useful.
Just discarding the prepared xacts is not the answer though.
... however, I have wondered a few times about making vacuum smarter about cases where the xmin is held down by prepared xacts or by replication slots. If we could record the oldest *and newest* xid needed by such resource retention markers we could potentially teach vacuum to remove intermediate dead rows. For high-churn workloads like like workqueue applications that could be a really big win.
We wouldn't need to track a fine-grained snapshot with an in-progress list (or inverted in-progress list like historic snapshots) for these. We'd just remember the needed xid range in [xmin,xmax] form. And we could even do the same for live backends' PGXACT - it might not be worth the price there, but if you have workloads that have batch xacts + high churn rate xacts it'd be pretty appealing.
It wouldn't help with xid wraparound concerns, but it could help a lot with bloat caused by old snapshots for some very common workloads.
--
