From 010e99b403ec733d50c71a7d4ef646b1b446ef07 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Wed, 15 Jun 2016 22:52:58 -0400 Subject: [PATCH 2/2] Add VACUUM (DISABLE_PAGE_SKIPPING) for emergencies. --- doc/src/sgml/ref/vacuum.sgml | 21 ++++++++- src/backend/commands/vacuum.c | 9 ++++ src/backend/commands/vacuumlazy.c | 87 ++++++++++++++++++++---------------- src/backend/parser/gram.y | 10 +++++ src/include/nodes/parsenodes.h | 3 +- src/test/regress/expected/vacuum.out | 1 + src/test/regress/sql/vacuum.sql | 2 + 7 files changed, 92 insertions(+), 41 deletions(-) diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 19fd748..dee1c5b 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, ...] ) ] [ table_name [ (column_name [, ...] ) ] ] +VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE | DISABLE_PAGE_SKIPPING } [, ...] ) ] [ table_name [ (column_name [, ...] ) ] ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table_name ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table_name [ (column_name [, ...] ) ] ] @@ -130,6 +130,25 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ + DISABLE_PAGE_SKIPPING + + + Normally, VACUUM will skip pages based on the visibility map. Pages where + all tuples are known to be frozen can always be skipped, and those + where all tuples are known to be visible to all transactions may be + skipped except when performing an aggressive vacuum. Furthermore, + except when performing an aggressive vacuum, some pages may be skipped + in order to avoid waiting for other sessions to finish using them. + This option disables all page-skipping behavior, and is intended to + be used only the contents of the visibility map are thought to + be suspect, which should happen only if there is a hardware or software + issue causing database corruption. + + + + + table_name diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 25a55ab..0563e63 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -186,6 +186,15 @@ vacuum(int options, RangeVar *relation, Oid relid, VacuumParams *params, stmttype))); /* + * Sanity check DISABLE_PAGE_SKIPPING option. + */ + if ((options & VACOPT_FULL) != 0 && + (options & VACOPT_DISABLE_PAGE_SKIPPING) != 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("VACUUM option DISABLE_PAGE_SKIPPING cannot be used with FULL"))); + + /* * Send info about dead objects to the statistics collector, unless we are * in autovacuum --- autovacuum.c does this for itself. */ diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c index cb5777f..7a67fa5 100644 --- a/src/backend/commands/vacuumlazy.c +++ b/src/backend/commands/vacuumlazy.c @@ -137,8 +137,9 @@ static BufferAccessStrategy vac_strategy; /* non-export function prototypes */ -static void lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats, - Relation *Irel, int nindexes, bool aggressive); +static void lazy_scan_heap(Relation onerel, int options, + LVRelStats *vacrelstats, Relation *Irel, int nindexes, + bool aggressive); static void lazy_vacuum_heap(Relation onerel, LVRelStats *vacrelstats); static bool lazy_check_needs_freeze(Buffer buf, bool *hastup); static void lazy_vacuum_index(Relation indrel, @@ -223,15 +224,17 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params, &MultiXactCutoff, &mxactFullScanLimit); /* - * We request an aggressive scan if either the table's frozen Xid is now + * We request an aggressive scan if the table's frozen Xid is now * older than or equal to the requested Xid full-table scan limit; or if * the table's minimum MultiXactId is older than or equal to the requested - * mxid full-table scan limit. + * mxid full-table scan limit; or if DISABLE_PAGE_SKIPPING was specified. */ aggressive = TransactionIdPrecedesOrEquals(onerel->rd_rel->relfrozenxid, xidFullScanLimit); aggressive |= MultiXactIdPrecedesOrEquals(onerel->rd_rel->relminmxid, mxactFullScanLimit); + if (options & VACOPT_DISABLE_PAGE_SKIPPING) + aggressive = true; vacrelstats = (LVRelStats *) palloc0(sizeof(LVRelStats)); @@ -246,7 +249,7 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params, vacrelstats->hasindex = (nindexes > 0); /* Do the vacuuming */ - lazy_scan_heap(onerel, vacrelstats, Irel, nindexes, aggressive); + lazy_scan_heap(onerel, options, vacrelstats, Irel, nindexes, aggressive); /* Done with indexes */ vac_close_indexes(nindexes, Irel, NoLock); @@ -441,7 +444,7 @@ vacuum_log_cleanup_info(Relation rel, LVRelStats *vacrelstats) * reference them have been killed. */ static void -lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats, +lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats, Relation *Irel, int nindexes, bool aggressive) { BlockNumber nblocks, @@ -542,25 +545,28 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats, * the last page. This is worth avoiding mainly because such a lock must * be replayed on any hot standby, where it can be disruptive. */ - for (next_unskippable_block = 0; - next_unskippable_block < nblocks; - next_unskippable_block++) + next_unskippable_block = 0; + if ((options & VACOPT_DISABLE_PAGE_SKIPPING) == 0) { - uint8 vmstatus; - - vmstatus = visibilitymap_get_status(onerel, next_unskippable_block, - &vmbuffer); - if (aggressive) + while (next_unskippable_block < nblocks) { - if ((vmstatus & VISIBILITYMAP_ALL_FROZEN) == 0) - break; - } - else - { - if ((vmstatus & VISIBILITYMAP_ALL_VISIBLE) == 0) - break; + uint8 vmstatus; + + vmstatus = visibilitymap_get_status(onerel, next_unskippable_block, + &vmbuffer); + if (aggressive) + { + if ((vmstatus & VISIBILITYMAP_ALL_FROZEN) == 0) + break; + } + else + { + if ((vmstatus & VISIBILITYMAP_ALL_VISIBLE) == 0) + break; + } + vacuum_delay_point(); + next_unskippable_block++; } - vacuum_delay_point(); } if (next_unskippable_block >= SKIP_PAGES_THRESHOLD) @@ -594,26 +600,29 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats, if (blkno == next_unskippable_block) { /* Time to advance next_unskippable_block */ - for (next_unskippable_block++; - next_unskippable_block < nblocks; - next_unskippable_block++) + next_unskippable_block++; + if ((options & VACOPT_DISABLE_PAGE_SKIPPING) == 0) { - uint8 vmskipflags; - - vmskipflags = visibilitymap_get_status(onerel, - next_unskippable_block, - &vmbuffer); - if (aggressive) + while (next_unskippable_block < nblocks) { - if ((vmskipflags & VISIBILITYMAP_ALL_FROZEN) == 0) - break; - } - else - { - if ((vmskipflags & VISIBILITYMAP_ALL_VISIBLE) == 0) - break; + uint8 vmskipflags; + + vmskipflags = visibilitymap_get_status(onerel, + next_unskippable_block, + &vmbuffer); + if (aggressive) + { + if ((vmskipflags & VISIBILITYMAP_ALL_FROZEN) == 0) + break; + } + else + { + if ((vmskipflags & VISIBILITYMAP_ALL_VISIBLE) == 0) + break; + } + vacuum_delay_point(); + next_unskippable_block++; } - vacuum_delay_point(); } /* diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 2c950f9..edf4516 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -9370,6 +9370,16 @@ vacuum_option_elem: | VERBOSE { $$ = VACOPT_VERBOSE; } | FREEZE { $$ = VACOPT_FREEZE; } | FULL { $$ = VACOPT_FULL; } + | IDENT + { + if (strcmp($1, "disable_page_skipping") == 0) + $$ = VACOPT_DISABLE_PAGE_SKIPPING; + else + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("unrecognized VACUUM option \"%s\"", $1), + parser_errposition(@1))); + } ; AnalyzeStmt: diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 714cf15..d36d9c6 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2822,7 +2822,8 @@ typedef enum VacuumOption VACOPT_FREEZE = 1 << 3, /* FREEZE option */ VACOPT_FULL = 1 << 4, /* FULL (non-concurrent) vacuum */ VACOPT_NOWAIT = 1 << 5, /* don't wait to get lock (autovacuum only) */ - VACOPT_SKIPTOAST = 1 << 6 /* don't process the TOAST table, if any */ + VACOPT_SKIPTOAST = 1 << 6, /* don't process the TOAST table, if any */ + VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7 /* don't skip any pages */ } VacuumOption; typedef struct VacuumStmt diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index d2d7503..9b604be 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -79,5 +79,6 @@ ERROR: ANALYZE cannot be executed from VACUUM or ANALYZE CONTEXT: SQL function "do_analyze" statement 1 SQL function "wrap_do_analyze" statement 1 VACUUM FULL vactst; +VACUUM (DISABLE_PAGE_SKIPPING) vaccluster; DROP TABLE vaccluster; DROP TABLE vactst; diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql index f841201..7b819f6 100644 --- a/src/test/regress/sql/vacuum.sql +++ b/src/test/regress/sql/vacuum.sql @@ -60,5 +60,7 @@ VACUUM FULL pg_database; VACUUM FULL vaccluster; VACUUM FULL vactst; +VACUUM (DISABLE_PAGE_SKIPPING) vaccluster; + DROP TABLE vaccluster; DROP TABLE vactst; -- 2.5.4 (Apple Git-61)