Hi,
On 2022-02-03 21:08:03 -0500, Robert Haas wrote:
> On Thu, Feb 3, 2022 at 8:35 PM Andres Freund <andres@anarazel.de> wrote:
> > We can compute the:
> > 1) oldest slot by xmin, with name
> > 2) oldest walsender by xmin, with pid
> > 3) oldest prepared transaction id by xid / xmin, with name
> > 4) oldest in-progress transaction id by xid / xmin, with name
> > 5) oldest database datfrozenxid, with database name
> >
> > If 1-4) are close to 5), there's no point in trying to vacuum aggressively, it
> > won't help. So we instead can say that the xmin horizon (with a better name)
> > is held back by the oldest of these, with enough identifying information for
> > the user to actually know where to look.
>
> Yes. This kind of thing strikes me as potentially a huge help. To
> rephrase that in other terms, we could tell the user what the actual
> problem is instead of suggesting to them that they shut down their
> database just for fun. It's "just for fun" because (a) it typically
> won't fix the real problem, which is most often (1) or (3) from your
> list, and even if it's (2) or (4) they could just kill the session
> instead of shutting down the whole database
Not that it matters, but IME the leading cause is 5). Often due to autovacuum
configuration. Which reminded me of the one thing that single user mode
is actually helpful for: Being able to start a manual VACUUM.
Once autovacuum is churning along in anti-wrap mode, with multiple workers, it
can be hard to manually VACUUM without waiting for autovacuum to do it's
throttled thing. The only way is to start the manual VACUUM and kill
autovacuum workers whenever they're blocking the manual vacuum(s).
Which reminds me: Perhaps we ought to hint about reducing / removing
autovacuum cost limits in this situation? And perhaps make autovacuum absorb
config changes while running? It's annoying that an autovac halfway into a
huge table doesn't absorb changed cost limits for example.
> (b) no matter what needs to be done, whether it's VACUUM or ROLLBACK
> PREPARED or something else, they may as well do that thing in multi-user
> mode rather than single-user mode, unless we as PostgreSQL developers forgot
> to make that actually work.
One thing that we made quite hard is to rollback prepared transactions,
because we require to be in the same database (a lot of fun in single user
mode with a lot of databases). We can't commit in the same database, but I
wonder if it's doable to allow rollbacks?
Greetings,
Andres Freund
