On Fri, Mar 29, 2019 at 4:39 AM Robert Haas <robertmh...@gmail.com> wrote:
>
> On Thu, Mar 28, 2019 at 2:00 AM Masahiko Sawada <sawada.m...@gmail.com> wrote:
> > The patch adds the basic functionality to disable index cleanup but
> > one possible argument could be whether we should always disable it
> > when anti-wraparound vacuum. As discussed on another thread[1]
> > anti-wraparound vacuum still could lead the I/O burst problem and take
> > a long time, especially for append-only large table. Originally the
> > purpose of this feature is to resolve the problem that vacuum takes a
> > long time even if the table has just a few dead tuples, which is a
> > quite common situation of anti-wraparound vacuum. It might be too late
> > to discuss but if we always disable it when anti-wraparound vacuum
> > then users don't need to do "VACUUM (INDEX_CLEANUP false)" manually on
> > PostgreSQL 12. Dose anyone have opinions?
>
> I think we can respect the configured value of the option even for
> aggressive vacuums, but I don't think we should change aggressive
> vacuums to work that way by default. You are correct that the table
> might have only a few dead tuples, but it might also have a lot of
> dead tuples; I have heard rumors of a PostgreSQL installation that had
> autovacuum = off and non-stop wraparound autovacuums desperately
> trying to forestall shutdown. That's probably a lot less likely now
> that we have the freeze map and such a system would almost surely have
> a nasty bloat problem, but disabling index cleanup by default would
> make it worse.
Understood and agreed. Always setting it to false would affect much
and there are users who expect anti-wraparound vacuums to reclaim
garbage.
>
> I think the solution in the long run here is to (1) allow the
> index_cleanup option (or the corresponding reloption) to override the
> default behavior and (2) eventually change the default behavior from
> 'always yes' to 'depends on how many dead tuples we found'. But I
> think that the second of those things is not appropriate to consider
> changing in PG 12 at this point.
The current patch already takes (1) and I agreed with you that (2)
would be for PG 13 or later. So the patch would be helpful for such
users as well.
Attached updated patches. These patches are applied on top of 0001
patch on parallel vacuum thread[1].
[1]
https://www.postgresql.org/message-id/CAD21AoBaFcKBAeL5_%2B%2Bj%2BVzir2vBBcF4juW7qH8b3HsQY%3DQ6%2Bw%40mail.gmail.com
Regards,
--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
From 4a16701f327d26cf499cbe9c22916d5081a232d2 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <sawada.m...@gmail.com>
Date: Fri, 1 Feb 2019 16:02:50 +0100
Subject: [PATCH v11 2/2] Add --disable-index-cleanup option to vacuumdb.
---
doc/src/sgml/ref/vacuumdb.sgml | 15 +++++++++++++++
src/bin/scripts/t/100_vacuumdb.pl | 9 ++++++++-
src/bin/scripts/vacuumdb.c | 29 +++++++++++++++++++++++++++++
3 files changed, 52 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 41c7f3d..02d6e46 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -118,6 +118,21 @@ PostgreSQL documentation
</varlistentry>
<varlistentry>
+ <term><option>--disable-index-cleanup</option></term>
+ <listitem>
+ <para>
+ Disable index vacuuming and index cleanup.
+ </para>
+ <note>
+ <para>
+ This option is only available for servers running
+ <productname>PostgreSQL</productname> 12 and later.
+ </para>
+ </note>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-e</option></term>
<term><option>--echo</option></term>
<listitem>
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 7f3a9b1..2b7cd18 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -3,7 +3,7 @@ use warnings;
use PostgresNode;
use TestLib;
-use Test::More tests => 44;
+use Test::More tests => 47;
program_help_ok('vacuumdb');
program_version_ok('vacuumdb');
@@ -38,6 +38,10 @@ $node->issues_sql_like(
qr/statement: VACUUM \(DISABLE_PAGE_SKIPPING\).*;/,
'vacuumdb --disable-page-skipping');
$node->issues_sql_like(
+ [ 'vacuumdb', '--disable-index-cleanup', 'postgres' ],
+ qr/statement: VACUUM \(INDEX_CLEANUP FALSE\).*;/,
+ 'vacuumdb --disable-index-cleanup');
+$node->issues_sql_like(
[ 'vacuumdb', '--skip-locked', 'postgres' ],
qr/statement: VACUUM \(SKIP_LOCKED\).*;/,
'vacuumdb --skip-locked');
@@ -48,6 +52,9 @@ $node->issues_sql_like(
$node->command_fails(
[ 'vacuumdb', '--analyze-only', '--disable-page-skipping', 'postgres' ],
'--analyze-only and --disable-page-skipping specified together');
+$node->command_fails(
+ [ 'vacuumdb', '--analyze-only', '--disable-index-cleanup', 'postgres' ],
+ '--analyze-only and --disable-index-cleanup specified together');
$node->command_ok([qw(vacuumdb -Z --table=pg_am dbname=template1)],
'vacuumdb with connection string');
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 5ac41ea..c090ee7 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -42,6 +42,7 @@ typedef struct vacuumingOptions
bool full;
bool freeze;
bool disable_page_skipping;
+ bool disable_index_cleanup;
bool skip_locked;
int min_xid_age;
int min_mxid_age;
@@ -117,6 +118,7 @@ main(int argc, char *argv[])
{"skip-locked", no_argument, NULL, 5},
{"min-xid-age", required_argument, NULL, 6},
{"min-mxid-age", required_argument, NULL, 7},
+ {"disable-index-cleanup", no_argument, NULL, 8},
{NULL, 0, NULL, 0}
};
@@ -244,6 +246,11 @@ main(int argc, char *argv[])
exit(1);
}
break;
+ case 8:
+ {
+ vacopts.disable_index_cleanup = true;
+ break;
+ }
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -288,6 +295,12 @@ main(int argc, char *argv[])
progname, "disable-page-skipping");
exit(1);
}
+ if (vacopts.disable_index_cleanup)
+ {
+ fprintf(stderr, _("%s: cannot use the \"%s\" option when performing only analyze\n"),
+ progname, "disable-index-cleanup");
+ exit(1);
+ }
/* allow 'and_analyze' with 'analyze_only' */
}
@@ -418,6 +431,14 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
exit(1);
}
+ if (vacopts->disable_index_cleanup && PQserverVersion(conn) < 120000)
+ {
+ PQfinish(conn);
+ fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 12\n"),
+ progname, "disable-index-cleanup");
+ exit(1);
+ }
+
if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
{
PQfinish(conn);
@@ -868,6 +889,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
sep = comma;
}
+ if (vacopts->disable_index_cleanup)
+ {
+ /* INDEX_CLEANUP is supported since 12 */
+ Assert(serverVersion >= 120000);
+ appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
+ sep = comma;
+ }
if (vacopts->skip_locked)
{
/* SKIP_LOCKED is supported since v12 */
@@ -1221,6 +1249,7 @@ help(const char *progname)
printf(_(" -a, --all vacuum all databases\n"));
printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
+ printf(_(" --disable-index-cleanup disable index vacuuming and index cleanup\n"));
printf(_(" -e, --echo show the commands being sent to the server\n"));
printf(_(" -f, --full do full vacuuming\n"));
printf(_(" -F, --freeze freeze row transaction information\n"));
--
1.8.3.1
From 5a50a20c346e6e96006d4b92f3b1ec7b0692e66f Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <sawada.m...@gmail.com>
Date: Thu, 7 Mar 2019 09:45:11 +0900
Subject: [PATCH v11 1/2] Add INDEX_CLEANUP option to VACUUM command
If this option is false, VACUUM does HOT-pruning for live tuples but
doesn't remove dead tuples completely and disables index vacuum.
vacrelstats->dead_tuples could have tuples that became dead after
checked at a HOT-pruning time, which are not marked as dead. Per
discussion on pgsql-hackers We normally records and remove them but
with this option we don't process and leave for the next vacuum for
simplifing the code. That's okay because it's very rare condition and
those tuples will be processed by the next vacuum.
---
doc/src/sgml/ref/create_table.sgml | 17 ++++++++
doc/src/sgml/ref/vacuum.sgml | 28 ++++++++++++
src/backend/access/common/reloptions.c | 13 +++++-
src/backend/access/heap/vacuumlazy.c | 78 ++++++++++++++++++++++++++--------
src/backend/commands/vacuum.c | 21 ++++++++-
src/backend/postmaster/autovacuum.c | 2 +-
src/bin/psql/tab-complete.c | 6 ++-
src/include/commands/vacuum.h | 3 +-
src/include/utils/rel.h | 1 +
src/test/regress/expected/vacuum.out | 3 ++
src/test/regress/sql/vacuum.sql | 3 ++
11 files changed, 151 insertions(+), 24 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 1660784..a6eba05 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1345,6 +1345,23 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</varlistentry>
<varlistentry>
+ <term><literal>vacuum_index_cleanup</literal> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ Per table setting to use <literal>INDEX_CLEANUP</literal> option
+ of <command>VACUUM</command> command. The default value is <literal>true</literal>.
+ If <literal>false</literal>, autovacuum daemon and <command>VACUUM</command>
+ never perform index vacuuming and index cleanup, that is, always set
+ <literal>INDEX_CLEANUP</literal> option to <literal>false</literal>.
+ Note that out of disk space due to index bloat. Setting this parameter to
+ <literal>false</literal> makes sense to avoid scanning large indexes when
+ the table has a few dead tuples. See <xref linkend="sql-vacuum"/> for more
+ details on <literal>INDEX_CLEANUP</literal> option.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>autovacuum_vacuum_threshold</literal>, <literal>toast.autovacuum_vacuum_threshold</literal> (<type>integer</type>)</term>
<listitem>
<para>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 906d0c2..3f87979 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -32,6 +32,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
ANALYZE [ <replaceable class="parameter">boolean</replaceable> ]
DISABLE_PAGE_SKIPPING [ <replaceable class="parameter">boolean</replaceable> ]
SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
+ INDEX_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -182,6 +183,26 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</varlistentry>
<varlistentry>
+ <term><literal>INDEX_CLEANUP</literal></term>
+ <listitem>
+ <para>
+ <command>VACUUM</command> removes dead tuples and prunes HOT-updated
+ tuples chain for live tuples on table. If the table has any dead tuple
+ it removes them from both the table and its indexes and marks the
+ corresponding item identifers as available for re-use. If this option
+ is <literal>FALSE</literal> <command>VACUUM</command> still removes
+ dead tuples from the table, but it does not process any indexes, and
+ the item identifers are marked as dead instead of available. This is
+ suitable for avoiding transaction ID wraparound
+ (see <xref linkend="vacuum-for-wraparound"/>) but not sufficient for
+ avoiding index bloat. It defaults to <literal>TRUE</literal>.
+ This option is ignored if the table does not have index. This cannot
+ be used in conjunction with <literal>FULL</literal> option.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">boolean</replaceable></term>
<listitem>
<para>
@@ -275,6 +296,13 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</para>
<para>
+ Setting a values for <literal>vacuum_index_cleanup</literal> via
+ <xref linkend="sql-altertable"/> also enables and disables index cleanup.
+ The <literal>INDEX_CLEANUP</literal> options to
+ <command>VACUUM</command> takes precedence over this option.
+ </para>
+
+ <para>
<command>VACUUM</command> causes a substantial increase in I/O traffic,
which might cause poor performance for other active sessions. Therefore,
it is sometimes advisable to use the cost-based vacuum delay feature.
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index b58a1f7..e2c0de3 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -138,6 +138,15 @@ static relopt_bool boolRelOpts[] =
},
false
},
+ {
+ {
+ "vacuum_index_cleanup",
+ "Enables index vacuuming and index cleanup",
+ RELOPT_KIND_HEAP,
+ ShareUpdateExclusiveLock
+ },
+ true
+ },
/* list terminator */
{{NULL}}
};
@@ -1388,7 +1397,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
{"parallel_workers", RELOPT_TYPE_INT,
offsetof(StdRdOptions, parallel_workers)},
{"vacuum_cleanup_index_scale_factor", RELOPT_TYPE_REAL,
- offsetof(StdRdOptions, vacuum_cleanup_index_scale_factor)}
+ offsetof(StdRdOptions, vacuum_cleanup_index_scale_factor)},
+ {"vacuum_index_cleanup", RELOPT_TYPE_BOOL,
+ offsetof(StdRdOptions, vacuum_index_cleanup)}
};
options = parseRelOptions(reloptions, validate, kind, &numoptions);
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 5c554f9..4911131 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -112,8 +112,8 @@
typedef struct LVRelStats
{
- /* hasindex = true means two-pass strategy; false means one-pass */
- bool hasindex;
+ /* useindex = true means two-pass strategy; false means one-pass */
+ bool useindex;
/* Overall statistics about rel */
BlockNumber old_rel_pages; /* previous value of pg_class.relpages */
BlockNumber rel_pages; /* total number of pages */
@@ -125,6 +125,8 @@ typedef struct LVRelStats
double new_rel_tuples; /* new estimated total # of tuples */
double new_live_tuples; /* new estimated total # of live tuples */
double new_dead_tuples; /* new estimated total # of dead tuples */
+ double nleft_dead_tuples; /* # of dead tuples we left */
+ double nleft_dead_itemids; /* # of dead item pointers we left */
BlockNumber pages_removed;
double tuples_deleted;
BlockNumber nonempty_pages; /* actually, last nonempty page + 1 */
@@ -258,7 +260,8 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
/* Open all indexes of the relation */
vac_open_indexes(onerel, RowExclusiveLock, &nindexes, &Irel);
- vacrelstats->hasindex = (nindexes > 0);
+ vacrelstats->useindex = (nindexes > 0 &&
+ (params->options & VACOPT_INDEX_CLEANUP) != 0);
/* Do the vacuuming */
lazy_scan_heap(onerel, params->options, vacrelstats, Irel, nindexes, aggressive);
@@ -332,7 +335,7 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
new_rel_pages,
new_live_tuples,
new_rel_allvisible,
- vacrelstats->hasindex,
+ nindexes > 0,
new_frozen_xid,
new_min_multi,
false);
@@ -404,6 +407,10 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
vacrelstats->new_dead_tuples,
OldestXmin);
appendStringInfo(&buf,
+ _("%.0f tuples and %.0f item identifiers are left as dead.\n"),
+ vacrelstats->nleft_dead_tuples,
+ vacrelstats->nleft_dead_itemids);
+ appendStringInfo(&buf,
_("buffer usage: %d hits, %d misses, %d dirtied\n"),
VacuumPageHit,
VacuumPageMiss,
@@ -485,7 +492,10 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
live_tuples, /* live tuples (reltuples estimate) */
tups_vacuumed, /* tuples cleaned up by vacuum */
nkeep, /* dead-but-not-removable tuples */
- nunused; /* unused item pointers */
+ nunused, /* unused item pointers */
+ nleft_dead_tuples, /* tuples we left as dead */
+ nleft_dead_itemids; /* item pointers we left as dead,
+ * includes nleft_dead_tuples. */
IndexBulkDeleteResult **indstats;
int i;
PGRUsage ru0;
@@ -518,6 +528,7 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
empty_pages = vacuumed_pages = 0;
next_fsm_block_to_vacuum = (BlockNumber) 0;
num_tuples = live_tuples = tups_vacuumed = nkeep = nunused = 0;
+ nleft_dead_itemids = nleft_dead_tuples = 0;
indstats = (IndexBulkDeleteResult **)
palloc0(nindexes * sizeof(IndexBulkDeleteResult *));
@@ -1054,7 +1065,16 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
HeapTupleIsHeapOnly(&tuple))
nkeep += 1;
else
+ {
tupgone = true; /* we can delete the tuple */
+
+ /*
+ * Since the dead tuples will be not be vacuumed
+ * and ignored when index cleanup is disabled we
+ * count them for reporting.
+ */
+ nleft_dead_tuples++;
+ }
all_visible = false;
break;
case HEAPTUPLE_LIVE:
@@ -1206,15 +1226,33 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
}
/*
- * If there are no indexes then we can vacuum the page right now
- * instead of doing a second scan.
+ * If there are no indexes we can vacuum the page right now instead of
+ * doing a second scan. Also we don't do that but forget dead tuples
+ * when index cleanup is disabled.
*/
- if (nindexes == 0 &&
- vacrelstats->num_dead_tuples > 0)
+ if (!vacrelstats->useindex && vacrelstats->num_dead_tuples > 0)
{
- /* Remove tuples from heap */
- lazy_vacuum_page(onerel, blkno, buf, 0, vacrelstats, &vmbuffer);
- has_dead_tuples = false;
+ if (nindexes == 0)
+ {
+ /* Remove tuples from heap if the table has no index */
+ lazy_vacuum_page(onerel, blkno, buf, 0, vacrelstats, &vmbuffer);
+ vacuumed_pages++;
+ has_dead_tuples = false;
+ }
+ else
+ {
+ /*
+ * Here, we have indexes but index cleanup is disabled. Instead of
+ * vacuuming the dead tuples on the heap, we just forget them.
+ *
+ * Note that vacrelstats->dead_tuples could have tuples which
+ * became dead after HOT-pruning but are not marked dead yet.
+ * We do not process them because it's a very rare condition, and
+ * the next vacuum will process them anyway.
+ */
+ Assert((options & VACOPT_INDEX_CLEANUP) == 0);
+ nleft_dead_itemids += vacrelstats->num_dead_tuples;
+ }
/*
* Forget the now-vacuumed tuples, and press on, but be careful
@@ -1222,7 +1260,6 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
* valid.
*/
vacrelstats->num_dead_tuples = 0;
- vacuumed_pages++;
/*
* Periodically do incremental FSM vacuuming to make newly-freed
@@ -1348,7 +1385,9 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
/* save stats for use later */
vacrelstats->tuples_deleted = tups_vacuumed;
- vacrelstats->new_dead_tuples = nkeep;
+ vacrelstats->new_dead_tuples = nkeep + nleft_dead_tuples;
+ vacrelstats->nleft_dead_tuples = nleft_dead_tuples;
+ vacrelstats->nleft_dead_itemids = nleft_dead_itemids;
/* now we can compute the new value for pg_class.reltuples */
vacrelstats->new_live_tuples = vac_estimate_reltuples(onerel,
@@ -1417,8 +1456,11 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
PROGRESS_VACUUM_PHASE_INDEX_CLEANUP);
/* Do post-vacuum cleanup and statistics update for each index */
- for (i = 0; i < nindexes; i++)
- lazy_cleanup_index(Irel[i], indstats[i], vacrelstats);
+ if (vacrelstats->useindex)
+ {
+ for (i = 0; i < nindexes; i++)
+ lazy_cleanup_index(Irel[i], indstats[i], vacrelstats);
+ }
/* If no indexes, make log report that lazy_vacuum_heap would've made */
if (vacuumed_pages)
@@ -1449,6 +1491,8 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
"%u pages are entirely empty.\n",
empty_pages),
empty_pages);
+ appendStringInfo(&buf, "%.0f tuples and %.0f item identifiers are left as dead.\n",
+ nleft_dead_tuples, nleft_dead_itemids);
appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0));
ereport(elevel,
@@ -2092,7 +2136,7 @@ lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks)
autovacuum_work_mem != -1 ?
autovacuum_work_mem : maintenance_work_mem;
- if (vacrelstats->hasindex)
+ if (vacrelstats->useindex)
{
maxtuples = (vac_work_mem * 1024L) / sizeof(ItemPointerData);
maxtuples = Min(maxtuples, INT_MAX);
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 10df766..1d1b215 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -93,6 +93,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
bool freeze = false;
bool full = false;
bool disable_page_skipping = false;
+ bool index_cleanup = true; /* by default */
ListCell *lc;
/* Parse options list */
@@ -120,6 +121,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
full = defGetBoolean(opt);
else if (strcmp(opt->defname, "disable_page_skipping") == 0)
disable_page_skipping = defGetBoolean(opt);
+ else if (strcmp(opt->defname, "index_cleanup") == 0)
+ index_cleanup = defGetBoolean(opt);
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -135,7 +138,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
(analyze ? VACOPT_ANALYZE : 0) |
(freeze ? VACOPT_FREEZE : 0) |
(full ? VACOPT_FULL : 0) |
- (disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0);
+ (disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0) |
+ (index_cleanup ? VACOPT_INDEX_CLEANUP : 0);
/* sanity checks on options */
Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE));
@@ -251,7 +255,8 @@ vacuum(List *relations, VacuumParams *params,
stmttype)));
/*
- * Sanity check DISABLE_PAGE_SKIPPING option.
+ * Sanity check DISABLE_PAGE_SKIPPING option and INDEX_CLEANUP
+ * option.
*/
if ((params->options & VACOPT_FULL) != 0 &&
(params->options & VACOPT_DISABLE_PAGE_SKIPPING) != 0)
@@ -259,6 +264,11 @@ vacuum(List *relations, VacuumParams *params,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("VACUUM option DISABLE_PAGE_SKIPPING cannot be used with FULL")));
+ if ((params->options & VACOPT_FULL) != 0 &&
+ (params->options & VACOPT_INDEX_CLEANUP) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("VACUUM option INDEX_CLEANUP cannot be set to false with FULL")));
/*
* Send info about dead objects to the statistics collector, unless we are
* in autovacuum --- autovacuum.c does this for itself.
@@ -1720,6 +1730,13 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
LockRelationIdForSession(&onerelid, lmode);
/*
+ * Disables index cleanup based on reloptions.
+ */
+ if (onerel->rd_options &&
+ !((StdRdOptions *) onerel->rd_options)->vacuum_index_cleanup)
+ params->options &= ~(VACOPT_INDEX_CLEANUP);
+
+ /*
* Remember the relation's TOAST relation for later, if the caller asked
* us to process it. In VACUUM FULL, though, the toast table is
* automatically rebuilt by cluster_rel so we shouldn't recurse to it.
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index fa875db..51c60fc 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2883,7 +2883,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
tab->at_relid = relid;
tab->at_sharedrel = classForm->relisshared;
tab->at_params.options = VACOPT_SKIPTOAST |
- (dovacuum ? VACOPT_VACUUM : 0) |
+ (dovacuum ? VACOPT_VACUUM | VACOPT_INDEX_CLEANUP : 0) |
(doanalyze ? VACOPT_ANALYZE : 0) |
(!wraparound ? VACOPT_SKIP_LOCKED : 0);
tab->at_params.freeze_min_age = freeze_min_age;
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index c18977c..c60efd9 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1039,6 +1039,7 @@ static const char *const table_storage_parameters[] = {
"toast.log_autovacuum_min_duration",
"toast_tuple_target",
"user_catalog_table",
+ "vacuum_index_cleanup",
NULL
};
@@ -3431,8 +3432,9 @@ psql_completion(const char *text, int start, int end)
*/
if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
COMPLETE_WITH("FULL", "FREEZE", "ANALYZE", "VERBOSE",
- "DISABLE_PAGE_SKIPPING", "SKIP_LOCKED");
- else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED"))
+ "DISABLE_PAGE_SKIPPING", "SKIP_LOCKED",
+ "INDEX_CLEANUP");
+ else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP"))
COMPLETE_WITH("ON", "OFF");
}
else if (HeadMatches("VACUUM") && TailMatches("("))
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 77086f3..7bbc18c 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -145,7 +145,8 @@ typedef enum VacuumOption
VACOPT_FULL = 1 << 4, /* FULL (non-concurrent) vacuum */
VACOPT_SKIP_LOCKED = 1 << 5, /* skip if cannot get lock */
VACOPT_SKIPTOAST = 1 << 6, /* don't process the TOAST table, if any */
- VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7 /* don't skip any pages */
+ VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7, /* don't skip any pages */
+ VACOPT_INDEX_CLEANUP = 1 << 8 /* Do index vacuum and cleanup */
} VacuumOption;
/*
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 5402851..89a7fbf 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -266,6 +266,7 @@ typedef struct StdRdOptions
AutoVacOpts autovacuum; /* autovacuum-related options */
bool user_catalog_table; /* use as an additional catalog relation */
int parallel_workers; /* max number of parallel workers */
+ bool vacuum_index_cleanup; /* enables index vacuuming and cleanup */
} StdRdOptions;
#define HEAP_MIN_FILLFACTOR 10
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 07d0703..a1de992 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -80,6 +80,9 @@ CONTEXT: SQL function "do_analyze" statement 1
SQL function "wrap_do_analyze" statement 1
VACUUM FULL vactst;
VACUUM (DISABLE_PAGE_SKIPPING) vaccluster;
+VACUUM (INDEX_CLEANUP FALSE) vaccluster;
+VACUUM (INDEX_CLEANUP FALSE) vactst; -- option is ignored if no indexes
+VACUUM (INDEX_CLEANUP FALSE, FREEZE) vaccluster;
-- partitioned table
CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a);
CREATE TABLE vacparted1 PARTITION OF vacparted FOR VALUES IN (1);
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 81f3822..8745e34 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -61,6 +61,9 @@ VACUUM FULL vaccluster;
VACUUM FULL vactst;
VACUUM (DISABLE_PAGE_SKIPPING) vaccluster;
+VACUUM (INDEX_CLEANUP FALSE) vaccluster;
+VACUUM (INDEX_CLEANUP FALSE) vactst; -- option is ignored if no indexes
+VACUUM (INDEX_CLEANUP FALSE, FREEZE) vaccluster;
-- partitioned table
CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a);
--
1.8.3.1
