On Sun, May 8, 2016 at 3:18 PM, Masahiko Sawada <sawada.m...@gmail.com> wrote:
> On Sat, May 7, 2016 at 11:08 PM, Masahiko Sawada <sawada.m...@gmail.com>
> wrote:
>> On Sat, May 7, 2016 at 6:00 AM, Joshua D. Drake <j...@commandprompt.com>
>> wrote:
>>> On 05/06/2016 01:58 PM, Stephen Frost wrote:
>>>>
>>>> * Joshua D. Drake (j...@commandprompt.com) wrote:
>>>>>
>>>>> Yeah I thought about that, it is the word "FORCE" that bothers me.
>>>>> When you use FORCE there is an assumption that no matter what, it
>>>>> plows through (think rm -f). So if we don't use FROZEN, that's cool
>>>>> but FORCE doesn't work either.
>>>>
>>>>
>>>> Isn't that exactly what this FORCE option being contemplated would do
>>>> though? Plow through the entire relation, regardless of what the VM
>>>> says is all frozen or not?
>>>>
>>>> Seems like FORCE is a good word for that to me.
>>>
>>>
>>> Except that we aren't FORCING a vacuum. That is the part I have contention
>>> with. To me, FORCE means:
>>>
>>> No matter what else is happening, we are vacuuming this relation (think
>>> locks).
>>>
>>> But I am also not going to dig in my heals. If that is truly what -hackers
>>> come up with, thank you at least considering what I said.
>>>
>>> Sincerely,
>>>
>>> JD
>>>
>>
>> As Joshua mentioned, FORCE word might imply doing VACUUM while plowing
>> through locks.
>> I guess that it might confuse the users.
>> IMO, since this option will be a way for emergency, SCANALL word works for
>> me.
>>
>> Or other ideas are,
>> VACUUM IGNOREVM
>> VACUUM RESCURE
>>
>
> Oops, VACUUM RESCUE is correct.
>
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.
Regards,
--
Masahiko Sawada
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 19fd748..130a70e 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 | SCANALL } [, ...] ) ] [ <replaceable class="PARAMETER">table_name</replaceable> [ (<replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ] ]
+VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ SCANALL ] [ <replaceable class="PARAMETER">table_name</replaceable> ]
+VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ SCANALL ] 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>SCANALL</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..85e04ac 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 SCANALL 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.
+ * SCANALL 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..f73ca47 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; }
+ | SCANALL { $$ = VACOPT_SCANALL; }
;
AnalyzeStmt:
@@ -9411,7 +9418,9 @@ opt_full: FULL { $$ = TRUE; }
opt_freeze: FREEZE { $$ = TRUE; }
| /*EMPTY*/ { $$ = FALSE; }
;
-
+opt_scanall: SCANALL { $$ = 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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
