On Mon, Jun 27, 2022 at 12:36 PM Justin Pryzby wrote:
> By chance, I came across this prior thread which advocated the same thing in a
> initially (rather than indirectly as in this year's thread).
Revisiting this topic reminded me that PostgreSQL 14 (the first
version that had the wraparound
On Thu, Feb 03, 2022 at 01:05:50PM -0500, Robert Haas wrote:
> On Thu, Dec 9, 2021 at 8:56 PM Andres Freund wrote:
> > I think we should move *away* from single user mode, rather than the
> > opposite. It's a substantial code burden and it's hard to use.
>
> Yes. This thread seems to be largely
On Wed, Mar 16, 2022 at 4:48 AM Peter Geoghegan wrote:
>
> On Wed, Feb 16, 2022 at 12:43 AM John Naylor
> wrote:
> > I'll put some effort in finding any way that it might not be robust.
> > After that, changing the message and docs is trivial.
>
> It would be great to be able to totally drop the
On Wed, Feb 16, 2022 at 12:43 AM John Naylor
wrote:
> I'll put some effort in finding any way that it might not be robust.
> After that, changing the message and docs is trivial.
It would be great to be able to totally drop the idea of using
single-user mode before Postgres 15 feature freeze.
On Sun, Feb 20, 2022 at 2:15 PM Andres Freund wrote:
> We could e.g. add an error if FreezeMultiXactId() needs to create a new
> multixact for a far-in-the-past xid. That's not great, of course, but if we
> include the precise cause (pid of backend / prepared xact name / slot name /
> ...)
On Sun, Feb 20, 2022 at 02:15:37PM -0800, Andres Freund wrote:
> On 2022-02-19 20:57:57 -0800, Noah Misch wrote:
> > On Wed, Feb 16, 2022 at 03:43:12PM +0700, John Naylor wrote:
> > > On Wed, Feb 16, 2022 at 6:17 AM Peter Geoghegan wrote:
> > > > On Tue, Feb 15, 2022 at 9:28 AM Peter Geoghegan
Hi,
On 2022-02-19 20:57:57 -0800, Noah Misch wrote:
> On Wed, Feb 16, 2022 at 03:43:12PM +0700, John Naylor wrote:
> > On Wed, Feb 16, 2022 at 6:17 AM Peter Geoghegan wrote:
> > > On Tue, Feb 15, 2022 at 9:28 AM Peter Geoghegan wrote:
> >
> > > > I did notice from my own testing of the
On Wed, Feb 16, 2022 at 03:43:12PM +0700, John Naylor wrote:
> On Wed, Feb 16, 2022 at 6:17 AM Peter Geoghegan wrote:
> > On Tue, Feb 15, 2022 at 9:28 AM Peter Geoghegan wrote:
>
> > > I did notice from my own testing of the failsafe (by artificially
> > > inducing wraparound failure using an
On Wed, Feb 16, 2022 at 10:08 PM Peter Geoghegan wrote:
> > 6. Sometimes the user decides to run VACUUM FULL instead of plain
> > VACUUM because it sounds better.
>
> It's a pity that the name suggests otherwise. If only we'd named it
> something that suggests "option of last resort". Oh well.
On Wed, Feb 16, 2022 at 6:56 PM Robert Haas wrote:
> I think that's not really what is happening, at least not in the cases
> that typically are brought to my attention. In those cases, the
> typical pattern is:
> 5. None of the tables in the database have been vacuumed in a long
> time. There
On Wed, Feb 16, 2022 at 4:48 PM Peter Geoghegan wrote:
> There might well be an element of survivorship bias here. Most VACUUM
> operations won't ever attempt truncation (speaking very generally).
> How many times might (say) the customer that John mentioned have
> accidentally gone over
On Wed, Feb 16, 2022 at 1:04 PM Robert Haas wrote:
> No, what I'm saying is that people running older versions routinely
> run VACUUM in single-user mode because otherwise it fails due to the
> truncation issue. But once they go into single-user mode they lose
> protection.
Seems logically
On Wed, Feb 16, 2022 at 3:21 PM Peter Geoghegan wrote:
> On Wed, Feb 16, 2022 at 12:11 PM Robert Haas wrote:
> > No, I think it's PostgreSQL 13, because before the vacuum failsafe
> > thing you could end up truncating enough tables during vacuum
> > operations to actually wrap around.
>
> Why
On Wed, Feb 16, 2022 at 12:11 PM Robert Haas wrote:
> No, I think it's PostgreSQL 13, because before the vacuum failsafe
> thing you could end up truncating enough tables during vacuum
> operations to actually wrap around.
Why wouldn't the xidStopLimit thing prevent actual incorrect answers
to
On Wed, Feb 16, 2022 at 2:18 PM Peter Geoghegan wrote:
> It seems as if the advice about single user mode persisted for no
> great reason at all. Technically there were some remaining reasons to
> keep it around (like the truncation thing), but overall these
> secondary reasons could have been
On Wed, Feb 16, 2022 at 1:28 PM Peter Geoghegan wrote:
> On Wed, Feb 16, 2022 at 10:18 AM Andres Freund wrote:
> > > I'm pretty sure that some people believe that wraparound can cause
> > > actual data corruption
> >
> > Well, historically they're not wrong.
>
> True, but the most recent version
On Wed, Feb 16, 2022 at 10:27 AM Peter Geoghegan wrote:
> True, but the most recent version where that's actually possible is
> PostgreSQL 8.0, which was released in early 2005.
It just occurred to me that the main historic reason for the single
user mode advice was the lack of virtual XIDs. The
On Wed, Feb 16, 2022 at 10:18 AM Andres Freund wrote:
> > I'm pretty sure that some people believe that wraparound can cause
> > actual data corruption
>
> Well, historically they're not wrong.
True, but the most recent version where that's actually possible is
PostgreSQL 8.0, which was released
Hi,
On 2022-02-16 10:14:19 -0800, Peter Geoghegan wrote:
> Absolutely -- couldn't agree more. Do you think it's worth targeting
> 14 here, or just HEAD?
I'd go for HEAD first, but wouldn't protest against 14.
> I'm pretty sure that some people believe that wraparound can cause
> actual data
On Wed, Feb 16, 2022 at 9:56 AM Robert Haas wrote:
> +1. But I think we might want to try to write documentation around
> this. We should explicitly tell people NOT to use single-user mode,
> because that stupid message has been there for a long time and a lot
> of people have probably
On Wed, Feb 16, 2022 at 12:51 PM Peter Geoghegan wrote:
> Good news!
+1. But I think we might want to try to write documentation around
this. We should explicitly tell people NOT to use single-user mode,
because that stupid message has been there for a long time and a lot
of people have probably
On Wed, Feb 16, 2022 at 8:48 AM Masahiko Sawada wrote:
> FYI, I've tested the situation that I assumed autovacuum can not
> correct the problem; when the system had already crossed xidStopLimit,
> it keeps failing to vacuum on tables that appear in the front of the
> list and have sufficient
On Wed, Feb 16, 2022 at 2:29 AM Peter Geoghegan wrote:
>
> On Mon, Feb 14, 2022 at 10:04 PM John Naylor
> wrote:
> > Well, the point of inventing this new vacuum mode was because I
> > thought that upon reaching xidStopLimit, we couldn't issue commands,
> > period, under the postmaster. If it
On Wed, Feb 16, 2022 at 12:43 AM John Naylor
wrote:
> I'll put some effort in finding any way that it might not be robust.
> After that, changing the message and docs is trivial.
Great, thanks John.
--
Peter Geoghegan
On Wed, Feb 16, 2022 at 6:17 AM Peter Geoghegan wrote:
>
> On Tue, Feb 15, 2022 at 9:28 AM Peter Geoghegan wrote:
> > I did notice from my own testing of the failsafe (by artificially
> > inducing wraparound failure using an XID burning C function) that
> > autovacuum seemed to totally correct
On Tue, Feb 15, 2022 at 9:28 AM Peter Geoghegan wrote:
> On Mon, Feb 14, 2022 at 10:04 PM John Naylor
> wrote:
> > Well, the point of inventing this new vacuum mode was because I
> > thought that upon reaching xidStopLimit, we couldn't issue commands,
> > period, under the postmaster. If it was
On Mon, Feb 14, 2022 at 10:04 PM John Naylor
wrote:
> Well, the point of inventing this new vacuum mode was because I
> thought that upon reaching xidStopLimit, we couldn't issue commands,
> period, under the postmaster. If it was easier to get a test instance
> to xidStopLimit, I certainly would
On Tue, Feb 15, 2022 at 1:04 AM John Naylor
wrote:
> Well, the point of inventing this new vacuum mode was because I
> thought that upon reaching xidStopLimit, we couldn't issue commands,
> period, under the postmaster. If it was easier to get a test instance
> to xidStopLimit, I certainly would
On Tue, Feb 15, 2022 at 11:22 AM Peter Geoghegan wrote:
>
> On Mon, Feb 14, 2022 at 8:04 PM John Naylor
> wrote:
> > The failsafe mode does disable truncation as of v14:
> >
> > commit 60f1f09ff44308667ef6c72fbafd68235e55ae27
> > Author: Peter Geoghegan
> > Date: Tue Apr 13 12:58:31 2021
On Mon, Feb 14, 2022 at 8:04 PM John Naylor
wrote:
> The failsafe mode does disable truncation as of v14:
>
> commit 60f1f09ff44308667ef6c72fbafd68235e55ae27
> Author: Peter Geoghegan
> Date: Tue Apr 13 12:58:31 2021 -0700
>
> Don't truncate heap when VACUUM's failsafe is in effect.
That's
On Fri, Feb 4, 2022 at 4:58 AM Robert Haas 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
On Thu, Feb 3, 2022 at 7:30 PM Robert Haas wrote:
>
> That error comes from GetNewTransactionId(), so that function must
> either try to execute DML or do something else which causes an XID to
> be assigned. I think a plain SELECT should work just fine.
It was indeed doing writes, so that much
On Thu, Feb 03, 2022 at 07:26:01PM -0800, Andres Freund wrote:
> 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
Hi,
On 2022-02-03 21:08:03 -0500, Robert Haas wrote:
> On Thu, Feb 3, 2022 at 8:35 PM Andres Freund 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
On Thu, Feb 3, 2022 at 8:35 PM Andres Freund wrote:
> Yea, I'd have no problem leaving the "hard" limit somewhere closer to 1
> million (although 100k should be just as well), but introduce a softer "only
> vacuum/drop/truncate" limit a good bit before that.
+1.
> To address the "as long as"
Hi,
On 2022-02-03 17:02:15 -0500, Robert Haas wrote:
> On Thu, Feb 3, 2022 at 4:50 PM Andres Freund 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 /
On Thu, Feb 3, 2022 at 5:08 PM John Naylor wrote:
> Looking closer, there is a function defined by an extension. I'd have
> to dig further to see if writes happen. The error is exactly what
> we've been talking about:
>
> 2022-01-03 22:03:23 PST ERROR: database is not accepting commands to
>
On Thu, Feb 3, 2022 at 4:58 PM Andres Freund wrote:
>
> Hi,
>
> On 2022-02-03 16:18:27 -0500, John Naylor wrote:
> > I just checked some client case notes where they tried just that
> > before getting outside help, and both SELECT and VACUUM FREEZE
> > commands were rejected.
>
> What kind of
On Thu, Feb 3, 2022 at 4:50 PM Andres Freund 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
Hi,
On 2022-02-03 16:18:27 -0500, John Naylor wrote:
> I just checked some client case notes where they tried just that
> before getting outside help, and both SELECT and VACUUM FREEZE
> commands were rejected.
What kind of SELECT was that? Any chance it caused a write via functions, a
view,
On Thu, Feb 3, 2022 at 4:18 PM John Naylor wrote:
> I just checked some client case notes where they tried just that
> before getting outside help, and both SELECT and VACUUM FREEZE
> commands were rejected. The failure is clearly indicated in the log.
It would be helpful to know how it failed -
Hi,
On 2022-02-03 13:42:20 -0500, Robert Haas wrote:
> They *do* have a choice. They can continue to operate the system in
> multi-user mode, they can have read access to their data, and they can
> run VACUUM and other non-XID-allocating commands to fix the issue.
> Sure, their application can't
On Thu, Feb 3, 2022 at 1:42 PM Robert Haas wrote:
>
> On Thu, Feb 3, 2022 at 1:34 PM John Naylor
> wrote:
> > The word "advice" sounds like people have a choice, rather than the
> > system not accepting commands anymore. It would be much less painful
> > if the system closed connections and
On Thu, Feb 3, 2022 at 1:34 PM John Naylor wrote:
> The word "advice" sounds like people have a choice, rather than the
> system not accepting commands anymore. It would be much less painful
> if the system closed connections and forbade all but superusers to
> connect, but that sounds like a lot
On Thu, Feb 3, 2022 at 1:06 PM Robert Haas wrote:
>
> On Thu, Dec 9, 2021 at 8:56 PM Andres Freund wrote:
> > I think we should move *away* from single user mode, rather than the
> > opposite. It's a substantial code burden and it's hard to use.
>
> Yes. This thread seems to be largely devoted
On Thu, Dec 9, 2021 at 8:56 PM Andres Freund wrote:
> I think we should move *away* from single user mode, rather than the
> opposite. It's a substantial code burden and it's hard to use.
Yes. This thread seems to be largely devoted to the topic of making
single-user vacuum work better, but I
ands of tables, with a wide range of sizes and ages.
>
> While that seems like a nice property to have, it does complicate
> things, so can be left for follow-on work.
I added that in the attached 003.
--
Justin
>From a870303f4bd62b7c653a4bef53ed6d2748268bc0 Mon Sep 17 00:00:00 2001
Thinking further about the use of emergency mode, we have this:
"If for some reason autovacuum fails to clear old XIDs from a table,
the system will begin to emit warning messages like this when the
database's oldest XIDs reach forty million transactions from the
wraparound point:
WARNING:
On Thu, Feb 3, 2022 at 3:14 AM Masahiko Sawada wrote:
> + The only other option that may be combined with
> VERBOSE, although in single-user mode no client
> messages are
> + output.
>
> Given VERBOSE with EMERGENCY can work only in multi-user mode, why
> only VERBOSE can be specified
On Wed, Feb 2, 2022 at 6:50 AM John Naylor wrote:
>
> On Thu, Jan 27, 2022 at 8:28 PM Justin Pryzby wrote:
>
> > I'm sure you meant "&" here (fixed in attached patch to appease the cfbot):
> > + if (options | VACOPT_MINIMAL)
>
> Thanks for catching that! That copy-pasto was also
On Thu, Jan 27, 2022 at 8:28 PM Justin Pryzby wrote:
> I'm sure you meant "&" here (fixed in attached patch to appease the cfbot):
> + if (options | VACOPT_MINIMAL)
Thanks for catching that! That copy-pasto was also masking my failure
to process the option properly -- fixed in the
mode, but not limited to it (so it's easy to test)
> - to get out of single user mode as quickly as possible
>From 03c567bb534219acdd76b0acc40e544c76f938e5 Mon Sep 17 00:00:00 2001
From: John Naylor
Date: Fri, 21 Jan 2022 17:41:58 -0500
Subject: [PATCH] do only critical work during single-user vacuum
On 1/21/22, 2:43 PM, "John Naylor" wrote:
> - to have a simple, easy to type, command
AFAICT the disagreement is really just about the grammar.
Sawada-san's idea would look something like
VACUUM (FREEZE, INDEX_CLEANUP OFF, MIN_XID_AGE 16, MIN_MXID_AGE
16);
while your
On Wed, Jan 19, 2022 at 5:26 PM Michael Paquier wrote:
>
> Could you avoid introducing a new grammar pattern in VACUUM? Any new
> option had better be within the parenthesized part as it is extensible
> at will with its set of DefElems.
This new behavior is not an option that one can sensibly
On Thu, Jan 20, 2022 at 4:14 AM John Naylor
wrote:
>
> On Wed, Jan 19, 2022 at 12:46 AM Masahiko Sawada
> wrote:
> >
> > On Fri, Jan 14, 2022 at 7:04 AM Bossart, Nathan wrote:
> > >
> > > I guess I'm ultimately imagining the new options as replacing the
> > > vacuumdb implementation. IOW
On Wed, Jan 19, 2022 at 09:11:48PM +, Bossart, Nathan wrote:
> I personally think VACUUM FOR WRAPAROUND is the best of the options
> provided thus far.
Could you avoid introducing a new grammar pattern in VACUUM? Any new
option had better be within the parenthesized part as it is extensible
On 1/19/22, 11:15 AM, "John Naylor" wrote:
> This seems to be the motivating reason for wanting new configurability
> on the server side. In any case, new knobs are out of scope for this
> thread. If the use case is compelling enough, may I suggest starting a
> new thread?
Sure. Perhaps the new
On 1/18/22, 9:47 PM, "Masahiko Sawada" wrote:
> IIUC what we want to do here are two things: (1) select only old
> tables and (2) set INDEX_CLEANUP = off, TRUNCATE = off, and FREEZE =
> on. VACUUM LIMIT statement does both things at the same time. Although
> I’m concerned a bit about its
On Wed, Jan 19, 2022 at 12:46 AM Masahiko Sawada wrote:
>
> On Fri, Jan 14, 2022 at 7:04 AM Bossart, Nathan wrote:
> >
> > I guess I'm ultimately imagining the new options as replacing the
> > vacuumdb implementation. IOW vacuumdb would just use MIN_(M)XID_AGE
> > behind the scenes (as would a
On Fri, Jan 14, 2022 at 7:04 AM Bossart, Nathan wrote:
>
> On 1/13/22, 4:58 AM, "John Naylor" wrote:
> > On Wed, Jan 12, 2022 at 12:26 PM Bossart, Nathan
> > wrote:
> >> As I've stated upthread, Sawada-san's suggested approach was my
> >> initial reaction to this thread. I'm not wedded to the
I see a CF entry has been created already, and the cfbot doesn't like
my PoC. To prevent confusion, I've taken the liberty of switching the
author to myself and set to Waiting on Author. FWIW, my local build
passed make check-world after applying Justin's fix and changing a
couple other things.
On 1/13/22, 4:58 AM, "John Naylor" wrote:
> On Wed, Jan 12, 2022 at 12:26 PM Bossart, Nathan wrote:
>> As I've stated upthread, Sawada-san's suggested approach was my
>> initial reaction to this thread. I'm not wedded to the idea of adding
>> new options, but I think there are a couple of
On Wed, Jan 12, 2022 at 12:26 PM Bossart, Nathan wrote:
>
> > - For the general case, we would now have the ability to vacuum a
> > table, and possibly have no effect at all. That seems out of place
> > with the other options.
>
> Perhaps a message would be emitted when tables are specified but
>
On 1/12/22, 7:43 AM, "John Naylor" wrote:
> On Wed, Jan 12, 2022 at 1:49 AM Masahiko Sawada wrote:
>> As another idea, we might be able to add a new option that takes an
>> optional integer value, like VACUUM (MIN_XID), VACUUM (MIN_MXID), and
>> VACUUM (MIN_XID 50). We vacuum only tables
On Wed, Jan 12, 2022 at 1:49 AM Masahiko Sawada wrote:
> It seems to me that adding new syntax instead of a new option is less
> flexible. In the future, for instance, when we support parallel heap
> scan for VACUUM, we may want to add a parallel-related option to both
> VACUUM statement and
On Tue, Jan 11, 2022 at 9:20 PM Justin Pryzby wrote:
>
> On Tue, Jan 11, 2022 at 07:58:56PM -0500, John Naylor wrote:
> > + // FIXME: also check reloption
> > + // WIP: 95% is a starting point for discussion
> > + if ((table_xid_age < autovacuum_freeze_max_age
On Tue, Jan 11, 2022 at 8:57 PM Peter Geoghegan wrote:
> On Tue, Jan 11, 2022 at 4:59 PM John Naylor
> > For the PoC I wanted to try re-using existing keywords. I went with
> > "VACUUM LIMIT" since LIMIT is already a keyword that cannot be used as
> > a table name. It also brings "wraparound
On Wed, Jan 12, 2022 at 10:57 AM Peter Geoghegan wrote:
>
> On Tue, Jan 11, 2022 at 4:59 PM John Naylor
> wrote:
> > I've attached a PoC *untested* patch to show what it would look like
> > as a top-level statement. If the "shape" is uncontroversial, I'll put
> > work into testing it and
On Tue, Jan 11, 2022 at 07:58:56PM -0500, John Naylor wrote:
> + // FIXME: also check reloption
> + // WIP: 95% is a starting point for discussion
> + if ((table_xid_age < autovacuum_freeze_max_age * 0.95) ||
> + (table_mxid_age <
On Tue, Jan 11, 2022 at 4:59 PM John Naylor
wrote:
> I've attached a PoC *untested* patch to show what it would look like
> as a top-level statement. If the "shape" is uncontroversial, I'll put
> work into testing it and fleshing it out.
Great!
> For the PoC I wanted to try re-using existing
On Tue, Dec 21, 2021 at 4:56 PM Peter Geoghegan wrote:
> But if we're going to add a new option to the VACUUM command (or
> something of similar scope), then we might as well add a new behavior
> that is reasonably exact -- something that (say) only *starts* a
> VACUUM for those tables whose
On Tue, Dec 21, 2021 at 6:39 PM Masahiko Sawada wrote:
> Even not in the situation where the database has to run as the
> single-user mode to freeze tuples, I think there would be some use
> cases where users want to run vacuum (in failsafe mode) on tables with
> relfrozenxid/relminmxid greater
On Tue, Dec 21, 2021 at 10:39 PM Masahiko Sawada wrote:
>
> On Wed, Dec 22, 2021 at 6:56 AM Peter Geoghegan wrote:
> >
> > This new command/facility should probably not be a new flag to the
> > VACUUM command, as such. Rather, I think that it should either be an
> > SQL-callable function, or a
On Wed, Dec 22, 2021 at 6:56 AM Peter Geoghegan wrote:
>
> On Tue, Dec 21, 2021 at 1:31 PM John Naylor
> wrote:
> > On second thought, we don't really need another number here. We could
> > simply go by the existing failsafe parameter, and if the admin wants a
> > different value, it's already
On Tue, Dec 21, 2021 at 5:56 PM Peter Geoghegan wrote:
>
> On Tue, Dec 21, 2021 at 1:31 PM John Naylor
> wrote:
> > On second thought, we don't really need another number here. We could
> > simply go by the existing failsafe parameter, and if the admin wants a
> > different value, it's already
On Tue, Dec 21, 2021 at 1:31 PM John Naylor
wrote:
> On second thought, we don't really need another number here. We could
> simply go by the existing failsafe parameter, and if the admin wants a
> different value, it's already possible to specify
>
I wrote:
> Instead of a boolean, it seems like the new option should specify some
> age below which VACUUM will skip the table entirely, and above which
> will enter fail-safe mode. As mentioned earlier, the shutdown hint
> could spell out the exact command. With this design, it would specify
>
On Tue, Dec 21, 2021 at 3:56 AM Masahiko Sawada wrote:
>
> On Tue, Dec 21, 2021 at 1:53 PM Peter Geoghegan wrote:
> >
> > On Mon, Dec 20, 2021 at 8:40 PM Masahiko Sawada
> > wrote:
> > > BTW a vacuum automatically enters failsafe mode under the situation
> > > where the user has to run a
On Tue, Dec 21, 2021 at 1:53 PM Peter Geoghegan wrote:
>
> On Mon, Dec 20, 2021 at 8:40 PM Masahiko Sawada wrote:
> > BTW a vacuum automatically enters failsafe mode under the situation
> > where the user has to run a vacuum in the single-user mode, right?
>
> Only for the table that had the
On Mon, Dec 20, 2021 at 8:40 PM Masahiko Sawada wrote:
> BTW a vacuum automatically enters failsafe mode under the situation
> where the user has to run a vacuum in the single-user mode, right?
Only for the table that had the problem. Maybe there are no other
tables that a database level
On Tue, Dec 21, 2021 at 12:46 PM Andres Freund wrote:
>
> Hi,
>
> On 2021-12-20 17:17:26 -0800, Peter Geoghegan wrote:
> > On Thu, Dec 9, 2021 at 8:41 PM Bossart, Nathan wrote:
> > > I like the idea of having a built-in function that does the bare
> > > minimum to resolve wraparound emergencies,
Hi,
On 2021-12-20 17:17:26 -0800, Peter Geoghegan wrote:
> On Thu, Dec 9, 2021 at 8:41 PM Bossart, Nathan wrote:
> > I like the idea of having a built-in function that does the bare
> > minimum to resolve wraparound emergencies, and I think providing some
> > sort of simple progress indicator
On Thu, Dec 9, 2021 at 8:41 PM Bossart, Nathan wrote:
> I like the idea of having a built-in function that does the bare
> minimum to resolve wraparound emergencies, and I think providing some
> sort of simple progress indicator (even if rudimentary) would be very
> useful.
If John doesn't have
On 12/9/21, 5:27 PM, "Peter Geoghegan" wrote:
> I imagine that this new function (to handle maintenance tasks in the
> event of a wraparound emergency) would output information about its
> progress. For example, it would make an up-front decision about which
> tables needed to be vacuumed in
On Thu, Dec 9, 2021 at 5:56 PM Andres Freund wrote:
> I think we should move *away* from single user mode, rather than the
> opposite. It's a substantial code burden and it's hard to use.
I wouldn't say that this is moving closer to single user mode.
> I don't think single user mode is a good
Hi,
On 2021-12-09 16:34:53 -0800, Peter Geoghegan wrote:
> But even still, why not have some variant of single-user mode just for
> this task?
> Something that's easy to use when the DBA is rudely
> awakened at 4am -- something a little bit like a big red button that
> fixes the exact problem of
On Thu, Dec 9, 2021 at 5:12 PM Bossart, Nathan wrote:
> As Andres noted, such a feature might be useful during normal
> operation, too. Perhaps the vacuumdb --min-xid-age stuff should be
> moved to a new VACUUM option.
I was thinking of something like pg_import_system_collations() for
this: a
On 12/9/21, 5:06 PM, "Bossart, Nathan" wrote:
> On 12/9/21, 4:36 PM, "Peter Geoghegan" wrote:
>> We could then apply this criteria in new code that implements this
>> "big red button" (maybe this is a new option for the postgres
>> executable, a little like --single?). Something that's
On 12/9/21, 4:36 PM, "Peter Geoghegan" wrote:
> We could then apply this criteria in new code that implements this
> "big red button" (maybe this is a new option for the postgres
> executable, a little like --single?). Something that's reasonably
> targeted, and dead simple to use.
+1
Nathan
On 12/9/21, 12:33 PM, "Bossart, Nathan" wrote:
> On 12/9/21, 11:34 AM, "John Naylor" wrote:
>> Now that we have a concept of a fail-safe vacuum, maybe it would be
>> beneficial to skip a vacuum in single-user mode if the fail-safe
>> criteria were not met at the beginning of vacuuming a
On Thu, Dec 9, 2021 at 3:53 PM John Naylor wrote:
> > single-user mode should prompt the user about
> > what exact VACUUM command they ought to run to get things going.
>
> The current message is particularly bad in its vagueness because some
> users immediately reach for VACUUM FULL, which quite
On Thu, Dec 9, 2021 at 5:13 PM Peter Geoghegan wrote:
> Oh, I think I misunderstood. Your concern is for the case where the
> DBA runs a simple "VACUUM" in single-user mode; you want to skip over
> tables that don't really need to advance relfrozenxid, automatically.
Right.
> I can see an
Hi,
On 2021-12-09 15:28:18 -0400, John Naylor wrote:
> When a user must shut down and restart in single-user mode to run
> vacuum on an entire database, that does a lot of work that's
> unnecessary for getting the system online again, even without
> index_cleanup. We had a recent case where a
On Thu, Dec 9, 2021 at 1:04 PM Peter Geoghegan wrote:
> On Thu, Dec 9, 2021 at 11:28 AM John Naylor
> wrote:
> > Now that we have a concept of a fail-safe vacuum, maybe it would be
> > beneficial to skip a vacuum in single-user mode if the fail-safe
> > criteria were not met at the beginning of
On Thu, Dec 9, 2021 at 11:28 AM John Naylor
wrote:
> Now that we have a concept of a fail-safe vacuum, maybe it would be
> beneficial to skip a vacuum in single-user mode if the fail-safe
> criteria were not met at the beginning of vacuuming a relation.
Obviously the main goal of the failsafe is
On 12/9/21, 11:34 AM, "John Naylor" wrote:
> When a user must shut down and restart in single-user mode to run
> vacuum on an entire database, that does a lot of work that's
> unnecessary for getting the system online again, even without
> index_cleanup. We had a recent case where a single-user
When a user must shut down and restart in single-user mode to run
vacuum on an entire database, that does a lot of work that's
unnecessary for getting the system online again, even without
index_cleanup. We had a recent case where a single-user vacuum took
around 3 days to complete.
Now that we
97 matches
Mail list logo
