public inbox for [email protected]
help / color / mirror / Atom feedFrom: Fujii Masao <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate
Date: Thu, 11 Jun 2026 12:31:28 +0900
Message-ID: <CAHGQGwFKngUKWOM=N3sAOGCL95TjQf2jM8E3put7=cmGKHHg0A@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
On Fri, Apr 4, 2025 at 10:30 PM PG Bug reporting form
<[email protected]> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 18876
> Logged by: TAKATSUKA Haruka
> Email address: [email protected]
> PostgreSQL version: 17.4
> Operating system: any
> Description:
>
> In src/backend/access/transam/multixact.c, there are the following hint
> messages:
>
> "Execute a database-wide VACUUM in that database.\n"
> "You might also need to commit or roll back old prepared transactions, or
> drop stale replication slots."
>
> "To avoid MultiXactId assignment failures, execute a database-wide VACUUM
> in that database.\n"
> "You might also need to commit or roll back old prepared transactions, or
> drop stale replication slots."
>
> I think that their "drop stale replication slots" is not appropriate.
>
> Because NewRelminMxid is determined by GetOldestMultiXactId() and its
> comment says:
> * Return the oldest MultiXactId that's still possibly still seen as live
> by
> * any running transaction. Older ones might still exist on disk, but
> they no
> * longer have any running member transaction.
>
> Thus, the presence of an old mxid in a tuple pending removal by a slot is
> not considered to affect it.
>
> In addition, as far as I have tested, leaving the old inactive replication
> slot does not cause mxid_age(relminmxid) not to decrease after VACUUM.
I think you're right. So I prepared and attached a patch that refines
the MXID wraparound hint messages. The patch also updates the
documentation to clarify the relationship between replication slots and
MXID wraparound.
Thought?
Regards,
--
Fujii Masao
From 84fe025cc5592d7e01ba77b4b2c5eea8d8d5f35d Mon Sep 17 00:00:00 2001
From: "masao.fujii" <[email protected]’s-MacBook-Pro>
Date: Thu, 11 Jun 2026 11:18:00 +0900
Subject: [PATCH v1] Refine MultiXact wraparound hints
Replication slots can hold back XID horizons, but they do not prevent
MultiXact cleanup. Therefore, this commit removes replication slot
advice from MultiXact wraparound hint messages and update
the maintenance documentation to clarify the distinction.
Backpatch to all supported versions.
---
doc/src/sgml/maintenance.sgml | 6 ++++++
src/backend/access/transam/multixact.c | 12 ++++++------
src/backend/commands/vacuum.c | 2 +-
3 files changed, 13 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index d6d44d74069..20552317891 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -820,6 +820,12 @@ HINT: Stop the postmaster and vacuum that database in single-user mode.
<simpara>Running transactions and prepared transactions can be ignored if there
is no chance that they might appear in a multixact.</simpara>
</listitem>
+ <listitem>
+ <simpara>Unlike transaction ID wraparound, replication slots do not
+ directly hold back multixact cleanup. Dropping stale replication
+ slots is therefore not usually relevant to resolving multixact ID
+ wraparound problems.</simpara>
+ </listitem>
<listitem>
<simpara>MXID information is not directly visible in system views such as
<literal>pg_stat_activity</literal>; however, looking for old XIDs is still a good
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 8408492879b..0778a7c9b80 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1159,14 +1159,14 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database \"%s\"",
oldest_datname),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
else
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database with OID %u",
oldest_datoid),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
}
/*
@@ -1190,7 +1190,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
oldest_datname,
multiWrapLimit - result),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
else
ereport(WARNING,
(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -1199,7 +1199,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
oldest_datoid,
multiWrapLimit - result),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
}
/* Re-acquire lock and start over */
@@ -2475,7 +2475,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
oldest_datname,
multiWrapLimit - curMulti),
errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
else
ereport(WARNING,
(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -2484,7 +2484,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
oldest_datoid,
multiWrapLimit - curMulti),
errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
}
}
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 6c3208eeb84..3185062bf16 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1176,7 +1176,7 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params,
ereport(WARNING,
(errmsg("cutoff for freezing multixacts is far in the past"),
errhint("Close open transactions soon to avoid wraparound problems.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
/*
* Determine the minimum freeze age to use: as specified by the caller, or
--
2.53.0
From 855ad33c97e48d68f6e188e5a11036c9e47883ad Mon Sep 17 00:00:00 2001
From: "masao.fujii" <[email protected]’s-MacBook-Pro>
Date: Thu, 11 Jun 2026 11:18:00 +0900
Subject: [PATCH v1] Refine MultiXact wraparound hints
Replication slots can hold back XID horizons, but they do not prevent
MultiXact cleanup. Therefore, this commit removes replication slot
advice from MultiXact wraparound hint messages and update
the maintenance documentation to clarify the distinction.
Backpatch to all supported versions.
---
doc/src/sgml/maintenance.sgml | 6 ++++++
src/backend/access/transam/multixact.c | 12 ++++++------
2 files changed, 12 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index aa2df371efb..ddca8ea4166 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -819,6 +819,12 @@ HINT: Stop the postmaster and vacuum that database in single-user mode.
<simpara>Running transactions and prepared transactions can be ignored if there
is no chance that they might appear in a multixact.</simpara>
</listitem>
+ <listitem>
+ <simpara>Unlike transaction ID wraparound, replication slots do not
+ directly hold back multixact cleanup. Dropping stale replication
+ slots is therefore not usually relevant to resolving multixact ID
+ wraparound problems.</simpara>
+ </listitem>
<listitem>
<simpara>MXID information is not directly visible in system views such as
<literal>pg_stat_activity</literal>; however, looking for old XIDs is still a good
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 8b5220d8aab..dfa470d2228 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1158,14 +1158,14 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database \"%s\"",
oldest_datname),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
else
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database with OID %u",
oldest_datoid),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
}
/*
@@ -1189,7 +1189,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
oldest_datname,
multiWrapLimit - result),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
else
ereport(WARNING,
(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -1198,7 +1198,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
oldest_datoid,
multiWrapLimit - result),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
}
/* Re-acquire lock and start over */
@@ -2476,7 +2476,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
oldest_datname,
multiWrapLimit - curMulti),
errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
else
ereport(WARNING,
(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -2485,7 +2485,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
oldest_datoid,
multiWrapLimit - curMulti),
errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
}
}
--
2.53.0
Attachments:
[text/plain] v1-0001-PG16-Refine-MultiXact-wraparound-hints.txt (5.6K, ../CAHGQGwFKngUKWOM=N3sAOGCL95TjQf2jM8E3put7=cmGKHHg0A@mail.gmail.com/2-v1-0001-PG16-Refine-MultiXact-wraparound-hints.txt)
download | inline diff:
From 84fe025cc5592d7e01ba77b4b2c5eea8d8d5f35d Mon Sep 17 00:00:00 2001
From: "masao.fujii" <[email protected]’s-MacBook-Pro>
Date: Thu, 11 Jun 2026 11:18:00 +0900
Subject: [PATCH v1] Refine MultiXact wraparound hints
Replication slots can hold back XID horizons, but they do not prevent
MultiXact cleanup. Therefore, this commit removes replication slot
advice from MultiXact wraparound hint messages and update
the maintenance documentation to clarify the distinction.
Backpatch to all supported versions.
---
doc/src/sgml/maintenance.sgml | 6 ++++++
src/backend/access/transam/multixact.c | 12 ++++++------
src/backend/commands/vacuum.c | 2 +-
3 files changed, 13 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index d6d44d74069..20552317891 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -820,6 +820,12 @@ HINT: Stop the postmaster and vacuum that database in single-user mode.
<simpara>Running transactions and prepared transactions can be ignored if there
is no chance that they might appear in a multixact.</simpara>
</listitem>
+ <listitem>
+ <simpara>Unlike transaction ID wraparound, replication slots do not
+ directly hold back multixact cleanup. Dropping stale replication
+ slots is therefore not usually relevant to resolving multixact ID
+ wraparound problems.</simpara>
+ </listitem>
<listitem>
<simpara>MXID information is not directly visible in system views such as
<literal>pg_stat_activity</literal>; however, looking for old XIDs is still a good
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 8408492879b..0778a7c9b80 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1159,14 +1159,14 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database \"%s\"",
oldest_datname),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
else
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database with OID %u",
oldest_datoid),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
}
/*
@@ -1190,7 +1190,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
oldest_datname,
multiWrapLimit - result),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
else
ereport(WARNING,
(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -1199,7 +1199,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
oldest_datoid,
multiWrapLimit - result),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
}
/* Re-acquire lock and start over */
@@ -2475,7 +2475,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
oldest_datname,
multiWrapLimit - curMulti),
errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
else
ereport(WARNING,
(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -2484,7 +2484,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
oldest_datoid,
multiWrapLimit - curMulti),
errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
}
}
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 6c3208eeb84..3185062bf16 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1176,7 +1176,7 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params,
ereport(WARNING,
(errmsg("cutoff for freezing multixacts is far in the past"),
errhint("Close open transactions soon to avoid wraparound problems.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
/*
* Determine the minimum freeze age to use: as specified by the caller, or
--
2.53.0
[application/octet-stream] v1-0001-Refine-MultiXact-wraparound-hints.patch (6.0K, ../CAHGQGwFKngUKWOM=N3sAOGCL95TjQf2jM8E3put7=cmGKHHg0A@mail.gmail.com/3-v1-0001-Refine-MultiXact-wraparound-hints.patch)
download | inline diff:
From b6529d80b6aa7514c5c43a2e9f69caf35e666af4 Mon Sep 17 00:00:00 2001
From: "masao.fujii" <[email protected]’s-MacBook-Pro>
Date: Thu, 11 Jun 2026 11:18:00 +0900
Subject: [PATCH v1] Refine MultiXact wraparound hints
Replication slots can hold back XID horizons, but they do not prevent
MultiXact cleanup. Therefore, this commit removes replication slot
advice from MultiXact wraparound hint messages and update
the maintenance documentation to clarify the distinction.
Backpatch to all supported versions.
---
doc/src/sgml/maintenance.sgml | 6 ++++++
src/backend/access/transam/multixact.c | 12 ++++++------
src/backend/commands/vacuum.c | 2 +-
3 files changed, 13 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 4a21bdb5de7..ec4204f88d9 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -869,6 +869,12 @@ HINT: Execute a database-wide VACUUM in that database.
<simpara>Running transactions and prepared transactions can be ignored if there
is no chance that they might appear in a multixact.</simpara>
</listitem>
+ <listitem>
+ <simpara>Unlike transaction ID wraparound, replication slots do not
+ directly hold back multixact cleanup. Dropping stale replication
+ slots is therefore not usually relevant to resolving multixact ID
+ wraparound problems.</simpara>
+ </listitem>
<listitem>
<simpara>MXID information is not directly visible in system views such as
<literal>pg_stat_activity</literal>; however, looking for old XIDs is still a good
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 10cbc0d76bd..8c694658132 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1040,14 +1040,14 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
errmsg("database is not accepting commands that assign new MultiXactIds to avoid wraparound data loss in database \"%s\"",
oldest_datname),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
else
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("database is not accepting commands that assign new MultiXactIds to avoid wraparound data loss in database with OID %u",
oldest_datoid),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
}
/*
@@ -1073,7 +1073,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
(double) (multiWrapLimit - result) / (MaxMultiXactId / 2) * 100),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
else
ereport(WARNING,
(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -1084,7 +1084,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
(double) (multiWrapLimit - result) / (MaxMultiXactId / 2) * 100),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
}
/* Re-acquire lock and start over */
@@ -2211,7 +2211,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
(double) (multiWrapLimit - curMulti) / (MaxMultiXactId / 2) * 100),
errhint("To avoid MultiXactId assignment failures, execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
else
ereport(WARNING,
(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -2222,7 +2222,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
(double) (multiWrapLimit - curMulti) / (MaxMultiXactId / 2) * 100),
errhint("To avoid MultiXactId assignment failures, execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
}
}
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index a4abb29cf64..38539a6fd3d 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1177,7 +1177,7 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params,
ereport(WARNING,
(errmsg("cutoff for freezing multixacts is far in the past"),
errhint("Close open transactions soon to avoid wraparound problems.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
/*
* Determine the minimum freeze age to use: as specified by the caller, or
--
2.53.0
[text/plain] v1-0001-PG15_PG14-Refine-MultiXact-wraparound-hints.txt (4.9K, ../CAHGQGwFKngUKWOM=N3sAOGCL95TjQf2jM8E3put7=cmGKHHg0A@mail.gmail.com/4-v1-0001-PG15_PG14-Refine-MultiXact-wraparound-hints.txt)
download | inline diff:
From 855ad33c97e48d68f6e188e5a11036c9e47883ad Mon Sep 17 00:00:00 2001
From: "masao.fujii" <[email protected]’s-MacBook-Pro>
Date: Thu, 11 Jun 2026 11:18:00 +0900
Subject: [PATCH v1] Refine MultiXact wraparound hints
Replication slots can hold back XID horizons, but they do not prevent
MultiXact cleanup. Therefore, this commit removes replication slot
advice from MultiXact wraparound hint messages and update
the maintenance documentation to clarify the distinction.
Backpatch to all supported versions.
---
doc/src/sgml/maintenance.sgml | 6 ++++++
src/backend/access/transam/multixact.c | 12 ++++++------
2 files changed, 12 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index aa2df371efb..ddca8ea4166 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -819,6 +819,12 @@ HINT: Stop the postmaster and vacuum that database in single-user mode.
<simpara>Running transactions and prepared transactions can be ignored if there
is no chance that they might appear in a multixact.</simpara>
</listitem>
+ <listitem>
+ <simpara>Unlike transaction ID wraparound, replication slots do not
+ directly hold back multixact cleanup. Dropping stale replication
+ slots is therefore not usually relevant to resolving multixact ID
+ wraparound problems.</simpara>
+ </listitem>
<listitem>
<simpara>MXID information is not directly visible in system views such as
<literal>pg_stat_activity</literal>; however, looking for old XIDs is still a good
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 8b5220d8aab..dfa470d2228 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1158,14 +1158,14 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database \"%s\"",
oldest_datname),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
else
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database with OID %u",
oldest_datoid),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
}
/*
@@ -1189,7 +1189,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
oldest_datname,
multiWrapLimit - result),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
else
ereport(WARNING,
(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -1198,7 +1198,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
oldest_datoid,
multiWrapLimit - result),
errhint("Execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
}
/* Re-acquire lock and start over */
@@ -2476,7 +2476,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
oldest_datname,
multiWrapLimit - curMulti),
errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
else
ereport(WARNING,
(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -2485,7 +2485,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
oldest_datoid,
multiWrapLimit - curMulti),
errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
- "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+ "You might also need to commit or roll back old prepared transactions.")));
}
}
--
2.53.0
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate
In-Reply-To: <CAHGQGwFKngUKWOM=N3sAOGCL95TjQf2jM8E3put7=cmGKHHg0A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox