On Tue, May 10, 2016 at 11:30 PM, Robert Haas <[email protected]> wrote:
> On Mon, May 9, 2016 at 7:40 PM, Ants Aasma <[email protected]> wrote:
>> On Mon, May 9, 2016 at 10:53 PM, Robert Haas <[email protected]> wrote:
>>> On Sun, May 8, 2016 at 10:42 PM, Masahiko Sawada <[email protected]>
>>> wrote:
>>>> Attached draft patch adds SCANALL option to VACUUM in order to scan
>>>> all pages forcibly while ignoring visibility map information.
>>>> The option name is SCANALL for now but we could change it after got
>>>> consensus.
>>>
>>> If we're going to go that way, I'd say it should be scan_all rather
>>> than scanall. Makes it clearer, at least IMHO.
>>
>> Just to add some diversity to opinions, maybe there should be a
>> separate command for performing integrity checks. Currently the best
>> ways to actually verify database correctness do so as a side effect.
>> The question that I get pretty much every time after I explain why we
>> have data checksums, is "how do I check that they are correct" and we
>> don't have a nice answer for that now. We could also use some ways to
>> sniff out corrupted rows that don't involve crashing the server in a
>> loop. Vacuuming pages that supposedly don't need vacuuming just to
>> verify integrity seems very much in the same vein.
>>
>> I know right now isn't exactly the best time to hastily slap on such a
>> feature, but I just wanted the thought to be out there for
>> consideration.
>
> I think that it's quite reasonable to have ways of performing an
> integrity check that are separate from VACUUM, but this is about
> having a way to force VACUUM to scan all-frozen pages
Or second way I came up with is having tool to remove particular _vm
file safely, which is executed via SQL or client tool like
pg_resetxlog.
Attached updated VACUUM SCAN_ALL patch.
Please find it.
Regards,
--
Masahiko Sawada
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 19fd748..8f63fad 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -21,9 +21,9 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, ...] ) ] [ <replaceable class="PARAMETER">table_name</replaceable> [ (<replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ] ]
-VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ <replaceable class="PARAMETER">table_name</replaceable> ]
-VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ <replaceable class="PARAMETER">table_name</replaceable> [ (<replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ] ]
+VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE | SCAN_ALL } [, ...] ) ] [ <replaceable class="PARAMETER">table_name</replaceable> [ (<replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ] ]
+VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ SCAN_ALL ] [ <replaceable class="PARAMETER">table_name</replaceable> ]
+VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ SCAN_ALL ] ANALYZE [ <replaceable class="PARAMETER">table_name</replaceable> [ (<replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ] ]
</synopsis>
</refsynopsisdiv>
@@ -120,6 +120,17 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ <replaceable class="PARAMETER">
</varlistentry>
<varlistentry>
+ <term><literal>SCAN_ALL</literal></term>
+ <listitem>
+ <para>
+ Selects forcibly full page scanning vacuum while ignoring visibility map.
+ Forcibly full page scanning vacuum is always performed when the table is
+ rewritten so this option is redundant when <literal>FULL</> is specified.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>ANALYZE</literal></term>
<listitem>
<para>
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index 426e756..eee93c4 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -138,7 +138,7 @@ static BufferAccessStrategy vac_strategy;
/* non-export function prototypes */
static void lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
- Relation *Irel, int nindexes, bool aggressive);
+ Relation *Irel, int nindexes, bool aggressive, bool scan_all);
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,
@@ -185,6 +185,7 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params,
double read_rate,
write_rate;
bool aggressive; /* should we scan all unfrozen pages? */
+ bool scan_all; /* should we scan all pages forcibly? */
bool scanned_all_unfrozen; /* actually scanned all such pages? */
TransactionId xidFullScanLimit;
MultiXactId mxactFullScanLimit;
@@ -233,6 +234,9 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params,
aggressive |= MultiXactIdPrecedesOrEquals(onerel->rd_rel->relminmxid,
mxactFullScanLimit);
+ /* If SCAN_ALL option is specified, we have to scan all pages forcibly */
+ scan_all = options & VACOPT_SCANALL;
+
vacrelstats = (LVRelStats *) palloc0(sizeof(LVRelStats));
vacrelstats->old_rel_pages = onerel->rd_rel->relpages;
@@ -246,14 +250,14 @@ 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, vacrelstats, Irel, nindexes, aggressive, scan_all);
/* Done with indexes */
vac_close_indexes(nindexes, Irel, NoLock);
/*
- * Compute whether we actually scanned the whole relation. If we did, we
- * can adjust relfrozenxid and relminmxid.
+ * Compute whether we actually scanned the whole relation. If we did,
+ * we can adjust relfrozenxid and relminmxid.
*
* NB: We need to check this before truncating the relation, because that
* will change ->rel_pages.
@@ -261,7 +265,7 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params,
if ((vacrelstats->scanned_pages + vacrelstats->frozenskipped_pages)
< vacrelstats->rel_pages)
{
- Assert(!aggressive);
+ Assert(!aggressive && !scan_all);
scanned_all_unfrozen = false;
}
else
@@ -442,7 +446,7 @@ vacuum_log_cleanup_info(Relation rel, LVRelStats *vacrelstats)
*/
static void
lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
- Relation *Irel, int nindexes, bool aggressive)
+ Relation *Irel, int nindexes, bool aggressive, bool scan_all)
{
BlockNumber nblocks,
blkno;
@@ -513,6 +517,10 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
* such pages do not need freezing and do not affect the value that we can
* safely set for relfrozenxid or relminmxid.
*
+ * When scan_all is set, we have to scan all pages forcibly while ignoring
+ * visibility map status, and then we can safely set for relfrozenxid or
+ * relminmxid.
+ *
* Before entering the main loop, establish the invariant that
* next_unskippable_block is the next block number >= blkno that's not we
* can't skip based on the visibility map, either all-visible for a
@@ -639,11 +647,12 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
/*
* The current block is potentially skippable; if we've seen a
* long enough run of skippable blocks to justify skipping it, and
- * we're not forced to check it, then go ahead and skip.
- * Otherwise, the page must be at least all-visible if not
- * all-frozen, so we can set all_visible_according_to_vm = true.
+ * SCAN_ALL option is not specified, and we're not forced to check it,
+ * then go ahead and skip. Otherwise, the page must be at least
+ * all-visible if not all-frozen, so we can set
+ * all_visible_according_to_vm = true.
*/
- if (skipping_blocks && !FORCE_CHECK_PAGE())
+ if (skipping_blocks && !scan_all && !FORCE_CHECK_PAGE())
{
/*
* Tricky, tricky. If this is in aggressive vacuum, the page
@@ -1316,6 +1325,7 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
"Skipped %u pages due to buffer pins.\n",
vacrelstats->pinskipped_pages),
vacrelstats->pinskipped_pages);
+
appendStringInfo(&buf, ngettext("%u page is entirely empty.\n",
"%u pages are entirely empty.\n",
empty_pages),
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 18ec5f0..085a6f5 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -408,7 +408,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> overlay_placing substr_from substr_for
%type <boolean> opt_instead
-%type <boolean> opt_unique opt_concurrently opt_verbose opt_full
+%type <boolean> opt_unique opt_concurrently opt_verbose opt_full opt_scanall
%type <boolean> opt_freeze opt_default opt_recheck
%type <defelt> opt_binary opt_oids copy_delimiter
@@ -626,7 +626,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
ROW ROWS RULE
- SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
+ SAVEPOINT SCHEMA SCROLL SCANALL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW
SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P START
STATEMENT STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING
@@ -9299,7 +9299,7 @@ cluster_index_specification:
*
*****************************************************************************/
-VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
+VacuumStmt: VACUUM opt_full opt_freeze opt_verbose opt_scanall
{
VacuumStmt *n = makeNode(VacuumStmt);
n->options = VACOPT_VACUUM;
@@ -9309,11 +9309,13 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
n->options |= VACOPT_FREEZE;
if ($4)
n->options |= VACOPT_VERBOSE;
+ if ($5)
+ n->options |= VACOPT_SCANALL;
n->relation = NULL;
n->va_cols = NIL;
$$ = (Node *)n;
}
- | VACUUM opt_full opt_freeze opt_verbose qualified_name
+ | VACUUM opt_full opt_freeze opt_verbose opt_scanall qualified_name
{
VacuumStmt *n = makeNode(VacuumStmt);
n->options = VACOPT_VACUUM;
@@ -9323,13 +9325,15 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
n->options |= VACOPT_FREEZE;
if ($4)
n->options |= VACOPT_VERBOSE;
- n->relation = $5;
+ if ($5)
+ n->options |= VACOPT_SCANALL;
+ n->relation = $6;
n->va_cols = NIL;
$$ = (Node *)n;
}
- | VACUUM opt_full opt_freeze opt_verbose AnalyzeStmt
+ | VACUUM opt_full opt_freeze opt_verbose opt_scanall AnalyzeStmt
{
- VacuumStmt *n = (VacuumStmt *) $5;
+ VacuumStmt *n = (VacuumStmt *) $6;
n->options |= VACOPT_VACUUM;
if ($2)
n->options |= VACOPT_FULL;
@@ -9337,6 +9341,8 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
n->options |= VACOPT_FREEZE;
if ($4)
n->options |= VACOPT_VERBOSE;
+ if ($5)
+ n->options |= VACOPT_SCANALL;
$$ = (Node *)n;
}
| VACUUM '(' vacuum_option_list ')'
@@ -9369,6 +9375,7 @@ vacuum_option_elem:
| VERBOSE { $$ = VACOPT_VERBOSE; }
| FREEZE { $$ = VACOPT_FREEZE; }
| FULL { $$ = VACOPT_FULL; }
+ | SCAN_ALL { $$ = VACOPT_SCANALL; }
;
AnalyzeStmt:
@@ -9411,7 +9418,9 @@ opt_full: FULL { $$ = TRUE; }
opt_freeze: FREEZE { $$ = TRUE; }
| /*EMPTY*/ { $$ = FALSE; }
;
-
+opt_scanall: SCAN_ALL { $$ = TRUE; }
+ | /* EMPTY */ { $$ = FALSE; }
+ ;
opt_name_list:
'(' name_list ')' { $$ = $2; }
| /*EMPTY*/ { $$ = NIL; }
@@ -14083,6 +14092,7 @@ type_func_name_keyword:
| OUTER_P
| OVERLAPS
| RIGHT
+ | SCANALL
| SIMILAR
| TABLESAMPLE
| VERBOSE
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 714cf15..fc6338d 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_SCANALL = 1 << 7 /* SCANALL option */
} VacuumOption;
typedef struct VacuumStmt
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 17ffef5..04214b0 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -335,6 +335,7 @@ PG_KEYWORD("row", ROW, COL_NAME_KEYWORD)
PG_KEYWORD("rows", ROWS, UNRESERVED_KEYWORD)
PG_KEYWORD("rule", RULE, UNRESERVED_KEYWORD)
PG_KEYWORD("savepoint", SAVEPOINT, UNRESERVED_KEYWORD)
+PG_KEYWORD("scanall", SCANALL, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("schema", SCHEMA, UNRESERVED_KEYWORD)
PG_KEYWORD("scroll", SCROLL, UNRESERVED_KEYWORD)
PG_KEYWORD("search", SEARCH, UNRESERVED_KEYWORD)
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
