public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v18 1/3] Move pg_stat_statements query jumbling to core. 14+ messages / 6 participants [nested] [flat]
* [PATCH v18 1/3] Move pg_stat_statements query jumbling to core. @ 2020-10-13 18:11 Julien Rouhaud <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Julien Rouhaud @ 2020-10-13 18:11 UTC (permalink / raw) A new compute_queryid GUC is also added, to control whether the queryid should be computed. It's now possible to disable core queryid computation and use pg_stat_statements with a different algorithm to compute the queryid by using third-party module. Author: Julien Rouhaud Reviewed-by: Discussion: https://postgr.es/m/CA+8PKvQnMfOE-c3YLRwxOsCYXQDyP8VXs6CDtMZp1V4=D4LuFA@mail.gmail.com --- .../pg_stat_statements/pg_stat_statements.c | 805 +---------------- .../pg_stat_statements.conf | 1 + doc/src/sgml/config.sgml | 18 + src/backend/parser/analyze.c | 14 +- src/backend/tcop/postgres.c | 6 +- src/backend/utils/misc/Makefile | 1 + src/backend/utils/misc/guc.c | 10 + src/backend/utils/misc/postgresql.conf.sample | 1 + src/backend/utils/misc/queryjumble.c | 834 ++++++++++++++++++ src/include/parser/analyze.h | 4 +- src/include/utils/guc.h | 1 + src/include/utils/queryjumble.h | 58 ++ 12 files changed, 969 insertions(+), 784 deletions(-) create mode 100644 src/backend/utils/misc/queryjumble.c create mode 100644 src/include/utils/queryjumble.h diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 62cccbfa44..99bc7184cb 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -8,24 +8,9 @@ * a shared hashtable. (We track only as many distinct queries as will fit * in the designated amount of shared memory.) * - * As of Postgres 9.2, this module normalizes query entries. Normalization - * is a process whereby similar queries, typically differing only in their - * constants (though the exact rules are somewhat more subtle than that) are - * recognized as equivalent, and are tracked as a single entry. This is - * particularly useful for non-prepared queries. - * - * Normalization is implemented by fingerprinting queries, selectively - * serializing those fields of each query tree's nodes that are judged to be - * essential to the query. This is referred to as a query jumble. This is - * distinct from a regular serialization in that various extraneous - * information is ignored as irrelevant or not essential to the query, such - * as the collations of Vars and, most notably, the values of constants. - * - * This jumble is acquired at the end of parse analysis of each query, and - * a 64-bit hash of it is stored into the query's Query.queryId field. - * The server then copies this value around, making it available in plan - * tree(s) generated from the query. The executor can then use this value - * to blame query costs on the proper queryId. + * As of Postgres 9.2, this module normalizes query entries. As of Postgres + * 14, the normalization is done by the core, if compute_queryid is enabled, or + * by third-party modules if enabled. * * To facilitate presenting entries to users, we create "representative" query * strings in which constants are replaced with parameter symbols ($n), to @@ -114,8 +99,6 @@ static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100; #define USAGE_DEALLOC_PERCENT 5 /* free this % of entries at once */ #define IS_STICKY(c) ((c.calls[PGSS_PLAN] + c.calls[PGSS_EXEC]) == 0) -#define JUMBLE_SIZE 1024 /* query serialization buffer size */ - /* * Extension version number, for supporting older extension versions' objects */ @@ -235,40 +218,6 @@ typedef struct pgssSharedState pgssGlobalStats stats; /* global statistics for pgss */ } pgssSharedState; -/* - * Struct for tracking locations/lengths of constants during normalization - */ -typedef struct pgssLocationLen -{ - int location; /* start offset in query text */ - int length; /* length in bytes, or -1 to ignore */ -} pgssLocationLen; - -/* - * Working state for computing a query jumble and producing a normalized - * query string - */ -typedef struct pgssJumbleState -{ - /* Jumble of current query tree */ - unsigned char *jumble; - - /* Number of bytes used in jumble[] */ - Size jumble_len; - - /* Array of locations of constants that should be removed */ - pgssLocationLen *clocations; - - /* Allocated length of clocations array */ - int clocations_buf_size; - - /* Current number of valid entries in clocations array */ - int clocations_count; - - /* highest Param id we've seen, in order to start normalization correctly */ - int highest_extern_param_id; -} pgssJumbleState; - /*---- Local variables ----*/ /* Current nesting depth of ExecutorRun+ProcessUtility calls */ @@ -342,7 +291,8 @@ PG_FUNCTION_INFO_V1(pg_stat_statements_info); static void pgss_shmem_startup(void); static void pgss_shmem_shutdown(int code, Datum arg); -static void pgss_post_parse_analyze(ParseState *pstate, Query *query); +static void pgss_post_parse_analyze(ParseState *pstate, Query *query, + JumbleState *jstate); static PlannedStmt *pgss_planner(Query *parse, const char *query_string, int cursorOptions, @@ -364,7 +314,7 @@ static void pgss_store(const char *query, uint64 queryId, double total_time, uint64 rows, const BufferUsage *bufusage, const WalUsage *walusage, - pgssJumbleState *jstate); + JumbleState *jstate); static void pg_stat_statements_internal(FunctionCallInfo fcinfo, pgssVersion api_version, bool showtext); @@ -380,16 +330,9 @@ static char *qtext_fetch(Size query_offset, int query_len, static bool need_gc_qtexts(void); static void gc_qtexts(void); static void entry_reset(Oid userid, Oid dbid, uint64 queryid); -static void AppendJumble(pgssJumbleState *jstate, - const unsigned char *item, Size size); -static void JumbleQuery(pgssJumbleState *jstate, Query *query); -static void JumbleRangeTable(pgssJumbleState *jstate, List *rtable); -static void JumbleRowMarks(pgssJumbleState *jstate, List *rowMarks); -static void JumbleExpr(pgssJumbleState *jstate, Node *node); -static void RecordConstLocation(pgssJumbleState *jstate, int location); -static char *generate_normalized_query(pgssJumbleState *jstate, const char *query, +static char *generate_normalized_query(JumbleState *jstate, const char *query, int query_loc, int *query_len_p); -static void fill_in_constant_lengths(pgssJumbleState *jstate, const char *query, +static void fill_in_constant_lengths(JumbleState *jstate, const char *query, int query_loc); static int comp_location(const void *a, const void *b); @@ -851,15 +794,10 @@ error: * Post-parse-analysis hook: mark query with a queryId */ static void -pgss_post_parse_analyze(ParseState *pstate, Query *query) +pgss_post_parse_analyze(ParseState *pstate, Query *query, JumbleState *jstate) { - pgssJumbleState jstate; - if (prev_post_parse_analyze_hook) - prev_post_parse_analyze_hook(pstate, query); - - /* Assert we didn't do this already */ - Assert(query->queryId == UINT64CONST(0)); + prev_post_parse_analyze_hook(pstate, query, jstate); /* Safety check... */ if (!pgss || !pgss_hash || !pgss_enabled(exec_nested_level)) @@ -879,35 +817,14 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query) return; } - /* Set up workspace for query jumbling */ - jstate.jumble = (unsigned char *) palloc(JUMBLE_SIZE); - jstate.jumble_len = 0; - jstate.clocations_buf_size = 32; - jstate.clocations = (pgssLocationLen *) - palloc(jstate.clocations_buf_size * sizeof(pgssLocationLen)); - jstate.clocations_count = 0; - jstate.highest_extern_param_id = 0; - - /* Compute query ID and mark the Query node with it */ - JumbleQuery(&jstate, query); - query->queryId = - DatumGetUInt64(hash_any_extended(jstate.jumble, jstate.jumble_len, 0)); - /* - * If we are unlucky enough to get a hash of zero, use 1 instead, to - * prevent confusion with the utility-statement case. + * If query jumbling were able to identify any ignorable constants, we + * immediately create a hash table entry for the query, so that we can + * record the normalized form of the query string. If there were no such + * constants, the normalized string would be the same as the query text + * anyway, so there's no need for an early entry. */ - if (query->queryId == UINT64CONST(0)) - query->queryId = UINT64CONST(1); - - /* - * If we were able to identify any ignorable constants, we immediately - * create a hash table entry for the query, so that we can record the - * normalized form of the query string. If there were no such constants, - * the normalized string would be the same as the query text anyway, so - * there's no need for an early entry. - */ - if (jstate.clocations_count > 0) + if (jstate && jstate->clocations_count > 0) pgss_store(pstate->p_sourcetext, query->queryId, query->stmt_location, @@ -917,7 +834,7 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query) 0, NULL, NULL, - &jstate); + jstate); } /* @@ -1267,7 +1184,7 @@ pgss_store(const char *query, uint64 queryId, double total_time, uint64 rows, const BufferUsage *bufusage, const WalUsage *walusage, - pgssJumbleState *jstate) + JumbleState *jstate) { pgssHashKey key; pgssEntry *entry; @@ -2627,678 +2544,6 @@ release_lock: LWLockRelease(pgss->lock); } -/* - * AppendJumble: Append a value that is substantive in a given query to - * the current jumble. - */ -static void -AppendJumble(pgssJumbleState *jstate, const unsigned char *item, Size size) -{ - unsigned char *jumble = jstate->jumble; - Size jumble_len = jstate->jumble_len; - - /* - * Whenever the jumble buffer is full, we hash the current contents and - * reset the buffer to contain just that hash value, thus relying on the - * hash to summarize everything so far. - */ - while (size > 0) - { - Size part_size; - - if (jumble_len >= JUMBLE_SIZE) - { - uint64 start_hash; - - start_hash = DatumGetUInt64(hash_any_extended(jumble, - JUMBLE_SIZE, 0)); - memcpy(jumble, &start_hash, sizeof(start_hash)); - jumble_len = sizeof(start_hash); - } - part_size = Min(size, JUMBLE_SIZE - jumble_len); - memcpy(jumble + jumble_len, item, part_size); - jumble_len += part_size; - item += part_size; - size -= part_size; - } - jstate->jumble_len = jumble_len; -} - -/* - * Wrappers around AppendJumble to encapsulate details of serialization - * of individual local variable elements. - */ -#define APP_JUMB(item) \ - AppendJumble(jstate, (const unsigned char *) &(item), sizeof(item)) -#define APP_JUMB_STRING(str) \ - AppendJumble(jstate, (const unsigned char *) (str), strlen(str) + 1) - -/* - * JumbleQuery: Selectively serialize the query tree, appending significant - * data to the "query jumble" while ignoring nonsignificant data. - * - * Rule of thumb for what to include is that we should ignore anything not - * semantically significant (such as alias names) as well as anything that can - * be deduced from child nodes (else we'd just be double-hashing that piece - * of information). - */ -static void -JumbleQuery(pgssJumbleState *jstate, Query *query) -{ - Assert(IsA(query, Query)); - Assert(query->utilityStmt == NULL); - - APP_JUMB(query->commandType); - /* resultRelation is usually predictable from commandType */ - JumbleExpr(jstate, (Node *) query->cteList); - JumbleRangeTable(jstate, query->rtable); - JumbleExpr(jstate, (Node *) query->jointree); - JumbleExpr(jstate, (Node *) query->targetList); - JumbleExpr(jstate, (Node *) query->onConflict); - JumbleExpr(jstate, (Node *) query->returningList); - JumbleExpr(jstate, (Node *) query->groupClause); - JumbleExpr(jstate, (Node *) query->groupingSets); - JumbleExpr(jstate, query->havingQual); - JumbleExpr(jstate, (Node *) query->windowClause); - JumbleExpr(jstate, (Node *) query->distinctClause); - JumbleExpr(jstate, (Node *) query->sortClause); - JumbleExpr(jstate, query->limitOffset); - JumbleExpr(jstate, query->limitCount); - JumbleRowMarks(jstate, query->rowMarks); - JumbleExpr(jstate, query->setOperations); -} - -/* - * Jumble a range table - */ -static void -JumbleRangeTable(pgssJumbleState *jstate, List *rtable) -{ - ListCell *lc; - - foreach(lc, rtable) - { - RangeTblEntry *rte = lfirst_node(RangeTblEntry, lc); - - APP_JUMB(rte->rtekind); - switch (rte->rtekind) - { - case RTE_RELATION: - APP_JUMB(rte->relid); - JumbleExpr(jstate, (Node *) rte->tablesample); - break; - case RTE_SUBQUERY: - JumbleQuery(jstate, rte->subquery); - break; - case RTE_JOIN: - APP_JUMB(rte->jointype); - break; - case RTE_FUNCTION: - JumbleExpr(jstate, (Node *) rte->functions); - break; - case RTE_TABLEFUNC: - JumbleExpr(jstate, (Node *) rte->tablefunc); - break; - case RTE_VALUES: - JumbleExpr(jstate, (Node *) rte->values_lists); - break; - case RTE_CTE: - - /* - * Depending on the CTE name here isn't ideal, but it's the - * only info we have to identify the referenced WITH item. - */ - APP_JUMB_STRING(rte->ctename); - APP_JUMB(rte->ctelevelsup); - break; - case RTE_NAMEDTUPLESTORE: - APP_JUMB_STRING(rte->enrname); - break; - case RTE_RESULT: - break; - default: - elog(ERROR, "unrecognized RTE kind: %d", (int) rte->rtekind); - break; - } - } -} - -/* - * Jumble a rowMarks list - */ -static void -JumbleRowMarks(pgssJumbleState *jstate, List *rowMarks) -{ - ListCell *lc; - - foreach(lc, rowMarks) - { - RowMarkClause *rowmark = lfirst_node(RowMarkClause, lc); - - if (!rowmark->pushedDown) - { - APP_JUMB(rowmark->rti); - APP_JUMB(rowmark->strength); - APP_JUMB(rowmark->waitPolicy); - } - } -} - -/* - * Jumble an expression tree - * - * In general this function should handle all the same node types that - * expression_tree_walker() does, and therefore it's coded to be as parallel - * to that function as possible. However, since we are only invoked on - * queries immediately post-parse-analysis, we need not handle node types - * that only appear in planning. - * - * Note: the reason we don't simply use expression_tree_walker() is that the - * point of that function is to support tree walkers that don't care about - * most tree node types, but here we care about all types. We should complain - * about any unrecognized node type. - */ -static void -JumbleExpr(pgssJumbleState *jstate, Node *node) -{ - ListCell *temp; - - if (node == NULL) - return; - - /* Guard against stack overflow due to overly complex expressions */ - check_stack_depth(); - - /* - * We always emit the node's NodeTag, then any additional fields that are - * considered significant, and then we recurse to any child nodes. - */ - APP_JUMB(node->type); - - switch (nodeTag(node)) - { - case T_Var: - { - Var *var = (Var *) node; - - APP_JUMB(var->varno); - APP_JUMB(var->varattno); - APP_JUMB(var->varlevelsup); - } - break; - case T_Const: - { - Const *c = (Const *) node; - - /* We jumble only the constant's type, not its value */ - APP_JUMB(c->consttype); - /* Also, record its parse location for query normalization */ - RecordConstLocation(jstate, c->location); - } - break; - case T_Param: - { - Param *p = (Param *) node; - - APP_JUMB(p->paramkind); - APP_JUMB(p->paramid); - APP_JUMB(p->paramtype); - /* Also, track the highest external Param id */ - if (p->paramkind == PARAM_EXTERN && - p->paramid > jstate->highest_extern_param_id) - jstate->highest_extern_param_id = p->paramid; - } - break; - case T_Aggref: - { - Aggref *expr = (Aggref *) node; - - APP_JUMB(expr->aggfnoid); - JumbleExpr(jstate, (Node *) expr->aggdirectargs); - JumbleExpr(jstate, (Node *) expr->args); - JumbleExpr(jstate, (Node *) expr->aggorder); - JumbleExpr(jstate, (Node *) expr->aggdistinct); - JumbleExpr(jstate, (Node *) expr->aggfilter); - } - break; - case T_GroupingFunc: - { - GroupingFunc *grpnode = (GroupingFunc *) node; - - JumbleExpr(jstate, (Node *) grpnode->refs); - } - break; - case T_WindowFunc: - { - WindowFunc *expr = (WindowFunc *) node; - - APP_JUMB(expr->winfnoid); - APP_JUMB(expr->winref); - JumbleExpr(jstate, (Node *) expr->args); - JumbleExpr(jstate, (Node *) expr->aggfilter); - } - break; - case T_SubscriptingRef: - { - SubscriptingRef *sbsref = (SubscriptingRef *) node; - - JumbleExpr(jstate, (Node *) sbsref->refupperindexpr); - JumbleExpr(jstate, (Node *) sbsref->reflowerindexpr); - JumbleExpr(jstate, (Node *) sbsref->refexpr); - JumbleExpr(jstate, (Node *) sbsref->refassgnexpr); - } - break; - case T_FuncExpr: - { - FuncExpr *expr = (FuncExpr *) node; - - APP_JUMB(expr->funcid); - JumbleExpr(jstate, (Node *) expr->args); - } - break; - case T_NamedArgExpr: - { - NamedArgExpr *nae = (NamedArgExpr *) node; - - APP_JUMB(nae->argnumber); - JumbleExpr(jstate, (Node *) nae->arg); - } - break; - case T_OpExpr: - case T_DistinctExpr: /* struct-equivalent to OpExpr */ - case T_NullIfExpr: /* struct-equivalent to OpExpr */ - { - OpExpr *expr = (OpExpr *) node; - - APP_JUMB(expr->opno); - JumbleExpr(jstate, (Node *) expr->args); - } - break; - case T_ScalarArrayOpExpr: - { - ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) node; - - APP_JUMB(expr->opno); - APP_JUMB(expr->useOr); - JumbleExpr(jstate, (Node *) expr->args); - } - break; - case T_BoolExpr: - { - BoolExpr *expr = (BoolExpr *) node; - - APP_JUMB(expr->boolop); - JumbleExpr(jstate, (Node *) expr->args); - } - break; - case T_SubLink: - { - SubLink *sublink = (SubLink *) node; - - APP_JUMB(sublink->subLinkType); - APP_JUMB(sublink->subLinkId); - JumbleExpr(jstate, (Node *) sublink->testexpr); - JumbleQuery(jstate, castNode(Query, sublink->subselect)); - } - break; - case T_FieldSelect: - { - FieldSelect *fs = (FieldSelect *) node; - - APP_JUMB(fs->fieldnum); - JumbleExpr(jstate, (Node *) fs->arg); - } - break; - case T_FieldStore: - { - FieldStore *fstore = (FieldStore *) node; - - JumbleExpr(jstate, (Node *) fstore->arg); - JumbleExpr(jstate, (Node *) fstore->newvals); - } - break; - case T_RelabelType: - { - RelabelType *rt = (RelabelType *) node; - - APP_JUMB(rt->resulttype); - JumbleExpr(jstate, (Node *) rt->arg); - } - break; - case T_CoerceViaIO: - { - CoerceViaIO *cio = (CoerceViaIO *) node; - - APP_JUMB(cio->resulttype); - JumbleExpr(jstate, (Node *) cio->arg); - } - break; - case T_ArrayCoerceExpr: - { - ArrayCoerceExpr *acexpr = (ArrayCoerceExpr *) node; - - APP_JUMB(acexpr->resulttype); - JumbleExpr(jstate, (Node *) acexpr->arg); - JumbleExpr(jstate, (Node *) acexpr->elemexpr); - } - break; - case T_ConvertRowtypeExpr: - { - ConvertRowtypeExpr *crexpr = (ConvertRowtypeExpr *) node; - - APP_JUMB(crexpr->resulttype); - JumbleExpr(jstate, (Node *) crexpr->arg); - } - break; - case T_CollateExpr: - { - CollateExpr *ce = (CollateExpr *) node; - - APP_JUMB(ce->collOid); - JumbleExpr(jstate, (Node *) ce->arg); - } - break; - case T_CaseExpr: - { - CaseExpr *caseexpr = (CaseExpr *) node; - - JumbleExpr(jstate, (Node *) caseexpr->arg); - foreach(temp, caseexpr->args) - { - CaseWhen *when = lfirst_node(CaseWhen, temp); - - JumbleExpr(jstate, (Node *) when->expr); - JumbleExpr(jstate, (Node *) when->result); - } - JumbleExpr(jstate, (Node *) caseexpr->defresult); - } - break; - case T_CaseTestExpr: - { - CaseTestExpr *ct = (CaseTestExpr *) node; - - APP_JUMB(ct->typeId); - } - break; - case T_ArrayExpr: - JumbleExpr(jstate, (Node *) ((ArrayExpr *) node)->elements); - break; - case T_RowExpr: - JumbleExpr(jstate, (Node *) ((RowExpr *) node)->args); - break; - case T_RowCompareExpr: - { - RowCompareExpr *rcexpr = (RowCompareExpr *) node; - - APP_JUMB(rcexpr->rctype); - JumbleExpr(jstate, (Node *) rcexpr->largs); - JumbleExpr(jstate, (Node *) rcexpr->rargs); - } - break; - case T_CoalesceExpr: - JumbleExpr(jstate, (Node *) ((CoalesceExpr *) node)->args); - break; - case T_MinMaxExpr: - { - MinMaxExpr *mmexpr = (MinMaxExpr *) node; - - APP_JUMB(mmexpr->op); - JumbleExpr(jstate, (Node *) mmexpr->args); - } - break; - case T_SQLValueFunction: - { - SQLValueFunction *svf = (SQLValueFunction *) node; - - APP_JUMB(svf->op); - /* type is fully determined by op */ - APP_JUMB(svf->typmod); - } - break; - case T_XmlExpr: - { - XmlExpr *xexpr = (XmlExpr *) node; - - APP_JUMB(xexpr->op); - JumbleExpr(jstate, (Node *) xexpr->named_args); - JumbleExpr(jstate, (Node *) xexpr->args); - } - break; - case T_NullTest: - { - NullTest *nt = (NullTest *) node; - - APP_JUMB(nt->nulltesttype); - JumbleExpr(jstate, (Node *) nt->arg); - } - break; - case T_BooleanTest: - { - BooleanTest *bt = (BooleanTest *) node; - - APP_JUMB(bt->booltesttype); - JumbleExpr(jstate, (Node *) bt->arg); - } - break; - case T_CoerceToDomain: - { - CoerceToDomain *cd = (CoerceToDomain *) node; - - APP_JUMB(cd->resulttype); - JumbleExpr(jstate, (Node *) cd->arg); - } - break; - case T_CoerceToDomainValue: - { - CoerceToDomainValue *cdv = (CoerceToDomainValue *) node; - - APP_JUMB(cdv->typeId); - } - break; - case T_SetToDefault: - { - SetToDefault *sd = (SetToDefault *) node; - - APP_JUMB(sd->typeId); - } - break; - case T_CurrentOfExpr: - { - CurrentOfExpr *ce = (CurrentOfExpr *) node; - - APP_JUMB(ce->cvarno); - if (ce->cursor_name) - APP_JUMB_STRING(ce->cursor_name); - APP_JUMB(ce->cursor_param); - } - break; - case T_NextValueExpr: - { - NextValueExpr *nve = (NextValueExpr *) node; - - APP_JUMB(nve->seqid); - APP_JUMB(nve->typeId); - } - break; - case T_InferenceElem: - { - InferenceElem *ie = (InferenceElem *) node; - - APP_JUMB(ie->infercollid); - APP_JUMB(ie->inferopclass); - JumbleExpr(jstate, ie->expr); - } - break; - case T_TargetEntry: - { - TargetEntry *tle = (TargetEntry *) node; - - APP_JUMB(tle->resno); - APP_JUMB(tle->ressortgroupref); - JumbleExpr(jstate, (Node *) tle->expr); - } - break; - case T_RangeTblRef: - { - RangeTblRef *rtr = (RangeTblRef *) node; - - APP_JUMB(rtr->rtindex); - } - break; - case T_JoinExpr: - { - JoinExpr *join = (JoinExpr *) node; - - APP_JUMB(join->jointype); - APP_JUMB(join->isNatural); - APP_JUMB(join->rtindex); - JumbleExpr(jstate, join->larg); - JumbleExpr(jstate, join->rarg); - JumbleExpr(jstate, join->quals); - } - break; - case T_FromExpr: - { - FromExpr *from = (FromExpr *) node; - - JumbleExpr(jstate, (Node *) from->fromlist); - JumbleExpr(jstate, from->quals); - } - break; - case T_OnConflictExpr: - { - OnConflictExpr *conf = (OnConflictExpr *) node; - - APP_JUMB(conf->action); - JumbleExpr(jstate, (Node *) conf->arbiterElems); - JumbleExpr(jstate, conf->arbiterWhere); - JumbleExpr(jstate, (Node *) conf->onConflictSet); - JumbleExpr(jstate, conf->onConflictWhere); - APP_JUMB(conf->constraint); - APP_JUMB(conf->exclRelIndex); - JumbleExpr(jstate, (Node *) conf->exclRelTlist); - } - break; - case T_List: - foreach(temp, (List *) node) - { - JumbleExpr(jstate, (Node *) lfirst(temp)); - } - break; - case T_IntList: - foreach(temp, (List *) node) - { - APP_JUMB(lfirst_int(temp)); - } - break; - case T_SortGroupClause: - { - SortGroupClause *sgc = (SortGroupClause *) node; - - APP_JUMB(sgc->tleSortGroupRef); - APP_JUMB(sgc->eqop); - APP_JUMB(sgc->sortop); - APP_JUMB(sgc->nulls_first); - } - break; - case T_GroupingSet: - { - GroupingSet *gsnode = (GroupingSet *) node; - - JumbleExpr(jstate, (Node *) gsnode->content); - } - break; - case T_WindowClause: - { - WindowClause *wc = (WindowClause *) node; - - APP_JUMB(wc->winref); - APP_JUMB(wc->frameOptions); - JumbleExpr(jstate, (Node *) wc->partitionClause); - JumbleExpr(jstate, (Node *) wc->orderClause); - JumbleExpr(jstate, wc->startOffset); - JumbleExpr(jstate, wc->endOffset); - } - break; - case T_CommonTableExpr: - { - CommonTableExpr *cte = (CommonTableExpr *) node; - - /* we store the string name because RTE_CTE RTEs need it */ - APP_JUMB_STRING(cte->ctename); - APP_JUMB(cte->ctematerialized); - JumbleQuery(jstate, castNode(Query, cte->ctequery)); - } - break; - case T_SetOperationStmt: - { - SetOperationStmt *setop = (SetOperationStmt *) node; - - APP_JUMB(setop->op); - APP_JUMB(setop->all); - JumbleExpr(jstate, setop->larg); - JumbleExpr(jstate, setop->rarg); - } - break; - case T_RangeTblFunction: - { - RangeTblFunction *rtfunc = (RangeTblFunction *) node; - - JumbleExpr(jstate, rtfunc->funcexpr); - } - break; - case T_TableFunc: - { - TableFunc *tablefunc = (TableFunc *) node; - - JumbleExpr(jstate, tablefunc->docexpr); - JumbleExpr(jstate, tablefunc->rowexpr); - JumbleExpr(jstate, (Node *) tablefunc->colexprs); - } - break; - case T_TableSampleClause: - { - TableSampleClause *tsc = (TableSampleClause *) node; - - APP_JUMB(tsc->tsmhandler); - JumbleExpr(jstate, (Node *) tsc->args); - JumbleExpr(jstate, (Node *) tsc->repeatable); - } - break; - default: - /* Only a warning, since we can stumble along anyway */ - elog(WARNING, "unrecognized node type: %d", - (int) nodeTag(node)); - break; - } -} - -/* - * Record location of constant within query string of query tree - * that is currently being walked. - */ -static void -RecordConstLocation(pgssJumbleState *jstate, int location) -{ - /* -1 indicates unknown or undefined location */ - if (location >= 0) - { - /* enlarge array if needed */ - if (jstate->clocations_count >= jstate->clocations_buf_size) - { - jstate->clocations_buf_size *= 2; - jstate->clocations = (pgssLocationLen *) - repalloc(jstate->clocations, - jstate->clocations_buf_size * - sizeof(pgssLocationLen)); - } - jstate->clocations[jstate->clocations_count].location = location; - /* initialize lengths to -1 to simplify fill_in_constant_lengths */ - jstate->clocations[jstate->clocations_count].length = -1; - jstate->clocations_count++; - } -} - /* * Generate a normalized version of the query string that will be used to * represent all similar queries. @@ -3319,7 +2564,7 @@ RecordConstLocation(pgssJumbleState *jstate, int location) * Returns a palloc'd string. */ static char * -generate_normalized_query(pgssJumbleState *jstate, const char *query, +generate_normalized_query(JumbleState *jstate, const char *query, int query_loc, int *query_len_p) { char *norm_query; @@ -3426,10 +2671,10 @@ generate_normalized_query(pgssJumbleState *jstate, const char *query, * reason for a constant to start with a '-'. */ static void -fill_in_constant_lengths(pgssJumbleState *jstate, const char *query, +fill_in_constant_lengths(JumbleState *jstate, const char *query, int query_loc) { - pgssLocationLen *locs; + LocationLen *locs; core_yyscan_t yyscanner; core_yy_extra_type yyextra; core_YYSTYPE yylval; @@ -3443,7 +2688,7 @@ fill_in_constant_lengths(pgssJumbleState *jstate, const char *query, */ if (jstate->clocations_count > 1) qsort(jstate->clocations, jstate->clocations_count, - sizeof(pgssLocationLen), comp_location); + sizeof(LocationLen), comp_location); locs = jstate->clocations; /* initialize the flex scanner --- should match raw_parser() */ @@ -3523,13 +2768,13 @@ fill_in_constant_lengths(pgssJumbleState *jstate, const char *query, } /* - * comp_location: comparator for qsorting pgssLocationLen structs by location + * comp_location: comparator for qsorting LocationLen structs by location */ static int comp_location(const void *a, const void *b) { - int l = ((const pgssLocationLen *) a)->location; - int r = ((const pgssLocationLen *) b)->location; + int l = ((const LocationLen *) a)->location; + int r = ((const LocationLen *) b)->location; if (l < r) return -1; diff --git a/contrib/pg_stat_statements/pg_stat_statements.conf b/contrib/pg_stat_statements/pg_stat_statements.conf index 13346e2807..d98411ea3f 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.conf +++ b/contrib/pg_stat_statements/pg_stat_statements.conf @@ -1 +1,2 @@ shared_preload_libraries = 'pg_stat_statements' +compute_queryid = on diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index a218d78bef..6834ea3735 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -7536,6 +7536,24 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; <title>Statistics Monitoring</title> <variablelist> + <varlistentry id="guc-compute-queryid" xreflabel="compute_queryid"> + <term><varname>compute_queryid</varname> (<type>boolean</type>) + <indexterm> + <primary><varname>compute_queryid</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Enables or disables in core query identifier computation.arameter. The + <xref linkend="pgstatstatements"/> extension requires a query + identifier to be computed. Note that an external module can + alternatively be used if the in core query identifier computation + specification doesn't suit your need. In this case, in core + computation must be disabled. The default is <literal>off</literal>. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><varname>log_statement_stats</varname> (<type>boolean</type>) <indexterm> diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 0f3a70c49a..ddfb97b543 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -46,6 +46,8 @@ #include "parser/parsetree.h" #include "rewrite/rewriteManip.h" #include "utils/builtins.h" +#include "utils/guc.h" +#include "utils/queryjumble.h" #include "utils/rel.h" @@ -107,6 +109,7 @@ parse_analyze(RawStmt *parseTree, const char *sourceText, { ParseState *pstate = make_parsestate(NULL); Query *query; + JumbleState *jstate = NULL; Assert(sourceText != NULL); /* required as of 8.4 */ @@ -119,8 +122,11 @@ parse_analyze(RawStmt *parseTree, const char *sourceText, query = transformTopLevelStmt(pstate, parseTree); + if (compute_queryid) + jstate = JumbleQuery(query, sourceText); + if (post_parse_analyze_hook) - (*post_parse_analyze_hook) (pstate, query); + (*post_parse_analyze_hook) (pstate, query, jstate); free_parsestate(pstate); @@ -140,6 +146,7 @@ parse_analyze_varparams(RawStmt *parseTree, const char *sourceText, { ParseState *pstate = make_parsestate(NULL); Query *query; + JumbleState *jstate = NULL; Assert(sourceText != NULL); /* required as of 8.4 */ @@ -152,8 +159,11 @@ parse_analyze_varparams(RawStmt *parseTree, const char *sourceText, /* make sure all is well with parameter types */ check_variable_parameters(pstate, query); + if (compute_queryid) + jstate = JumbleQuery(query, sourceText); + if (post_parse_analyze_hook) - (*post_parse_analyze_hook) (pstate, query); + (*post_parse_analyze_hook) (pstate, query, jstate); free_parsestate(pstate); diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 8a0332dde9..c11af652de 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -653,6 +653,7 @@ pg_analyze_and_rewrite_params(RawStmt *parsetree, ParseState *pstate; Query *query; List *querytree_list; + JumbleState *jstate = NULL; Assert(query_string != NULL); /* required as of 8.4 */ @@ -671,8 +672,11 @@ pg_analyze_and_rewrite_params(RawStmt *parsetree, query = transformTopLevelStmt(pstate, parsetree); + if (compute_queryid) + jstate = JumbleQuery(query, query_string); + if (post_parse_analyze_hook) - (*post_parse_analyze_hook) (pstate, query); + (*post_parse_analyze_hook) (pstate, query, jstate); free_parsestate(pstate); diff --git a/src/backend/utils/misc/Makefile b/src/backend/utils/misc/Makefile index 2397fc2453..1d5327cf64 100644 --- a/src/backend/utils/misc/Makefile +++ b/src/backend/utils/misc/Makefile @@ -22,6 +22,7 @@ OBJS = \ pg_rusage.o \ ps_status.o \ queryenvironment.o \ + queryjumble.o \ rls.o \ sampling.o \ superuser.o \ diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 855076b1fd..74a7d7f992 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -512,6 +512,7 @@ extern const struct config_enum_entry dynamic_shared_memory_options[]; /* * GUC option variables that are exported from this module */ +bool compute_queryid = false; bool log_duration = false; bool Debug_print_plan = false; bool Debug_print_parse = false; @@ -1407,6 +1408,15 @@ static struct config_bool ConfigureNamesBool[] = true, NULL, NULL, NULL }, + { + {"compute_queryid", PGC_SUSET, STATS_MONITORING, + gettext_noop("Compute query identifiers."), + NULL + }, + &compute_queryid, + false, + NULL, NULL, NULL + }, { {"log_parser_stats", PGC_SUSET, STATS_MONITORING, gettext_noop("Writes parser performance statistics to the server log."), diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index f46c2dd7a8..31230b5704 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -594,6 +594,7 @@ # - Monitoring - +#compute_queryid = off #log_parser_stats = off #log_planner_stats = off #log_executor_stats = off diff --git a/src/backend/utils/misc/queryjumble.c b/src/backend/utils/misc/queryjumble.c new file mode 100644 index 0000000000..ae84fcac6e --- /dev/null +++ b/src/backend/utils/misc/queryjumble.c @@ -0,0 +1,834 @@ +/*------------------------------------------------------------------------- + * + * queryjumble.c + * Query normalization and fingerprinting. + * + * Normalization is a process whereby similar queries, typically differing only + * in their constants (though the exact rules are somewhat more subtle than + * that) are recognized as equivalent, and are tracked as a single entry. This + * is particularly useful for non-prepared queries. + * + * Normalization is implemented by fingerprinting queries, selectively + * serializing those fields of each query tree's nodes that are judged to be + * essential to the query. This is referred to as a query jumble. This is + * distinct from a regular serialization in that various extraneous + * information is ignored as irrelevant or not essential to the query, such + * as the collations of Vars and, most notably, the values of constants. + * + * This jumble is acquired at the end of parse analysis of each query, and + * a 64-bit hash of it is stored into the query's Query.queryId field. + * The server then copies this value around, making it available in plan + * tree(s) generated from the query. The executor can then use this value + * to blame query costs on the proper queryId. + * + * Portions Copyright (c) 1996-2020, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * + * IDENTIFICATION + * src/backend/utils/misc/queryjumble.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "common/hashfn.h" +#include "miscadmin.h" +#include "parser/scansup.h" +#include "utils/queryjumble.h" + +#define JUMBLE_SIZE 1024 /* query serialization buffer size */ + +static uint64 compute_utility_queryid(const char *str, int query_len); +static void AppendJumble(JumbleState *jstate, + const unsigned char *item, Size size); +static void JumbleQueryInternal(JumbleState *jstate, Query *query); +static void JumbleRangeTable(JumbleState *jstate, List *rtable); +static void JumbleRowMarks(JumbleState *jstate, List *rowMarks); +static void JumbleExpr(JumbleState *jstate, Node *node); +static void RecordConstLocation(JumbleState *jstate, int location); + +/* + * Given a possibly multi-statement source string, confine our attention to the + * relevant part of the string. + */ +const char * +clean_querytext(const char *query, int *location, int *len) +{ + int query_location = *location; + int query_len = *len; + + /* First apply starting offset, unless it's -1 (unknown). */ + if (query_location >= 0) + { + Assert(query_location <= strlen(query)); + query += query_location; + /* Length of 0 (or -1) means "rest of string" */ + if (query_len <= 0) + query_len = strlen(query); + else + Assert(query_len <= strlen(query)); + } + else + { + /* If query location is unknown, distrust query_len as well */ + query_location = 0; + query_len = strlen(query); + } + + /* + * Discard leading and trailing whitespace, too. Use scanner_isspace() + * not libc's isspace(), because we want to match the lexer's behavior. + */ + while (query_len > 0 && scanner_isspace(query[0])) + query++, query_location++, query_len--; + while (query_len > 0 && scanner_isspace(query[query_len - 1])) + query_len--; + + *location = query_location; + *len = query_len; + + return query; +} + +JumbleState * +JumbleQuery(Query *query, const char *querytext) +{ + JumbleState *jstate = NULL; + if (query->utilityStmt) + { + const char *sql; + int query_location = query->stmt_location; + int query_len = query->stmt_len; + + /* + * Confine our attention to the relevant part of the string, if the + * query is a portion of a multi-statement source string. + */ + sql = clean_querytext(querytext, &query_location, &query_len); + + query->queryId = compute_utility_queryid(sql, query_len); + } + else + { + jstate = (JumbleState *) palloc(sizeof(JumbleState)); + + /* Set up workspace for query jumbling */ + jstate->jumble = (unsigned char *) palloc(JUMBLE_SIZE); + jstate->jumble_len = 0; + jstate->clocations_buf_size = 32; + jstate->clocations = (LocationLen *) + palloc(jstate->clocations_buf_size * sizeof(LocationLen)); + jstate->clocations_count = 0; + jstate->highest_extern_param_id = 0; + + /* Compute query ID and mark the Query node with it */ + JumbleQueryInternal(jstate, query); + query->queryId = DatumGetUInt64(hash_any_extended(jstate->jumble, + jstate->jumble_len, + 0)); + + /* + * If we are unlucky enough to get a hash of zero, use 1 instead, to + * prevent confusion with the utility-statement case. + */ + if (query->queryId == UINT64CONST(0)) + query->queryId = UINT64CONST(1); + } + + return jstate; +} + +/* + * Compute a query identifier for the given utility query string. + */ +static uint64 +compute_utility_queryid(const char *str, int query_len) +{ + uint64 queryId; + + queryId = DatumGetUInt64(hash_any_extended((const unsigned char *) str, + query_len, 0)); + + /* + * If we are unlucky enough to get a hash of zero(invalid), use + * queryID as 2 instead, queryID 1 is already in use for normal + * statements. + */ + if (queryId == UINT64CONST(0)) + queryId = UINT64CONST(2); + + return queryId; +} + +/* + * AppendJumble: Append a value that is substantive in a given query to + * the current jumble. + */ +static void +AppendJumble(JumbleState *jstate, const unsigned char *item, Size size) +{ + unsigned char *jumble = jstate->jumble; + Size jumble_len = jstate->jumble_len; + + /* + * Whenever the jumble buffer is full, we hash the current contents and + * reset the buffer to contain just that hash value, thus relying on the + * hash to summarize everything so far. + */ + while (size > 0) + { + Size part_size; + + if (jumble_len >= JUMBLE_SIZE) + { + uint64 start_hash; + + start_hash = DatumGetUInt64(hash_any_extended(jumble, + JUMBLE_SIZE, 0)); + memcpy(jumble, &start_hash, sizeof(start_hash)); + jumble_len = sizeof(start_hash); + } + part_size = Min(size, JUMBLE_SIZE - jumble_len); + memcpy(jumble + jumble_len, item, part_size); + jumble_len += part_size; + item += part_size; + size -= part_size; + } + jstate->jumble_len = jumble_len; +} + +/* + * Wrappers around AppendJumble to encapsulate details of serialization + * of individual local variable elements. + */ +#define APP_JUMB(item) \ + AppendJumble(jstate, (const unsigned char *) &(item), sizeof(item)) +#define APP_JUMB_STRING(str) \ + AppendJumble(jstate, (const unsigned char *) (str), strlen(str) + 1) + +/* + * JumbleQueryInternal: Selectively serialize the query tree, appending + * significant data to the "query jumble" while ignoring nonsignificant data. + * + * Rule of thumb for what to include is that we should ignore anything not + * semantically significant (such as alias names) as well as anything that can + * be deduced from child nodes (else we'd just be double-hashing that piece + * of information). + */ +static void +JumbleQueryInternal(JumbleState *jstate, Query *query) +{ + Assert(IsA(query, Query)); + Assert(query->utilityStmt == NULL); + + APP_JUMB(query->commandType); + /* resultRelation is usually predictable from commandType */ + JumbleExpr(jstate, (Node *) query->cteList); + JumbleRangeTable(jstate, query->rtable); + JumbleExpr(jstate, (Node *) query->jointree); + JumbleExpr(jstate, (Node *) query->targetList); + JumbleExpr(jstate, (Node *) query->onConflict); + JumbleExpr(jstate, (Node *) query->returningList); + JumbleExpr(jstate, (Node *) query->groupClause); + JumbleExpr(jstate, (Node *) query->groupingSets); + JumbleExpr(jstate, query->havingQual); + JumbleExpr(jstate, (Node *) query->windowClause); + JumbleExpr(jstate, (Node *) query->distinctClause); + JumbleExpr(jstate, (Node *) query->sortClause); + JumbleExpr(jstate, query->limitOffset); + JumbleExpr(jstate, query->limitCount); + JumbleRowMarks(jstate, query->rowMarks); + JumbleExpr(jstate, query->setOperations); +} + +/* + * Jumble a range table + */ +static void +JumbleRangeTable(JumbleState *jstate, List *rtable) +{ + ListCell *lc; + + foreach(lc, rtable) + { + RangeTblEntry *rte = lfirst_node(RangeTblEntry, lc); + + APP_JUMB(rte->rtekind); + switch (rte->rtekind) + { + case RTE_RELATION: + APP_JUMB(rte->relid); + JumbleExpr(jstate, (Node *) rte->tablesample); + break; + case RTE_SUBQUERY: + JumbleQueryInternal(jstate, rte->subquery); + break; + case RTE_JOIN: + APP_JUMB(rte->jointype); + break; + case RTE_FUNCTION: + JumbleExpr(jstate, (Node *) rte->functions); + break; + case RTE_TABLEFUNC: + JumbleExpr(jstate, (Node *) rte->tablefunc); + break; + case RTE_VALUES: + JumbleExpr(jstate, (Node *) rte->values_lists); + break; + case RTE_CTE: + + /* + * Depending on the CTE name here isn't ideal, but it's the + * only info we have to identify the referenced WITH item. + */ + APP_JUMB_STRING(rte->ctename); + APP_JUMB(rte->ctelevelsup); + break; + case RTE_NAMEDTUPLESTORE: + APP_JUMB_STRING(rte->enrname); + break; + case RTE_RESULT: + break; + default: + elog(ERROR, "unrecognized RTE kind: %d", (int) rte->rtekind); + break; + } + } +} + +/* + * Jumble a rowMarks list + */ +static void +JumbleRowMarks(JumbleState *jstate, List *rowMarks) +{ + ListCell *lc; + + foreach(lc, rowMarks) + { + RowMarkClause *rowmark = lfirst_node(RowMarkClause, lc); + + if (!rowmark->pushedDown) + { + APP_JUMB(rowmark->rti); + APP_JUMB(rowmark->strength); + APP_JUMB(rowmark->waitPolicy); + } + } +} + +/* + * Jumble an expression tree + * + * In general this function should handle all the same node types that + * expression_tree_walker() does, and therefore it's coded to be as parallel + * to that function as possible. However, since we are only invoked on + * queries immediately post-parse-analysis, we need not handle node types + * that only appear in planning. + * + * Note: the reason we don't simply use expression_tree_walker() is that the + * point of that function is to support tree walkers that don't care about + * most tree node types, but here we care about all types. We should complain + * about any unrecognized node type. + */ +static void +JumbleExpr(JumbleState *jstate, Node *node) +{ + ListCell *temp; + + if (node == NULL) + return; + + /* Guard against stack overflow due to overly complex expressions */ + check_stack_depth(); + + /* + * We always emit the node's NodeTag, then any additional fields that are + * considered significant, and then we recurse to any child nodes. + */ + APP_JUMB(node->type); + + switch (nodeTag(node)) + { + case T_Var: + { + Var *var = (Var *) node; + + APP_JUMB(var->varno); + APP_JUMB(var->varattno); + APP_JUMB(var->varlevelsup); + } + break; + case T_Const: + { + Const *c = (Const *) node; + + /* We jumble only the constant's type, not its value */ + APP_JUMB(c->consttype); + /* Also, record its parse location for query normalization */ + RecordConstLocation(jstate, c->location); + } + break; + case T_Param: + { + Param *p = (Param *) node; + + APP_JUMB(p->paramkind); + APP_JUMB(p->paramid); + APP_JUMB(p->paramtype); + /* Also, track the highest external Param id */ + if (p->paramkind == PARAM_EXTERN && + p->paramid > jstate->highest_extern_param_id) + jstate->highest_extern_param_id = p->paramid; + } + break; + case T_Aggref: + { + Aggref *expr = (Aggref *) node; + + APP_JUMB(expr->aggfnoid); + JumbleExpr(jstate, (Node *) expr->aggdirectargs); + JumbleExpr(jstate, (Node *) expr->args); + JumbleExpr(jstate, (Node *) expr->aggorder); + JumbleExpr(jstate, (Node *) expr->aggdistinct); + JumbleExpr(jstate, (Node *) expr->aggfilter); + } + break; + case T_GroupingFunc: + { + GroupingFunc *grpnode = (GroupingFunc *) node; + + JumbleExpr(jstate, (Node *) grpnode->refs); + } + break; + case T_WindowFunc: + { + WindowFunc *expr = (WindowFunc *) node; + + APP_JUMB(expr->winfnoid); + APP_JUMB(expr->winref); + JumbleExpr(jstate, (Node *) expr->args); + JumbleExpr(jstate, (Node *) expr->aggfilter); + } + break; + case T_SubscriptingRef: + { + SubscriptingRef *sbsref = (SubscriptingRef *) node; + + JumbleExpr(jstate, (Node *) sbsref->refupperindexpr); + JumbleExpr(jstate, (Node *) sbsref->reflowerindexpr); + JumbleExpr(jstate, (Node *) sbsref->refexpr); + JumbleExpr(jstate, (Node *) sbsref->refassgnexpr); + } + break; + case T_FuncExpr: + { + FuncExpr *expr = (FuncExpr *) node; + + APP_JUMB(expr->funcid); + JumbleExpr(jstate, (Node *) expr->args); + } + break; + case T_NamedArgExpr: + { + NamedArgExpr *nae = (NamedArgExpr *) node; + + APP_JUMB(nae->argnumber); + JumbleExpr(jstate, (Node *) nae->arg); + } + break; + case T_OpExpr: + case T_DistinctExpr: /* struct-equivalent to OpExpr */ + case T_NullIfExpr: /* struct-equivalent to OpExpr */ + { + OpExpr *expr = (OpExpr *) node; + + APP_JUMB(expr->opno); + JumbleExpr(jstate, (Node *) expr->args); + } + break; + case T_ScalarArrayOpExpr: + { + ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) node; + + APP_JUMB(expr->opno); + APP_JUMB(expr->useOr); + JumbleExpr(jstate, (Node *) expr->args); + } + break; + case T_BoolExpr: + { + BoolExpr *expr = (BoolExpr *) node; + + APP_JUMB(expr->boolop); + JumbleExpr(jstate, (Node *) expr->args); + } + break; + case T_SubLink: + { + SubLink *sublink = (SubLink *) node; + + APP_JUMB(sublink->subLinkType); + APP_JUMB(sublink->subLinkId); + JumbleExpr(jstate, (Node *) sublink->testexpr); + JumbleQueryInternal(jstate, castNode(Query, sublink->subselect)); + } + break; + case T_FieldSelect: + { + FieldSelect *fs = (FieldSelect *) node; + + APP_JUMB(fs->fieldnum); + JumbleExpr(jstate, (Node *) fs->arg); + } + break; + case T_FieldStore: + { + FieldStore *fstore = (FieldStore *) node; + + JumbleExpr(jstate, (Node *) fstore->arg); + JumbleExpr(jstate, (Node *) fstore->newvals); + } + break; + case T_RelabelType: + { + RelabelType *rt = (RelabelType *) node; + + APP_JUMB(rt->resulttype); + JumbleExpr(jstate, (Node *) rt->arg); + } + break; + case T_CoerceViaIO: + { + CoerceViaIO *cio = (CoerceViaIO *) node; + + APP_JUMB(cio->resulttype); + JumbleExpr(jstate, (Node *) cio->arg); + } + break; + case T_ArrayCoerceExpr: + { + ArrayCoerceExpr *acexpr = (ArrayCoerceExpr *) node; + + APP_JUMB(acexpr->resulttype); + JumbleExpr(jstate, (Node *) acexpr->arg); + JumbleExpr(jstate, (Node *) acexpr->elemexpr); + } + break; + case T_ConvertRowtypeExpr: + { + ConvertRowtypeExpr *crexpr = (ConvertRowtypeExpr *) node; + + APP_JUMB(crexpr->resulttype); + JumbleExpr(jstate, (Node *) crexpr->arg); + } + break; + case T_CollateExpr: + { + CollateExpr *ce = (CollateExpr *) node; + + APP_JUMB(ce->collOid); + JumbleExpr(jstate, (Node *) ce->arg); + } + break; + case T_CaseExpr: + { + CaseExpr *caseexpr = (CaseExpr *) node; + + JumbleExpr(jstate, (Node *) caseexpr->arg); + foreach(temp, caseexpr->args) + { + CaseWhen *when = lfirst_node(CaseWhen, temp); + + JumbleExpr(jstate, (Node *) when->expr); + JumbleExpr(jstate, (Node *) when->result); + } + JumbleExpr(jstate, (Node *) caseexpr->defresult); + } + break; + case T_CaseTestExpr: + { + CaseTestExpr *ct = (CaseTestExpr *) node; + + APP_JUMB(ct->typeId); + } + break; + case T_ArrayExpr: + JumbleExpr(jstate, (Node *) ((ArrayExpr *) node)->elements); + break; + case T_RowExpr: + JumbleExpr(jstate, (Node *) ((RowExpr *) node)->args); + break; + case T_RowCompareExpr: + { + RowCompareExpr *rcexpr = (RowCompareExpr *) node; + + APP_JUMB(rcexpr->rctype); + JumbleExpr(jstate, (Node *) rcexpr->largs); + JumbleExpr(jstate, (Node *) rcexpr->rargs); + } + break; + case T_CoalesceExpr: + JumbleExpr(jstate, (Node *) ((CoalesceExpr *) node)->args); + break; + case T_MinMaxExpr: + { + MinMaxExpr *mmexpr = (MinMaxExpr *) node; + + APP_JUMB(mmexpr->op); + JumbleExpr(jstate, (Node *) mmexpr->args); + } + break; + case T_SQLValueFunction: + { + SQLValueFunction *svf = (SQLValueFunction *) node; + + APP_JUMB(svf->op); + /* type is fully determined by op */ + APP_JUMB(svf->typmod); + } + break; + case T_XmlExpr: + { + XmlExpr *xexpr = (XmlExpr *) node; + + APP_JUMB(xexpr->op); + JumbleExpr(jstate, (Node *) xexpr->named_args); + JumbleExpr(jstate, (Node *) xexpr->args); + } + break; + case T_NullTest: + { + NullTest *nt = (NullTest *) node; + + APP_JUMB(nt->nulltesttype); + JumbleExpr(jstate, (Node *) nt->arg); + } + break; + case T_BooleanTest: + { + BooleanTest *bt = (BooleanTest *) node; + + APP_JUMB(bt->booltesttype); + JumbleExpr(jstate, (Node *) bt->arg); + } + break; + case T_CoerceToDomain: + { + CoerceToDomain *cd = (CoerceToDomain *) node; + + APP_JUMB(cd->resulttype); + JumbleExpr(jstate, (Node *) cd->arg); + } + break; + case T_CoerceToDomainValue: + { + CoerceToDomainValue *cdv = (CoerceToDomainValue *) node; + + APP_JUMB(cdv->typeId); + } + break; + case T_SetToDefault: + { + SetToDefault *sd = (SetToDefault *) node; + + APP_JUMB(sd->typeId); + } + break; + case T_CurrentOfExpr: + { + CurrentOfExpr *ce = (CurrentOfExpr *) node; + + APP_JUMB(ce->cvarno); + if (ce->cursor_name) + APP_JUMB_STRING(ce->cursor_name); + APP_JUMB(ce->cursor_param); + } + break; + case T_NextValueExpr: + { + NextValueExpr *nve = (NextValueExpr *) node; + + APP_JUMB(nve->seqid); + APP_JUMB(nve->typeId); + } + break; + case T_InferenceElem: + { + InferenceElem *ie = (InferenceElem *) node; + + APP_JUMB(ie->infercollid); + APP_JUMB(ie->inferopclass); + JumbleExpr(jstate, ie->expr); + } + break; + case T_TargetEntry: + { + TargetEntry *tle = (TargetEntry *) node; + + APP_JUMB(tle->resno); + APP_JUMB(tle->ressortgroupref); + JumbleExpr(jstate, (Node *) tle->expr); + } + break; + case T_RangeTblRef: + { + RangeTblRef *rtr = (RangeTblRef *) node; + + APP_JUMB(rtr->rtindex); + } + break; + case T_JoinExpr: + { + JoinExpr *join = (JoinExpr *) node; + + APP_JUMB(join->jointype); + APP_JUMB(join->isNatural); + APP_JUMB(join->rtindex); + JumbleExpr(jstate, join->larg); + JumbleExpr(jstate, join->rarg); + JumbleExpr(jstate, join->quals); + } + break; + case T_FromExpr: + { + FromExpr *from = (FromExpr *) node; + + JumbleExpr(jstate, (Node *) from->fromlist); + JumbleExpr(jstate, from->quals); + } + break; + case T_OnConflictExpr: + { + OnConflictExpr *conf = (OnConflictExpr *) node; + + APP_JUMB(conf->action); + JumbleExpr(jstate, (Node *) conf->arbiterElems); + JumbleExpr(jstate, conf->arbiterWhere); + JumbleExpr(jstate, (Node *) conf->onConflictSet); + JumbleExpr(jstate, conf->onConflictWhere); + APP_JUMB(conf->constraint); + APP_JUMB(conf->exclRelIndex); + JumbleExpr(jstate, (Node *) conf->exclRelTlist); + } + break; + case T_List: + foreach(temp, (List *) node) + { + JumbleExpr(jstate, (Node *) lfirst(temp)); + } + break; + case T_IntList: + foreach(temp, (List *) node) + { + APP_JUMB(lfirst_int(temp)); + } + break; + case T_SortGroupClause: + { + SortGroupClause *sgc = (SortGroupClause *) node; + + APP_JUMB(sgc->tleSortGroupRef); + APP_JUMB(sgc->eqop); + APP_JUMB(sgc->sortop); + APP_JUMB(sgc->nulls_first); + } + break; + case T_GroupingSet: + { + GroupingSet *gsnode = (GroupingSet *) node; + + JumbleExpr(jstate, (Node *) gsnode->content); + } + break; + case T_WindowClause: + { + WindowClause *wc = (WindowClause *) node; + + APP_JUMB(wc->winref); + APP_JUMB(wc->frameOptions); + JumbleExpr(jstate, (Node *) wc->partitionClause); + JumbleExpr(jstate, (Node *) wc->orderClause); + JumbleExpr(jstate, wc->startOffset); + JumbleExpr(jstate, wc->endOffset); + } + break; + case T_CommonTableExpr: + { + CommonTableExpr *cte = (CommonTableExpr *) node; + + /* we store the string name because RTE_CTE RTEs need it */ + APP_JUMB_STRING(cte->ctename); + APP_JUMB(cte->ctematerialized); + JumbleQueryInternal(jstate, castNode(Query, cte->ctequery)); + } + break; + case T_SetOperationStmt: + { + SetOperationStmt *setop = (SetOperationStmt *) node; + + APP_JUMB(setop->op); + APP_JUMB(setop->all); + JumbleExpr(jstate, setop->larg); + JumbleExpr(jstate, setop->rarg); + } + break; + case T_RangeTblFunction: + { + RangeTblFunction *rtfunc = (RangeTblFunction *) node; + + JumbleExpr(jstate, rtfunc->funcexpr); + } + break; + case T_TableFunc: + { + TableFunc *tablefunc = (TableFunc *) node; + + JumbleExpr(jstate, tablefunc->docexpr); + JumbleExpr(jstate, tablefunc->rowexpr); + JumbleExpr(jstate, (Node *) tablefunc->colexprs); + } + break; + case T_TableSampleClause: + { + TableSampleClause *tsc = (TableSampleClause *) node; + + APP_JUMB(tsc->tsmhandler); + JumbleExpr(jstate, (Node *) tsc->args); + JumbleExpr(jstate, (Node *) tsc->repeatable); + } + break; + default: + /* Only a warning, since we can stumble along anyway */ + elog(WARNING, "unrecognized node type: %d", + (int) nodeTag(node)); + break; + } +} + +/* + * Record location of constant within query string of query tree + * that is currently being walked. + */ +static void +RecordConstLocation(JumbleState *jstate, int location) +{ + /* -1 indicates unknown or undefined location */ + if (location >= 0) + { + /* enlarge array if needed */ + if (jstate->clocations_count >= jstate->clocations_buf_size) + { + jstate->clocations_buf_size *= 2; + jstate->clocations = (LocationLen *) + repalloc(jstate->clocations, + jstate->clocations_buf_size * + sizeof(LocationLen)); + } + jstate->clocations[jstate->clocations_count].location = location; + /* initialize lengths to -1 to simplify third-party module usage */ + jstate->clocations[jstate->clocations_count].length = -1; + jstate->clocations_count++; + } +} diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h index 4a3c9686f9..6716db6c13 100644 --- a/src/include/parser/analyze.h +++ b/src/include/parser/analyze.h @@ -15,10 +15,12 @@ #define ANALYZE_H #include "parser/parse_node.h" +#include "utils/queryjumble.h" /* Hook for plugins to get control at end of parse analysis */ typedef void (*post_parse_analyze_hook_type) (ParseState *pstate, - Query *query); + Query *query, + JumbleState *jstate); extern PGDLLIMPORT post_parse_analyze_hook_type post_parse_analyze_hook; diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h index 5004ee4177..40c4a75bac 100644 --- a/src/include/utils/guc.h +++ b/src/include/utils/guc.h @@ -248,6 +248,7 @@ extern bool log_btree_build_stats; extern PGDLLIMPORT bool check_function_bodies; extern bool session_auth_is_superuser; +extern bool compute_queryid; extern bool log_duration; extern int log_parameter_max_length; extern int log_parameter_max_length_on_error; diff --git a/src/include/utils/queryjumble.h b/src/include/utils/queryjumble.h new file mode 100644 index 0000000000..14087eea43 --- /dev/null +++ b/src/include/utils/queryjumble.h @@ -0,0 +1,58 @@ +/*------------------------------------------------------------------------- + * + * queryjumble.h + * Query normalization and fingerprinting. + * + * Portions Copyright (c) 1996-2020, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * src/include/utils/queryjumble.h + * + *------------------------------------------------------------------------- + */ +#ifndef QUERYJUBLE_H +#define QUERYJUBLE_H + +#include "nodes/parsenodes.h" + +#define JUMBLE_SIZE 1024 /* query serialization buffer size */ + +/* + * Struct for tracking locations/lengths of constants during normalization + */ +typedef struct LocationLen +{ + int location; /* start offset in query text */ + int length; /* length in bytes, or -1 to ignore */ +} LocationLen; + +/* + * Working state for computing a query jumble and producing a normalized + * query string + */ +typedef struct JumbleState +{ + /* Jumble of current query tree */ + unsigned char *jumble; + + /* Number of bytes used in jumble[] */ + Size jumble_len; + + /* Array of locations of constants that should be removed */ + LocationLen *clocations; + + /* Allocated length of clocations array */ + int clocations_buf_size; + + /* Current number of valid entries in clocations array */ + int clocations_count; + + /* highest Param id we've seen, in order to start normalization correctly */ + int highest_extern_param_id; +} JumbleState; + +const char *clean_querytext(const char *query, int *location, int *len); +JumbleState *JumbleQuery(Query *query, const char *querytext); + +#endif /* QUERYJUMBLE_H */ -- 2.30.1 --34gv3ipmk6hqkg74 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v18-0002-Expose-queryid-in-pg_stat_activity-and-log_line_.patch" ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: do only critical work during single-user vacuum? @ 2022-02-03 18:34 John Naylor <[email protected]> 2022-02-03 18:42 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: John Naylor @ 2022-02-03 18:34 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Andres Freund <[email protected]>; Peter Geoghegan <[email protected]>; PostgreSQL Hackers <[email protected]> On Thu, Feb 3, 2022 at 1:06 PM Robert Haas <[email protected]> wrote: > > On Thu, Dec 9, 2021 at 8:56 PM Andres Freund <[email protected]> wrote: > > I think we should move *away* from single user mode, rather than the > > opposite. It's a substantial code burden and it's hard to use. > > Yes. This thread seems to be largely devoted to the topic of making > single-user vacuum work better, but I don't see anyone asking the > question "why do we have a message that tells people to vacuum in > single user mode in the first place?". It's basically bad advice, with > one small exception that I'll talk about in a minute. The word "advice" sounds like people have a choice, rather than the system not accepting commands anymore. It would be much less painful if the system closed connections and forbade all but superusers to connect, but that sounds like a lot of work. (happy to be proven otherwise) -- John Naylor EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: do only critical work during single-user vacuum? 2022-02-03 18:34 Re: do only critical work during single-user vacuum? John Naylor <[email protected]> @ 2022-02-03 18:42 ` Robert Haas <[email protected]> 2022-02-03 21:18 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-03 21:50 ` Re: do only critical work during single-user vacuum? Andres Freund <[email protected]> 0 siblings, 2 replies; 14+ messages in thread From: Robert Haas @ 2022-02-03 18:42 UTC (permalink / raw) To: John Naylor <[email protected]>; +Cc: Andres Freund <[email protected]>; Peter Geoghegan <[email protected]>; PostgreSQL Hackers <[email protected]> On Thu, Feb 3, 2022 at 1:34 PM John Naylor <[email protected]> wrote: > The word "advice" sounds like people have a choice, rather than the > system not accepting commands anymore. It would be much less painful > if the system closed connections and forbade all but superusers to > connect, but that sounds like a lot of work. (happy to be proven > otherwise) They *do* have a choice. They can continue to operate the system in multi-user mode, they can have read access to their data, and they can run VACUUM and other non-XID-allocating commands to fix the issue. Sure, their application can't run commands that allocate XIDs, but it's not going to be able to do that if they go to single-user mode either. I don't understand why we would want the system to stop accepting connections other than superuser connections. That would provide strictly less functionality and I don't understand what it would gain. But it would still be better than going into single-user mode, which provides even less functionality and has basically no advantages of any kind. Why are you convinced that the user HAS to go to single-user mode? I don't think they have to do that, and I don't think they should want to do that. -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: do only critical work during single-user vacuum? 2022-02-03 18:34 Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-03 18:42 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> @ 2022-02-03 21:18 ` John Naylor <[email protected]> 1 sibling, 0 replies; 14+ messages in thread From: John Naylor @ 2022-02-03 21:18 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Andres Freund <[email protected]>; Peter Geoghegan <[email protected]>; PostgreSQL Hackers <[email protected]> On Thu, Feb 3, 2022 at 1:42 PM Robert Haas <[email protected]> wrote: > > On Thu, Feb 3, 2022 at 1:34 PM John Naylor <[email protected]> wrote: > > The word "advice" sounds like people have a choice, rather than the > > system not accepting commands anymore. It would be much less painful > > if the system closed connections and forbade all but superusers to > > connect, but that sounds like a lot of work. (happy to be proven > > otherwise) > > They *do* have a choice. They can continue to operate the system in > multi-user mode, they can have read access to their data, and they can > run VACUUM and other non-XID-allocating commands to fix the issue. > Sure, their application can't run commands that allocate XIDs, but > it's not going to be able to do that if they go to single-user mode > either. I just checked some client case notes where they tried just that before getting outside help, and both SELECT and VACUUM FREEZE commands were rejected. The failure is clearly indicated in the log. -- John Naylor EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: do only critical work during single-user vacuum? 2022-02-03 18:34 Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-03 18:42 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> @ 2022-02-03 21:50 ` Andres Freund <[email protected]> 2022-02-15 04:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 1 sibling, 1 reply; 14+ messages in thread From: Andres Freund @ 2022-02-03 21:50 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: John Naylor <[email protected]>; Peter Geoghegan <[email protected]>; PostgreSQL Hackers <[email protected]> Hi, On 2022-02-03 13:42:20 -0500, Robert Haas wrote: > They *do* have a choice. They can continue to operate the system in > multi-user mode, they can have read access to their data, and they can > run VACUUM and other non-XID-allocating commands to fix the issue. > Sure, their application can't run commands that allocate XIDs, but > it's not going to be able to do that if they go to single-user mode > either. I wonder if we shouldn't add some exceptions to the xid allocation prevention. It makes sense that we don't allow random DML. But it's e.g. often more realistic to drop / truncate a few tables with unimportant content, rather than spend the time vacuuming those. We could e.g. allow xid consumption within VACUUM, TRUNCATE, DROP TABLE / INDEX when run at the top level for longer than we allow it for anything else. > But it would still be better than going into single-user mode, which > provides even less functionality and has basically no advantages of > any kind. Indeed. Single user is the worst response to this (and just about anything else, really). Even just getting into the single user mode takes a while (shutdown checkpoint). The user interface is completely different (and awful). The buffer cache is completely cold. The system is slower because there's no wal writer / checkpointer running. Which basically is a list of things one absolutely do not wants when confronted with a wraparound situation. Greetings, Andres Freund ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: do only critical work during single-user vacuum? 2022-02-03 18:34 Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-03 18:42 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> 2022-02-03 21:50 ` Re: do only critical work during single-user vacuum? Andres Freund <[email protected]> @ 2022-02-15 04:04 ` John Naylor <[email protected]> 2022-02-15 04:21 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: John Naylor @ 2022-02-15 04:04 UTC (permalink / raw) To: Andres Freund <[email protected]>; +Cc: Robert Haas <[email protected]>; Peter Geoghegan <[email protected]>; PostgreSQL Hackers <[email protected]> On Fri, Feb 4, 2022 at 4:58 AM Robert Haas <[email protected]> wrote: > As I said > before, I know TRUNCATE has been an issue in the past, and if that's > not already fixed in v14, we should. If there's other stuff, we should > fix that too. The failsafe mode does disable truncation as of v14: commit 60f1f09ff44308667ef6c72fbafd68235e55ae27 Author: Peter Geoghegan <[email protected]> Date: Tue Apr 13 12:58:31 2021 -0700 Don't truncate heap when VACUUM's failsafe is in effect. -- To demonstrate to myself, I tried a few vacuums in a debugger session with a breakpoint at GetNewTransactionId(). I've only seen it reach here when heap truncation happens (or the not relevant for wraparound situations FULL and ANALYZE). With the maximum allowable setting of autovacuum_freeze_max_age of 2 billion, the highest allowable vacuum_failsafe_age is 2.1 billion, so heap truncation will be shut off before the warnings start. > And then we should KILL WITH FIRE > the message telling people to use single user mode -- and once we do > that, the question of what the behavior ought to be when someone does > run VACUUM in single user mode becomes a lot less important. Okay, so it sounds like changing the message is enough for v15? The other two things mentioned are nice-to-haves, but wouldn't need to hold back this minimal change, it seems: On Fri, Feb 4, 2022 at 4:50 AM Andres Freund <[email protected]> wrote: > I wonder if we shouldn't add some exceptions to the xid allocation > prevention. It makes sense that we don't allow random DML. But it's e.g. often > more realistic to drop / truncate a few tables with unimportant content, > rather than spend the time vacuuming those. We could e.g. allow xid > consumption within VACUUM, TRUNCATE, DROP TABLE / INDEX when run at the top > level for longer than we allow it for anything else. It seems like this would require having access to "nodetag(parsetree)" of the statement available in GetNewTransactionId. I don't immediately see an easy way to do that...is a global var within the realm of acceptability? On Fri, Feb 4, 2022 at 8:35 AM Andres Freund <[email protected]> wrote: > we'd actually tell the user a bit more what about what is causing the > problem. > > We can compute the: > 1) oldest slot by xmin, with name > 2) oldest walsender by xmin, with pid > 3) oldest prepared transaction id by xid / xmin, with name > 4) oldest in-progress transaction id by xid / xmin, with name > 5) oldest database datfrozenxid, with database name [...] > Also, adding an SRF providing the above in a useful format would be great for > monitoring and for "remote debugging" of problems. I concur it sounds very useful, and not terribly hard, but probably a v16 project. -- John Naylor EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: do only critical work during single-user vacuum? 2022-02-03 18:34 Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-03 18:42 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> 2022-02-03 21:50 ` Re: do only critical work during single-user vacuum? Andres Freund <[email protected]> 2022-02-15 04:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> @ 2022-02-15 04:21 ` Peter Geoghegan <[email protected]> 2022-02-15 06:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Peter Geoghegan @ 2022-02-15 04:21 UTC (permalink / raw) To: John Naylor <[email protected]>; +Cc: Andres Freund <[email protected]>; Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]> On Mon, Feb 14, 2022 at 8:04 PM John Naylor <[email protected]> wrote: > The failsafe mode does disable truncation as of v14: > > commit 60f1f09ff44308667ef6c72fbafd68235e55ae27 > Author: Peter Geoghegan <[email protected]> > Date: Tue Apr 13 12:58:31 2021 -0700 > > Don't truncate heap when VACUUM's failsafe is in effect. That's true, but bear in mind that it only does so when the specific table being vacuumed actually triggers the failsafe. I believe that VACUUM(EMERGENCY) doesn't just limit itself to vacuuming tables where this is guaranteed (or even likely). If I'm not mistaken, it's possible (even likely) that there will be a table whose age(relfrozenxid) is high enough for VACUUM(EMERGENCY) to target the table, and yet not so high that the failsafe will kick in at the earliest opportunity. > To demonstrate to myself, I tried a few vacuums in a debugger session > with a breakpoint at GetNewTransactionId(). I've only seen it reach > here when heap truncation happens (or the not relevant for wraparound > situations FULL and ANALYZE). It's possible for a manually issued VACUUM to directly disable truncation (same with index_cleanup). Without getting into the question of what the ideal behavior might be right now, I can say for sure that it wouldn't be difficult to teach VACUUM(EMERGENCY) to pass down the same options. The failsafe is essentially a mechanism that dynamically changes these options for an ongoing vacuum, once age(relfrozenxid) crosses a certain threshold. There is nothing fundamentally special about that. -- Peter Geoghegan ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: do only critical work during single-user vacuum? 2022-02-03 18:34 Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-03 18:42 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> 2022-02-03 21:50 ` Re: do only critical work during single-user vacuum? Andres Freund <[email protected]> 2022-02-15 04:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-15 04:21 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> @ 2022-02-15 06:04 ` John Naylor <[email protected]> 2022-02-15 14:16 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> 2022-02-15 17:28 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 0 siblings, 2 replies; 14+ messages in thread From: John Naylor @ 2022-02-15 06:04 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: Andres Freund <[email protected]>; Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]> On Tue, Feb 15, 2022 at 11:22 AM Peter Geoghegan <[email protected]> wrote: > > On Mon, Feb 14, 2022 at 8:04 PM John Naylor > <[email protected]> wrote: > > The failsafe mode does disable truncation as of v14: > > > > commit 60f1f09ff44308667ef6c72fbafd68235e55ae27 > > Author: Peter Geoghegan <[email protected]> > > Date: Tue Apr 13 12:58:31 2021 -0700 > > > > Don't truncate heap when VACUUM's failsafe is in effect. > > That's true, but bear in mind that it only does so when the specific > table being vacuumed actually triggers the failsafe. I believe that > VACUUM(EMERGENCY) doesn't just limit itself to vacuuming tables where > this is guaranteed (or even likely). If I'm not mistaken, it's > possible (even likely) that there will be a table whose > age(relfrozenxid) is high enough for VACUUM(EMERGENCY) to target the > table, and yet not so high that the failsafe will kick in at the > earliest opportunity. Well, the point of inventing this new vacuum mode was because I thought that upon reaching xidStopLimit, we couldn't issue commands, period, under the postmaster. If it was easier to get a test instance to xidStopLimit, I certainly would have discovered this sooner. When Andres wondered about getting away from single user mode, I assumed that would involve getting into areas too deep to tackle for v15. As Robert pointed out, lazy_truncate_heap is the only thing that can't happen for vacuum at this point, and fully explains why in versions < 14 our client's attempts to vacuum resulted in error. Since the failsafe mode turns off truncation, vacuum should now *just work* near wraparound. If there is any doubt, we can tighten the check for entering failsafe. Now, it's certainly possible that autovacuum is either not working at all because of something broken, or is not working on the oldest tables at the moment, so one thing we could do is to make VACUUM [with no tables listed] get the tables from pg_class in reverse order of max(xid age, mxid age). That way, the horizon will eventually pull back over time and the admin can optionally cancel the vacuum at some point. Since the order is harmless when it's not needed, we can do that unconditionally. -- John Naylor EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: do only critical work during single-user vacuum? 2022-02-03 18:34 Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-03 18:42 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> 2022-02-03 21:50 ` Re: do only critical work during single-user vacuum? Andres Freund <[email protected]> 2022-02-15 04:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-15 04:21 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 2022-02-15 06:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> @ 2022-02-15 14:16 ` Robert Haas <[email protected]> 1 sibling, 0 replies; 14+ messages in thread From: Robert Haas @ 2022-02-15 14:16 UTC (permalink / raw) To: John Naylor <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]> On Tue, Feb 15, 2022 at 1:04 AM John Naylor <[email protected]> wrote: > Well, the point of inventing this new vacuum mode was because I > thought that upon reaching xidStopLimit, we couldn't issue commands, > period, under the postmaster. If it was easier to get a test instance > to xidStopLimit, I certainly would have discovered this sooner. When > Andres wondered about getting away from single user mode, I assumed > that would involve getting into areas too deep to tackle for v15. As > Robert pointed out, lazy_truncate_heap is the only thing that can't > happen for vacuum at this point, and fully explains why in versions < > 14 our client's attempts to vacuum resulted in error. Since the > failsafe mode turns off truncation, vacuum should now *just work* near > wraparound. If there is any doubt, we can tighten the check for > entering failsafe. +1 to all of that. -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: do only critical work during single-user vacuum? 2022-02-03 18:34 Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-03 18:42 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> 2022-02-03 21:50 ` Re: do only critical work during single-user vacuum? Andres Freund <[email protected]> 2022-02-15 04:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-15 04:21 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 2022-02-15 06:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> @ 2022-02-15 17:28 ` Peter Geoghegan <[email protected]> 2022-02-15 23:17 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 2022-02-16 16:47 ` Re: do only critical work during single-user vacuum? Masahiko Sawada <[email protected]> 1 sibling, 2 replies; 14+ messages in thread From: Peter Geoghegan @ 2022-02-15 17:28 UTC (permalink / raw) To: John Naylor <[email protected]>; +Cc: Andres Freund <[email protected]>; Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]> On Mon, Feb 14, 2022 at 10:04 PM John Naylor <[email protected]> wrote: > Well, the point of inventing this new vacuum mode was because I > thought that upon reaching xidStopLimit, we couldn't issue commands, > period, under the postmaster. If it was easier to get a test instance > to xidStopLimit, I certainly would have discovered this sooner. I did notice from my own testing of the failsafe (by artificially inducing wraparound failure using an XID burning C function) that autovacuum seemed to totally correct the problem, even when the system had already crossed xidStopLimit - it came back on its own. I wasn't completely sure of how robust this effect was, though. > When > Andres wondered about getting away from single user mode, I assumed > that would involve getting into areas too deep to tackle for v15. As > Robert pointed out, lazy_truncate_heap is the only thing that can't > happen for vacuum at this point, and fully explains why in versions < > 14 our client's attempts to vacuum resulted in error. Since the > failsafe mode turns off truncation, vacuum should now *just work* near > wraparound. If there is any doubt, we can tighten the check for > entering failsafe. Obviously having to enter single user mode is horrid. If we can reasonably update the advice to something more reasonable now, then that would help users that find themselves in this situation a great deal. > Now, it's certainly possible that autovacuum is either not working at > all because of something broken, or is not working on the oldest > tables at the moment, so one thing we could do is to make VACUUM [with > no tables listed] get the tables from pg_class in reverse order of > max(xid age, mxid age). That way, the horizon will eventually pull > back over time and the admin can optionally cancel the vacuum at some > point. Since the order is harmless when it's not needed, we can do > that unconditionally. My ongoing work on freezing/relfrozenxid tends to make the age of relfrozenxid much more indicative of the amount of work that VACUUM would have to do when run -- not limited to freezing. You could probably do this anyway, but it's nice that that'll be true. -- Peter Geoghegan ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: do only critical work during single-user vacuum? 2022-02-03 18:34 Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-03 18:42 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> 2022-02-03 21:50 ` Re: do only critical work during single-user vacuum? Andres Freund <[email protected]> 2022-02-15 04:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-15 04:21 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 2022-02-15 06:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-15 17:28 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> @ 2022-02-15 23:17 ` Peter Geoghegan <[email protected]> 1 sibling, 0 replies; 14+ messages in thread From: Peter Geoghegan @ 2022-02-15 23:17 UTC (permalink / raw) To: John Naylor <[email protected]>; +Cc: Andres Freund <[email protected]>; Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]> On Tue, Feb 15, 2022 at 9:28 AM Peter Geoghegan <[email protected]> wrote: > On Mon, Feb 14, 2022 at 10:04 PM John Naylor > <[email protected]> wrote: > > Well, the point of inventing this new vacuum mode was because I > > thought that upon reaching xidStopLimit, we couldn't issue commands, > > period, under the postmaster. If it was easier to get a test instance > > to xidStopLimit, I certainly would have discovered this sooner. > > I did notice from my own testing of the failsafe (by artificially > inducing wraparound failure using an XID burning C function) that > autovacuum seemed to totally correct the problem, even when the system > had already crossed xidStopLimit - it came back on its own. I wasn't > completely sure of how robust this effect was, though. It seemed worth noting this in comments above should_attempt_truncation(). Pushed a commit to do that just now. Thanks -- Peter Geoghegan ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: do only critical work during single-user vacuum? 2022-02-03 18:34 Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-03 18:42 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> 2022-02-03 21:50 ` Re: do only critical work during single-user vacuum? Andres Freund <[email protected]> 2022-02-15 04:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-15 04:21 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 2022-02-15 06:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-15 17:28 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> @ 2022-02-16 16:47 ` Masahiko Sawada <[email protected]> 2022-02-16 17:50 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 1 sibling, 1 reply; 14+ messages in thread From: Masahiko Sawada @ 2022-02-16 16:47 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: John Naylor <[email protected]>; Andres Freund <[email protected]>; Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]> On Wed, Feb 16, 2022 at 2:29 AM Peter Geoghegan <[email protected]> wrote: > > On Mon, Feb 14, 2022 at 10:04 PM John Naylor > <[email protected]> wrote: > > Well, the point of inventing this new vacuum mode was because I > > thought that upon reaching xidStopLimit, we couldn't issue commands, > > period, under the postmaster. If it was easier to get a test instance > > to xidStopLimit, I certainly would have discovered this sooner. > > I did notice from my own testing of the failsafe (by artificially > inducing wraparound failure using an XID burning C function) that > autovacuum seemed to totally correct the problem, even when the system > had already crossed xidStopLimit - it came back on its own. I wasn't > completely sure of how robust this effect was, though. FYI, I've tested the situation that I assumed autovacuum can not correct the problem; when the system had already crossed xidStopLimit, it keeps failing to vacuum on tables that appear in the front of the list and have sufficient garbage to trigger the truncation but are not older than the failsafe limit. But contrary to my assumption, it did correct the problem since autovacuum continues to the next table in the list even after an error. This probably means that autovacuum eventually succeeds to process all tables that trigger the failsafe mode, ensuring advancing datfrozenxid, which is great. Regards, -- Masahiko Sawada EDB: https://www.enterprisedb.com/ ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: do only critical work during single-user vacuum? 2022-02-03 18:34 Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-03 18:42 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> 2022-02-03 21:50 ` Re: do only critical work during single-user vacuum? Andres Freund <[email protected]> 2022-02-15 04:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-15 04:21 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 2022-02-15 06:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-15 17:28 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 2022-02-16 16:47 ` Re: do only critical work during single-user vacuum? Masahiko Sawada <[email protected]> @ 2022-02-16 17:50 ` Peter Geoghegan <[email protected]> 2022-02-16 17:56 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Peter Geoghegan @ 2022-02-16 17:50 UTC (permalink / raw) To: Masahiko Sawada <[email protected]>; +Cc: John Naylor <[email protected]>; Andres Freund <[email protected]>; Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]> On Wed, Feb 16, 2022 at 8:48 AM Masahiko Sawada <[email protected]> wrote: > FYI, I've tested the situation that I assumed autovacuum can not > correct the problem; when the system had already crossed xidStopLimit, > it keeps failing to vacuum on tables that appear in the front of the > list and have sufficient garbage to trigger the truncation but are not > older than the failsafe limit. But contrary to my assumption, it did > correct the problem since autovacuum continues to the next table in > the list even after an error. This probably means that autovacuum > eventually succeeds to process all tables that trigger the failsafe > mode, ensuring advancing datfrozenxid, which is great. Right; it seems as if the situation is much improved, even when the failsafe didn't prevent the system from going over xidStopLimit. If autovacuum alone can bring the system back to a normal state as soon as possible, without a human needing to do anything special, then clearly the general risk is much smaller. Even this worst case scenario where "the failsafe has failed" is not so bad anymore, in practice. I don't think that it really matters if some concurrent non-emergency VACUUMs fail when attempting to truncate the table (it's no worse than ANALYZE failing, for example). Good news! -- Peter Geoghegan ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: do only critical work during single-user vacuum? 2022-02-03 18:34 Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-03 18:42 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> 2022-02-03 21:50 ` Re: do only critical work during single-user vacuum? Andres Freund <[email protected]> 2022-02-15 04:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-15 04:21 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 2022-02-15 06:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-15 17:28 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 2022-02-16 16:47 ` Re: do only critical work during single-user vacuum? Masahiko Sawada <[email protected]> 2022-02-16 17:50 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> @ 2022-02-16 17:56 ` Robert Haas <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Robert Haas @ 2022-02-16 17:56 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; John Naylor <[email protected]>; Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]> On Wed, Feb 16, 2022 at 12:51 PM Peter Geoghegan <[email protected]> wrote: > Good news! +1. But I think we might want to try to write documentation around this. We should explicitly tell people NOT to use single-user mode, because that stupid message has been there for a long time and a lot of people have probably internalized it by now. And we should also tell them that they SHOULD check for prepared transactions, old replication slots, etc. -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2022-02-16 17:56 UTC | newest] Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-10-13 18:11 [PATCH v18 1/3] Move pg_stat_statements query jumbling to core. Julien Rouhaud <[email protected]> 2022-02-03 18:34 Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-03 18:42 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> 2022-02-03 21:18 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-03 21:50 ` Re: do only critical work during single-user vacuum? Andres Freund <[email protected]> 2022-02-15 04:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-15 04:21 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 2022-02-15 06:04 ` Re: do only critical work during single-user vacuum? John Naylor <[email protected]> 2022-02-15 14:16 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]> 2022-02-15 17:28 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 2022-02-15 23:17 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 2022-02-16 16:47 ` Re: do only critical work during single-user vacuum? Masahiko Sawada <[email protected]> 2022-02-16 17:50 ` Re: do only critical work during single-user vacuum? Peter Geoghegan <[email protected]> 2022-02-16 17:56 ` Re: do only critical work during single-user vacuum? Robert Haas <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox