From: | John Naylor <john(dot)naylor(at)enterprisedb(dot)com> |
---|---|
To: | Andres Freund <andres(at)anarazel(dot)de> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Peter Geoghegan <pg(at)bowt(dot)ie>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: do only critical work during single-user vacuum? |
Date: | 2022-02-15 04:04:46 |
Message-ID: | CAFBsxsHp3brGqWu5vU_6d5wfOU5i40c8u3Hg=3TtN4qZwmAecw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Fri, Feb 4, 2022 at 4:58 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> As I said
> before, I know TRUNCATE has been an issue in the past, and if that's
> not already fixed in v14, we should. If there's other stuff, we should
> fix that too.
The failsafe mode does disable truncation as of v14:
commit 60f1f09ff44308667ef6c72fbafd68235e55ae27
Author: Peter Geoghegan <pg(at)bowt(dot)ie>
Date: Tue Apr 13 12:58:31 2021 -0700
Don't truncate heap when VACUUM's failsafe is in effect.
--
To demonstrate to myself, I tried a few vacuums in a debugger session
with a breakpoint at GetNewTransactionId(). I've only seen it reach
here when heap truncation happens (or the not relevant for wraparound
situations FULL and ANALYZE).
With the maximum allowable setting of autovacuum_freeze_max_age of 2
billion, the highest allowable vacuum_failsafe_age is 2.1 billion, so
heap truncation will be shut off before the warnings start.
> And then we should KILL WITH FIRE
> the message telling people to use single user mode -- and once we do
> that, the question of what the behavior ought to be when someone does
> run VACUUM in single user mode becomes a lot less important.
Okay, so it sounds like changing the message is enough for v15? The
other two things mentioned are nice-to-haves, but wouldn't need to
hold back this minimal change, it seems:
On Fri, Feb 4, 2022 at 4:50 AM Andres Freund <andres(at)anarazel(dot)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.
It seems like this would require having access to "nodetag(parsetree)"
of the statement available in GetNewTransactionId. I don't immediately
see an easy way to do that...is a global var within the realm of
acceptability?
On Fri, Feb 4, 2022 at 8:35 AM Andres Freund <andres(at)anarazel(dot)de> wrote:
> we'd actually tell the user a bit more what about what is causing the
> problem.
>
> 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
[...]
> Also, adding an SRF providing the above in a useful format would be great for
> monitoring and for "remote debugging" of problems.
I concur it sounds very useful, and not terribly hard, but probably a
v16 project.
--
John Naylor
EDB: http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2022-02-15 04:21:41 | Re: do only critical work during single-user vacuum? |
Previous Message | Michael Paquier | 2022-02-15 04:02:41 | Re: Rewriting the test of pg_upgrade as a TAP test - take three - remastered set |