Hello,
I sometimes feel annoyed when trying to VACUUM multiple specific
tables.
postgres=# vacuum a, b;
ERROR: syntax error at or near ","
LINE 1: vacuum a, b;
This patch just allows multiple targets for VACUUM command.
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
>From 481690f0d84a21db755a986a7f785e8bbbe0769e Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyot...@lab.ntt.co.jp>
Date: Fri, 28 Jul 2017 13:30:59 +0900
Subject: [PATCH 1/2] Make VaccumStmt capable to have multiple table parameters
---
src/backend/commands/vacuum.c | 18 +++++++++++++++---
src/backend/nodes/copyfuncs.c | 13 +++++++++++++
src/backend/nodes/equalfuncs.c | 11 +++++++++++
src/backend/parser/gram.y | 29 ++++++++++++++++-------------
src/include/nodes/nodes.h | 1 +
src/include/nodes/parsenodes.h | 10 ++++++++--
6 files changed, 64 insertions(+), 18 deletions(-)
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index faa1812..d6cd352 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -85,12 +85,12 @@ void
ExecVacuum(VacuumStmt *vacstmt, bool isTopLevel)
{
VacuumParams params;
+ ListCell *lc;
/* sanity checks on options */
Assert(vacstmt->options & (VACOPT_VACUUM | VACOPT_ANALYZE));
Assert((vacstmt->options & VACOPT_VACUUM) ||
!(vacstmt->options & (VACOPT_FULL | VACOPT_FREEZE)));
- Assert((vacstmt->options & VACOPT_ANALYZE) || vacstmt->va_cols == NIL);
Assert(!(vacstmt->options & VACOPT_SKIPTOAST));
/*
@@ -119,8 +119,20 @@ ExecVacuum(VacuumStmt *vacstmt, bool isTopLevel)
params.log_min_duration = -1;
/* Now go through the common routine */
- vacuum(vacstmt->options, vacstmt->relation, InvalidOid, ¶ms,
- vacstmt->va_cols, NULL, isTopLevel);
+ if (list_length(vacstmt->relcols) == 0)
+ vacuum(vacstmt->options, NULL, InvalidOid, ¶ms,
+ NIL, NULL, isTopLevel);
+ else
+ {
+ foreach (lc, vacstmt->relcols)
+ {
+ VacRelCols *relcol = (VacRelCols *) lfirst(lc);
+ Assert((vacstmt->options & VACOPT_ANALYZE) ||
+ relcol->va_cols == NIL);
+ vacuum(vacstmt->options, relcol->relation, InvalidOid, ¶ms,
+ relcol->va_cols, NULL, isTopLevel);
+ }
+ }
}
/*
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 7204169..761f758 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3764,6 +3764,16 @@ _copyVacuumStmt(const VacuumStmt *from)
VacuumStmt *newnode = makeNode(VacuumStmt);
COPY_SCALAR_FIELD(options);
+ COPY_NODE_FIELD(relcols);
+
+ return newnode;
+}
+
+static VacRelCols *
+_copyVacRelCols(const VacRelCols *from)
+{
+ VacRelCols *newnode = makeNode(VacRelCols);
+
COPY_NODE_FIELD(relation);
COPY_NODE_FIELD(va_cols);
@@ -5527,6 +5537,9 @@ copyObjectImpl(const void *from)
case T_PartitionCmd:
retval = _copyPartitionCmd(from);
break;
+ case T_VacRelCols:
+ retval = _copyVacRelCols(from);
+ break;
/*
* MISCELLANEOUS NODES
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 8d92c03..aea7168 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1663,6 +1663,14 @@ static bool
_equalVacuumStmt(const VacuumStmt *a, const VacuumStmt *b)
{
COMPARE_SCALAR_FIELD(options);
+ COMPARE_NODE_FIELD(relcols);
+
+ return true;
+}
+
+static bool
+_equalVacRelCols(const VacRelCols *a, const VacRelCols *b)
+{
COMPARE_NODE_FIELD(relation);
COMPARE_NODE_FIELD(va_cols);
@@ -3675,6 +3683,9 @@ equal(const void *a, const void *b)
case T_PartitionCmd:
retval = _equalPartitionCmd(a, b);
break;
+ case T_VacRelCols:
+ retval = _equalVacRelCols(a, b);
+ break;
default:
elog(ERROR, "unrecognized node type: %d",
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7d0de99..844c691 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10128,13 +10128,13 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
n->options |= VACOPT_FREEZE;
if ($4)
n->options |= VACOPT_VERBOSE;
- n->relation = NULL;
- n->va_cols = NIL;
+ n->relcols = NIL;
$$ = (Node *)n;
}
| VACUUM opt_full opt_freeze opt_verbose qualified_name
{
VacuumStmt *n = makeNode(VacuumStmt);
+ VacRelCols *relcol = makeNode(VacRelCols);
n->options = VACOPT_VACUUM;
if ($2)
n->options |= VACOPT_FULL;
@@ -10142,8 +10142,9 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
n->options |= VACOPT_FREEZE;
if ($4)
n->options |= VACOPT_VERBOSE;
- n->relation = $5;
- n->va_cols = NIL;
+ relcol->relation = $5;
+ relcol->va_cols = NIL;
+ n->relcols = list_make1(relcol);
$$ = (Node *)n;
}
| VACUUM opt_full opt_freeze opt_verbose AnalyzeStmt
@@ -10162,18 +10163,19 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
{
VacuumStmt *n = makeNode(VacuumStmt);
n->options = VACOPT_VACUUM | $3;
- n->relation = NULL;
- n->va_cols = NIL;
+ n->relcols = NIL;
$$ = (Node *) n;
}
| VACUUM '(' vacuum_option_list ')' qualified_name opt_name_list
{
VacuumStmt *n = makeNode(VacuumStmt);
+ VacRelCols *relcol = makeNode(VacRelCols);
n->options = VACOPT_VACUUM | $3;
- n->relation = $5;
- n->va_cols = $6;
- if (n->va_cols != NIL) /* implies analyze */
+ relcol->relation = $5;
+ relcol->va_cols = $6;
+ if (relcol->va_cols != NIL) /* implies analyze */
n->options |= VACOPT_ANALYZE;
+ n->relcols = list_make1(relcol);
$$ = (Node *) n;
}
;
@@ -10207,18 +10209,19 @@ AnalyzeStmt:
n->options = VACOPT_ANALYZE;
if ($2)
n->options |= VACOPT_VERBOSE;
- n->relation = NULL;
- n->va_cols = NIL;
+ n->relcols = NIL;
$$ = (Node *)n;
}
| analyze_keyword opt_verbose qualified_name opt_name_list
{
VacuumStmt *n = makeNode(VacuumStmt);
+ VacRelCols *relcol = makeNode(VacRelCols);
n->options = VACOPT_ANALYZE;
if ($2)
n->options |= VACOPT_VERBOSE;
- n->relation = $3;
- n->va_cols = $4;
+ relcol->relation = $3;
+ relcol->va_cols = $4;
+ n->relcols = list_make1(relcol);
$$ = (Node *)n;
}
;
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 27bd4f3..1c2e3a6 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -468,6 +468,7 @@ typedef enum NodeTag
T_PartitionBoundSpec,
T_PartitionRangeDatum,
T_PartitionCmd,
+ T_VacRelCols,
/*
* TAGS FOR REPLICATION GRAMMAR PARSE NODES (replnodes.h)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 5f2a4a7..3f800f1 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3090,12 +3090,18 @@ typedef enum VacuumOption
VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7 /* don't skip any pages */
} VacuumOption;
-typedef struct VacuumStmt
+typedef struct VacRelCols
{
NodeTag type;
- int options; /* OR of VacuumOption flags */
RangeVar *relation; /* single table to process, or NULL */
List *va_cols; /* list of column names, or NIL for all */
+} VacRelCols;
+
+typedef struct VacuumStmt
+{
+ NodeTag type;
+ int options; /* OR of VacuumOption flags */
+ List *relcols; /* List of VacRelCols */
} VacuumStmt;
/* ----------------------
--
2.9.2
>From 2325d0b66cfcbb15a0a9632ee2ad3c28ca3de98f Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyot...@lab.ntt.co.jp>
Date: Fri, 28 Jul 2017 15:10:04 +0900
Subject: [PATCH 2/2] Allow multiple target table of VACUUM
This patch allows VACUUM to take multiple tables.
---
doc/src/sgml/ref/vacuum.sgml | 6 +--
src/backend/parser/gram.y | 82 +++++++++++++++++++++++++++---------
src/test/regress/expected/vacuum.out | 4 +-
src/test/regress/sql/vacuum.sql | 4 +-
4 files changed, 67 insertions(+), 29 deletions(-)
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 421c18d..39cf334 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 | DISABLE_PAGE_SKIPPING } [, ...] ) ] [ <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 | DISABLE_PAGE_SKIPPING } [, ...] ) ] [ <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> [, ...] ) [, ...]] ]
</synopsis>
</refsynopsisdiv>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 844c691..3161b9e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -187,6 +187,7 @@ static void processCASbits(int cas_bits, int location, const char *constrType,
bool *deferrable, bool *initdeferred, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner);
static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
+static bool implies_analyze(List *relcols);
%}
@@ -306,6 +307,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> opt_lock lock_type cast_context
%type <ival> vacuum_option_list vacuum_option_elem
+%type <node> analyze_target_item
%type <boolean> opt_or_replace
opt_grant_grant_option opt_grant_admin_option
opt_nowait opt_if_exists opt_with_data
@@ -395,7 +397,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
relation_expr_list dostmt_opt_list
transform_element_list transform_type_list
TriggerTransitions TriggerReferencing
- publication_name_list
+ publication_name_list analyze_target_list
%type <list> group_by_list
%type <node> group_by_item empty_grouping_set rollup_clause cube_clause
@@ -3849,6 +3851,11 @@ CreateStatsStmt:
}
;
+opt_name_list:
+ '(' name_list ')' { $$ = $2; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
/*****************************************************************************
*
* QUERY :
@@ -10131,10 +10138,9 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
n->relcols = NIL;
$$ = (Node *)n;
}
- | VACUUM opt_full opt_freeze opt_verbose qualified_name
+ | VACUUM opt_full opt_freeze opt_verbose analyze_target_list
{
VacuumStmt *n = makeNode(VacuumStmt);
- VacRelCols *relcol = makeNode(VacRelCols);
n->options = VACOPT_VACUUM;
if ($2)
n->options |= VACOPT_FULL;
@@ -10142,9 +10148,9 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
n->options |= VACOPT_FREEZE;
if ($4)
n->options |= VACOPT_VERBOSE;
- relcol->relation = $5;
- relcol->va_cols = NIL;
- n->relcols = list_make1(relcol);
+ if (implies_analyze($5))
+ n->options |= VACOPT_ANALYZE;
+ n->relcols = $5;
$$ = (Node *)n;
}
| VACUUM opt_full opt_freeze opt_verbose AnalyzeStmt
@@ -10166,16 +10172,13 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
n->relcols = NIL;
$$ = (Node *) n;
}
- | VACUUM '(' vacuum_option_list ')' qualified_name opt_name_list
+ | VACUUM '(' vacuum_option_list ')' analyze_target_list
{
VacuumStmt *n = makeNode(VacuumStmt);
- VacRelCols *relcol = makeNode(VacRelCols);
n->options = VACOPT_VACUUM | $3;
- relcol->relation = $5;
- relcol->va_cols = $6;
- if (relcol->va_cols != NIL) /* implies analyze */
+ if (implies_analyze($5))
n->options |= VACOPT_ANALYZE;
- n->relcols = list_make1(relcol);
+ n->relcols = $5;
$$ = (Node *) n;
}
;
@@ -10212,16 +10215,13 @@ AnalyzeStmt:
n->relcols = NIL;
$$ = (Node *)n;
}
- | analyze_keyword opt_verbose qualified_name opt_name_list
+ | analyze_keyword opt_verbose analyze_target_list
{
VacuumStmt *n = makeNode(VacuumStmt);
- VacRelCols *relcol = makeNode(VacRelCols);
n->options = VACOPT_ANALYZE;
if ($2)
n->options |= VACOPT_VERBOSE;
- relcol->relation = $3;
- relcol->va_cols = $4;
- n->relcols = list_make1(relcol);
+ n->relcols = $3;
$$ = (Node *)n;
}
;
@@ -10244,11 +10244,33 @@ opt_freeze: FREEZE { $$ = TRUE; }
| /*EMPTY*/ { $$ = FALSE; }
;
-opt_name_list:
- '(' name_list ')' { $$ = $2; }
- | /*EMPTY*/ { $$ = NIL; }
+analyze_target_list:
+ analyze_target_item
+ {
+ $$ = list_make1($1);
+ }
+ | analyze_target_list ',' analyze_target_item
+ {
+ $$ = lappend($1, $3);
+ }
;
+analyze_target_item:
+ qualified_name
+ {
+ VacRelCols *n = makeNode(VacRelCols);
+ n->relation = $1;
+ n->va_cols = NIL;
+ $$ = (Node *)n;
+ }
+ | qualified_name '(' name_list ')'
+ {
+ VacRelCols *n = makeNode(VacRelCols);
+ n->relation = $1;
+ n->va_cols = $3;
+ $$ = (Node *)n;
+ }
+ ;
/*****************************************************************************
*
@@ -15906,6 +15928,26 @@ makeRecursiveViewSelect(char *relname, List *aliases, Node *query)
return (Node *) s;
}
+/*
+ * Retuns true if relcols implies VACOPT_ANALYZE
+ */
+static bool
+implies_analyze(List *relcols)
+{
+ ListCell *lc;
+
+ foreach (lc, relcols)
+ {
+ VacRelCols *t = (VacRelCols *) lfirst(lc);
+ Assert(IsA(t, VacRelCols));
+
+ if (t->va_cols != NIL) /* implies analyze */
+ return true;
+ }
+
+ return false;
+}
+
/* parser_init()
* Initialize to parse one query string
*/
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 6f68663..d7c8a1e 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -71,9 +71,7 @@ ANALYZE vaccluster;
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 pg_am;
-VACUUM FULL pg_class;
-VACUUM FULL pg_database;
+VACUUM FULL pg_am, pg_class, pg_database;
VACUUM FULL vaccluster;
ERROR: ANALYZE cannot be executed from VACUUM or ANALYZE
CONTEXT: SQL function "do_analyze" statement 1
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 7c5fb04..c67d019 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -54,9 +54,7 @@ CREATE INDEX ON vaccluster(wrap_do_analyze(i));
INSERT INTO vaccluster VALUES (1), (2);
ANALYZE vaccluster;
-VACUUM FULL pg_am;
-VACUUM FULL pg_class;
-VACUUM FULL pg_database;
+VACUUM FULL pg_am, pg_class, pg_database;
VACUUM FULL vaccluster;
VACUUM FULL vactst;
--
2.9.2
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
