Thread: ANALYZE command
This patch is a follow-up to the split of the vacuum and analyze (done by Bruce Momjian last year).
It adds a separate SQL extension command ANALYZE that will only perform the analyze part and thus only use shared
locks.
I will submit the doc and test changes in a separate patch, if you decide that we are really going ahead with the
ANALYZEcommand.
This version still does not update pg_class. Those statistics are still tied in the vacuum part (num. of pages, tuples
andindication if the relation has an index). The patch allows for analyze_rel() to work differently if run as part of
aVACUUM or as an standalone ANALYZE, so the missing bits can be added without changes to the current behavior of VACUUM
ANALYZE.
Possible future improvements:
1) Add the pg_class update to ANALYZE;
2) Add a VERBOSE option that prints information to gives the DBA a hint if it is worth of running VACUUM or not.
Thanks.
--
Fernando Nasser
Red Hat Inc. E-Mail: fnasser@redhat.comIndex: doc/src/sgml/indices.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/indices.sgml,v
retrieving revision 1.14
diff -c -p -r1.14 indices.sgml
*** doc/src/sgml/indices.sgml 2001/02/20 22:27:56 1.14
--- doc/src/sgml/indices.sgml 2001/04/24 14:25:05
*************** CREATE INDEX test1_id_index ON test1 (id
*** 71,78 ****
Once the index is created, no further intervention is required: the
system will use the index when it thinks it would be more efficient
than a sequential table scan. But you may have to run the
! <command>VACUUM ANALYZE</command> command regularly to update
! statistics to allow the query planner to make educated decisions.
Also read <xref linkend="performance-tips"> for information about
how to find out whether an index is used and when and why the
planner may choose to <emphasis>not</emphasis> use an index.
--- 71,79 ----
Once the index is created, no further intervention is required: the
system will use the index when it thinks it would be more efficient
than a sequential table scan. But you may have to run the
! <command>ANALYZE</command> (or <command>VACUUM ANALYZE</command>)
! command regularly to update statistics to allow the query planner
! to make educated decisions.
Also read <xref linkend="performance-tips"> for information about
how to find out whether an index is used and when and why the
planner may choose to <emphasis>not</emphasis> use an index.
Index: src/backend/commands/analyze.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/analyze.c,v
retrieving revision 1.16
diff -c -p -r1.16 analyze.c
*** src/backend/commands/analyze.c 2001/03/22 06:16:11 1.16
--- src/backend/commands/analyze.c 2001/04/24 14:25:06
***************
*** 35,41 ****
--- 35,46 ----
#include "utils/fmgroids.h"
#include "utils/inval.h"
#include "utils/syscache.h"
+ #include "utils/temprel.h"
+ static MemoryContext vac_context = NULL;
+
+ static int MESSAGE_LEVEL; /* message level */
+
#define swapLong(a,b) {long tmp; tmp=a; a=b; b=tmp;}
#define swapInt(a,b) {int tmp; tmp=a; a=b; b=tmp;}
#define swapDatum(a,b) {Datum tmp; tmp=a; a=b; b=tmp;}
*************** static void attr_stats(Relation onerel,
*** 49,61 ****
static void bucketcpy(Form_pg_attribute attr, Datum value, Datum *bucket, int *bucket_len);
static void update_attstats(Oid relid, int natts, VacAttrStats *vacattrstats);
static void del_stats(Oid relid, int attcnt, int *attnums);
/*
* analyze_rel() -- analyze relation
*/
void
! analyze_rel(Oid relid, List *anal_cols2, int MESSAGE_LEVEL)
{
HeapTuple tuple;
Relation onerel;
--- 54,175 ----
static void bucketcpy(Form_pg_attribute attr, Datum value, Datum *bucket, int *bucket_len);
static void update_attstats(Oid relid, int natts, VacAttrStats *vacattrstats);
static void del_stats(Oid relid, int attcnt, int *attnums);
+ static void do_analyze(NameData *VacRelP, List *anal_cols2);
+ static void analyze_rel_1(Oid relid, List *anal_cols2, int vacuum);
+
+
+ void
+ analyze(char *vacrel, List *anal_cols)
+ {
+ NameData VacRel;
+ Name VacRelName;
+ MemoryContext old;
+ List *le;
+ List *anal_cols2 = NIL;
+
+ /*
+ * We cannot run ANALYZE inside a user transaction block; if we were
+ * inside a transaction, then our commit- and
+ * start-transaction-command calls would not have the intended effect!
+ */
+ if (IsTransactionBlock())
+ elog(ERROR, "ANALYZE cannot run inside a BEGIN/END block");
+
+ MESSAGE_LEVEL = DEBUG;
+ /*
+ * Create special memory context for cross-transaction storage.
+ *
+ * Since it is a child of QueryContext, it will go away eventually even
+ * if we suffer an error; there's no need for special abort cleanup
+ * logic.
+ */
+ vac_context = AllocSetContextCreate(QueryContext,
+ "Analyze",
+ ALLOCSET_DEFAULT_MINSIZE,
+ ALLOCSET_DEFAULT_INITSIZE,
+ ALLOCSET_DEFAULT_MAXSIZE);
+
+ /* vacrel gets de-allocated on xact commit, so copy it to safe storage */
+ if (vacrel)
+ {
+ namestrcpy(&VacRel, vacrel);
+ VacRelName = &VacRel;
+ }
+ else
+ VacRelName = NULL;
+ /* must also copy the column list, if any, to safe storage */
+ old = MemoryContextSwitchTo(vac_context);
+ foreach(le, anal_cols)
+ {
+ char *col = (char *) lfirst(le);
+
+ anal_cols2 = lappend(anal_cols2, pstrdup(col));
+ }
+ MemoryContextSwitchTo(old);
+
+ /*
+ * Start up the analyzer.
+ *
+ * NOTE: since this commits the current transaction, the memory holding
+ * any passed-in parameters gets freed here. We must have already
+ * copied pass-by-reference parameters to safe storage. Don't make me
+ * fix this again!
+ */
+ vacuum_init();
+
+ /* analyze the database */
+ do_analyze(VacRelName, anal_cols2);
+
+ vacuum_shutdown();
+ }
+
/*
+ * do_analyze() -- analyze the database.
+ *
+ * This routine builds a list of relations to analyze, and then calls
+ * code that analyze them one at a time.
+ */
+ static void
+ do_analyze(NameData *VacRelP, List *anal_cols2)
+ {
+ VRelList vrl,
+ cur;
+
+ /* get list of relations */
+ vrl = vacuum_getrels(VacRelP);
+
+ /* analyze each heap relation */
+ for (cur = vrl; cur != (VRelList) NULL; cur = cur->vrl_next)
+ {
+ analyze_rel_1(cur->vrl_relid, anal_cols2, 0 /* not vacuum */);
+ }
+ }
+
+ /*
* analyze_rel() -- analyze relation
+ *
+ * Called through here when doing vacuum
*/
void
! analyze_rel(Oid relid, List *anal_cols2, int msg_level)
! {
! /*
! * Use the MESSAGE_LEVEL as set on vacuum.
! */
! MESSAGE_LEVEL = msg_level;
! analyze_rel_1(relid, anal_cols2, 1 /* vacuum */);
! }
!
! /*
! * analyze_rel_1() -- analyze relation
! *
! * Do the job for ANALYZE or VACUUM ANALYSE in one relation
! */
!
! static void
! analyze_rel_1(Oid relid, List *anal_cols2, int vacuum)
{
HeapTuple tuple;
Relation onerel;
*************** analyze_rel(Oid relid, List *anal_cols2,
*** 107,116 ****
{
/*
! * we already did an elog during vacuum elog(NOTICE, "Skipping
! * \"%s\" --- only table owner can VACUUM it",
! * RelationGetRelationName(onerel));
*/
heap_close(onerel, NoLock);
CommitTransactionCommand();
return;
--- 221,231 ----
{
/*
! * we already did an elog during vacuum
*/
+ if (!vacuum)
+ elog(NOTICE, "Skipping \"%s\" --- only table owner can VACUUM it",
+ RelationGetRelationName(onerel));
heap_close(onerel, NoLock);
CommitTransactionCommand();
return;
*************** analyze_rel(Oid relid, List *anal_cols2,
*** 127,133 ****
List *le;
if (length(anal_cols2) > attr_cnt)
! elog(ERROR, "vacuum: too many attributes specified for relation %s",
RelationGetRelationName(onerel));
attnums = (int *) palloc(attr_cnt * sizeof(int));
foreach(le, anal_cols2)
--- 242,248 ----
List *le;
if (length(anal_cols2) > attr_cnt)
! elog(ERROR, "analyze: too many attributes specified for relation %s",
RelationGetRelationName(onerel));
attnums = (int *) palloc(attr_cnt * sizeof(int));
foreach(le, anal_cols2)
*************** analyze_rel(Oid relid, List *anal_cols2,
*** 143,149 ****
attnums[tcnt++] = i;
else
{
! elog(ERROR, "vacuum: there is no attribute %s in %s",
col, RelationGetRelationName(onerel));
}
}
--- 258,264 ----
attnums[tcnt++] = i;
else
{
! elog(ERROR, "analyze: there is no attribute %s in %s",
col, RelationGetRelationName(onerel));
}
}
Index: src/backend/commands/vacuum.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.189
diff -c -p -r1.189 vacuum.c
*** src/backend/commands/vacuum.c 2001/03/25 23:23:58 1.189
--- src/backend/commands/vacuum.c 2001/04/24 14:25:06
*************** static int MESSAGE_LEVEL; /* message le
*** 60,69 ****
static TransactionId XmaxRecent;
/* non-export function prototypes */
- static void vacuum_init(void);
- static void vacuum_shutdown(void);
static void vac_vacuum(NameData *VacRelP, bool analyze, List *anal_cols2);
- static VRelList getrels(NameData *VacRelP);
static void vacuum_rel(Oid relid);
static void scan_heap(VRelStats *vacrelstats, Relation onerel, VacPageList vacuum_pages, VacPageList fraged_pages);
static void repair_frag(VRelStats *vacrelstats, Relation onerel, VacPageList vacuum_pages, VacPageList fraged_pages,
intnindices, Relation *Irel);
--- 60,66 ----
*************** vacuum(char *vacrel, bool verbose, bool
*** 186,199 ****
* vacuum_shutdown() to match the commit waiting for us back in
* PostgresMain().
*/
! static void
vacuum_init()
{
/* matches the StartTransaction in PostgresMain() */
CommitTransactionCommand();
}
! static void
vacuum_shutdown()
{
/* on entry, we are not in a transaction */
--- 183,196 ----
* vacuum_shutdown() to match the commit waiting for us back in
* PostgresMain().
*/
! void
vacuum_init()
{
/* matches the StartTransaction in PostgresMain() */
CommitTransactionCommand();
}
! void
vacuum_shutdown()
{
/* on entry, we are not in a transaction */
*************** vac_vacuum(NameData *VacRelP, bool analy
*** 237,243 ****
cur;
/* get list of relations */
! vrl = getrels(VacRelP);
/* vacuum each heap relation */
for (cur = vrl; cur != (VRelList) NULL; cur = cur->vrl_next)
--- 234,240 ----
cur;
/* get list of relations */
! vrl = vacuum_getrels(VacRelP);
/* vacuum each heap relation */
for (cur = vrl; cur != (VRelList) NULL; cur = cur->vrl_next)
*************** vac_vacuum(NameData *VacRelP, bool analy
*** 249,256 ****
}
}
! static VRelList
! getrels(NameData *VacRelP)
{
Relation rel;
TupleDesc tupdesc;
--- 246,253 ----
}
}
! VRelList
! vacuum_getrels(NameData *VacRelP)
{
Relation rel;
TupleDesc tupdesc;
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.140
diff -c -p -r1.140 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 2001/03/22 06:16:14 1.140
--- src/backend/nodes/copyfuncs.c 2001/04/24 14:25:07
*************** _copyVacuumStmt(VacuumStmt *from)
*** 2218,2223 ****
--- 2218,2235 ----
return newnode;
}
+ static AnalyzeStmt *
+ _copyAnalyzeStmt(AnalyzeStmt *from)
+ {
+ AnalyzeStmt *newnode = makeNode(AnalyzeStmt);
+
+ if (from->anarel)
+ newnode->anarel = pstrdup(from->anarel);
+ Node_Copy(from, newnode, va_spec);
+
+ return newnode;
+ }
+
static ExplainStmt *
_copyExplainStmt(ExplainStmt *from)
{
*************** copyObject(void *from)
*** 2780,2785 ****
--- 2792,2800 ----
break;
case T_RemoveOperStmt:
retval = _copyRemoveOperStmt(from);
+ break;
+ case T_AnalyzeStmt:
+ retval = _copyAnalyzeStmt(from);
break;
case T_RenameStmt:
retval = _copyRenameStmt(from);
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.88
diff -c -p -r1.88 equalfuncs.c
*** src/backend/nodes/equalfuncs.c 2001/03/22 03:59:31 1.88
--- src/backend/nodes/equalfuncs.c 2001/04/24 14:25:07
*************** _equalVacuumStmt(VacuumStmt *a, VacuumSt
*** 1126,1131 ****
--- 1126,1142 ----
}
static bool
+ _equalAnalyzeStmt(AnalyzeStmt *a, AnalyzeStmt *b)
+ {
+ if (!equalstr(a->anarel, b->anarel))
+ return false;
+ if (!equal(a->va_spec, b->va_spec))
+ return false;
+
+ return true;
+ }
+
+ static bool
_equalExplainStmt(ExplainStmt *a, ExplainStmt *b)
{
if (!equal(a->query, b->query))
*************** equal(void *a, void *b)
*** 1946,1951 ****
--- 1957,1965 ----
break;
case T_RemoveOperStmt:
retval = _equalRemoveOperStmt(a, b);
+ break;
+ case T_AnalyzeStmt:
+ retval = _equalAnalyzeStmt(a, b);
break;
case T_RenameStmt:
retval = _equalRenameStmt(a, b);
Index: src/backend/parser/gram.y
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.221
diff -c -p -r2.221 gram.y
*** src/backend/parser/gram.y 2001/02/18 18:06:10 2.221
--- src/backend/parser/gram.y 2001/04/24 14:25:12
*************** static void doNegateFloat(Value *v);
*** 130,135 ****
--- 130,136 ----
%type <node> stmt,
AlterGroupStmt, AlterSchemaStmt, AlterTableStmt, AlterUserStmt,
+ AnalyzeStmt, CheckPointStmt,
ClosePortalStmt, ClusterStmt, CommentStmt, ConstraintsSetStmt,
CopyStmt, CreateAsStmt, CreateGroupStmt, CreatePLangStmt,
CreateSchemaStmt, CreateSeqStmt, CreateStmt, CreateTrigStmt,
*************** static void doNegateFloat(Value *v);
*** 142,148 ****
RenameStmt, RevokeStmt, RuleActionStmt, RuleActionStmtOrEmpty,
RuleStmt, SelectStmt, TransactionStmt, TruncateStmt,
UnlistenStmt, UpdateStmt, VacuumStmt, VariableResetStmt,
! VariableSetStmt, VariableShowStmt, ViewStmt, CheckPointStmt
%type <node> select_no_parens, select_with_parens, select_clause,
simple_select
--- 143,149 ----
RenameStmt, RevokeStmt, RuleActionStmt, RuleActionStmtOrEmpty,
RuleStmt, SelectStmt, TransactionStmt, TruncateStmt,
UnlistenStmt, UpdateStmt, VacuumStmt, VariableResetStmt,
! VariableSetStmt, VariableShowStmt, ViewStmt
%type <node> select_no_parens, select_with_parens, select_clause,
simple_select
*************** stmt : AlterSchemaStmt
*** 426,431 ****
--- 427,433 ----
| AlterTableStmt
| AlterGroupStmt
| AlterUserStmt
+ | AnalyzeStmt
| ClosePortalStmt
| CopyStmt
| CreateStmt
*************** va_list: name
*** 3037,3042 ****
--- 3039,3074 ----
{ $$ = makeList1($1); }
| va_list ',' name
{ $$ = lappend($1, $3); }
+ ;
+
+
+ /*****************************************************************************
+ *
+ * QUERY:
+ * analyze
+ *
+ *****************************************************************************/
+
+ /* We use opt_va_list and va_list defined for vacuum. */
+
+ AnalyzeStmt: ANALYZE
+ {
+ AnalyzeStmt *n = makeNode(AnalyzeStmt);
+ n->anarel = NULL;
+ n->va_spec = NIL;
+ $$ = (Node *)n;
+ }
+ | ANALYZE relation_name opt_va_list
+ {
+ AnalyzeStmt *n = makeNode(AnalyzeStmt);
+ n->anarel = $2;
+ n->va_spec = $3;
+ if ( $3 != NIL && !$2 )
+ elog(ERROR,"ANALYZE syntax error at or near \"(\""
+ "\n\tRelation name must be specified");
+
+ $$ = (Node *)n;
+ }
;
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.109
diff -c -p -r1.109 utility.c
*** src/backend/tcop/utility.c 2001/03/22 06:16:17 1.109
--- src/backend/tcop/utility.c 2001/04/24 14:25:12
*************** ProcessUtility(Node *parsetree,
*** 711,716 ****
--- 711,723 ----
((VacuumStmt *) parsetree)->va_spec);
break;
+ case T_AnalyzeStmt:
+ set_ps_display(commandTag = "ANALYZE");
+
+ analyze(((AnalyzeStmt *) parsetree)->anarel,
+ ((AnalyzeStmt *) parsetree)->va_spec);
+ break;
+
case T_ExplainStmt:
{
ExplainStmt *stmt = (ExplainStmt *) parsetree;
Index: src/include/commands/vacuum.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/commands/vacuum.h,v
retrieving revision 1.34
diff -c -p -r1.34 vacuum.h
*** src/include/commands/vacuum.h 2001/03/22 04:00:43 1.34
--- src/include/commands/vacuum.h 2001/04/24 14:25:12
*************** extern bool VacuumRunning;
*** 122,127 ****
--- 122,131 ----
extern void vc_abort(void);
extern void vacuum(char *vacrel, bool verbose, bool analyze, List *anal_cols);
+ extern void vacuum_init(void);
+ extern void vacuum_shutdown(void);
+ extern VRelList vacuum_getrels(NameData *VacRelP);
+ extern void analyze(char *vacrel, List *anal_cols);
extern void analyze_rel(Oid relid, List *anal_cols2, int MESSAGE_LEVEL);
#define ATTNVALS_SCALE 1000000000 /* XXX so it can act as a float4 */
Index: src/include/nodes/nodes.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/nodes/nodes.h,v
retrieving revision 1.88
diff -c -p -r1.88 nodes.h
*** src/include/nodes/nodes.h 2001/03/22 06:16:20 1.88
--- src/include/nodes/nodes.h 2001/04/24 14:25:12
*************** typedef enum NodeTag
*** 165,172 ****
T_RemoveAggrStmt,
T_RemoveFuncStmt,
T_RemoveOperStmt,
! T_RemoveStmt_XXX, /* not used anymore; this tag# is
! * available */
T_RenameStmt,
T_RuleStmt,
T_NotifyStmt,
--- 165,171 ----
T_RemoveAggrStmt,
T_RemoveFuncStmt,
T_RemoveOperStmt,
! T_AnalyzeStmt,
T_RenameStmt,
T_RuleStmt,
T_NotifyStmt,
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.126
diff -c -p -r1.126 parsenodes.h
*** src/include/nodes/parsenodes.h 2001/03/23 04:49:56 1.126
--- src/include/nodes/parsenodes.h 2001/04/24 14:25:13
*************** typedef struct VacuumStmt
*** 703,708 ****
--- 703,719 ----
} VacuumStmt;
/* ----------------------
+ * Analyze Statement
+ * ----------------------
+ */
+ typedef struct AnalyzeStmt
+ {
+ NodeTag type;
+ char *anarel; /* table to analyze */
+ List *va_spec; /* columns to analyse */
+ } AnalyzeStmt;
+
+ /* ----------------------
* Explain Statement
* ----------------------
*/
Index: src/interfaces/ecpg/preproc/preproc.y
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/ecpg/preproc/preproc.y,v
retrieving revision 1.136
diff -c -p -r1.136 preproc.y
*** src/interfaces/ecpg/preproc/preproc.y 2001/04/05 08:21:14 1.136
--- src/interfaces/ecpg/preproc/preproc.y 2001/04/24 14:25:16
*************** make_name(void)
*** 302,308 ****
%type <str> copy_delimiter ListenStmt CopyStmt copy_file_name opt_binary
%type <str> opt_with_copy FetchStmt direction fetch_how_many from_in
%type <str> ClosePortalStmt DropStmt VacuumStmt opt_verbose func_arg
! %type <str> opt_analyze opt_va_list va_list ExplainStmt index_params
%type <str> index_list func_index index_elem opt_class access_method_clause
%type <str> index_opt_unique IndexStmt func_return ConstInterval
%type <str> func_args_list func_args opt_with ProcedureStmt def_arg
--- 302,308 ----
%type <str> copy_delimiter ListenStmt CopyStmt copy_file_name opt_binary
%type <str> opt_with_copy FetchStmt direction fetch_how_many from_in
%type <str> ClosePortalStmt DropStmt VacuumStmt opt_verbose func_arg
! %type <str> opt_analyze opt_va_list va_list AnalyzeStmt ExplainStmt index_params
%type <str> index_list func_index index_elem opt_class access_method_clause
%type <str> index_opt_unique IndexStmt func_return ConstInterval
%type <str> func_args_list func_args opt_with ProcedureStmt def_arg
*************** stmt: AlterSchemaStmt { output_state
*** 447,452 ****
--- 447,453 ----
| CreatedbStmt { output_statement($1, 0, NULL, connection); }
| DropdbStmt { output_statement($1, 0, NULL, connection); }
| VacuumStmt { output_statement($1, 0, NULL, connection); }
+ | AnalyzeStmt { output_statement($1, 0, NULL, connection); }
| VariableSetStmt { output_statement($1, 0, NULL, connection); }
| VariableShowStmt { output_statement($1, 0, NULL, connection); }
| VariableResetStmt { output_statement($1, 0, NULL, connection); }
*************** va_list: name
*** 2282,2287 ****
--- 2283,2310 ----
{ $$=$1; }
| va_list ',' name
{ $$=cat_str(3, $1, make_str(","), $3); }
+ ;
+
+
+ /*****************************************************************************
+ *
+ * QUERY:
+ * analyze
+ *
+ *****************************************************************************/
+
+ /* We use opt_va_list and va_list defined for vacuum. */
+
+ AnalyzeStmt: ANALYZE
+ {
+ $$ = cat_str(1, make_str("analyze"));
+ }
+ | ANALYZE relation_name opt_va_list
+ {
+ if ( strlen($3) > 0 && strlen($2) == 0 )
+ mmerror(ET_ERROR, "ANALYZE syntax error at or near \"(\"\n\tRelations name must be
specified");
+ $$ = cat_str(3, make_str("analyze"), $2, $3);
+ }
;
[My apologies: I forgot to tell to which sources the patch applies, as required and, which is worse, I attached the
wrongpatch; I have corrected it now.]
This patch is a follow-up to the split of the vacuum and analyze (done by Bruce Momjian last year). I applies to
today'sCVS sources.
It adds a separate SQL extension command ANALYZE that will only perform the analyze part and thus only use shared
locks.
I will submit the doc and test changes in a separate patch, if you decide that we are really going ahead with the
ANALYZEcommand.
This version still does not update pg_class. Those statistics are still tied in the vacuum part (num. of pages, tuples
andindication if the relation has an index). The patch allows for analyze_rel() to work differently if run as part of
aVACUUM or as an standalone ANALYZE, so the missing bits can be added without changes to the current
behavior of VACUUM ANALYZE.
Possible future improvements:
1) Add the pg_class update to ANALYZE;
2) Add a VERBOSE option that prints information to gives the DBA a hint if it is worth of running VACUUM or not.
Thanks.
--
Fernando Nasser
Red Hat Inc. E-Mail: fnasser@redhat.comIndex: src/backend/commands/analyze.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/analyze.c,v
retrieving revision 1.16
diff -c -p -r1.16 analyze.c
*** src/backend/commands/analyze.c 2001/03/22 06:16:11 1.16
--- src/backend/commands/analyze.c 2001/04/24 22:22:24
***************
*** 35,41 ****
--- 35,46 ----
#include "utils/fmgroids.h"
#include "utils/inval.h"
#include "utils/syscache.h"
+ #include "utils/temprel.h"
+ static MemoryContext vac_context = NULL;
+
+ static int MESSAGE_LEVEL; /* message level */
+
#define swapLong(a,b) {long tmp; tmp=a; a=b; b=tmp;}
#define swapInt(a,b) {int tmp; tmp=a; a=b; b=tmp;}
#define swapDatum(a,b) {Datum tmp; tmp=a; a=b; b=tmp;}
*************** static void attr_stats(Relation onerel,
*** 49,61 ****
static void bucketcpy(Form_pg_attribute attr, Datum value, Datum *bucket, int *bucket_len);
static void update_attstats(Oid relid, int natts, VacAttrStats *vacattrstats);
static void del_stats(Oid relid, int attcnt, int *attnums);
/*
* analyze_rel() -- analyze relation
*/
void
! analyze_rel(Oid relid, List *anal_cols2, int MESSAGE_LEVEL)
{
HeapTuple tuple;
Relation onerel;
--- 54,175 ----
static void bucketcpy(Form_pg_attribute attr, Datum value, Datum *bucket, int *bucket_len);
static void update_attstats(Oid relid, int natts, VacAttrStats *vacattrstats);
static void del_stats(Oid relid, int attcnt, int *attnums);
+ static void do_analyze(NameData *VacRelP, List *anal_cols2);
+ static void analyze_rel_1(Oid relid, List *anal_cols2, int vacuum);
+
+
+ void
+ analyze(char *vacrel, List *anal_cols)
+ {
+ NameData VacRel;
+ Name VacRelName;
+ MemoryContext old;
+ List *le;
+ List *anal_cols2 = NIL;
+
+ /*
+ * We cannot run ANALYZE inside a user transaction block; if we were
+ * inside a transaction, then our commit- and
+ * start-transaction-command calls would not have the intended effect!
+ */
+ if (IsTransactionBlock())
+ elog(ERROR, "ANALYZE cannot run inside a BEGIN/END block");
+
+ MESSAGE_LEVEL = DEBUG;
+ /*
+ * Create special memory context for cross-transaction storage.
+ *
+ * Since it is a child of QueryContext, it will go away eventually even
+ * if we suffer an error; there's no need for special abort cleanup
+ * logic.
+ */
+ vac_context = AllocSetContextCreate(QueryContext,
+ "Analyze",
+ ALLOCSET_DEFAULT_MINSIZE,
+ ALLOCSET_DEFAULT_INITSIZE,
+ ALLOCSET_DEFAULT_MAXSIZE);
+
+ /* vacrel gets de-allocated on xact commit, so copy it to safe storage */
+ if (vacrel)
+ {
+ namestrcpy(&VacRel, vacrel);
+ VacRelName = &VacRel;
+ }
+ else
+ VacRelName = NULL;
+ /* must also copy the column list, if any, to safe storage */
+ old = MemoryContextSwitchTo(vac_context);
+ foreach(le, anal_cols)
+ {
+ char *col = (char *) lfirst(le);
+
+ anal_cols2 = lappend(anal_cols2, pstrdup(col));
+ }
+ MemoryContextSwitchTo(old);
+
+ /*
+ * Start up the analyzer.
+ *
+ * NOTE: since this commits the current transaction, the memory holding
+ * any passed-in parameters gets freed here. We must have already
+ * copied pass-by-reference parameters to safe storage. Don't make me
+ * fix this again!
+ */
+ vacuum_init();
+
+ /* analyze the database */
+ do_analyze(VacRelName, anal_cols2);
+
+ vacuum_shutdown(vac_context);
+ }
+
/*
+ * do_analyze() -- analyze the database.
+ *
+ * This routine builds a list of relations to analyze, and then calls
+ * code that analyze them one at a time.
+ */
+ static void
+ do_analyze(NameData *VacRelP, List *anal_cols2)
+ {
+ VRelList vrl,
+ cur;
+
+ /* get list of relations */
+ vrl = vacuum_getrels(VacRelP, vac_context);
+
+ /* analyze each heap relation */
+ for (cur = vrl; cur != (VRelList) NULL; cur = cur->vrl_next)
+ {
+ analyze_rel_1(cur->vrl_relid, anal_cols2, 0 /* not vacuum */);
+ }
+ }
+
+ /*
* analyze_rel() -- analyze relation
+ *
+ * Called through here when doing vacuum
*/
void
! analyze_rel(Oid relid, List *anal_cols2, int msg_level)
! {
! /*
! * Use the MESSAGE_LEVEL as set on vacuum.
! */
! MESSAGE_LEVEL = msg_level;
! analyze_rel_1(relid, anal_cols2, 1 /* vacuum */);
! }
!
! /*
! * analyze_rel_1() -- analyze relation
! *
! * Do the job for ANALYZE or VACUUM ANALYSE in one relation
! */
!
! static void
! analyze_rel_1(Oid relid, List *anal_cols2, int vacuum)
{
HeapTuple tuple;
Relation onerel;
*************** analyze_rel(Oid relid, List *anal_cols2,
*** 107,116 ****
{
/*
! * we already did an elog during vacuum elog(NOTICE, "Skipping
! * \"%s\" --- only table owner can VACUUM it",
! * RelationGetRelationName(onerel));
*/
heap_close(onerel, NoLock);
CommitTransactionCommand();
return;
--- 221,231 ----
{
/*
! * we already did an elog during vacuum
*/
+ if (!vacuum)
+ elog(NOTICE, "Skipping \"%s\" --- only table owner can ANALYZE it",
+ RelationGetRelationName(onerel));
heap_close(onerel, NoLock);
CommitTransactionCommand();
return;
*************** analyze_rel(Oid relid, List *anal_cols2,
*** 127,133 ****
List *le;
if (length(anal_cols2) > attr_cnt)
! elog(ERROR, "vacuum: too many attributes specified for relation %s",
RelationGetRelationName(onerel));
attnums = (int *) palloc(attr_cnt * sizeof(int));
foreach(le, anal_cols2)
--- 242,248 ----
List *le;
if (length(anal_cols2) > attr_cnt)
! elog(ERROR, "analyze: too many attributes specified for relation %s",
RelationGetRelationName(onerel));
attnums = (int *) palloc(attr_cnt * sizeof(int));
foreach(le, anal_cols2)
*************** analyze_rel(Oid relid, List *anal_cols2,
*** 143,149 ****
attnums[tcnt++] = i;
else
{
! elog(ERROR, "vacuum: there is no attribute %s in %s",
col, RelationGetRelationName(onerel));
}
}
--- 258,264 ----
attnums[tcnt++] = i;
else
{
! elog(ERROR, "analyze: there is no attribute %s in %s",
col, RelationGetRelationName(onerel));
}
}
Index: src/backend/commands/vacuum.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.189
diff -c -p -r1.189 vacuum.c
*** src/backend/commands/vacuum.c 2001/03/25 23:23:58 1.189
--- src/backend/commands/vacuum.c 2001/04/24 22:22:25
*************** static int MESSAGE_LEVEL; /* message le
*** 60,69 ****
static TransactionId XmaxRecent;
/* non-export function prototypes */
- static void vacuum_init(void);
- static void vacuum_shutdown(void);
static void vac_vacuum(NameData *VacRelP, bool analyze, List *anal_cols2);
- static VRelList getrels(NameData *VacRelP);
static void vacuum_rel(Oid relid);
static void scan_heap(VRelStats *vacrelstats, Relation onerel, VacPageList vacuum_pages, VacPageList fraged_pages);
static void repair_frag(VRelStats *vacrelstats, Relation onerel, VacPageList vacuum_pages, VacPageList fraged_pages,
intnindices, Relation *Irel);
--- 60,66 ----
*************** vacuum(char *vacrel, bool verbose, bool
*** 163,169 ****
vac_vacuum(VacRelName, analyze, anal_cols2);
/* clean up */
! vacuum_shutdown();
}
/*
--- 160,166 ----
vac_vacuum(VacRelName, analyze, anal_cols2);
/* clean up */
! vacuum_shutdown(vac_context);
}
/*
*************** vacuum(char *vacrel, bool verbose, bool
*** 186,200 ****
* vacuum_shutdown() to match the commit waiting for us back in
* PostgresMain().
*/
! static void
vacuum_init()
{
/* matches the StartTransaction in PostgresMain() */
CommitTransactionCommand();
}
! static void
! vacuum_shutdown()
{
/* on entry, we are not in a transaction */
--- 183,197 ----
* vacuum_shutdown() to match the commit waiting for us back in
* PostgresMain().
*/
! void
vacuum_init()
{
/* matches the StartTransaction in PostgresMain() */
CommitTransactionCommand();
}
! void
! vacuum_shutdown(MemoryContext context)
{
/* on entry, we are not in a transaction */
*************** vacuum_shutdown()
*** 218,224 ****
* StartTransactionCommand, else we might be trying to delete the
* active context!
*/
! MemoryContextDelete(vac_context);
vac_context = NULL;
}
--- 215,221 ----
* StartTransactionCommand, else we might be trying to delete the
* active context!
*/
! MemoryContextDelete(context);
vac_context = NULL;
}
*************** vac_vacuum(NameData *VacRelP, bool analy
*** 237,243 ****
cur;
/* get list of relations */
! vrl = getrels(VacRelP);
/* vacuum each heap relation */
for (cur = vrl; cur != (VRelList) NULL; cur = cur->vrl_next)
--- 234,240 ----
cur;
/* get list of relations */
! vrl = vacuum_getrels(VacRelP, vac_context);
/* vacuum each heap relation */
for (cur = vrl; cur != (VRelList) NULL; cur = cur->vrl_next)
*************** vac_vacuum(NameData *VacRelP, bool analy
*** 249,256 ****
}
}
! static VRelList
! getrels(NameData *VacRelP)
{
Relation rel;
TupleDesc tupdesc;
--- 246,253 ----
}
}
! VRelList
! vacuum_getrels(NameData *VacRelP, MemoryContext context)
{
Relation rel;
TupleDesc tupdesc;
*************** getrels(NameData *VacRelP)
*** 317,327 ****
/* get a relation list entry for this guy */
if (vrl == (VRelList) NULL)
vrl = cur = (VRelList)
! MemoryContextAlloc(vac_context, sizeof(VRelListData));
else
{
cur->vrl_next = (VRelList)
! MemoryContextAlloc(vac_context, sizeof(VRelListData));
cur = cur->vrl_next;
}
--- 314,324 ----
/* get a relation list entry for this guy */
if (vrl == (VRelList) NULL)
vrl = cur = (VRelList)
! MemoryContextAlloc(context, sizeof(VRelListData));
else
{
cur->vrl_next = (VRelList)
! MemoryContextAlloc(context, sizeof(VRelListData));
cur = cur->vrl_next;
}
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.140
diff -c -p -r1.140 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 2001/03/22 06:16:14 1.140
--- src/backend/nodes/copyfuncs.c 2001/04/24 22:22:25
*************** _copyVacuumStmt(VacuumStmt *from)
*** 2218,2223 ****
--- 2218,2235 ----
return newnode;
}
+ static AnalyzeStmt *
+ _copyAnalyzeStmt(AnalyzeStmt *from)
+ {
+ AnalyzeStmt *newnode = makeNode(AnalyzeStmt);
+
+ if (from->anarel)
+ newnode->anarel = pstrdup(from->anarel);
+ Node_Copy(from, newnode, va_spec);
+
+ return newnode;
+ }
+
static ExplainStmt *
_copyExplainStmt(ExplainStmt *from)
{
*************** copyObject(void *from)
*** 2780,2785 ****
--- 2792,2800 ----
break;
case T_RemoveOperStmt:
retval = _copyRemoveOperStmt(from);
+ break;
+ case T_AnalyzeStmt:
+ retval = _copyAnalyzeStmt(from);
break;
case T_RenameStmt:
retval = _copyRenameStmt(from);
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.88
diff -c -p -r1.88 equalfuncs.c
*** src/backend/nodes/equalfuncs.c 2001/03/22 03:59:31 1.88
--- src/backend/nodes/equalfuncs.c 2001/04/24 22:22:25
*************** _equalVacuumStmt(VacuumStmt *a, VacuumSt
*** 1126,1131 ****
--- 1126,1142 ----
}
static bool
+ _equalAnalyzeStmt(AnalyzeStmt *a, AnalyzeStmt *b)
+ {
+ if (!equalstr(a->anarel, b->anarel))
+ return false;
+ if (!equal(a->va_spec, b->va_spec))
+ return false;
+
+ return true;
+ }
+
+ static bool
_equalExplainStmt(ExplainStmt *a, ExplainStmt *b)
{
if (!equal(a->query, b->query))
*************** equal(void *a, void *b)
*** 1946,1951 ****
--- 1957,1965 ----
break;
case T_RemoveOperStmt:
retval = _equalRemoveOperStmt(a, b);
+ break;
+ case T_AnalyzeStmt:
+ retval = _equalAnalyzeStmt(a, b);
break;
case T_RenameStmt:
retval = _equalRenameStmt(a, b);
Index: src/backend/parser/gram.y
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.221
diff -c -p -r2.221 gram.y
*** src/backend/parser/gram.y 2001/02/18 18:06:10 2.221
--- src/backend/parser/gram.y 2001/04/24 22:22:26
*************** static void doNegateFloat(Value *v);
*** 130,135 ****
--- 130,136 ----
%type <node> stmt,
AlterGroupStmt, AlterSchemaStmt, AlterTableStmt, AlterUserStmt,
+ AnalyzeStmt, CheckPointStmt,
ClosePortalStmt, ClusterStmt, CommentStmt, ConstraintsSetStmt,
CopyStmt, CreateAsStmt, CreateGroupStmt, CreatePLangStmt,
CreateSchemaStmt, CreateSeqStmt, CreateStmt, CreateTrigStmt,
*************** static void doNegateFloat(Value *v);
*** 142,148 ****
RenameStmt, RevokeStmt, RuleActionStmt, RuleActionStmtOrEmpty,
RuleStmt, SelectStmt, TransactionStmt, TruncateStmt,
UnlistenStmt, UpdateStmt, VacuumStmt, VariableResetStmt,
! VariableSetStmt, VariableShowStmt, ViewStmt, CheckPointStmt
%type <node> select_no_parens, select_with_parens, select_clause,
simple_select
--- 143,149 ----
RenameStmt, RevokeStmt, RuleActionStmt, RuleActionStmtOrEmpty,
RuleStmt, SelectStmt, TransactionStmt, TruncateStmt,
UnlistenStmt, UpdateStmt, VacuumStmt, VariableResetStmt,
! VariableSetStmt, VariableShowStmt, ViewStmt
%type <node> select_no_parens, select_with_parens, select_clause,
simple_select
*************** stmt : AlterSchemaStmt
*** 426,431 ****
--- 427,433 ----
| AlterTableStmt
| AlterGroupStmt
| AlterUserStmt
+ | AnalyzeStmt
| ClosePortalStmt
| CopyStmt
| CreateStmt
*************** va_list: name
*** 3037,3042 ****
--- 3039,3074 ----
{ $$ = makeList1($1); }
| va_list ',' name
{ $$ = lappend($1, $3); }
+ ;
+
+
+ /*****************************************************************************
+ *
+ * QUERY:
+ * analyze
+ *
+ *****************************************************************************/
+
+ /* We use opt_va_list and va_list defined for vacuum. */
+
+ AnalyzeStmt: ANALYZE
+ {
+ AnalyzeStmt *n = makeNode(AnalyzeStmt);
+ n->anarel = NULL;
+ n->va_spec = NIL;
+ $$ = (Node *)n;
+ }
+ | ANALYZE relation_name opt_va_list
+ {
+ AnalyzeStmt *n = makeNode(AnalyzeStmt);
+ n->anarel = $2;
+ n->va_spec = $3;
+ if ( $3 != NIL && !$2 )
+ elog(ERROR,"ANALYZE syntax error at or near \"(\""
+ "\n\tRelation name must be specified");
+
+ $$ = (Node *)n;
+ }
;
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.109
diff -c -p -r1.109 utility.c
*** src/backend/tcop/utility.c 2001/03/22 06:16:17 1.109
--- src/backend/tcop/utility.c 2001/04/24 22:22:26
*************** ProcessUtility(Node *parsetree,
*** 711,716 ****
--- 711,723 ----
((VacuumStmt *) parsetree)->va_spec);
break;
+ case T_AnalyzeStmt:
+ set_ps_display(commandTag = "ANALYZE");
+
+ analyze(((AnalyzeStmt *) parsetree)->anarel,
+ ((AnalyzeStmt *) parsetree)->va_spec);
+ break;
+
case T_ExplainStmt:
{
ExplainStmt *stmt = (ExplainStmt *) parsetree;
Index: src/include/commands/vacuum.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/commands/vacuum.h,v
retrieving revision 1.34
diff -c -p -r1.34 vacuum.h
*** src/include/commands/vacuum.h 2001/03/22 04:00:43 1.34
--- src/include/commands/vacuum.h 2001/04/24 22:22:29
*************** extern bool VacuumRunning;
*** 122,127 ****
--- 122,131 ----
extern void vc_abort(void);
extern void vacuum(char *vacrel, bool verbose, bool analyze, List *anal_cols);
+ extern void vacuum_init(void);
+ extern void vacuum_shutdown(MemoryContext vac_context);
+ extern VRelList vacuum_getrels(NameData *VacRelP, MemoryContext vac_context);
+ extern void analyze(char *vacrel, List *anal_cols);
extern void analyze_rel(Oid relid, List *anal_cols2, int MESSAGE_LEVEL);
#define ATTNVALS_SCALE 1000000000 /* XXX so it can act as a float4 */
Index: src/include/nodes/nodes.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/nodes/nodes.h,v
retrieving revision 1.89
diff -c -p -r1.89 nodes.h
*** src/include/nodes/nodes.h 2001/04/24 00:08:38 1.89
--- src/include/nodes/nodes.h 2001/04/24 22:22:29
*************** typedef enum NodeTag
*** 165,171 ****
T_RemoveAggrStmt,
T_RemoveFuncStmt,
T_RemoveOperStmt,
! T_RemoveStmt_XXX, /* not used anymore; tag# available */
T_RenameStmt,
T_RuleStmt,
T_NotifyStmt,
--- 165,171 ----
T_RemoveAggrStmt,
T_RemoveFuncStmt,
T_RemoveOperStmt,
! T_AnalyzeStmt,
T_RenameStmt,
T_RuleStmt,
T_NotifyStmt,
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.126
diff -c -p -r1.126 parsenodes.h
*** src/include/nodes/parsenodes.h 2001/03/23 04:49:56 1.126
--- src/include/nodes/parsenodes.h 2001/04/24 22:22:29
*************** typedef struct VacuumStmt
*** 703,708 ****
--- 703,719 ----
} VacuumStmt;
/* ----------------------
+ * Analyze Statement
+ * ----------------------
+ */
+ typedef struct AnalyzeStmt
+ {
+ NodeTag type;
+ char *anarel; /* table to analyze */
+ List *va_spec; /* columns to analyse */
+ } AnalyzeStmt;
+
+ /* ----------------------
* Explain Statement
* ----------------------
*/
Index: src/interfaces/ecpg/preproc/preproc.y
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/ecpg/preproc/preproc.y,v
retrieving revision 1.136
diff -c -p -r1.136 preproc.y
*** src/interfaces/ecpg/preproc/preproc.y 2001/04/05 08:21:14 1.136
--- src/interfaces/ecpg/preproc/preproc.y 2001/04/24 22:22:30
*************** make_name(void)
*** 302,308 ****
%type <str> copy_delimiter ListenStmt CopyStmt copy_file_name opt_binary
%type <str> opt_with_copy FetchStmt direction fetch_how_many from_in
%type <str> ClosePortalStmt DropStmt VacuumStmt opt_verbose func_arg
! %type <str> opt_analyze opt_va_list va_list ExplainStmt index_params
%type <str> index_list func_index index_elem opt_class access_method_clause
%type <str> index_opt_unique IndexStmt func_return ConstInterval
%type <str> func_args_list func_args opt_with ProcedureStmt def_arg
--- 302,308 ----
%type <str> copy_delimiter ListenStmt CopyStmt copy_file_name opt_binary
%type <str> opt_with_copy FetchStmt direction fetch_how_many from_in
%type <str> ClosePortalStmt DropStmt VacuumStmt opt_verbose func_arg
! %type <str> opt_analyze opt_va_list va_list AnalyzeStmt ExplainStmt index_params
%type <str> index_list func_index index_elem opt_class access_method_clause
%type <str> index_opt_unique IndexStmt func_return ConstInterval
%type <str> func_args_list func_args opt_with ProcedureStmt def_arg
*************** stmt: AlterSchemaStmt { output_state
*** 447,452 ****
--- 447,453 ----
| CreatedbStmt { output_statement($1, 0, NULL, connection); }
| DropdbStmt { output_statement($1, 0, NULL, connection); }
| VacuumStmt { output_statement($1, 0, NULL, connection); }
+ | AnalyzeStmt { output_statement($1, 0, NULL, connection); }
| VariableSetStmt { output_statement($1, 0, NULL, connection); }
| VariableShowStmt { output_statement($1, 0, NULL, connection); }
| VariableResetStmt { output_statement($1, 0, NULL, connection); }
*************** va_list: name
*** 2282,2287 ****
--- 2283,2310 ----
{ $$=$1; }
| va_list ',' name
{ $$=cat_str(3, $1, make_str(","), $3); }
+ ;
+
+
+ /*****************************************************************************
+ *
+ * QUERY:
+ * analyze
+ *
+ *****************************************************************************/
+
+ /* We use opt_va_list and va_list defined for vacuum. */
+
+ AnalyzeStmt: ANALYZE
+ {
+ $$ = cat_str(1, make_str("analyze"));
+ }
+ | ANALYZE relation_name opt_va_list
+ {
+ if ( strlen($3) > 0 && strlen($2) == 0 )
+ mmerror(ET_ERROR, "ANALYZE syntax error at or near \"(\"\n\tRelations name must be
specified");
+ $$ = cat_str(3, make_str("analyze"), $2, $3);
+ }
;
I will keep this for possible inclusing in 7.2. Thanks.
> [My apologies: I forgot to tell to which sources the patch applies, as required and, which is worse, I attached the
wrongpatch; I have corrected it now.]
>
>
> This patch is a follow-up to the split of the vacuum and analyze (done by Bruce Momjian last year). I applies to
today'sCVS sources.
>
> It adds a separate SQL extension command ANALYZE that will only perform the analyze part and thus only use shared
locks.
>
> I will submit the doc and test changes in a separate patch, if you decide that we are really going ahead with the
ANALYZEcommand.
>
> This version still does not update pg_class. Those statistics are still tied in the vacuum part (num. of pages,
tuplesand indication if the relation has an index). The patch allows for analyze_rel() to work differently if run as
partof a VACUUM or as an standalone ANALYZE, so the missing bits can be added without changes to the current
> behavior of VACUUM ANALYZE.
>
> Possible future improvements:
>
> 1) Add the pg_class update to ANALYZE;
>
> 2) Add a VERBOSE option that prints information to gives the DBA a hint if it is worth of running VACUUM or not.
>
> Thanks.
>
>
> --
> Fernando Nasser
> Red Hat Inc. E-Mail: fnasser@redhat.com
> Index: src/backend/commands/analyze.c
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/analyze.c,v
> retrieving revision 1.16
> diff -c -p -r1.16 analyze.c
> *** src/backend/commands/analyze.c 2001/03/22 06:16:11 1.16
> --- src/backend/commands/analyze.c 2001/04/24 22:22:24
> ***************
> *** 35,41 ****
> --- 35,46 ----
> #include "utils/fmgroids.h"
> #include "utils/inval.h"
> #include "utils/syscache.h"
> + #include "utils/temprel.h"
>
> + static MemoryContext vac_context = NULL;
> +
> + static int MESSAGE_LEVEL; /* message level */
> +
> #define swapLong(a,b) {long tmp; tmp=a; a=b; b=tmp;}
> #define swapInt(a,b) {int tmp; tmp=a; a=b; b=tmp;}
> #define swapDatum(a,b) {Datum tmp; tmp=a; a=b; b=tmp;}
> *************** static void attr_stats(Relation onerel,
> *** 49,61 ****
> static void bucketcpy(Form_pg_attribute attr, Datum value, Datum *bucket, int *bucket_len);
> static void update_attstats(Oid relid, int natts, VacAttrStats *vacattrstats);
> static void del_stats(Oid relid, int attcnt, int *attnums);
>
>
> /*
> * analyze_rel() -- analyze relation
> */
> void
> ! analyze_rel(Oid relid, List *anal_cols2, int MESSAGE_LEVEL)
> {
> HeapTuple tuple;
> Relation onerel;
> --- 54,175 ----
> static void bucketcpy(Form_pg_attribute attr, Datum value, Datum *bucket, int *bucket_len);
> static void update_attstats(Oid relid, int natts, VacAttrStats *vacattrstats);
> static void del_stats(Oid relid, int attcnt, int *attnums);
> + static void do_analyze(NameData *VacRelP, List *anal_cols2);
> + static void analyze_rel_1(Oid relid, List *anal_cols2, int vacuum);
> +
> +
> + void
> + analyze(char *vacrel, List *anal_cols)
> + {
> + NameData VacRel;
> + Name VacRelName;
> + MemoryContext old;
> + List *le;
> + List *anal_cols2 = NIL;
> +
> + /*
> + * We cannot run ANALYZE inside a user transaction block; if we were
> + * inside a transaction, then our commit- and
> + * start-transaction-command calls would not have the intended effect!
> + */
> + if (IsTransactionBlock())
> + elog(ERROR, "ANALYZE cannot run inside a BEGIN/END block");
> +
> + MESSAGE_LEVEL = DEBUG;
>
> + /*
> + * Create special memory context for cross-transaction storage.
> + *
> + * Since it is a child of QueryContext, it will go away eventually even
> + * if we suffer an error; there's no need for special abort cleanup
> + * logic.
> + */
> + vac_context = AllocSetContextCreate(QueryContext,
> + "Analyze",
> + ALLOCSET_DEFAULT_MINSIZE,
> + ALLOCSET_DEFAULT_INITSIZE,
> + ALLOCSET_DEFAULT_MAXSIZE);
> +
> + /* vacrel gets de-allocated on xact commit, so copy it to safe storage */
> + if (vacrel)
> + {
> + namestrcpy(&VacRel, vacrel);
> + VacRelName = &VacRel;
> + }
> + else
> + VacRelName = NULL;
>
> + /* must also copy the column list, if any, to safe storage */
> + old = MemoryContextSwitchTo(vac_context);
> + foreach(le, anal_cols)
> + {
> + char *col = (char *) lfirst(le);
> +
> + anal_cols2 = lappend(anal_cols2, pstrdup(col));
> + }
> + MemoryContextSwitchTo(old);
> +
> + /*
> + * Start up the analyzer.
> + *
> + * NOTE: since this commits the current transaction, the memory holding
> + * any passed-in parameters gets freed here. We must have already
> + * copied pass-by-reference parameters to safe storage. Don't make me
> + * fix this again!
> + */
> + vacuum_init();
> +
> + /* analyze the database */
> + do_analyze(VacRelName, anal_cols2);
> +
> + vacuum_shutdown(vac_context);
> + }
> +
> /*
> + * do_analyze() -- analyze the database.
> + *
> + * This routine builds a list of relations to analyze, and then calls
> + * code that analyze them one at a time.
> + */
> + static void
> + do_analyze(NameData *VacRelP, List *anal_cols2)
> + {
> + VRelList vrl,
> + cur;
> +
> + /* get list of relations */
> + vrl = vacuum_getrels(VacRelP, vac_context);
> +
> + /* analyze each heap relation */
> + for (cur = vrl; cur != (VRelList) NULL; cur = cur->vrl_next)
> + {
> + analyze_rel_1(cur->vrl_relid, anal_cols2, 0 /* not vacuum */);
> + }
> + }
> +
> + /*
> * analyze_rel() -- analyze relation
> + *
> + * Called through here when doing vacuum
> */
> void
> ! analyze_rel(Oid relid, List *anal_cols2, int msg_level)
> ! {
> ! /*
> ! * Use the MESSAGE_LEVEL as set on vacuum.
> ! */
> ! MESSAGE_LEVEL = msg_level;
> ! analyze_rel_1(relid, anal_cols2, 1 /* vacuum */);
> ! }
> !
> ! /*
> ! * analyze_rel_1() -- analyze relation
> ! *
> ! * Do the job for ANALYZE or VACUUM ANALYSE in one relation
> ! */
> !
> ! static void
> ! analyze_rel_1(Oid relid, List *anal_cols2, int vacuum)
> {
> HeapTuple tuple;
> Relation onerel;
> *************** analyze_rel(Oid relid, List *anal_cols2,
> *** 107,116 ****
> {
>
> /*
> ! * we already did an elog during vacuum elog(NOTICE, "Skipping
> ! * \"%s\" --- only table owner can VACUUM it",
> ! * RelationGetRelationName(onerel));
> */
> heap_close(onerel, NoLock);
> CommitTransactionCommand();
> return;
> --- 221,231 ----
> {
>
> /*
> ! * we already did an elog during vacuum
> */
> + if (!vacuum)
> + elog(NOTICE, "Skipping \"%s\" --- only table owner can ANALYZE it",
> + RelationGetRelationName(onerel));
> heap_close(onerel, NoLock);
> CommitTransactionCommand();
> return;
> *************** analyze_rel(Oid relid, List *anal_cols2,
> *** 127,133 ****
> List *le;
>
> if (length(anal_cols2) > attr_cnt)
> ! elog(ERROR, "vacuum: too many attributes specified for relation %s",
> RelationGetRelationName(onerel));
> attnums = (int *) palloc(attr_cnt * sizeof(int));
> foreach(le, anal_cols2)
> --- 242,248 ----
> List *le;
>
> if (length(anal_cols2) > attr_cnt)
> ! elog(ERROR, "analyze: too many attributes specified for relation %s",
> RelationGetRelationName(onerel));
> attnums = (int *) palloc(attr_cnt * sizeof(int));
> foreach(le, anal_cols2)
> *************** analyze_rel(Oid relid, List *anal_cols2,
> *** 143,149 ****
> attnums[tcnt++] = i;
> else
> {
> ! elog(ERROR, "vacuum: there is no attribute %s in %s",
> col, RelationGetRelationName(onerel));
> }
> }
> --- 258,264 ----
> attnums[tcnt++] = i;
> else
> {
> ! elog(ERROR, "analyze: there is no attribute %s in %s",
> col, RelationGetRelationName(onerel));
> }
> }
> Index: src/backend/commands/vacuum.c
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/vacuum.c,v
> retrieving revision 1.189
> diff -c -p -r1.189 vacuum.c
> *** src/backend/commands/vacuum.c 2001/03/25 23:23:58 1.189
> --- src/backend/commands/vacuum.c 2001/04/24 22:22:25
> *************** static int MESSAGE_LEVEL; /* message le
> *** 60,69 ****
> static TransactionId XmaxRecent;
>
> /* non-export function prototypes */
> - static void vacuum_init(void);
> - static void vacuum_shutdown(void);
> static void vac_vacuum(NameData *VacRelP, bool analyze, List *anal_cols2);
> - static VRelList getrels(NameData *VacRelP);
> static void vacuum_rel(Oid relid);
> static void scan_heap(VRelStats *vacrelstats, Relation onerel, VacPageList vacuum_pages, VacPageList fraged_pages);
> static void repair_frag(VRelStats *vacrelstats, Relation onerel, VacPageList vacuum_pages, VacPageList
fraged_pages,int nindices, Relation *Irel);
> --- 60,66 ----
> *************** vacuum(char *vacrel, bool verbose, bool
> *** 163,169 ****
> vac_vacuum(VacRelName, analyze, anal_cols2);
>
> /* clean up */
> ! vacuum_shutdown();
> }
>
> /*
> --- 160,166 ----
> vac_vacuum(VacRelName, analyze, anal_cols2);
>
> /* clean up */
> ! vacuum_shutdown(vac_context);
> }
>
> /*
> *************** vacuum(char *vacrel, bool verbose, bool
> *** 186,200 ****
> * vacuum_shutdown() to match the commit waiting for us back in
> * PostgresMain().
> */
> ! static void
> vacuum_init()
> {
> /* matches the StartTransaction in PostgresMain() */
> CommitTransactionCommand();
> }
>
> ! static void
> ! vacuum_shutdown()
> {
> /* on entry, we are not in a transaction */
>
> --- 183,197 ----
> * vacuum_shutdown() to match the commit waiting for us back in
> * PostgresMain().
> */
> ! void
> vacuum_init()
> {
> /* matches the StartTransaction in PostgresMain() */
> CommitTransactionCommand();
> }
>
> ! void
> ! vacuum_shutdown(MemoryContext context)
> {
> /* on entry, we are not in a transaction */
>
> *************** vacuum_shutdown()
> *** 218,224 ****
> * StartTransactionCommand, else we might be trying to delete the
> * active context!
> */
> ! MemoryContextDelete(vac_context);
> vac_context = NULL;
> }
>
> --- 215,221 ----
> * StartTransactionCommand, else we might be trying to delete the
> * active context!
> */
> ! MemoryContextDelete(context);
> vac_context = NULL;
> }
>
> *************** vac_vacuum(NameData *VacRelP, bool analy
> *** 237,243 ****
> cur;
>
> /* get list of relations */
> ! vrl = getrels(VacRelP);
>
> /* vacuum each heap relation */
> for (cur = vrl; cur != (VRelList) NULL; cur = cur->vrl_next)
> --- 234,240 ----
> cur;
>
> /* get list of relations */
> ! vrl = vacuum_getrels(VacRelP, vac_context);
>
> /* vacuum each heap relation */
> for (cur = vrl; cur != (VRelList) NULL; cur = cur->vrl_next)
> *************** vac_vacuum(NameData *VacRelP, bool analy
> *** 249,256 ****
> }
> }
>
> ! static VRelList
> ! getrels(NameData *VacRelP)
> {
> Relation rel;
> TupleDesc tupdesc;
> --- 246,253 ----
> }
> }
>
> ! VRelList
> ! vacuum_getrels(NameData *VacRelP, MemoryContext context)
> {
> Relation rel;
> TupleDesc tupdesc;
> *************** getrels(NameData *VacRelP)
> *** 317,327 ****
> /* get a relation list entry for this guy */
> if (vrl == (VRelList) NULL)
> vrl = cur = (VRelList)
> ! MemoryContextAlloc(vac_context, sizeof(VRelListData));
> else
> {
> cur->vrl_next = (VRelList)
> ! MemoryContextAlloc(vac_context, sizeof(VRelListData));
> cur = cur->vrl_next;
> }
>
> --- 314,324 ----
> /* get a relation list entry for this guy */
> if (vrl == (VRelList) NULL)
> vrl = cur = (VRelList)
> ! MemoryContextAlloc(context, sizeof(VRelListData));
> else
> {
> cur->vrl_next = (VRelList)
> ! MemoryContextAlloc(context, sizeof(VRelListData));
> cur = cur->vrl_next;
> }
>
> Index: src/backend/nodes/copyfuncs.c
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v
> retrieving revision 1.140
> diff -c -p -r1.140 copyfuncs.c
> *** src/backend/nodes/copyfuncs.c 2001/03/22 06:16:14 1.140
> --- src/backend/nodes/copyfuncs.c 2001/04/24 22:22:25
> *************** _copyVacuumStmt(VacuumStmt *from)
> *** 2218,2223 ****
> --- 2218,2235 ----
> return newnode;
> }
>
> + static AnalyzeStmt *
> + _copyAnalyzeStmt(AnalyzeStmt *from)
> + {
> + AnalyzeStmt *newnode = makeNode(AnalyzeStmt);
> +
> + if (from->anarel)
> + newnode->anarel = pstrdup(from->anarel);
> + Node_Copy(from, newnode, va_spec);
> +
> + return newnode;
> + }
> +
> static ExplainStmt *
> _copyExplainStmt(ExplainStmt *from)
> {
> *************** copyObject(void *from)
> *** 2780,2785 ****
> --- 2792,2800 ----
> break;
> case T_RemoveOperStmt:
> retval = _copyRemoveOperStmt(from);
> + break;
> + case T_AnalyzeStmt:
> + retval = _copyAnalyzeStmt(from);
> break;
> case T_RenameStmt:
> retval = _copyRenameStmt(from);
> Index: src/backend/nodes/equalfuncs.c
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v
> retrieving revision 1.88
> diff -c -p -r1.88 equalfuncs.c
> *** src/backend/nodes/equalfuncs.c 2001/03/22 03:59:31 1.88
> --- src/backend/nodes/equalfuncs.c 2001/04/24 22:22:25
> *************** _equalVacuumStmt(VacuumStmt *a, VacuumSt
> *** 1126,1131 ****
> --- 1126,1142 ----
> }
>
> static bool
> + _equalAnalyzeStmt(AnalyzeStmt *a, AnalyzeStmt *b)
> + {
> + if (!equalstr(a->anarel, b->anarel))
> + return false;
> + if (!equal(a->va_spec, b->va_spec))
> + return false;
> +
> + return true;
> + }
> +
> + static bool
> _equalExplainStmt(ExplainStmt *a, ExplainStmt *b)
> {
> if (!equal(a->query, b->query))
> *************** equal(void *a, void *b)
> *** 1946,1951 ****
> --- 1957,1965 ----
> break;
> case T_RemoveOperStmt:
> retval = _equalRemoveOperStmt(a, b);
> + break;
> + case T_AnalyzeStmt:
> + retval = _equalAnalyzeStmt(a, b);
> break;
> case T_RenameStmt:
> retval = _equalRenameStmt(a, b);
> Index: src/backend/parser/gram.y
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/parser/gram.y,v
> retrieving revision 2.221
> diff -c -p -r2.221 gram.y
> *** src/backend/parser/gram.y 2001/02/18 18:06:10 2.221
> --- src/backend/parser/gram.y 2001/04/24 22:22:26
> *************** static void doNegateFloat(Value *v);
> *** 130,135 ****
> --- 130,136 ----
>
> %type <node> stmt,
> AlterGroupStmt, AlterSchemaStmt, AlterTableStmt, AlterUserStmt,
> + AnalyzeStmt, CheckPointStmt,
> ClosePortalStmt, ClusterStmt, CommentStmt, ConstraintsSetStmt,
> CopyStmt, CreateAsStmt, CreateGroupStmt, CreatePLangStmt,
> CreateSchemaStmt, CreateSeqStmt, CreateStmt, CreateTrigStmt,
> *************** static void doNegateFloat(Value *v);
> *** 142,148 ****
> RenameStmt, RevokeStmt, RuleActionStmt, RuleActionStmtOrEmpty,
> RuleStmt, SelectStmt, TransactionStmt, TruncateStmt,
> UnlistenStmt, UpdateStmt, VacuumStmt, VariableResetStmt,
> ! VariableSetStmt, VariableShowStmt, ViewStmt, CheckPointStmt
>
> %type <node> select_no_parens, select_with_parens, select_clause,
> simple_select
> --- 143,149 ----
> RenameStmt, RevokeStmt, RuleActionStmt, RuleActionStmtOrEmpty,
> RuleStmt, SelectStmt, TransactionStmt, TruncateStmt,
> UnlistenStmt, UpdateStmt, VacuumStmt, VariableResetStmt,
> ! VariableSetStmt, VariableShowStmt, ViewStmt
>
> %type <node> select_no_parens, select_with_parens, select_clause,
> simple_select
> *************** stmt : AlterSchemaStmt
> *** 426,431 ****
> --- 427,433 ----
> | AlterTableStmt
> | AlterGroupStmt
> | AlterUserStmt
> + | AnalyzeStmt
> | ClosePortalStmt
> | CopyStmt
> | CreateStmt
> *************** va_list: name
> *** 3037,3042 ****
> --- 3039,3074 ----
> { $$ = makeList1($1); }
> | va_list ',' name
> { $$ = lappend($1, $3); }
> + ;
> +
> +
> + /*****************************************************************************
> + *
> + * QUERY:
> + * analyze
> + *
> + *****************************************************************************/
> +
> + /* We use opt_va_list and va_list defined for vacuum. */
> +
> + AnalyzeStmt: ANALYZE
> + {
> + AnalyzeStmt *n = makeNode(AnalyzeStmt);
> + n->anarel = NULL;
> + n->va_spec = NIL;
> + $$ = (Node *)n;
> + }
> + | ANALYZE relation_name opt_va_list
> + {
> + AnalyzeStmt *n = makeNode(AnalyzeStmt);
> + n->anarel = $2;
> + n->va_spec = $3;
> + if ( $3 != NIL && !$2 )
> + elog(ERROR,"ANALYZE syntax error at or near \"(\""
> + "\n\tRelation name must be specified");
> +
> + $$ = (Node *)n;
> + }
> ;
>
>
> Index: src/backend/tcop/utility.c
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/tcop/utility.c,v
> retrieving revision 1.109
> diff -c -p -r1.109 utility.c
> *** src/backend/tcop/utility.c 2001/03/22 06:16:17 1.109
> --- src/backend/tcop/utility.c 2001/04/24 22:22:26
> *************** ProcessUtility(Node *parsetree,
> *** 711,716 ****
> --- 711,723 ----
> ((VacuumStmt *) parsetree)->va_spec);
> break;
>
> + case T_AnalyzeStmt:
> + set_ps_display(commandTag = "ANALYZE");
> +
> + analyze(((AnalyzeStmt *) parsetree)->anarel,
> + ((AnalyzeStmt *) parsetree)->va_spec);
> + break;
> +
> case T_ExplainStmt:
> {
> ExplainStmt *stmt = (ExplainStmt *) parsetree;
> Index: src/include/commands/vacuum.h
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/commands/vacuum.h,v
> retrieving revision 1.34
> diff -c -p -r1.34 vacuum.h
> *** src/include/commands/vacuum.h 2001/03/22 04:00:43 1.34
> --- src/include/commands/vacuum.h 2001/04/24 22:22:29
> *************** extern bool VacuumRunning;
> *** 122,127 ****
> --- 122,131 ----
>
> extern void vc_abort(void);
> extern void vacuum(char *vacrel, bool verbose, bool analyze, List *anal_cols);
> + extern void vacuum_init(void);
> + extern void vacuum_shutdown(MemoryContext vac_context);
> + extern VRelList vacuum_getrels(NameData *VacRelP, MemoryContext vac_context);
> + extern void analyze(char *vacrel, List *anal_cols);
> extern void analyze_rel(Oid relid, List *anal_cols2, int MESSAGE_LEVEL);
>
> #define ATTNVALS_SCALE 1000000000 /* XXX so it can act as a float4 */
> Index: src/include/nodes/nodes.h
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/nodes/nodes.h,v
> retrieving revision 1.89
> diff -c -p -r1.89 nodes.h
> *** src/include/nodes/nodes.h 2001/04/24 00:08:38 1.89
> --- src/include/nodes/nodes.h 2001/04/24 22:22:29
> *************** typedef enum NodeTag
> *** 165,171 ****
> T_RemoveAggrStmt,
> T_RemoveFuncStmt,
> T_RemoveOperStmt,
> ! T_RemoveStmt_XXX, /* not used anymore; tag# available */
> T_RenameStmt,
> T_RuleStmt,
> T_NotifyStmt,
> --- 165,171 ----
> T_RemoveAggrStmt,
> T_RemoveFuncStmt,
> T_RemoveOperStmt,
> ! T_AnalyzeStmt,
> T_RenameStmt,
> T_RuleStmt,
> T_NotifyStmt,
> Index: src/include/nodes/parsenodes.h
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
> retrieving revision 1.126
> diff -c -p -r1.126 parsenodes.h
> *** src/include/nodes/parsenodes.h 2001/03/23 04:49:56 1.126
> --- src/include/nodes/parsenodes.h 2001/04/24 22:22:29
> *************** typedef struct VacuumStmt
> *** 703,708 ****
> --- 703,719 ----
> } VacuumStmt;
>
> /* ----------------------
> + * Analyze Statement
> + * ----------------------
> + */
> + typedef struct AnalyzeStmt
> + {
> + NodeTag type;
> + char *anarel; /* table to analyze */
> + List *va_spec; /* columns to analyse */
> + } AnalyzeStmt;
> +
> + /* ----------------------
> * Explain Statement
> * ----------------------
> */
> Index: src/interfaces/ecpg/preproc/preproc.y
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/ecpg/preproc/preproc.y,v
> retrieving revision 1.136
> diff -c -p -r1.136 preproc.y
> *** src/interfaces/ecpg/preproc/preproc.y 2001/04/05 08:21:14 1.136
> --- src/interfaces/ecpg/preproc/preproc.y 2001/04/24 22:22:30
> *************** make_name(void)
> *** 302,308 ****
> %type <str> copy_delimiter ListenStmt CopyStmt copy_file_name opt_binary
> %type <str> opt_with_copy FetchStmt direction fetch_how_many from_in
> %type <str> ClosePortalStmt DropStmt VacuumStmt opt_verbose func_arg
> ! %type <str> opt_analyze opt_va_list va_list ExplainStmt index_params
> %type <str> index_list func_index index_elem opt_class access_method_clause
> %type <str> index_opt_unique IndexStmt func_return ConstInterval
> %type <str> func_args_list func_args opt_with ProcedureStmt def_arg
> --- 302,308 ----
> %type <str> copy_delimiter ListenStmt CopyStmt copy_file_name opt_binary
> %type <str> opt_with_copy FetchStmt direction fetch_how_many from_in
> %type <str> ClosePortalStmt DropStmt VacuumStmt opt_verbose func_arg
> ! %type <str> opt_analyze opt_va_list va_list AnalyzeStmt ExplainStmt index_params
> %type <str> index_list func_index index_elem opt_class access_method_clause
> %type <str> index_opt_unique IndexStmt func_return ConstInterval
> %type <str> func_args_list func_args opt_with ProcedureStmt def_arg
> *************** stmt: AlterSchemaStmt { output_state
> *** 447,452 ****
> --- 447,453 ----
> | CreatedbStmt { output_statement($1, 0, NULL, connection); }
> | DropdbStmt { output_statement($1, 0, NULL, connection); }
> | VacuumStmt { output_statement($1, 0, NULL, connection); }
> + | AnalyzeStmt { output_statement($1, 0, NULL, connection); }
> | VariableSetStmt { output_statement($1, 0, NULL, connection); }
> | VariableShowStmt { output_statement($1, 0, NULL, connection); }
> | VariableResetStmt { output_statement($1, 0, NULL, connection); }
> *************** va_list: name
> *** 2282,2287 ****
> --- 2283,2310 ----
> { $$=$1; }
> | va_list ',' name
> { $$=cat_str(3, $1, make_str(","), $3); }
> + ;
> +
> +
> + /*****************************************************************************
> + *
> + * QUERY:
> + * analyze
> + *
> + *****************************************************************************/
> +
> + /* We use opt_va_list and va_list defined for vacuum. */
> +
> + AnalyzeStmt: ANALYZE
> + {
> + $$ = cat_str(1, make_str("analyze"));
> + }
> + | ANALYZE relation_name opt_va_list
> + {
> + if ( strlen($3) > 0 && strlen($2) == 0 )
> + mmerror(ET_ERROR, "ANALYZE syntax error at or near \"(\"\n\tRelations name must be
specified");
> + $$ = cat_str(3, make_str("analyze"), $2, $3);
> + }
> ;
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Fernando Nasser <fnasser@cygnus.com> writes:
> It adds a separate SQL extension command ANALYZE that will only perform the analyze part and thus only use shared
locks.
> I will submit the doc and test changes in a separate patch, if you decide that we are really going ahead with the
ANALYZEcommand.
I've already done this as part of work discussed recently on pghackers.
However, I haven't got round to writing the doc changes yet, so if you
have those then please send 'em along ;-)
regards, tom lane
Seems this has already been done by Tom Lane. Thanks.
> [My apologies: I forgot to tell to which sources the patch
> applies, as required and, which is worse, I attached the wrong
> patch; I have corrected it now.]
>
>
> This patch is a follow-up to the split of the vacuum and analyze
> (done by Bruce Momjian last year). I applies to today's CVS
> sources.
>
> It adds a separate SQL extension command ANALYZE that will only
> perform the analyze part and thus only use shared locks.
>
> I will submit the doc and test changes in a separate patch, if
> you decide that we are really going ahead with the ANALYZE
> command.
>
> This version still does not update pg_class. Those statistics
> are still tied in the vacuum part (num. of pages, tuples and
> indication if the relation has an index). The patch allows for
> analyze_rel() to work differently if run as part of a VACUUM or
> as an standalone ANALYZE, so the missing bits can be added
> without changes to the current behavior of VACUUM ANALYZE.
>
> Possible future improvements:
>
> 1) Add the pg_class update to ANALYZE;
>
> 2) Add a VERBOSE option that prints information to gives the
> DBA a hint if it is worth of running VACUUM or not.
>
> Thanks.
>
>
> -- Fernando Nasser Red Hat Inc. E-Mail:
> fnasser@redhat.com
> Index: src/backend/commands/analyze.c
> ===================================================================
> RCS file:
> /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/analyze.c,v
> retrieving revision 1.16 diff -c -p -r1.16 analyze.c ***
> src/backend/commands/analyze.c 2001/03/22 06:16:11 1.16
> --- src/backend/commands/analyze.c 2001/04/24 22:22:24
> *************** *** 35,41 **** --- 35,46 ----
> #include "utils/fmgroids.h" #include "utils/inval.h" #include
> "utils/syscache.h" + #include "utils/temprel.h"
>
> + static MemoryContext vac_context = NULL; + + static int
> MESSAGE_LEVEL; /* message level */ +
> #define swapLong(a,b) {long tmp; tmp=a; a=b; b=tmp;}
> #define swapInt(a,b) {int tmp; tmp=a; a=b; b=tmp;}
> #define swapDatum(a,b) {Datum tmp; tmp=a; a=b; b=tmp;}
> *************** static void attr_stats(Relation onerel, ***
> 49,61 ****
> static void bucketcpy(Form_pg_attribute attr, Datum value,
> Datum *bucket, int *bucket_len); static void update_attstats(Oid
> relid, int natts, VacAttrStats *vacattrstats); static void
> del_stats(Oid relid, int attcnt, int *attnums);
>
>
> /*
> * analyze_rel() -- analyze relation */ void ! analyze_rel(Oid
> relid, List *anal_cols2, int MESSAGE_LEVEL)
> {
> HeapTuple tuple; Relation onerel; --- 54,175
> ----
> static void bucketcpy(Form_pg_attribute attr, Datum value,
> Datum *bucket, int *bucket_len); static void update_attstats(Oid
> relid, int natts, VacAttrStats *vacattrstats); static void
> del_stats(Oid relid, int attcnt, int *attnums); + static void
> do_analyze(NameData *VacRelP, List *anal_cols2); + static void
> analyze_rel_1(Oid relid, List *anal_cols2, int vacuum); + + +
> void + analyze(char *vacrel, List *anal_cols) + { + NameData
> VacRel; + Name VacRelName; + MemoryContext old;
> + List *le; + List *anal_cols2 = NIL; + +
> /* + * We cannot run ANALYZE inside a user transaction block;
> if we were + * inside a transaction, then our commit- and
> + * start-transaction-command calls would not have the
> intended effect! + */ + if (IsTransactionBlock()) +
> elog(ERROR, "ANALYZE cannot run inside a BEGIN/END block"); +
> + MESSAGE_LEVEL = DEBUG;
>
> + /* + * Create special memory context for cross-transaction
> storage. + * + * Since it is a child of QueryContext,
> it will go away eventually even + * if we suffer an error;
> there's no need for special abort cleanup + * logic. +
> */ + vac_context = AllocSetContextCreate(QueryContext, +
> "Analyze", +
> ALLOCSET_DEFAULT_MINSIZE, +
> ALLOCSET_DEFAULT_INITSIZE, +
> ALLOCSET_DEFAULT_MAXSIZE); + + /* vacrel gets de-allocated
> on xact commit, so copy it to safe storage */ + if (vacrel)
> + { + namestrcpy(&VacRel, vacrel); +
> VacRelName = &VacRel; + } + else + VacRelName
> = NULL;
>
> + /* must also copy the column list, if any, to safe storage
> */ + old = MemoryContextSwitchTo(vac_context); + foreach(le,
> anal_cols) + { + char *col = (char *)
> lfirst(le); + + anal_cols2 = lappend(anal_cols2,
> pstrdup(col)); + } + MemoryContextSwitchTo(old); + +
> /* + * Start up the analyzer. + * + * NOTE: since
> this commits the current transaction, the memory holding +
> * any passed-in parameters gets freed here. We must have already
> + * copied pass-by-reference parameters to safe storage.
> Don't make me + * fix this again! + */ + vacuum_init();
> + + /* analyze the database */ + do_analyze(VacRelName,
> anal_cols2); + + vacuum_shutdown(vac_context); + } +
> /* + * do_analyze() -- analyze the database. + * + *
> This routine builds a list of relations to analyze, and then
> calls + * code that analyze them one at a time. + */
> + static void + do_analyze(NameData *VacRelP, List *anal_cols2)
> + { + VRelList vrl, + cur;
> + + /* get list of relations */ + vrl = vacuum_getrels(VacRelP,
> vac_context); + + /* analyze each heap relation */ + for
> (cur = vrl; cur != (VRelList) NULL; cur = cur->vrl_next) +
> { + analyze_rel_1(cur->vrl_relid, anal_cols2, 0 /*
> not vacuum */); + } + } + + /*
> * analyze_rel() -- analyze relation + * + * Called through
> here when doing vacuum
> */ void ! analyze_rel(Oid relid, List *anal_cols2, int
> msg_level) ! { ! /* ! * Use the MESSAGE_LEVEL as set on
> vacuum. ! */ ! MESSAGE_LEVEL = msg_level; !
> analyze_rel_1(relid, anal_cols2, 1 /* vacuum */); ! } ! ! /*
> ! * analyze_rel_1() -- analyze relation ! * ! * Do the job
> for ANALYZE or VACUUM ANALYSE in one relation ! */ ! ! static
> void ! analyze_rel_1(Oid relid, List *anal_cols2, int vacuum)
> {
> HeapTuple tuple; Relation onerel; ***************
> analyze_rel(Oid relid, List *anal_cols2, *** 107,116 ****
> {
>
> /*
> ! * we already did an elog during vacuum elog(NOTICE,
> "Skipping ! * \"%s\" --- only table owner can VACUUM
> it", ! * RelationGetRelationName(onerel));
> */ heap_close(onerel, NoLock); CommitTransactionCommand();
> return;
> --- 221,231 ----
> {
>
> /*
> ! * we already did an elog during vacuum
> */
> + if (!vacuum) + elog(NOTICE,
> "Skipping \"%s\" --- only table owner can ANALYZE it", +
> RelationGetRelationName(onerel));
> heap_close(onerel, NoLock); CommitTransactionCommand();
> return;
> *************** analyze_rel(Oid relid, List *anal_cols2, ***
> 127,133 ****
> List *le;
>
> if (length(anal_cols2) > attr_cnt)
> ! elog(ERROR, "vacuum: too many attributes
> specified for relation %s",
> RelationGetRelationName(onerel));
> attnums = (int *) palloc(attr_cnt * sizeof(int));
> foreach(le, anal_cols2)
> --- 242,248 ----
> List *le;
>
> if (length(anal_cols2) > attr_cnt)
> ! elog(ERROR, "analyze: too many attributes
> specified for relation %s",
> RelationGetRelationName(onerel));
> attnums = (int *) palloc(attr_cnt * sizeof(int));
> foreach(le, anal_cols2)
> *************** analyze_rel(Oid relid, List *anal_cols2, ***
> 143,149 ****
> attnums[tcnt++] = i; else {
> ! elog(ERROR, "vacuum: there is no
> attribute %s in %s",
> col,
> RelationGetRelationName(onerel));
> } }
> --- 258,264 ----
> attnums[tcnt++] = i; else {
> ! elog(ERROR, "analyze: there is no
> attribute %s in %s",
> col,
> RelationGetRelationName(onerel));
> } }
> Index: src/backend/commands/vacuum.c
> ===================================================================
> RCS file:
> /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/vacuum.c,v
> retrieving revision 1.189 diff -c -p -r1.189 vacuum.c ***
> src/backend/commands/vacuum.c 2001/03/25 23:23:58 1.189
> --- src/backend/commands/vacuum.c 2001/04/24 22:22:25
> *************** static int MESSAGE_LEVEL; /* message
> le *** 60,69 ****
> static TransactionId XmaxRecent;
>
> /* non-export function prototypes */ - static void vacuum_init(void);
> - static void vacuum_shutdown(void);
> static void vac_vacuum(NameData *VacRelP, bool analyze, List
> *anal_cols2); - static VRelList getrels(NameData *VacRelP);
> static void vacuum_rel(Oid relid); static void scan_heap(VRelStats
> *vacrelstats, Relation onerel, VacPageList vacuum_pages,
> VacPageList fraged_pages); static void repair_frag(VRelStats
> *vacrelstats, Relation onerel, VacPageList vacuum_pages,
> VacPageList fraged_pages, int nindices, Relation *Irel); ---
> 60,66 ---- *************** vacuum(char *vacrel, bool verbose,
> bool *** 163,169 ****
> vac_vacuum(VacRelName, analyze, anal_cols2);
>
> /* clean up */ ! vacuum_shutdown();
> }
>
> /* --- 160,166 ----
> vac_vacuum(VacRelName, analyze, anal_cols2);
>
> /* clean up */ ! vacuum_shutdown(vac_context);
> }
>
> /* *************** vacuum(char *vacrel, bool verbose, bool
> *** 186,200 ****
> * vacuum_shutdown() to match the commit waiting for
> us back in * PostgresMain(). */ ! static void
> vacuum_init() {
> /* matches the StartTransaction in PostgresMain() */
> CommitTransactionCommand(); }
>
> ! static void ! vacuum_shutdown()
> {
> /* on entry, we are not in a transaction */
>
> --- 183,197 ----
> * vacuum_shutdown() to match the commit waiting for
> us back in * PostgresMain(). */ ! void
> vacuum_init() {
> /* matches the StartTransaction in PostgresMain() */
> CommitTransactionCommand(); }
>
> ! void ! vacuum_shutdown(MemoryContext context)
> {
> /* on entry, we are not in a transaction */
>
> *************** vacuum_shutdown() *** 218,224 ****
> * StartTransactionCommand, else we might be trying to
> delete the * active context! */ !
> MemoryContextDelete(vac_context);
> vac_context = NULL; }
>
> --- 215,221 ----
> * StartTransactionCommand, else we might be trying to
> delete the * active context! */ !
> MemoryContextDelete(context);
> vac_context = NULL; }
>
> *************** vac_vacuum(NameData *VacRelP, bool analy ***
> 237,243 ****
> cur;
>
> /* get list of relations */ ! vrl = getrels(VacRelP);
>
> /* vacuum each heap relation */ for (cur = vrl; cur !=
> (VRelList) NULL; cur = cur->vrl_next) --- 234,240 ----
> cur;
>
> /* get list of relations */ ! vrl = vacuum_getrels(VacRelP,
> vac_context);
>
> /* vacuum each heap relation */ for (cur = vrl; cur !=
> (VRelList) NULL; cur = cur->vrl_next) ***************
> vac_vacuum(NameData *VacRelP, bool analy *** 249,256 ****
> } }
>
> ! static VRelList ! getrels(NameData *VacRelP)
> {
> Relation rel; TupleDesc tupdesc; --- 246,253
> ----
> } }
>
> ! VRelList ! vacuum_getrels(NameData *VacRelP, MemoryContext
> context)
> {
> Relation rel; TupleDesc tupdesc; ***************
> getrels(NameData *VacRelP) *** 317,327 ****
> /* get a relation list entry for this guy */ if
> (vrl == (VRelList) NULL)
> vrl = cur = (VRelList)
> ! MemoryContextAlloc(vac_context,
> sizeof(VRelListData));
> else {
> cur->vrl_next = (VRelList)
> ! MemoryContextAlloc(vac_context,
> sizeof(VRelListData));
> cur = cur->vrl_next; }
>
> --- 314,324 ----
> /* get a relation list entry for this guy */ if
> (vrl == (VRelList) NULL)
> vrl = cur = (VRelList)
> ! MemoryContextAlloc(context,
> sizeof(VRelListData));
> else {
> cur->vrl_next = (VRelList)
> ! MemoryContextAlloc(context,
> sizeof(VRelListData));
> cur = cur->vrl_next; }
>
> Index: src/backend/nodes/copyfuncs.c
> ===================================================================
> RCS file:
> /home/projects/pgsql/cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v
> retrieving revision 1.140 diff -c -p -r1.140 copyfuncs.c ***
> src/backend/nodes/copyfuncs.c 2001/03/22 06:16:14 1.140
> --- src/backend/nodes/copyfuncs.c 2001/04/24 22:22:25
> *************** _copyVacuumStmt(VacuumStmt *from) *** 2218,2223
> **** --- 2218,2235 ----
> return newnode; }
>
> + static AnalyzeStmt * + _copyAnalyzeStmt(AnalyzeStmt *from) +
> { + AnalyzeStmt *newnode = makeNode(AnalyzeStmt); + + if
> (from->anarel) + newnode->anarel = pstrdup(from->anarel);
> + Node_Copy(from, newnode, va_spec); + + return newnode;
> + } +
> static ExplainStmt * _copyExplainStmt(ExplainStmt *from) {
> *************** copyObject(void *from) *** 2780,2785 **** ---
> 2792,2800 ----
> break; case T_RemoveOperStmt:
> retval = _copyRemoveOperStmt(from);
> + break; + case T_AnalyzeStmt:
> + retval = _copyAnalyzeStmt(from);
> break; case T_RenameStmt:
> retval = _copyRenameStmt(from);
> Index: src/backend/nodes/equalfuncs.c
> ===================================================================
> RCS file:
> /home/projects/pgsql/cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v
> retrieving revision 1.88 diff -c -p -r1.88 equalfuncs.c ***
> src/backend/nodes/equalfuncs.c 2001/03/22 03:59:31 1.88
> --- src/backend/nodes/equalfuncs.c 2001/04/24 22:22:25
> *************** _equalVacuumStmt(VacuumStmt *a, VacuumSt ***
> 1126,1131 **** --- 1126,1142 ----
> }
>
> static bool + _equalAnalyzeStmt(AnalyzeStmt *a, AnalyzeStmt
> *b) + { + if (!equalstr(a->anarel, b->anarel)) +
> return false; + if (!equal(a->va_spec, b->va_spec)) +
> return false; + + return true; + } + + static bool
> _equalExplainStmt(ExplainStmt *a, ExplainStmt *b) {
> if (!equal(a->query, b->query)) *************** equal(void
> *a, void *b) *** 1946,1951 **** --- 1957,1965 ----
> break; case T_RemoveOperStmt:
> retval = _equalRemoveOperStmt(a, b);
> + break; + case T_AnalyzeStmt:
> + retval = _equalAnalyzeStmt(a, b);
> break; case T_RenameStmt:
> retval = _equalRenameStmt(a, b);
> Index: src/backend/parser/gram.y
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/parser/gram.y,v
> retrieving revision 2.221 diff -c -p -r2.221 gram.y ***
> src/backend/parser/gram.y 2001/02/18 18:06:10 2.221
> --- src/backend/parser/gram.y 2001/04/24 22:22:26
> *************** static void doNegateFloat(Value *v); *** 130,135
> **** --- 130,136 ----
>
> %type <node> stmt,
> AlterGroupStmt, AlterSchemaStmt, AlterTableStmt,
> AlterUserStmt,
> + AnalyzeStmt, CheckPointStmt,
> ClosePortalStmt, ClusterStmt, CommentStmt,
> ConstraintsSetStmt, CopyStmt, CreateAsStmt,
> CreateGroupStmt, CreatePLangStmt, CreateSchemaStmt,
> CreateSeqStmt, CreateStmt, CreateTrigStmt,
> *************** static void doNegateFloat(Value *v); *** 142,148
> ****
> RenameStmt, RevokeStmt, RuleActionStmt,
> RuleActionStmtOrEmpty, RuleStmt, SelectStmt,
> TransactionStmt, TruncateStmt, UnlistenStmt,
> UpdateStmt, VacuumStmt, VariableResetStmt,
> ! VariableSetStmt, VariableShowStmt, ViewStmt,
> CheckPointStmt
>
> %type <node> select_no_parens, select_with_parens,
> select_clause,
> simple_select
> --- 143,149 ----
> RenameStmt, RevokeStmt, RuleActionStmt,
> RuleActionStmtOrEmpty, RuleStmt, SelectStmt,
> TransactionStmt, TruncateStmt, UnlistenStmt,
> UpdateStmt, VacuumStmt, VariableResetStmt,
> ! VariableSetStmt, VariableShowStmt, ViewStmt
>
> %type <node> select_no_parens, select_with_parens,
> select_clause,
> simple_select
> *************** stmt : AlterSchemaStmt *** 426,431 ****
> --- 427,433 ----
> | AlterTableStmt | AlterGroupStmt | AlterUserStmt
> + | AnalyzeStmt
> | ClosePortalStmt | CopyStmt | CreateStmt
> *************** va_list: name *** 3037,3042 **** --- 3039,3074
> ----
> { $$ = makeList1($1); }
> | va_list ',' name
> { $$ = lappend($1, $3); }
> + ; + + +
> /*****************************************************************************
> + * + * QUERY: + * analyze
> + * +
> *****************************************************************************/
> + + /* We use opt_va_list and va_list defined for vacuum. */ +
> + AnalyzeStmt: ANALYZE + { +
> AnalyzeStmt *n = makeNode(AnalyzeStmt); +
> n->anarel = NULL; +
> n->va_spec = NIL; + $$
> = (Node *)n; + } +
> | ANALYZE relation_name opt_va_list +
> { + AnalyzeStmt *n =
> makeNode(AnalyzeStmt); +
> n->anarel = $2; + n->va_spec
> = $3; + if ( $3 != NIL &&
> !$2 ) + elog(ERROR,"ANALYZE
> syntax error at or near \"(\"" +
> "\n\tRelation name must be specified"); + +
> $$ = (Node *)n; + }
> ;
>
>
> Index: src/backend/tcop/utility.c
> ===================================================================
> RCS file:
> /home/projects/pgsql/cvsroot/pgsql/src/backend/tcop/utility.c,v
> retrieving revision 1.109 diff -c -p -r1.109 utility.c ***
> src/backend/tcop/utility.c 2001/03/22 06:16:17 1.109
> --- src/backend/tcop/utility.c 2001/04/24 22:22:26
> *************** ProcessUtility(Node *parsetree, *** 711,716 ****
> --- 711,723 ----
> ((VacuumStmt *) parsetree)->va_spec);
> break;
>
> + case T_AnalyzeStmt: +
> set_ps_display(commandTag = "ANALYZE"); + +
> analyze(((AnalyzeStmt *) parsetree)->anarel, +
> ((AnalyzeStmt *) parsetree)->va_spec); +
> break; +
> case T_ExplainStmt:
> {
> ExplainStmt *stmt = (ExplainStmt
> *) parsetree;
> Index: src/include/commands/vacuum.h
> ===================================================================
> RCS file:
> /home/projects/pgsql/cvsroot/pgsql/src/include/commands/vacuum.h,v
> retrieving revision 1.34 diff -c -p -r1.34 vacuum.h ***
> src/include/commands/vacuum.h 2001/03/22 04:00:43 1.34
> --- src/include/commands/vacuum.h 2001/04/24 22:22:29
> *************** extern bool VacuumRunning; *** 122,127 **** ---
> 122,131 ----
>
> extern void vc_abort(void); extern void vacuum(char *vacrel,
> bool verbose, bool analyze, List *anal_cols); + extern void
> vacuum_init(void); + extern void vacuum_shutdown(MemoryContext
> vac_context); + extern VRelList vacuum_getrels(NameData *VacRelP,
> MemoryContext vac_context); + extern void analyze(char *vacrel,
> List *anal_cols);
> extern void analyze_rel(Oid relid, List *anal_cols2, int
> MESSAGE_LEVEL);
>
> #define ATTNVALS_SCALE 1000000000 /* XXX so
> it can act as a float4 */ Index: src/include/nodes/nodes.h
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/nodes/nodes.h,v
> retrieving revision 1.89 diff -c -p -r1.89 nodes.h ***
> src/include/nodes/nodes.h 2001/04/24 00:08:38 1.89
> --- src/include/nodes/nodes.h 2001/04/24 22:22:29
> *************** typedef enum NodeTag *** 165,171 ****
> T_RemoveAggrStmt, T_RemoveFuncStmt, T_RemoveOperStmt, !
> T_RemoveStmt_XXX, /* not used anymore;
> tag# available */
> T_RenameStmt, T_RuleStmt, T_NotifyStmt, --- 165,171 ----
> T_RemoveAggrStmt, T_RemoveFuncStmt, T_RemoveOperStmt, !
> T_AnalyzeStmt,
> T_RenameStmt, T_RuleStmt, T_NotifyStmt, Index:
> src/include/nodes/parsenodes.h
> ===================================================================
> RCS file:
> /home/projects/pgsql/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
> retrieving revision 1.126 diff -c -p -r1.126 parsenodes.h ***
> src/include/nodes/parsenodes.h 2001/03/23 04:49:56 1.126
> --- src/include/nodes/parsenodes.h 2001/04/24 22:22:29
> *************** typedef struct VacuumStmt *** 703,708 **** ---
> 703,719 ----
> } VacuumStmt;
>
> /* ---------------------- + * Analyze Statement +
> * ---------------------- + */ + typedef struct AnalyzeStmt +
> { + NodeTag type; + char *anarel;
> /* table to analyze */ + List *va_spec; /*
> columns to analyse */ + } AnalyzeStmt; + + /* ----------------------
> * Explain Statement * ---------------------- */ Index:
> src/interfaces/ecpg/preproc/preproc.y
> ===================================================================
> RCS file:
> /home/projects/pgsql/cvsroot/pgsql/src/interfaces/ecpg/preproc/preproc.y,v
> retrieving revision 1.136 diff -c -p -r1.136 preproc.y ***
> src/interfaces/ecpg/preproc/preproc.y 2001/04/05 08:21:14
> 1.136 --- src/interfaces/ecpg/preproc/preproc.y 2001/04/24
> 22:22:30 *************** make_name(void) *** 302,308 ****
> %type <str> copy_delimiter ListenStmt CopyStmt copy_file_name
> opt_binary %type <str> opt_with_copy FetchStmt direction
> fetch_how_many from_in %type <str> ClosePortalStmt DropStmt
> VacuumStmt opt_verbose func_arg ! %type <str> opt_analyze
> opt_va_list va_list ExplainStmt index_params
> %type <str> index_list func_index index_elem opt_class
> access_method_clause %type <str> index_opt_unique IndexStmt
> func_return ConstInterval %type <str> func_args_list
> func_args opt_with ProcedureStmt def_arg --- 302,308 ----
> %type <str> copy_delimiter ListenStmt CopyStmt copy_file_name
> opt_binary %type <str> opt_with_copy FetchStmt direction
> fetch_how_many from_in %type <str> ClosePortalStmt DropStmt
> VacuumStmt opt_verbose func_arg ! %type <str> opt_analyze
> opt_va_list va_list AnalyzeStmt ExplainStmt index_params
> %type <str> index_list func_index index_elem opt_class
> access_method_clause %type <str> index_opt_unique IndexStmt
> func_return ConstInterval %type <str> func_args_list
> func_args opt_with ProcedureStmt def_arg *************** stmt:
> AlterSchemaStmt { output_state *** 447,452
> **** --- 447,453 ----
> | CreatedbStmt { output_statement($1, 0,
> NULL, connection); } | DropdbStmt {
> output_statement($1, 0, NULL, connection); } |
> VacuumStmt { output_statement($1, 0,
> NULL, connection); }
> + | AnalyzeStmt { output_statement($1, 0,
> NULL, connection); }
> | VariableSetStmt { output_statement($1, 0,
> NULL, connection); } | VariableShowStmt {
> output_statement($1, 0, NULL, connection); } |
> VariableResetStmt { output_statement($1, 0,
> NULL, connection); }
> *************** va_list: name *** 2282,2287 **** --- 2283,2310
> ----
> { $$=$1; }
> | va_list ',' name
> { $$=cat_str(3, $1, make_str(","),
> $3); }
> + ; + + +
> /*****************************************************************************
> + * + * QUERY: + * analyze
> + * +
> *****************************************************************************/
> + + /* We use opt_va_list and va_list defined for vacuum. */ +
> + AnalyzeStmt: ANALYZE + { +
> $$ = cat_str(1, make_str("analyze")); +
> } + | ANALYZE relation_name opt_va_list +
> { + if ( strlen($3) > 0 &&
> strlen($2) == 0 ) +
> mmerror(ET_ERROR, "ANALYZE syntax error at or near \"(\"\n\tRelations
> name must be specified"); +
> $$ = cat_str(3, make_str("analyze"), $2, $3); +
> }
> ;
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026