In this new version, I added a couple of fields to VacuumStmt node. How
strongly do we feel this would cause an ABI break? Would we be more
comfortable if I put them at the end of the struct for 9.3 instead?
Do we expect third-party code to be calling vacuum()?
Also, AutoVacOpts (used as part of reloptions) gained three extra
fields. Since this is in the middle of StdRdOptions, it'd be somewhat
more involve to put these at the end of that struct. This might be a
problem if somebody has a module calling RelationIsSecurityView(). If
anyone thinks we should be concerned about such an ABI change, please
shout quickly.
Here is patch v3, which should be final or close to. Changes from
previous:
Robert Haas wrote:
> Using Multixact capitalized just so seems odd to me. Probably should
> be lower case (multiple places).
Changed it to be all lower case. Originally the X was also upper case,
which looked even odder.
> This part needs some copy-editing:
>
> + <para>
> + Vacuum also allows removal of old files from the
> + <filename>pg_multixact/members</> and
> <filename>pg_multixact/offsets</>
> + subdirectories, which is why the default is a relatively low
> + 50 million transactions.
>
> Vacuuming multixacts also allows...? And: 50 million multixacts, not
> transactions.
I reworded this rather completely.
I was missing a change to SetMultiXactIdLimit to use the multixact value
instead of the one for Xids, and passing the values computed by
autovacuum to vacuum().
Per discussion, new default values are 150 million for
vacuum_multixact_freeze_table_age (same as the one for Xids), and 5
million for vacuum_multixact_freeze_min_age. I decided to raise
autovacuum_multixact_freeze_max_age to 400 million, i.e. double the one
for Xids; so there should be no more emergency vacuuming than before
unless multixact consumption is more than double that for Xids. (Now
that I re-read this, the same rationale would have me setting the
default for vacuum_multixact_freeze_table_age to 300 million. Any votes
on that?).
I adjusted the default values everywhere (docs and sample config), and
fixed one or two typos in the docco for Xid vacuuming that I happened to
notice, as well. postgresql.conf.sample contained a couple of
space-before-tab which I removed.
<!-- I thought about using a struct to pass all four values around in
multiple routines rather than 4 ints (vacuum_set_xid_limits,
cluster_rel, rebuild_relation, copy_heap_data). Decided not to for the
time being. Perhaps a patch for HEAD only. -->
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***************
*** 4730,4735 **** COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
--- 4730,4762 ----
</listitem>
</varlistentry>
+ <varlistentry id="guc-autovacuum-multixact-freeze-max-age" xreflabel="autovacuum_multixact_freeze_max_age">
+ <term><varname>autovacuum_multixact_freeze_max_age</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>autovacuum_multixact_freeze_max_age</varname> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies the maximum age (in multixacts) that a table's
+ <structname>pg_class</>.<structfield>relminmxid</> field can
+ attain before a <command>VACUUM</> operation is forced to
+ prevent multixact ID wraparound within the table.
+ Note that the system will launch autovacuum processes to
+ prevent wraparound even when autovacuum is otherwise disabled.
+ </para>
+
+ <para>
+ Vacuuming multixacts also allows removal of old files from the
+ <filename>pg_multixact/members</> and <filename>pg_multixact/offsets</>
+ subdirectories, which is why the default is a relatively low
+ 400 million multixacts.
+ This parameter can only be set at server start, but the setting
+ can be reduced for individual tables by changing storage parameters.
+ For more information see <xref linkend="vacuum-for-multixact-wraparound">.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
<term><varname>autovacuum_vacuum_cost_delay</varname> (<type>integer</type>)</term>
<indexterm>
***************
*** 5138,5144 **** COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
<structname>pg_class</>.<structfield>relfrozenxid</> field has reached
the age specified by this setting. The default is 150 million
transactions. Although users can set this value anywhere from zero to
! one billion, <command>VACUUM</> will silently limit the effective value
to 95% of <xref linkend="guc-autovacuum-freeze-max-age">, so that a
periodical manual <command>VACUUM</> has a chance to run before an
anti-wraparound autovacuum is launched for the table. For more
--- 5165,5171 ----
<structname>pg_class</>.<structfield>relfrozenxid</> field has reached
the age specified by this setting. The default is 150 million
transactions. Although users can set this value anywhere from zero to
! two billions, <command>VACUUM</> will silently limit the effective value
to 95% of <xref linkend="guc-autovacuum-freeze-max-age">, so that a
periodical manual <command>VACUUM</> has a chance to run before an
anti-wraparound autovacuum is launched for the table. For more
***************
*** 5169,5174 **** COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
--- 5196,5242 ----
</listitem>
</varlistentry>
+ <varlistentry id="guc-vacuum-multixact-freeze-table-age" xreflabel="vacuum_multixact_freeze_table_age">
+ <term><varname>vacuum_multixact_freeze_table_age</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>vacuum_multixact_freeze_table_age</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ <command>VACUUM</> performs a whole-table scan if the table's
+ <structname>pg_class</>.<structfield>relminmxid</> field has reached
+ the age specified by this setting. The default is 150 million multixacts.
+ Although users can set this value anywhere from zero to two billions,
+ <command>VACUUM</> will silently limit the effective value to 95% of
+ <xref linkend="guc-autovacuum-multixact-freeze-max-age">, so that a
+ periodical manual <command>VACUUM</> has a chance to run before an
+ anti-wraparound is launched for the table.
+ For more information see <xref linkend="vacuum-for-multixact-wraparound">.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-multixact-freeze-min-age" xreflabel="vacuum_multixact_freeze_min_age">
+ <term><varname>vacuum_multixact_freeze_min_age</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>vacuum_multixact_freeze_min_age</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies the cutoff age (in multixacts) that <command>VACUUM</>
+ should use to decide whether to replace multixact IDs with a newer
+ transaction ID or multixact ID while scanning a table. The default
+ is 5 million multixacts.
+ Although users can set this value anywhere from zero to one billion,
+ <command>VACUUM</> will silently limit the effective value to half
+ the value of <xref linkend="guc-autovacuum-multixact-freeze-max-age">,
+ so that there is not an unreasonably short time between forced
+ autovacuums.
+ For more information see <xref linkend="vacuum-for-multixact-wraparound">.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-bytea-output" xreflabel="bytea_output">
<term><varname>bytea_output</varname> (<type>enum</type>)</term>
<indexterm>
*** a/doc/src/sgml/maintenance.sgml
--- b/doc/src/sgml/maintenance.sgml
***************
*** 108,114 ****
<listitem>
<simpara>To protect against loss of very old data due to
! <firstterm>transaction ID wraparound</>.</simpara>
</listitem>
</orderedlist>
--- 108,115 ----
<listitem>
<simpara>To protect against loss of very old data due to
! <firstterm>transaction ID wraparound</> or
! <firstterm>multixact ID wraparound</>.</simpara>
</listitem>
</orderedlist>
***************
*** 379,384 ****
--- 380,390 ----
<secondary>wraparound</secondary>
</indexterm>
+ <indexterm>
+ <primary>wraparound</primary>
+ <secondary>of transaction IDs</secondary>
+ </indexterm>
+
<para>
<productname>PostgreSQL</productname>'s MVCC transaction semantics
depend on being able to compare transaction ID (<acronym>XID</>)
***************
*** 599,604 **** HINT: Stop the postmaster and use a standalone backend to VACUUM in "mydb".
--- 605,658 ----
page for details about using a single-user backend.
</para>
+ <sect3 id="vacuum-for-multixact-wraparound">
+ <title>Multixacts and Wraparound</title>
+
+ <indexterm>
+ <primary>MultiXactId</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>wraparound</primary>
+ <secondary>of multixact IDs</secondary>
+ </indexterm>
+
+ <para>
+ <firstterm>Multixacts</> are used to implement row locking by
+ multiple transactions: since there is limited space in the tuple
+ header to store lock information, that information is stored as a
+ multixact separately in the <filename>pg_multixact</> subdirectory,
+ and only its ID is in the <structfield>xmax</> field
+ in the tuple header.
+ Similar to transaction IDs, multixact IDs are implemented as a
+ 32-bit counter and corresponding storage, all of which requires
+ careful aging management, storage cleanup, and wraparound handling.
+ </para>
+
+ <para>
+ During a <command>VACUUM</> table scan, either partial or of the whole
+ table, any multixact ID older than
+ <xref linkend="guc-vacuum-multixact-freeze-min-age">
+ is replaced by a different value, which can be the zero value, a single
+ transaction ID, or a newer multixact ID. For each table,
+ <structname>pg_class</>.<structfield>relminmxid</> stores the oldest
+ possible value still stored in any tuple of that table. Every time this
+ value is older than
+ <xref linkend="guc-vacuum-multixact-freeze-table-age">, a whole-table
+ scan is forced. Whole-table <command>VACUUM</> scans, regardless of
+ what causes them, enable advancing the value for that table.
+ Eventually, as all tables in all databases are scanned and their
+ oldest multixact values are advanced, on-disk storage for older
+ multixacts can be removed.
+ </para>
+
+ <para>
+ As a safety device, a whole-table vacuum scan will occur for any table
+ whose multixact-age is greater than
+ <xref linkend="guc-autovacuum-multixact-freeze-max-age">.
+ This will occur even if autovacuum is nominally disabled.
+ </para>
+ </sect3>
</sect2>
<sect2 id="autovacuum">
*** a/doc/src/sgml/ref/create_table.sgml
--- b/doc/src/sgml/ref/create_table.sgml
***************
*** 981,987 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
<para>
Custom <xref linkend="guc-vacuum-freeze-min-age"> parameter. Note that
autovacuum will ignore attempts to set a per-table
! <literal>autovacuum_freeze_min_age</> larger than the half system-wide
<xref linkend="guc-autovacuum-freeze-max-age"> setting.
</para>
</listitem>
--- 981,987 ----
<para>
Custom <xref linkend="guc-vacuum-freeze-min-age"> parameter. Note that
autovacuum will ignore attempts to set a per-table
! <literal>autovacuum_freeze_min_age</> larger than half the system-wide
<xref linkend="guc-autovacuum-freeze-max-age"> setting.
</para>
</listitem>
***************
*** 1010,1015 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
--- 1010,1052 ----
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>autovacuum_multixact_freeze_min_age</literal>, <literal>toast.autovacuum_multixact_freeze_min_age</literal> (<type>integer</type>)</term>
+ <listitem>
+ <para>
+ Custom <xref linkend="guc-vacuum-multixact-freeze-min-age"> parameter.
+ Note that autovacuum will ignore attempts to set a per-table
+ <literal>autovacuum_multixact_freeze_min_age</> larger than half the
+ system-wide <xref linkend="guc-autovacuum-multixact-freeze-max-age">
+ setting.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>autovacuum_multixact_freeze_max_age</literal>, <literal>toast.autovacuum_multixact_freeze_max_age</literal> (<type>integer</type>)</term>
+ <listitem>
+ <para>
+ Custom <xref linkend="guc-autovacuum-multixact-freeze-max-age"> parameter. Note
+ that autovacuum will ignore attempts to set a per-table
+ <literal>autovacuum_multixact_freeze_max_age</> larger than the
+ system-wide setting (it can only be set smaller). Note that while you
+ can set <literal>autovacuum_multixact_freeze_max_age</> very small,
+ or even zero, this is usually unwise since it will force frequent
+ vacuuming.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>autovacuum_multixact_freeze_table_age</literal>, <literal>toast.autovacuum_multixact_freeze_table_age</literal> (<type>integer</type>)</term>
+ <listitem>
+ <para>
+ Custom <xref linkend="guc-vacuum-multixact-freeze-table-age"> parameter.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect2>
*** a/src/backend/access/common/reloptions.c
--- b/src/backend/access/common/reloptions.c
***************
*** 164,169 **** static relopt_int intRelOpts[] =
--- 164,177 ----
},
{
{
+ "autovacuum_multixact_freeze_min_age",
+ "Minimum MultiXact age at which VACUUM should freeze a row MultiXact's, for autovacuum",
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ },
+ -1, 0, 1000000000
+ },
+ {
+ {
"autovacuum_freeze_max_age",
"Age at which to autovacuum a table to prevent transaction ID wraparound",
RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
***************
*** 172,182 **** static relopt_int intRelOpts[] =
--- 180,205 ----
},
{
{
+ "autovacuum_multixact_freeze_max_age",
+ "MultiXact age at which to autovacuum a table to prevent MultiXact wraparound",
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ },
+ -1, 100000000, 2000000000
+ },
+ {
+ {
"autovacuum_freeze_table_age",
"Age at which VACUUM should perform a full table sweep to replace old Xid values with FrozenXID",
RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
}, -1, 0, 2000000000
},
+ {
+ {
+ "autovacuum_multixact_freeze_table_age",
+ "Age of MultiXact at which VACUUM should perform a full table sweep to replace old MultiXact values with newer ones",
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ }, -1, 0, 2000000000
+ },
/* list terminator */
{{NULL}}
};
*** a/src/backend/access/transam/multixact.c
--- b/src/backend/access/transam/multixact.c
***************
*** 2055,2065 **** SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
Assert(MultiXactIdIsValid(oldest_datminmxid));
/*
! * The place where we actually get into deep trouble is halfway around
! * from the oldest potentially-existing XID/multi. (This calculation is
! * probably off by one or two counts for Xids, because the special XIDs
! * reduce the size of the loop a little bit. But we throw in plenty of
! * slop below, so it doesn't matter.)
*/
multiWrapLimit = oldest_datminmxid + (MaxMultiXactId >> 1);
if (multiWrapLimit < FirstMultiXactId)
--- 2055,2067 ----
Assert(MultiXactIdIsValid(oldest_datminmxid));
/*
! * Since multixacts wrap differently from transaction IDs, this logic is
! * not entirely correct: in some scenarios we could go for longer than 2
! * billion multixacts without seeing any data loss, and in some others we
! * could get in trouble before that if the new pg_multixact/members data
! * stomps on the previous cycle's data. For lack of a better mechanism we
! * use the same logic as for transaction IDs, that is, start taking action
! * halfway around the oldest potentially-existing multixact.
*/
multiWrapLimit = oldest_datminmxid + (MaxMultiXactId >> 1);
if (multiWrapLimit < FirstMultiXactId)
***************
*** 2093,2104 **** SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
/*
* We'll start trying to force autovacuums when oldest_datminmxid gets to
! * be more than autovacuum_freeze_max_age mxids old.
*
! * It's a bit ugly to just reuse limits for xids that way, but it doesn't
! * seem worth adding separate GUCs for that purpose.
*/
! multiVacLimit = oldest_datminmxid + autovacuum_freeze_max_age;
if (multiVacLimit < FirstMultiXactId)
multiVacLimit += FirstMultiXactId;
--- 2095,2107 ----
/*
* We'll start trying to force autovacuums when oldest_datminmxid gets to
! * be more than autovacuum_multixact_freeze_max_age mxids old.
*
! * Note: autovacuum_multixact_freeze_max_age is a PGC_POSTMASTER parameter
! * so that we don't have to worry about dealing with on-the-fly changes
! * in its value. See SetTransactionIdLimit.
*/
! multiVacLimit = oldest_datminmxid + autovacuum_multixact_freeze_max_age;
if (multiVacLimit < FirstMultiXactId)
multiVacLimit += FirstMultiXactId;
*** a/src/backend/access/transam/varsup.c
--- b/src/backend/access/transam/varsup.c
***************
*** 313,319 **** SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
* value. It doesn't look practical to update shared state from a GUC
* assign hook (too many processes would try to execute the hook,
* resulting in race conditions as well as crashes of those not connected
! * to shared memory). Perhaps this can be improved someday.
*/
xidVacLimit = oldest_datfrozenxid + autovacuum_freeze_max_age;
if (xidVacLimit < FirstNormalTransactionId)
--- 313,320 ----
* value. It doesn't look practical to update shared state from a GUC
* assign hook (too many processes would try to execute the hook,
* resulting in race conditions as well as crashes of those not connected
! * to shared memory). Perhaps this can be improved someday. See also
! * SetMultiXactIdLimit.
*/
xidVacLimit = oldest_datfrozenxid + autovacuum_freeze_max_age;
if (xidVacLimit < FirstNormalTransactionId)
*** a/src/backend/commands/cluster.c
--- b/src/backend/commands/cluster.c
***************
*** 64,72 **** typedef struct
static void rebuild_relation(Relation OldHeap, Oid indexOid,
! int freeze_min_age, int freeze_table_age, bool verbose);
static void copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex,
! int freeze_min_age, int freeze_table_age, bool verbose,
bool *pSwapToastByContent, TransactionId *pFreezeXid,
MultiXactId *pCutoffMulti);
static List *get_tables_to_cluster(MemoryContext cluster_context);
--- 64,76 ----
static void rebuild_relation(Relation OldHeap, Oid indexOid,
! int freeze_min_age, int freeze_table_age,
! int multixact_freeze_min_age, int multixact_freeze_table_age,
! bool verbose);
static void copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex,
! int freeze_min_age, int freeze_table_age,
! int multixact_freeze_min_age, int multixact_freeze_table_age,
! bool verbose,
bool *pSwapToastByContent, TransactionId *pFreezeXid,
MultiXactId *pCutoffMulti);
static List *get_tables_to_cluster(MemoryContext cluster_context);
***************
*** 179,185 **** cluster(ClusterStmt *stmt, bool isTopLevel)
* Do the job. We use a -1 freeze_min_age to avoid having CLUSTER
* freeze tuples earlier than a plain VACUUM would.
*/
! cluster_rel(tableOid, indexOid, false, stmt->verbose, -1, -1);
}
else
{
--- 183,189 ----
* Do the job. We use a -1 freeze_min_age to avoid having CLUSTER
* freeze tuples earlier than a plain VACUUM would.
*/
! cluster_rel(tableOid, indexOid, false, stmt->verbose, -1, -1, -1, -1);
}
else
{
***************
*** 230,236 **** cluster(ClusterStmt *stmt, bool isTopLevel)
PushActiveSnapshot(GetTransactionSnapshot());
/* Do the job. As above, use a -1 freeze_min_age. */
cluster_rel(rvtc->tableOid, rvtc->indexOid, true, stmt->verbose,
! -1, -1);
PopActiveSnapshot();
CommitTransactionCommand();
}
--- 234,240 ----
PushActiveSnapshot(GetTransactionSnapshot());
/* Do the job. As above, use a -1 freeze_min_age. */
cluster_rel(rvtc->tableOid, rvtc->indexOid, true, stmt->verbose,
! -1, -1, -1, -1);
PopActiveSnapshot();
CommitTransactionCommand();
}
***************
*** 262,268 **** cluster(ClusterStmt *stmt, bool isTopLevel)
*/
void
cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose,
! int freeze_min_age, int freeze_table_age)
{
Relation OldHeap;
--- 266,273 ----
*/
void
cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose,
! int freeze_min_age, int freeze_table_age,
! int multixact_freeze_min_age, int multixact_freeze_table_age)
{
Relation OldHeap;
***************
*** 407,412 **** cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose,
--- 412,418 ----
/* rebuild_relation does all the dirty work */
rebuild_relation(OldHeap, indexOid, freeze_min_age, freeze_table_age,
+ multixact_freeze_min_age, multixact_freeze_table_age,
verbose);
/* NB: rebuild_relation does heap_close() on OldHeap */
***************
*** 566,572 **** mark_index_clustered(Relation rel, Oid indexOid, bool is_internal)
*/
static void
rebuild_relation(Relation OldHeap, Oid indexOid,
! int freeze_min_age, int freeze_table_age, bool verbose)
{
Oid tableOid = RelationGetRelid(OldHeap);
Oid tableSpace = OldHeap->rd_rel->reltablespace;
--- 572,580 ----
*/
static void
rebuild_relation(Relation OldHeap, Oid indexOid,
! int freeze_min_age, int freeze_table_age,
! int multixact_freeze_min_age, int multixact_freeze_table_age,
! bool verbose)
{
Oid tableOid = RelationGetRelid(OldHeap);
Oid tableSpace = OldHeap->rd_rel->reltablespace;
***************
*** 591,597 **** rebuild_relation(Relation OldHeap, Oid indexOid,
/* Copy the heap data into the new table in the desired order */
copy_heap_data(OIDNewHeap, tableOid, indexOid,
! freeze_min_age, freeze_table_age, verbose,
&swap_toast_by_content, &frozenXid, &cutoffMulti);
/*
--- 599,607 ----
/* Copy the heap data into the new table in the desired order */
copy_heap_data(OIDNewHeap, tableOid, indexOid,
! freeze_min_age, freeze_table_age,
! multixact_freeze_min_age, multixact_freeze_table_age,
! verbose,
&swap_toast_by_content, &frozenXid, &cutoffMulti);
/*
***************
*** 733,739 **** make_new_heap(Oid OIDOldHeap, Oid NewTableSpace)
*/
static void
copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex,
! int freeze_min_age, int freeze_table_age, bool verbose,
bool *pSwapToastByContent, TransactionId *pFreezeXid,
MultiXactId *pCutoffMulti)
{
--- 743,751 ----
*/
static void
copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex,
! int freeze_min_age, int freeze_table_age,
! int multixact_freeze_min_age, int multixact_freeze_table_age,
! bool verbose,
bool *pSwapToastByContent, TransactionId *pFreezeXid,
MultiXactId *pCutoffMulti)
{
***************
*** 849,854 **** copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex,
--- 861,867 ----
* compute xids used to freeze and weed out dead tuples.
*/
vacuum_set_xid_limits(freeze_min_age, freeze_table_age,
+ multixact_freeze_min_age, multixact_freeze_table_age,
OldHeap->rd_rel->relisshared,
&OldestXmin, &FreezeXid, NULL, &MultiXactCutoff,
NULL);
*** a/src/backend/commands/vacuum.c
--- b/src/backend/commands/vacuum.c
***************
*** 55,60 ****
--- 55,62 ----
*/
int vacuum_freeze_min_age;
int vacuum_freeze_table_age;
+ int vacuum_multixact_freeze_min_age;
+ int vacuum_multixact_freeze_table_age;
/* A few variables that don't seem worth passing around as parameters */
***************
*** 398,403 **** get_rel_oids(Oid relid, const RangeVar *vacrel)
--- 400,407 ----
void
vacuum_set_xid_limits(int freeze_min_age,
int freeze_table_age,
+ int multixact_freeze_min_age,
+ int multixact_freeze_table_age,
bool sharedRel,
TransactionId *oldestXmin,
TransactionId *freezeLimit,
***************
*** 406,414 **** vacuum_set_xid_limits(int freeze_min_age,
--- 410,420 ----
MultiXactId *mxactFullScanLimit)
{
int freezemin;
+ int mxid_freezemin;
TransactionId limit;
TransactionId safeLimit;
MultiXactId mxactLimit;
+ MultiXactId safeMxactLimit;
/*
* We can always ignore processes running lazy vacuum. This is because we
***************
*** 462,474 **** vacuum_set_xid_limits(int freeze_min_age,
*freezeLimit = limit;
/*
! * simplistic MultiXactId removal limit: use the same policy as for
! * freezing Xids (except we use the oldest known mxact instead of the
! * current next value).
*/
! mxactLimit = GetOldestMultiXactId() - freezemin;
if (mxactLimit < FirstMultiXactId)
mxactLimit = FirstMultiXactId;
*multiXactCutoff = mxactLimit;
if (xidFullScanLimit != NULL)
--- 468,503 ----
*freezeLimit = limit;
/*
! * Determine the minimum multixact freeze age to use: as specified by
! * caller, or vacuum_multixact_freeze_min_age, but in any case not more
! * than half autovacuum_multixact_freeze_max_age, so that autovacuums to
! * prevent MultiXact wraparound won't occur too frequently.
*/
! mxid_freezemin = multixact_freeze_min_age;
! if (mxid_freezemin < 0)
! mxid_freezemin = vacuum_multixact_freeze_min_age;
! mxid_freezemin = Min(mxid_freezemin,
! autovacuum_multixact_freeze_max_age / 2);
! Assert(mxid_freezemin >= 0);
!
! /* compute the cutoff multi, being careful to generate a valid value */
! mxactLimit = GetOldestMultiXactId() - mxid_freezemin;
if (mxactLimit < FirstMultiXactId)
mxactLimit = FirstMultiXactId;
+
+ safeMxactLimit =
+ ReadNextMultiXactId() - autovacuum_multixact_freeze_max_age;
+ if (safeMxactLimit < FirstMultiXactId)
+ safeMxactLimit = FirstMultiXactId;
+
+ if (MultiXactIdPrecedes(mxactLimit, safeMxactLimit))
+ {
+ ereport(WARNING,
+ (errmsg("oldest MultiXact is far in the past"),
+ errhint("Close open transactions with MultiXacts soon to avoid wraparound problems.")));
+ mxactLimit = safeMxactLimit;
+ }
+
*multiXactCutoff = mxactLimit;
if (xidFullScanLimit != NULL)
***************
*** 503,509 **** vacuum_set_xid_limits(int freeze_min_age,
/*
* Compute MultiXactId limit to cause a full-table vacuum, being
* careful not to generate an invalid multi. We just copy the logic
! * (and limits) from plain XIDs here.
*/
mxactLimit = ReadNextMultiXactId() - freezetable;
if (mxactLimit < FirstMultiXactId)
--- 532,548 ----
/*
* Compute MultiXactId limit to cause a full-table vacuum, being
* careful not to generate an invalid multi. We just copy the logic
! * from plain XIDs here.
! */
! freezetable = multixact_freeze_table_age;
! if (freezetable < 0)
! freezetable = vacuum_multixact_freeze_table_age;
! freezetable = Min(freezetable,
! autovacuum_multixact_freeze_max_age * 0.95);
! Assert(freezetable >= 0);
!
! /* Compute MultiXact limit causing a full-table vacuum, being careful
! * to generate a valid MultiXact value.
*/
mxactLimit = ReadNextMultiXactId() - freezetable;
if (mxactLimit < FirstMultiXactId)
***************
*** 511,516 **** vacuum_set_xid_limits(int freeze_min_age,
--- 550,559 ----
*mxactFullScanLimit = mxactLimit;
}
+ else
+ {
+ Assert(mxactFullScanLimit == NULL);
+ }
}
/*
***************
*** 1150,1156 **** vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound)
/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
cluster_rel(relid, InvalidOid, false,
(vacstmt->options & VACOPT_VERBOSE) != 0,
! vacstmt->freeze_min_age, vacstmt->freeze_table_age);
}
else
lazy_vacuum_rel(onerel, vacstmt, vac_strategy);
--- 1193,1201 ----
/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
cluster_rel(relid, InvalidOid, false,
(vacstmt->options & VACOPT_VERBOSE) != 0,
! vacstmt->freeze_min_age, vacstmt->freeze_table_age,
! vacstmt->multixact_freeze_min_age,
! vacstmt->multixact_freeze_table_age);
}
else
lazy_vacuum_rel(onerel, vacstmt, vac_strategy);
*** a/src/backend/commands/vacuumlazy.c
--- b/src/backend/commands/vacuumlazy.c
***************
*** 203,208 **** lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
--- 203,210 ----
vac_strategy = bstrategy;
vacuum_set_xid_limits(vacstmt->freeze_min_age, vacstmt->freeze_table_age,
+ vacstmt->multixact_freeze_min_age,
+ vacstmt->multixact_freeze_table_age,
onerel->rd_rel->relisshared,
&OldestXmin, &FreezeLimit, &xidFullScanLimit,
&MultiXactCutoff, &mxactFullScanLimit);
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 3206,3211 **** _copyVacuumStmt(const VacuumStmt *from)
--- 3206,3213 ----
COPY_SCALAR_FIELD(options);
COPY_SCALAR_FIELD(freeze_min_age);
COPY_SCALAR_FIELD(freeze_table_age);
+ COPY_SCALAR_FIELD(multixact_freeze_min_age);
+ COPY_SCALAR_FIELD(multixact_freeze_table_age);
COPY_NODE_FIELD(relation);
COPY_NODE_FIELD(va_cols);
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 1496,1501 **** _equalVacuumStmt(const VacuumStmt *a, const VacuumStmt *b)
--- 1496,1503 ----
COMPARE_SCALAR_FIELD(options);
COMPARE_SCALAR_FIELD(freeze_min_age);
COMPARE_SCALAR_FIELD(freeze_table_age);
+ COMPARE_SCALAR_FIELD(multixact_freeze_min_age);
+ COMPARE_SCALAR_FIELD(multixact_freeze_table_age);
COMPARE_NODE_FIELD(relation);
COMPARE_NODE_FIELD(va_cols);
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 8474,8479 **** VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
--- 8474,8481 ----
n->options |= VACOPT_VERBOSE;
n->freeze_min_age = $3 ? 0 : -1;
n->freeze_table_age = $3 ? 0 : -1;
+ n->multixact_freeze_min_age = $3 ? 0 : -1;
+ n->multixact_freeze_table_age = $3 ? 0 : -1;
n->relation = NULL;
n->va_cols = NIL;
$$ = (Node *)n;
***************
*** 8488,8493 **** VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
--- 8490,8497 ----
n->options |= VACOPT_VERBOSE;
n->freeze_min_age = $3 ? 0 : -1;
n->freeze_table_age = $3 ? 0 : -1;
+ n->multixact_freeze_min_age = $3 ? 0 : -1;
+ n->multixact_freeze_table_age = $3 ? 0 : -1;
n->relation = $5;
n->va_cols = NIL;
$$ = (Node *)n;
***************
*** 8502,8507 **** VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
--- 8506,8513 ----
n->options |= VACOPT_VERBOSE;
n->freeze_min_age = $3 ? 0 : -1;
n->freeze_table_age = $3 ? 0 : -1;
+ n->multixact_freeze_min_age = $3 ? 0 : -1;
+ n->multixact_freeze_table_age = $3 ? 0 : -1;
$$ = (Node *)n;
}
| VACUUM '(' vacuum_option_list ')'
***************
*** 8509,8517 **** VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
--- 8515,8531 ----
VacuumStmt *n = makeNode(VacuumStmt);
n->options = VACOPT_VACUUM | $3;
if (n->options & VACOPT_FREEZE)
+ {
n->freeze_min_age = n->freeze_table_age = 0;
+ n->multixact_freeze_min_age = 0;
+ n->multixact_freeze_table_age = 0;
+ }
else
+ {
n->freeze_min_age = n->freeze_table_age = -1;
+ n->multixact_freeze_min_age = -1;
+ n->multixact_freeze_table_age = -1;
+ }
n->relation = NULL;
n->va_cols = NIL;
$$ = (Node *) n;
***************
*** 8521,8529 **** VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
--- 8535,8551 ----
VacuumStmt *n = makeNode(VacuumStmt);
n->options = VACOPT_VACUUM | $3;
if (n->options & VACOPT_FREEZE)
+ {
n->freeze_min_age = n->freeze_table_age = 0;
+ n->multixact_freeze_min_age = 0;
+ n->multixact_freeze_table_age = 0;
+ }
else
+ {
n->freeze_min_age = n->freeze_table_age = -1;
+ n->multixact_freeze_min_age = -1;
+ n->multixact_freeze_table_age = -1;
+ }
n->relation = $5;
n->va_cols = $6;
if (n->va_cols != NIL) /* implies analyze */
***************
*** 8553,8558 **** AnalyzeStmt:
--- 8575,8582 ----
n->options |= VACOPT_VERBOSE;
n->freeze_min_age = -1;
n->freeze_table_age = -1;
+ n->multixact_freeze_min_age = -1;
+ n->multixact_freeze_table_age = -1;
n->relation = NULL;
n->va_cols = NIL;
$$ = (Node *)n;
***************
*** 8565,8570 **** AnalyzeStmt:
--- 8589,8596 ----
n->options |= VACOPT_VERBOSE;
n->freeze_min_age = -1;
n->freeze_table_age = -1;
+ n->multixact_freeze_min_age = -1;
+ n->multixact_freeze_table_age = -1;
n->relation = $3;
n->va_cols = $4;
$$ = (Node *)n;
*** a/src/backend/postmaster/autovacuum.c
--- b/src/backend/postmaster/autovacuum.c
***************
*** 116,121 **** double autovacuum_vac_scale;
--- 116,122 ----
int autovacuum_anl_thresh;
double autovacuum_anl_scale;
int autovacuum_freeze_max_age;
+ int autovacuum_multixact_freeze_max_age;
int autovacuum_vac_cost_delay;
int autovacuum_vac_cost_limit;
***************
*** 144,149 **** static MultiXactId recentMulti;
--- 145,152 ----
/* Default freeze ages to use for autovacuum (varies by database) */
static int default_freeze_min_age;
static int default_freeze_table_age;
+ static int default_multixact_freeze_min_age;
+ static int default_multixact_freeze_table_age;
/* Memory context for long-lived data */
static MemoryContext AutovacMemCxt;
***************
*** 185,190 **** typedef struct autovac_table
--- 188,195 ----
bool at_doanalyze;
int at_freeze_min_age;
int at_freeze_table_age;
+ int at_multixact_freeze_min_age;
+ int at_multixact_freeze_table_age;
int at_vacuum_cost_delay;
int at_vacuum_cost_limit;
bool at_wraparound;
***************
*** 1129,1135 **** do_start_worker(void)
/* Also determine the oldest datminmxid we will consider. */
recentMulti = ReadNextMultiXactId();
! multiForceLimit = recentMulti - autovacuum_freeze_max_age;
if (multiForceLimit < FirstMultiXactId)
multiForceLimit -= FirstMultiXactId;
--- 1134,1140 ----
/* Also determine the oldest datminmxid we will consider. */
recentMulti = ReadNextMultiXactId();
! multiForceLimit = recentMulti - autovacuum_multixact_freeze_max_age;
if (multiForceLimit < FirstMultiXactId)
multiForceLimit -= FirstMultiXactId;
***************
*** 1955,1965 **** do_autovacuum(void)
--- 1960,1974 ----
{
default_freeze_min_age = 0;
default_freeze_table_age = 0;
+ default_multixact_freeze_min_age = 0;
+ default_multixact_freeze_table_age = 0;
}
else
{
default_freeze_min_age = vacuum_freeze_min_age;
default_freeze_table_age = vacuum_freeze_table_age;
+ default_multixact_freeze_min_age = vacuum_multixact_freeze_min_age;
+ default_multixact_freeze_table_age = vacuum_multixact_freeze_table_age;
}
ReleaseSysCache(tuple);
***************
*** 2510,2515 **** table_recheck_autovac(Oid relid, HTAB *table_toast_map,
--- 2519,2526 ----
{
int freeze_min_age;
int freeze_table_age;
+ int multixact_freeze_min_age;
+ int multixact_freeze_table_age;
int vac_cost_limit;
int vac_cost_delay;
***************
*** 2543,2554 **** table_recheck_autovac(Oid relid, HTAB *table_toast_map,
--- 2554,2577 ----
? avopts->freeze_table_age
: default_freeze_table_age;
+ multixact_freeze_min_age = (avopts &&
+ avopts->multixact_freeze_min_age >= 0)
+ ? avopts->multixact_freeze_min_age
+ : default_multixact_freeze_min_age;
+
+ multixact_freeze_table_age = (avopts &&
+ avopts->multixact_freeze_table_age >= 0)
+ ? avopts->multixact_freeze_table_age
+ : default_multixact_freeze_table_age;
+
tab = palloc(sizeof(autovac_table));
tab->at_relid = relid;
tab->at_dovacuum = dovacuum;
tab->at_doanalyze = doanalyze;
tab->at_freeze_min_age = freeze_min_age;
tab->at_freeze_table_age = freeze_table_age;
+ tab->at_multixact_freeze_min_age = multixact_freeze_min_age;
+ tab->at_multixact_freeze_table_age = multixact_freeze_table_age;
tab->at_vacuum_cost_limit = vac_cost_limit;
tab->at_vacuum_cost_delay = vac_cost_delay;
tab->at_wraparound = wraparound;
***************
*** 2754,2759 **** autovacuum_do_vac_analyze(autovac_table *tab,
--- 2777,2784 ----
vacstmt.options |= VACOPT_ANALYZE;
vacstmt.freeze_min_age = tab->at_freeze_min_age;
vacstmt.freeze_table_age = tab->at_freeze_table_age;
+ vacstmt.multixact_freeze_min_age = tab->at_multixact_freeze_min_age;
+ vacstmt.multixact_freeze_table_age = tab->at_multixact_freeze_table_age;
/* we pass the OID, but might need this anyway for an error message */
vacstmt.relation = &rangevar;
vacstmt.va_cols = NIL;
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
***************
*** 1907,1912 **** static struct config_int ConfigureNamesInt[] =
--- 1907,1932 ----
},
{
+ {"vacuum_multixact_freeze_min_age", PGC_USERSET, CLIENT_CONN_STATEMENT,
+ gettext_noop("Minimum age at which VACUUM should freeze a MultiXactId in a table row."),
+ NULL
+ },
+ &vacuum_multixact_freeze_min_age,
+ 5000000, 0, 1000000000,
+ NULL, NULL, NULL
+ },
+
+ {
+ {"vacuum_multixact_freeze_table_age", PGC_USERSET, CLIENT_CONN_STATEMENT,
+ gettext_noop("Multixact age at which VACUUM should scan whole table to freeze tuples."),
+ NULL
+ },
+ &vacuum_multixact_freeze_table_age,
+ 150000000, 0, 2000000000,
+ NULL, NULL, NULL
+ },
+
+ {
{"vacuum_defer_cleanup_age", PGC_SIGHUP, REPLICATION_MASTER,
gettext_noop("Number of transactions by which VACUUM and HOT cleanup should be deferred, if any."),
NULL
***************
*** 2296,2301 **** static struct config_int ConfigureNamesInt[] =
--- 2316,2331 ----
NULL, NULL, NULL
},
{
+ /* see varsup.c for why this is PGC_POSTMASTER not PGC_SIGHUP */
+ {"autovacuum_multixact_freeze_max_age", PGC_POSTMASTER, AUTOVACUUM,
+ gettext_noop("Multixact age at which to autovacuum a table to prevent multixact wraparound."),
+ NULL
+ },
+ &autovacuum_multixact_freeze_max_age,
+ 400000000, 10000000, 2000000000,
+ NULL, NULL, NULL
+ },
+ {
/* see max_connections */
{"autovacuum_max_workers", PGC_POSTMASTER, AUTOVACUUM,
gettext_noop("Sets the maximum number of simultaneously running autovacuum worker processes."),
*** a/src/backend/utils/misc/postgresql.conf.sample
--- b/src/backend/utils/misc/postgresql.conf.sample
***************
*** 362,373 ****
# panic
#log_min_error_statement = error # values in order of decreasing detail:
! # debug5
# debug4
# debug3
# debug2
# debug1
! # info
# notice
# warning
# error
--- 362,373 ----
# panic
#log_min_error_statement = error # values in order of decreasing detail:
! # debug5
# debug4
# debug3
# debug2
# debug1
! # info
# notice
# warning
# error
***************
*** 431,437 ****
#track_counts = on
#track_io_timing = off
#track_functions = none # none, pl, all
! #track_activity_query_size = 1024 # (change requires restart)
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'
--- 431,437 ----
#track_counts = on
#track_io_timing = off
#track_functions = none # none, pl, all
! #track_activity_query_size = 1024 # (change requires restart)
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'
***************
*** 465,470 ****
--- 465,473 ----
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
+ #autovacuum_multixact_freeze_max_age = 400000000 # maximum Multixact age
+ # before forced vacuum
+ # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
***************
*** 492,497 ****
--- 495,502 ----
#lock_timeout = 0 # in milliseconds, 0 is disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
+ #vacuum_multixact_freeze_min_age = 5000000
+ #vacuum_multixact_freeze_table_age = 150000000
#bytea_output = 'hex' # hex, escape
#xmlbinary = 'base64'
#xmloption = 'content'
*** a/src/include/commands/cluster.h
--- b/src/include/commands/cluster.h
***************
*** 20,26 ****
extern void cluster(ClusterStmt *stmt, bool isTopLevel);
extern void cluster_rel(Oid tableOid, Oid indexOid, bool recheck,
! bool verbose, int freeze_min_age, int freeze_table_age);
extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid,
bool recheck, LOCKMODE lockmode);
extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal);
--- 20,27 ----
extern void cluster(ClusterStmt *stmt, bool isTopLevel);
extern void cluster_rel(Oid tableOid, Oid indexOid, bool recheck,
! bool verbose, int freeze_min_age, int freeze_table_age,
! int multixact_freeze_min_age, int multixact_freeze_table_age);
extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid,
bool recheck, LOCKMODE lockmode);
extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal);
*** a/src/include/commands/vacuum.h
--- b/src/include/commands/vacuum.h
***************
*** 136,141 **** extern PGDLLIMPORT int default_statistics_target; /* PGDLLIMPORT for
--- 136,143 ----
* PostGIS */
extern int vacuum_freeze_min_age;
extern int vacuum_freeze_table_age;
+ extern int vacuum_multixact_freeze_min_age;
+ extern int vacuum_multixact_freeze_table_age;
/* in commands/vacuum.c */
***************
*** 156,161 **** extern void vac_update_relstats(Relation relation,
--- 158,165 ----
TransactionId frozenxid,
MultiXactId minmulti);
extern void vacuum_set_xid_limits(int freeze_min_age, int freeze_table_age,
+ int multixact_freeze_min_age,
+ int multixact_freeze_table_age,
bool sharedRel,
TransactionId *oldestXmin,
TransactionId *freezeLimit,
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 2430,2435 **** typedef struct VacuumStmt
--- 2430,2437 ----
int options; /* OR of VacuumOption flags */
int freeze_min_age; /* min freeze age, or -1 to use default */
int freeze_table_age; /* age at which to scan whole table */
+ int multixact_freeze_min_age; /* min multixact freeze age, or -1 to use default */
+ int multixact_freeze_table_age; /* multixact age at which to scan whole table */
RangeVar *relation; /* single table to process, or NULL */
List *va_cols; /* list of column names, or NIL for all */
} VacuumStmt;
*** a/src/include/postmaster/autovacuum.h
--- b/src/include/postmaster/autovacuum.h
***************
*** 24,29 **** extern double autovacuum_vac_scale;
--- 24,30 ----
extern int autovacuum_anl_thresh;
extern double autovacuum_anl_scale;
extern int autovacuum_freeze_max_age;
+ extern int autovacuum_multixact_freeze_max_age;
extern int autovacuum_vac_cost_delay;
extern int autovacuum_vac_cost_limit;
*** a/src/include/utils/rel.h
--- b/src/include/utils/rel.h
***************
*** 198,203 **** typedef struct AutoVacOpts
--- 198,206 ----
int freeze_min_age;
int freeze_max_age;
int freeze_table_age;
+ int multixact_freeze_min_age;
+ int multixact_freeze_max_age;
+ int multixact_freeze_table_age;
float8 vacuum_scale_factor;
float8 analyze_scale_factor;
} AutoVacOpts;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
