agora inbox for pgsql-hackers@postgresql.orghelp / color / mirror / Atom feed
[PATCH 7/8] A couple more places for incremental sort 6+ messages / 2 participants [nested] [flat]
* [PATCH 5/5] A couple more places for incremental sort @ 2019-07-28 14:03 Tomas Vondra <tomas@2ndquadrant.com> 0 siblings, 0 replies; 6+ messages in thread From: Tomas Vondra @ 2019-07-28 14:03 UTC (permalink / raw) --- src/backend/optimizer/geqo/geqo_eval.c | 2 +- src/backend/optimizer/plan/planner.c | 218 ++++++++++++++++++++++++- 2 files changed, 216 insertions(+), 4 deletions(-) diff --git a/src/backend/optimizer/geqo/geqo_eval.c b/src/backend/optimizer/geqo/geqo_eval.c index 6d897936d7..ff33acc7b6 100644 --- a/src/backend/optimizer/geqo/geqo_eval.c +++ b/src/backend/optimizer/geqo/geqo_eval.c @@ -274,7 +274,7 @@ merge_clump(PlannerInfo *root, List *clumps, Clump *new_clump, int num_gene, * grouping_planner). */ if (old_clump->size + new_clump->size < num_gene) - generate_gather_paths(root, joinrel, false); + generate_useful_gather_paths(root, joinrel, false); /* Find and save the cheapest paths for this joinrel */ set_cheapest(joinrel); diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 84ed69ec5e..15223017c0 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -5070,6 +5070,67 @@ create_ordered_paths(PlannerInfo *root, add_path(ordered_rel, path); } + + /* + * Consider incremental sort with a gather merge on partial paths. + * + * XXX This is probably duplicate with the paths we already generate + * in generate_useful_gather_paths in apply_scanjoin_target_to_paths. + */ + if (enable_incrementalsort) + { + ListCell *lc; + + foreach (lc, input_rel->partial_pathlist) + { + Path *input_path = (Path *) lfirst(lc); + Path *sorted_path = input_path; + bool is_sorted; + int presorted_keys; + double total_groups; + + /* + * We don't care if this is the cheapest partial path - we + * can't simply skip it, because it may be partially sorted + * in which case we want to consider incremental sort on top + * of it (instead of full sort, which is what happens above). + */ + + is_sorted = pathkeys_common_contained_in(root->sort_pathkeys, + input_path->pathkeys, + &presorted_keys); + + /* also ignore already sorted paths */ + if (is_sorted) + continue; + + if (presorted_keys == 0) + continue; + + /* Also consider incremental sort. */ + sorted_path = (Path *) create_incremental_sort_path(root, + ordered_rel, + input_path, + root->sort_pathkeys, + presorted_keys, + limit_tuples); + total_groups = input_path->rows * + input_path->parallel_workers; + sorted_path = (Path *) + create_gather_merge_path(root, ordered_rel, + sorted_path, + sorted_path->pathtarget, + root->sort_pathkeys, NULL, + &total_groups); + + /* Add projection step if needed */ + if (sorted_path->pathtarget != target) + sorted_path = apply_projection_to_path(root, ordered_rel, + sorted_path, target); + + add_path(ordered_rel, sorted_path); + } + } } /* @@ -6570,12 +6631,18 @@ add_paths_to_grouping_rel(PlannerInfo *root, RelOptInfo *input_rel, foreach(lc, partially_grouped_rel->pathlist) { Path *path = (Path *) lfirst(lc); + Path *path_original = path; + bool is_sorted; + int presorted_keys; + + is_sorted = pathkeys_contained_in(root->group_pathkeys, + path->pathkeys); /* * Insert a Sort node, if required. But there's no point in * sorting anything but the cheapest path. */ - if (!pathkeys_contained_in(root->group_pathkeys, path->pathkeys)) + if (!is_sorted) { if (path != partially_grouped_rel->cheapest_total_path) continue; @@ -6606,6 +6673,56 @@ add_paths_to_grouping_rel(PlannerInfo *root, RelOptInfo *input_rel, parse->groupClause, havingQual, dNumGroups)); + + /* + * Now we may consider incremental sort on this path, but only + * when the path is not already sorted and when incremental sort + * is enabled. + */ + if (is_sorted || !enable_incrementalsort) + continue; + + /* Restore the input path (we might have addes Sort on top). */ + path = path_original; + + is_sorted = pathkeys_common_contained_in(root->group_pathkeys, + path->pathkeys, + &presorted_keys); + + /* We've already skipped fully sorted paths above. */ + Assert(!is_sorted); + + /* no shared prefix, not point in building incremental sort */ + if (presorted_keys == 0) + continue; + + path = (Path *) create_incremental_sort_path(root, + grouped_rel, + path, + root->group_pathkeys, + presorted_keys, + -1.0); + + if (parse->hasAggs) + add_path(grouped_rel, (Path *) + create_agg_path(root, + grouped_rel, + path, + grouped_rel->reltarget, + parse->groupClause ? AGG_SORTED : AGG_PLAIN, + AGGSPLIT_FINAL_DESERIAL, + parse->groupClause, + havingQual, + agg_final_costs, + dNumGroups)); + else + add_path(grouped_rel, (Path *) + create_group_path(root, + grouped_rel, + path, + parse->groupClause, + havingQual, + dNumGroups)); } } } @@ -6875,6 +6992,60 @@ create_partial_grouping_paths(PlannerInfo *root, dNumPartialGroups)); } } + + /* + * Also consider incremental sort on all partially sorted paths. + */ + if (enable_incrementalsort) + { + foreach(lc, input_rel->pathlist) + { + Path *path = (Path *) lfirst(lc); + bool is_sorted; + int presorted_keys; + + is_sorted = pathkeys_common_contained_in(root->group_pathkeys, + path->pathkeys, + &presorted_keys); + + /* also ignore already sorted paths */ + if (is_sorted) + continue; + + if (presorted_keys == 0) + continue; + + /* add incremental sort */ + path = (Path *) create_incremental_sort_path(root, + partially_grouped_rel, + path, + root->group_pathkeys, + presorted_keys, + -1.0); + + if (parse->hasAggs) + add_path(partially_grouped_rel, (Path *) + create_agg_path(root, + partially_grouped_rel, + path, + partially_grouped_rel->reltarget, + parse->groupClause ? AGG_SORTED : AGG_PLAIN, + AGGSPLIT_INITIAL_SERIAL, + parse->groupClause, + NIL, + agg_partial_costs, + dNumPartialGroups)); + else + add_path(partially_grouped_rel, (Path *) + create_group_path(root, + partially_grouped_rel, + path, + parse->groupClause, + NIL, + dNumPartialGroups)); + } + } + } if (can_sort && cheapest_partial_path != NULL) @@ -7067,10 +7238,11 @@ create_partial_grouping_paths(PlannerInfo *root, static void gather_grouping_paths(PlannerInfo *root, RelOptInfo *rel) { + ListCell *lc; Path *cheapest_partial_path; /* Try Gather for unordered paths and Gather Merge for ordered ones. */ - generate_gather_paths(root, rel, true); + generate_useful_gather_paths(root, rel, true); /* Try cheapest partial path + explicit Sort + Gather Merge. */ cheapest_partial_path = linitial(rel->partial_pathlist); @@ -7096,6 +7268,46 @@ gather_grouping_paths(PlannerInfo *root, RelOptInfo *rel) add_path(rel, path); } + + if (!enable_incrementalsort) + return; + + /* also consider incremental sort on partial paths, if enabled */ + foreach (lc, rel->partial_pathlist) + { + Path *path = (Path *) lfirst(lc); + bool is_sorted; + int presorted_keys; + double total_groups; + + is_sorted = pathkeys_common_contained_in(root->group_pathkeys, + path->pathkeys, + &presorted_keys); + + if (is_sorted) + continue; + + if (presorted_keys == 0) + continue; + + path = (Path *) create_incremental_sort_path(root, + rel, + path, + root->group_pathkeys, + presorted_keys, + -1.0); + + path = (Path *) + create_gather_merge_path(root, + rel, + path, + rel->reltarget, + root->group_pathkeys, + NULL, + &total_groups); + + add_path(rel, path); + } } /* @@ -7197,7 +7409,7 @@ apply_scanjoin_target_to_paths(PlannerInfo *root, * paths by doing it after the final scan/join target has been * applied. */ - generate_gather_paths(root, rel, false); + generate_useful_gather_paths(root, rel, false); /* Can't use parallel query above this level. */ rel->partial_pathlist = NIL; -- 2.21.1 --xvcuvgto6w2bcqjv-- ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH 7/8] A couple more places for incremental sort @ 2019-07-28 14:03 Tomas Vondra <tomas@2ndquadrant.com> 0 siblings, 0 replies; 6+ messages in thread From: Tomas Vondra @ 2019-07-28 14:03 UTC (permalink / raw) --- src/backend/optimizer/geqo/geqo_eval.c | 2 +- src/backend/optimizer/plan/planner.c | 220 ++++++++++++++++++++++++- 2 files changed, 217 insertions(+), 5 deletions(-) diff --git a/src/backend/optimizer/geqo/geqo_eval.c b/src/backend/optimizer/geqo/geqo_eval.c index 6d897936d7..ff33acc7b6 100644 --- a/src/backend/optimizer/geqo/geqo_eval.c +++ b/src/backend/optimizer/geqo/geqo_eval.c @@ -274,7 +274,7 @@ merge_clump(PlannerInfo *root, List *clumps, Clump *new_clump, int num_gene, * grouping_planner). */ if (old_clump->size + new_clump->size < num_gene) - generate_gather_paths(root, joinrel, false); + generate_useful_gather_paths(root, joinrel, false); /* Find and save the cheapest paths for this joinrel */ set_cheapest(joinrel); diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 46dc355af3..2880fcabe8 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -5080,6 +5080,67 @@ create_ordered_paths(PlannerInfo *root, add_path(ordered_rel, path); } + + /* + * Consider incremental sort with a gather merge on partial paths. + * + * XXX This is probably duplicate with the paths we already generate + * in generate_useful_gather_paths in apply_scanjoin_target_to_paths. + */ + if (enable_incrementalsort) + { + ListCell *lc; + + foreach(lc, input_rel->partial_pathlist) + { + Path *input_path = (Path *) lfirst(lc); + Path *sorted_path = input_path; + bool is_sorted; + int presorted_keys; + double total_groups; + + /* + * We don't care if this is the cheapest partial path - we + * can't simply skip it, because it may be partially sorted in + * which case we want to consider incremental sort on top of + * it (instead of full sort, which is what happens above). + */ + + is_sorted = pathkeys_common_contained_in(root->sort_pathkeys, + input_path->pathkeys, + &presorted_keys); + + /* Ignore already sorted paths */ + if (is_sorted) + continue; + + if (presorted_keys == 0) + continue; + + /* Since we have presorted keys, consider incremental sort. */ + sorted_path = (Path *) create_incremental_sort_path(root, + ordered_rel, + input_path, + root->sort_pathkeys, + presorted_keys, + limit_tuples); + total_groups = input_path->rows * + input_path->parallel_workers; + sorted_path = (Path *) + create_gather_merge_path(root, ordered_rel, + sorted_path, + sorted_path->pathtarget, + root->sort_pathkeys, NULL, + &total_groups); + + /* Add projection step if needed */ + if (sorted_path->pathtarget != target) + sorted_path = apply_projection_to_path(root, ordered_rel, + sorted_path, target); + + add_path(ordered_rel, sorted_path); + } + } } /* @@ -6580,12 +6641,18 @@ add_paths_to_grouping_rel(PlannerInfo *root, RelOptInfo *input_rel, foreach(lc, partially_grouped_rel->pathlist) { Path *path = (Path *) lfirst(lc); + Path *path_original = path; + bool is_sorted; + int presorted_keys; + + is_sorted = pathkeys_contained_in(root->group_pathkeys, + path->pathkeys); /* * Insert a Sort node, if required. But there's no point in * sorting anything but the cheapest path. */ - if (!pathkeys_contained_in(root->group_pathkeys, path->pathkeys)) + if (!is_sorted) { if (path != partially_grouped_rel->cheapest_total_path) continue; @@ -6616,6 +6683,56 @@ add_paths_to_grouping_rel(PlannerInfo *root, RelOptInfo *input_rel, parse->groupClause, havingQual, dNumGroups)); + + /* + * Now we may consider incremental sort on this path, but only + * when the path is not already sorted and when incremental + * sort is enabled. + */ + if (is_sorted || !enable_incrementalsort) + continue; + + /* Restore the input path (we might have added Sort on top). */ + path = path_original; + + is_sorted = pathkeys_common_contained_in(root->group_pathkeys, + path->pathkeys, + &presorted_keys); + + /* We've already skipped fully sorted paths above. */ + Assert(!is_sorted); + + /* no shared prefix, not point in building incremental sort */ + if (presorted_keys == 0) + continue; + + path = (Path *) create_incremental_sort_path(root, + grouped_rel, + path, + root->group_pathkeys, + presorted_keys, + -1.0); + + if (parse->hasAggs) + add_path(grouped_rel, (Path *) + create_agg_path(root, + grouped_rel, + path, + grouped_rel->reltarget, + parse->groupClause ? AGG_SORTED : AGG_PLAIN, + AGGSPLIT_FINAL_DESERIAL, + parse->groupClause, + havingQual, + agg_final_costs, + dNumGroups)); + else + add_path(grouped_rel, (Path *) + create_group_path(root, + grouped_rel, + path, + parse->groupClause, + havingQual, + dNumGroups)); } } } @@ -6887,6 +7004,60 @@ create_partial_grouping_paths(PlannerInfo *root, dNumPartialGroups)); } } + + /* + * Also consider incremental sort on all partially sorted paths. + */ + if (enable_incrementalsort) + { + foreach(lc, input_rel->pathlist) + { + Path *path = (Path *) lfirst(lc); + bool is_sorted; + int presorted_keys; + + is_sorted = pathkeys_common_contained_in(root->group_pathkeys, + path->pathkeys, + &presorted_keys); + + /* Ignore already sorted paths */ + if (is_sorted) + continue; + + if (presorted_keys == 0) + continue; + + /* Since we have presorted keys, consider incremental sort. */ + path = (Path *) create_incremental_sort_path(root, + partially_grouped_rel, + path, + root->group_pathkeys, + presorted_keys, + -1.0); + + if (parse->hasAggs) + add_path(partially_grouped_rel, (Path *) + create_agg_path(root, + partially_grouped_rel, + path, + partially_grouped_rel->reltarget, + parse->groupClause ? AGG_SORTED : AGG_PLAIN, + AGGSPLIT_INITIAL_SERIAL, + parse->groupClause, + NIL, + agg_partial_costs, + dNumPartialGroups)); + else + add_path(partially_grouped_rel, (Path *) + create_group_path(root, + partially_grouped_rel, + path, + parse->groupClause, + NIL, + dNumPartialGroups)); + } + } + } if (can_sort && cheapest_partial_path != NULL) @@ -6951,10 +7122,10 @@ create_partial_grouping_paths(PlannerInfo *root, /* We've already skipped fully sorted paths above. */ Assert(!is_sorted); - /* no shared prefix, not point in building incremental sort */ if (presorted_keys == 0) continue; + /* Since we have presorted keys, consider incremental sort. */ path = (Path *) create_incremental_sort_path(root, partially_grouped_rel, path, @@ -7079,10 +7250,11 @@ create_partial_grouping_paths(PlannerInfo *root, static void gather_grouping_paths(PlannerInfo *root, RelOptInfo *rel) { + ListCell *lc; Path *cheapest_partial_path; /* Try Gather for unordered paths and Gather Merge for ordered ones. */ - generate_gather_paths(root, rel, true); + generate_useful_gather_paths(root, rel, true); /* Try cheapest partial path + explicit Sort + Gather Merge. */ cheapest_partial_path = linitial(rel->partial_pathlist); @@ -7108,6 +7280,46 @@ gather_grouping_paths(PlannerInfo *root, RelOptInfo *rel) add_path(rel, path); } + + if (!enable_incrementalsort) + return; + + /* also consider incremental sort on partial paths, if enabled */ + foreach(lc, rel->partial_pathlist) + { + Path *path = (Path *) lfirst(lc); + bool is_sorted; + int presorted_keys; + double total_groups; + + is_sorted = pathkeys_common_contained_in(root->group_pathkeys, + path->pathkeys, + &presorted_keys); + + if (is_sorted) + continue; + + if (presorted_keys == 0) + continue; + + path = (Path *) create_incremental_sort_path(root, + rel, + path, + root->group_pathkeys, + presorted_keys, + -1.0); + + path = (Path *) + create_gather_merge_path(root, + rel, + path, + rel->reltarget, + root->group_pathkeys, + NULL, + &total_groups); + + add_path(rel, path); + } } /* @@ -7209,7 +7421,7 @@ apply_scanjoin_target_to_paths(PlannerInfo *root, * paths by doing it after the final scan/join target has been * applied. */ - generate_gather_paths(root, rel, false); + generate_useful_gather_paths(root, rel, false); /* Can't use parallel query above this level. */ rel->partial_pathlist = NIL; -- 2.21.1 --dfcjsgdukgytabqd Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="v39-0008-fix.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v37 09/11] Add support for min/max aggregates for IVM @ 2023-05-31 11:58 Yugo Nagata <nagata@sraoss.co.jp> 0 siblings, 0 replies; 6+ messages in thread From: Yugo Nagata @ 2023-05-31 11:58 UTC (permalink / raw) Supporting min and max is more complicated than count, sum, or avg. For an example of min, when tuples are inserted, the current min value in the view and the min value in the inseteted tuples are compared, then the smaller one is used as the latest min value. On the other hand, when tuples are deleted, if the current min value in the view equals to the min in the deleted tuples, we need re-computation the latest min value from base tables. Otherwise, the current value in the view remains. --- src/backend/commands/createas.c | 45 +++ src/backend/commands/matview.c | 644 +++++++++++++++++++++++++++++++- 2 files changed, 680 insertions(+), 9 deletions(-) diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c index 45a30309951..ed67f399b7c 100644 --- a/src/backend/commands/createas.c +++ b/src/backend/commands/createas.c @@ -1332,6 +1332,51 @@ check_aggregate_supports_ivm(Oid aggfnoid) case F_AVG_FLOAT8: case F_AVG_INTERVAL: + /* min */ + case F_MIN_ANYARRAY: + case F_MIN_INT8: + case F_MIN_INT4: + case F_MIN_INT2: + case F_MIN_OID: + case F_MIN_FLOAT4: + case F_MIN_FLOAT8: + case F_MIN_DATE: + case F_MIN_TIME: + case F_MIN_TIMETZ: + case F_MIN_MONEY: + case F_MIN_TIMESTAMP: + case F_MIN_TIMESTAMPTZ: + case F_MIN_INTERVAL: + case F_MIN_TEXT: + case F_MIN_NUMERIC: + case F_MIN_BPCHAR: + case F_MIN_TID: + case F_MIN_ANYENUM: + case F_MIN_INET: + case F_MIN_PG_LSN: + + /* max */ + case F_MAX_ANYARRAY: + case F_MAX_INT8: + case F_MAX_INT4: + case F_MAX_INT2: + case F_MAX_OID: + case F_MAX_FLOAT4: + case F_MAX_FLOAT8: + case F_MAX_DATE: + case F_MAX_TIME: + case F_MAX_TIMETZ: + case F_MAX_MONEY: + case F_MAX_TIMESTAMP: + case F_MAX_TIMESTAMPTZ: + case F_MAX_INTERVAL: + case F_MAX_TEXT: + case F_MAX_NUMERIC: + case F_MAX_BPCHAR: + case F_MAX_TID: + case F_MAX_ANYENUM: + case F_MAX_INET: + case F_MAX_PG_LSN: return true; default: diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index 710224aa994..3b3871c49e2 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -69,6 +69,34 @@ typedef struct #define MV_INIT_QUERYHASHSIZE 16 +/* MV query type codes */ +#define MV_PLAN_RECALC 1 +#define MV_PLAN_SET_VALUE 2 + +/* + * MI_QueryKey + * + * The key identifying a prepared SPI plan in our query hashtable + */ +typedef struct MV_QueryKey +{ + Oid matview_id; /* OID of materialized view */ + int32 query_type; /* query type ID, see MV_PLAN_XXX above */ +} MV_QueryKey; + +/* + * MV_QueryHashEntry + * + * Hash entry for cached plans used to maintain materialized views. + */ +typedef struct MV_QueryHashEntry +{ + MV_QueryKey key; + SPIPlanPtr plan; + SearchPathMatcher *search_path; /* search_path used for parsing + * and planning */ +} MV_QueryHashEntry; + /* * MV_TriggerHashEntry * @@ -121,6 +149,7 @@ typedef struct DroppedImmvInfo } DroppedImmvInfo; static HTAB *mv_trigger_info = NULL; +static HTAB *mv_query_cache = NULL; static HTAB *dropped_immv = NULL; static HTAB *LastIvmUpdateHash; @@ -210,6 +239,9 @@ static void append_set_clause_for_sum(const char *resname, StringInfo buf_old, static void append_set_clause_for_avg(const char *resname, StringInfo buf_old, StringInfo buf_new, StringInfo aggs_list, const char *aggtype); +static void append_set_clause_for_minmax(const char *resname, StringInfo buf_old, + StringInfo buf_new, StringInfo aggs_list, + bool is_min); static char *get_operation_string(IvmOp op, const char *col, const char *arg1, const char *arg2, const char* count_col, const char *castType); static char *get_null_condition_string(IvmOp op, const char *arg1, const char *arg2, @@ -218,17 +250,30 @@ static void apply_old_delta(const char *matviewname, const char *deltaname_old, List *keys); static void apply_old_delta_with_count(const char *matviewname, const char *deltaname_old, List *keys, StringInfo aggs_list, StringInfo aggs_set, - const char *count_colname); + List *minmax_list, List *is_min_list, + const char *count_colname, + SPITupleTable **tuptable_recalc, uint64 *num_recalc); static void apply_new_delta(const char *matviewname, const char *deltaname_new, StringInfo target_list); static void apply_new_delta_with_count(const char *matviewname, const char* deltaname_new, List *keys, StringInfo target_list, StringInfo aggs_set, const char* count_colname); static char *get_matching_condition_string(List *keys); +static char *get_returning_string(List *minmax_list, List *is_min_list, List *keys); +static char *get_minmax_recalc_condition_string(List *minmax_list, List *is_min_list); +static char *get_select_for_recalc_string(List *keys); +static void recalc_and_set_values(SPITupleTable *tuptable_recalc, int64 num_tuples, + List *namelist, List *keys, Relation matviewRel); +static SPIPlanPtr get_plan_for_recalc(Oid matviewOid, List *namelist, List *keys, Oid *keyTypes); +static SPIPlanPtr get_plan_for_set_values(Oid matviewOid, char *matviewname, List *namelist, + Oid *valTypes); static void generate_equal(StringInfo querybuf, Oid opttype, const char *leftop, const char *rightop); static void mv_InitHashTables(void); +static SPIPlanPtr mv_FetchPreparedPlan(MV_QueryKey *key); +static void mv_HashPreparedPlan(MV_QueryKey *key, SPIPlanPtr plan); +static void mv_BuildQueryKey(MV_QueryKey *key, Oid matview_id, int32 query_type); static void clean_up_IVM_hash_entry(MV_TriggerHashEntry *entry, bool is_abort, SubTransactionId subxid); static void setLastUpdateXid(Oid immv_oid, FullTransactionId xid); @@ -2343,6 +2388,8 @@ apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *n ListCell *lc; int i; List *keys = NIL; + List *minmax_list = NIL; + List *is_min_list = NIL; /* @@ -2424,6 +2471,17 @@ apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *n append_set_clause_for_avg(resname, aggs_set_old, aggs_set_new, aggs_list_buf, format_type_be(aggref->aggtype)); + /* min/max */ + else if (!strcmp(aggname, "min") || !strcmp(aggname, "max")) + { + bool is_min = (!strcmp(aggname, "min")); + + append_set_clause_for_minmax(resname, aggs_set_old, aggs_set_new, aggs_list_buf, is_min); + + /* make a resname list of min and max aggregates */ + minmax_list = lappend(minmax_list, resname); + is_min_list = lappend_int(is_min_list, is_min); + } else elog(ERROR, "unsupported aggregate function: %s", aggname); } @@ -2453,6 +2511,8 @@ apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *n if (old_tuplestores && tuplestore_tuple_count(old_tuplestores) > 0) { EphemeralNamedRelation enr = palloc(sizeof(EphemeralNamedRelationData)); + SPITupleTable *tuptable_recalc = NULL; + uint64 num_recalc; int rc; /* convert tuplestores to ENR, and register for SPI */ @@ -2471,10 +2531,18 @@ apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *n /* apply old delta and get rows to be recalculated */ apply_old_delta_with_count(matviewname, OLD_DELTA_ENRNAME, keys, aggs_list_buf, aggs_set_old, - count_colname); + minmax_list, is_min_list, + count_colname, &tuptable_recalc, &num_recalc); else apply_old_delta(matviewname, OLD_DELTA_ENRNAME, keys); + /* + * If we have min or max, we might have to recalculate aggregate values from base tables + * on some tuples. TIDs and keys such tuples are returned as a result of the above query. + */ + if (minmax_list && tuptable_recalc) + recalc_and_set_values(tuptable_recalc, num_recalc, minmax_list, keys, matviewRel); + } /* For tuple insertion */ if (new_tuplestores && tuplestore_tuple_count(new_tuplestores) > 0) @@ -2666,6 +2734,70 @@ append_set_clause_for_avg(const char *resname, StringInfo buf_old, ); } +/* + * append_set_clause_for_minmax + * + * Append SET clause string for min or max aggregation to given buffers. + * Also, append resnames required for calculating the aggregate value. + * is_min is true if this is min, false if not. + */ +static void +append_set_clause_for_minmax(const char *resname, StringInfo buf_old, + StringInfo buf_new, StringInfo aggs_list, + bool is_min) +{ + char *count_col = IVM_colname("count", resname); + + /* For tuple deletion */ + if (buf_old) + { + /* + * If the new value doesn't became NULL then use the value remaining + * in the view although this will be recomputated afterwords. + */ + appendStringInfo(buf_old, + ", %s = CASE WHEN %s THEN NULL ELSE %s END", + quote_qualified_identifier(NULL, resname), + get_null_condition_string(IVM_SUB, "mv", "t", count_col), + quote_qualified_identifier("mv", resname) + ); + /* count = mv.count - t.count */ + appendStringInfo(buf_old, + ", %s = %s", + quote_qualified_identifier(NULL, count_col), + get_operation_string(IVM_SUB, count_col, "mv", "t", NULL, NULL) + ); + } + /* For tuple insertion */ + if (buf_new) + { + /* + * min = LEAST(mv.min, diff.min) + * max = GREATEST(mv.max, diff.max) + */ + appendStringInfo(buf_new, + ", %s = CASE WHEN %s THEN NULL ELSE %s(%s,%s) END", + quote_qualified_identifier(NULL, resname), + get_null_condition_string(IVM_ADD, "mv", "diff", count_col), + + is_min ? "LEAST" : "GREATEST", + quote_qualified_identifier("mv", resname), + quote_qualified_identifier("diff", resname) + ); + /* count = mv.count + diff.count */ + appendStringInfo(buf_new, + ", %s = %s", + quote_qualified_identifier(NULL, count_col), + get_operation_string(IVM_ADD, count_col, "mv", "diff", NULL, NULL) + ); + } + + appendStringInfo(aggs_list, ", %s, %s", + quote_qualified_identifier("diff", resname), + quote_qualified_identifier("diff", IVM_colname("count", resname)) + ); +} + /* * get_operation_string * @@ -2768,19 +2900,44 @@ get_null_condition_string(IvmOp op, const char *arg1, const char *arg2, * list to identify a tuple in the view. If the view has aggregates, this * requires strings representing resnames of aggregates and SET clause for * updating aggregate values. + * + * If the view has min or max aggregate, this requires a list of resnames of + * min/max aggregates and a list of boolean which represents which entries in + * minmax_list is min. These are necessary to check if we need to recalculate + * min or max aggregate values. In this case, this query returns TID and keys + * of tuples which need to be recalculated. This result and the number of rows + * are stored in tuptables and num_recalc repectedly. + * */ static void apply_old_delta_with_count(const char *matviewname, const char *deltaname_old, List *keys, StringInfo aggs_list, StringInfo aggs_set, - const char *count_colname) + List *minmax_list, List *is_min_list, + const char *count_colname, + SPITupleTable **tuptable_recalc, uint64 *num_recalc) { StringInfoData querybuf; char *match_cond; + char *updt_returning = ""; + char *select_for_recalc = "SELECT"; bool agg_without_groupby = (list_length(keys) == 0); + Assert(tuptable_recalc != NULL); + Assert(num_recalc != NULL); + /* build WHERE condition for searching tuples to be deleted */ match_cond = get_matching_condition_string(keys); + /* + * We need a special RETURNING clause and SELECT statement for min/max to + * check which tuple needs re-calculation from base tables. + */ + if (minmax_list) + { + updt_returning = get_returning_string(minmax_list, is_min_list, keys); + select_for_recalc = get_select_for_recalc_string(keys); + } + /* Search for matching tuples from the view and update or delete if found. */ initStringInfo(&querybuf); appendStringInfo(&querybuf, @@ -2795,10 +2952,11 @@ apply_old_delta_with_count(const char *matviewname, const char *deltaname_old, "UPDATE %s AS mv SET %s = mv.%s OPERATOR(pg_catalog.-) t.%s " "%s" /* SET clauses for aggregates */ "FROM t WHERE mv.ctid OPERATOR(pg_catalog.=) t.ctid AND NOT for_dlt " - ")" - /* delete a tuple if this is to be deleted */ - "DELETE FROM %s AS mv USING t " - "WHERE mv.ctid OPERATOR(pg_catalog.=) t.ctid AND for_dlt", + "%s" /* RETURNING clause for recalc infomation */ + "), dlt AS (" /* delete a tuple if this is to be deleted */ + "DELETE FROM %s AS mv USING t " + "WHERE mv.ctid OPERATOR(pg_catalog.=) t.ctid AND for_dlt" + ") %s", /* SELECT returning which tuples need to be recalculated */ count_colname, count_colname, count_colname, (agg_without_groupby ? "false" : "true"), (aggs_list != NULL ? aggs_list->data : ""), @@ -2806,10 +2964,25 @@ apply_old_delta_with_count(const char *matviewname, const char *deltaname_old, match_cond, matviewname, count_colname, count_colname, count_colname, (aggs_set != NULL ? aggs_set->data : ""), - matviewname); + updt_returning, + matviewname, + select_for_recalc); - if (SPI_exec(querybuf.data, 0) != SPI_OK_DELETE) + if (SPI_exec(querybuf.data, 0) != SPI_OK_SELECT) elog(ERROR, "SPI_exec failed: %s", querybuf.data); + + + /* Return tuples to be recalculated. */ + if (minmax_list) + { + *tuptable_recalc = SPI_tuptable; + *num_recalc = SPI_processed; + } + else + { + *tuptable_recalc = NULL; + *num_recalc = 0; + } } /* @@ -2992,6 +3165,349 @@ get_matching_condition_string(List *keys) return match_cond.data; } +/* + * get_returning_string + * + * Build a string for RETURNING clause of UPDATE used in apply_old_delta_with_count. + * This clause returns ctid and a boolean value that indicates if we need to + * recalculate min or max value, for each updated row. + */ +static char * +get_returning_string(List *minmax_list, List *is_min_list, List *keys) +{ + StringInfoData returning; + char *recalc_cond; + ListCell *lc; + + Assert(minmax_list != NIL && is_min_list != NIL); + recalc_cond = get_minmax_recalc_condition_string(minmax_list, is_min_list); + + initStringInfo(&returning); + + appendStringInfo(&returning, "RETURNING mv.ctid AS tid, (%s) AS recalc", recalc_cond); + foreach (lc, keys) + { + Form_pg_attribute attr = (Form_pg_attribute) lfirst(lc); + char *resname = NameStr(attr->attname); + appendStringInfo(&returning, ", %s", quote_qualified_identifier("mv", resname)); + } + + return returning.data; +} + +/* + * get_minmax_recalc_condition_string + * + * Build a predicate string for checking if any min/max aggregate + * value needs to be recalculated. + */ +static char * +get_minmax_recalc_condition_string(List *minmax_list, List *is_min_list) +{ + StringInfoData recalc_cond; + ListCell *lc1, *lc2; + + initStringInfo(&recalc_cond); + + Assert (list_length(minmax_list) == list_length(is_min_list)); + + forboth (lc1, minmax_list, lc2, is_min_list) + { + char *resname = (char *) lfirst(lc1); + bool is_min = (bool) lfirst_int(lc2); + char *op_str = (is_min ? ">=" : "<="); + + appendStringInfo(&recalc_cond, "%s OPERATOR(pg_catalog.%s) %s", + quote_qualified_identifier("mv", resname), + op_str, + quote_qualified_identifier("t", resname) + ); + + if (lnext(minmax_list, lc1)) + appendStringInfo(&recalc_cond, " OR "); + } + + return recalc_cond.data; +} + +/* + * get_select_for_recalc_string + * + * Build a query to return tid and keys of tuples which need + * recalculation. This is used as the result of the query + * built by apply_old_delta. + */ +static char * +get_select_for_recalc_string(List *keys) +{ + StringInfoData qry; + ListCell *lc; + + initStringInfo(&qry); + + appendStringInfo(&qry, "SELECT tid"); + foreach (lc, keys) + { + Form_pg_attribute attr = (Form_pg_attribute) lfirst(lc); + appendStringInfo(&qry, ", %s", NameStr(attr->attname)); + } + + appendStringInfo(&qry, " FROM updt WHERE recalc"); + + return qry.data; +} + +/* + * recalc_and_set_values + * + * Recalculate tuples in a materialized from base tables and update these. + * The tuples which needs recalculation are specified by keys, and resnames + * of columns to be updated are specified by namelist. TIDs and key values + * are given by tuples in tuptable_recalc. Its first attribute must be TID + * and key values must be following this. + */ +static void +recalc_and_set_values(SPITupleTable *tuptable_recalc, int64 num_tuples, + List *namelist, List *keys, Relation matviewRel) +{ + TupleDesc tupdesc_recalc = tuptable_recalc->tupdesc; + Oid *keyTypes = NULL, *types = NULL; + char *keyNulls = NULL, *nulls = NULL; + Datum *keyVals = NULL, *vals = NULL; + int num_vals = list_length(namelist); + int num_keys = list_length(keys); + uint64 i; + Oid matviewOid; + char *matviewname; + + matviewOid = RelationGetRelid(matviewRel); + matviewname = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(matviewRel)), + RelationGetRelationName(matviewRel)); + + /* If we have keys, initialize arrays for them. */ + if (keys) + { + keyTypes = palloc(sizeof(Oid) * num_keys); + keyNulls = palloc(sizeof(char) * num_keys); + keyVals = palloc(sizeof(Datum) * num_keys); + /* a tuple contains keys to be recalculated and ctid to be updated*/ + Assert(tupdesc_recalc->natts == num_keys + 1); + + /* Types of key attributes */ + for (i = 0; i < num_keys; i++) + keyTypes[i] = TupleDescAttr(tupdesc_recalc, i + 1)->atttypid; + } + + /* allocate memory for all attribute names and tid */ + types = palloc(sizeof(Oid) * (num_vals + 1)); + nulls = palloc(sizeof(char) * (num_vals + 1)); + vals = palloc(sizeof(Datum) * (num_vals + 1)); + + /* For each tuple which needs recalculation */ + for (i = 0; i < num_tuples; i++) + { + int j; + bool isnull; + SPIPlanPtr plan; + SPITupleTable *tuptable_newvals; + TupleDesc tupdesc_newvals; + + /* Set group key values as parameters if needed. */ + if (keys) + { + for (j = 0; j < num_keys; j++) + { + keyVals[j] = SPI_getbinval(tuptable_recalc->vals[i], tupdesc_recalc, j + 2, &isnull); + if (isnull) + keyNulls[j] = 'n'; + else + keyNulls[j] = ' '; + } + } + + /* + * Get recalculated values from base tables. The result must be + * only one tuple thich contains the new values for specified keys. + */ + plan = get_plan_for_recalc(matviewOid, namelist, keys, keyTypes); + if (SPI_execute_plan(plan, keyVals, keyNulls, false, 0) != SPI_OK_SELECT) + elog(ERROR, "SPI_execute_plan"); + if (SPI_processed != 1) + elog(ERROR, "SPI_execute_plan returned zero or more than one rows"); + + tuptable_newvals = SPI_tuptable; + tupdesc_newvals = tuptable_newvals->tupdesc; + + Assert(tupdesc_newvals->natts == num_vals); + + /* Set the new values as parameters */ + for (j = 0; j < tupdesc_newvals->natts; j++) + { + if (i == 0) + types[j] = TupleDescAttr(tupdesc_newvals, j)->atttypid; + + vals[j] = SPI_getbinval(tuptable_newvals->vals[0], tupdesc_newvals, j + 1, &isnull); + if (isnull) + nulls[j] = 'n'; + else + nulls[j] = ' '; + } + /* Set TID of the view tuple to be updated as a parameter */ + types[j] = TIDOID; + vals[j] = SPI_getbinval(tuptable_recalc->vals[i], tupdesc_recalc, 1, &isnull); + nulls[j] = ' '; + + /* Update the view tuple to the new values */ + plan = get_plan_for_set_values(matviewOid, matviewname, namelist, types); + if (SPI_execute_plan(plan, vals, nulls, false, 0) != SPI_OK_UPDATE) + elog(ERROR, "SPI_execute_plan"); + } +} + + +/* + * get_plan_for_recalc + * + * Create or fetch a plan for recalculating value in the view's target list + * from base tables using the definition query of materialized view specified + * by matviewOid. namelist is a list of resnames of values to be recalculated. + * + * keys is a list of keys to identify tuples to be recalculated if this is not + * empty. KeyTypes is an array of types of keys. + */ +static SPIPlanPtr +get_plan_for_recalc(Oid matviewOid, List *namelist, List *keys, Oid *keyTypes) +{ + MV_QueryKey hash_key; + SPIPlanPtr plan; + + /* Fetch or prepare a saved plan for the recalculation */ + mv_BuildQueryKey(&hash_key, matviewOid, MV_PLAN_RECALC); + if ((plan = mv_FetchPreparedPlan(&hash_key)) == NULL) + { + ListCell *lc; + StringInfoData str; + char *viewdef; + + /* get view definition of matview */ + viewdef = text_to_cstring((text *) DatumGetPointer( + DirectFunctionCall1(pg_get_viewdef, ObjectIdGetDatum(matviewOid)))); + /* get rid of trailing semi-colon */ + viewdef[strlen(viewdef)-1] = '\0'; + + /* + * Build a query string for recalculating values. This is like + * + * SELECT x1, x2, x3, ... FROM ( ... view definition query ...) mv + * WHERE (key1, key2, ...) = ($1, $2, ...); + */ + + initStringInfo(&str); + appendStringInfo(&str, "SELECT "); + foreach (lc, namelist) + { + appendStringInfo(&str, "%s", (char *) lfirst(lc)); + if (lnext(namelist, lc)) + appendStringInfoString(&str, ", "); + } + appendStringInfo(&str, " FROM (%s) mv", viewdef); + + if (keys) + { + int i = 1; + char paramname[16]; + + appendStringInfo(&str, " WHERE ("); + foreach (lc, keys) + { + Form_pg_attribute attr = (Form_pg_attribute) lfirst(lc); + char *resname = NameStr(attr->attname); + Oid typid = attr->atttypid; + + sprintf(paramname, "$%d", i); + appendStringInfo(&str, "("); + generate_equal(&str, typid, resname, paramname); + appendStringInfo(&str, " OR (%s IS NULL AND %s IS NULL))", + resname, paramname); + + if (lnext(keys, lc)) + appendStringInfoString(&str, " AND "); + i++; + } + appendStringInfo(&str, ")"); + } + else + keyTypes = NULL; + + plan = SPI_prepare(str.data, list_length(keys), keyTypes); + if (plan == NULL) + elog(ERROR, "SPI_prepare returned %s for %s", SPI_result_code_string(SPI_result), str.data); + + SPI_keepplan(plan); + mv_HashPreparedPlan(&hash_key, plan); + } + + return plan; +} + +/* + * get_plan_for_set_values + * + * Create or fetch a plan for applying new values calculated by + * get_plan_for_recalc to a materialized view specified by matviewOid. + * matviewname is the name of the view. namelist is a list of resnames + * of attributes to be updated, and valTypes is an array of types of the + * values. + */ +static SPIPlanPtr +get_plan_for_set_values(Oid matviewOid, char *matviewname, List *namelist, + Oid *valTypes) +{ + MV_QueryKey key; + SPIPlanPtr plan; + + /* Fetch or prepare a saved plan for the real check */ + mv_BuildQueryKey(&key, matviewOid, MV_PLAN_SET_VALUE); + if ((plan = mv_FetchPreparedPlan(&key)) == NULL) + { + ListCell *lc; + StringInfoData str; + int i; + + /* + * Build a query string for applying min/max values. This is like + * + * UPDATE matviewname AS mv + * SET (x1, x2, x3, x4) = ($1, $2, $3, $4) + * WHERE ctid = $5; + */ + + initStringInfo(&str); + appendStringInfo(&str, "UPDATE %s AS mv SET (", matviewname); + foreach (lc, namelist) + { + appendStringInfo(&str, "%s", (char *) lfirst(lc)); + if (lnext(namelist, lc)) + appendStringInfoString(&str, ", "); + } + appendStringInfo(&str, ") = ROW("); + + for (i = 1; i <= list_length(namelist); i++) + appendStringInfo(&str, "%s$%d", (i==1 ? "" : ", "), i); + + appendStringInfo(&str, ") WHERE ctid OPERATOR(pg_catalog.=) $%d", i); + + plan = SPI_prepare(str.data, list_length(namelist) + 1, valTypes); + if (plan == NULL) + elog(ERROR, "SPI_prepare returned %s for %s", SPI_result_code_string(SPI_result), str.data); + + SPI_keepplan(plan); + mv_HashPreparedPlan(&key, plan); + } + + return plan; +} + /* * generate_equals * @@ -3025,6 +3541,13 @@ mv_InitHashTables(void) { HASHCTL ctl; + memset(&ctl, 0, sizeof(ctl)); + ctl.keysize = sizeof(MV_QueryKey); + ctl.entrysize = sizeof(MV_QueryHashEntry); + mv_query_cache = hash_create("MV query cache", + MV_INIT_QUERYHASHSIZE, + &ctl, HASH_ELEM | HASH_BLOBS); + memset(&ctl, 0, sizeof(ctl)); ctl.keysize = sizeof(Oid); ctl.entrysize = sizeof(MV_TriggerHashEntry); @@ -3039,6 +3562,109 @@ mv_InitHashTables(void) &ctl, HASH_ELEM | HASH_BLOBS); } +/* + * mv_FetchPreparedPlan + */ +static SPIPlanPtr +mv_FetchPreparedPlan(MV_QueryKey *key) +{ + MV_QueryHashEntry *entry; + SPIPlanPtr plan; + + /* + * On the first call initialize the hashtable + */ + if (!mv_query_cache) + mv_InitHashTables(); + + /* + * Lookup for the key + */ + entry = (MV_QueryHashEntry *) hash_search(mv_query_cache, + (void *) key, + HASH_FIND, NULL); + if (entry == NULL) + return NULL; + + /* + * Check whether the plan is still valid. If it isn't, we don't want to + * simply rely on plancache.c to regenerate it; rather we should start + * from scratch and rebuild the query text too. This is to cover cases + * such as table/column renames. We depend on the plancache machinery to + * detect possible invalidations, though. + * + * CAUTION: this check is only trustworthy if the caller has already + * locked both materialized views and base tables. + * + * Also, check whether the search_path is still the same as when we made it. + * If it isn't, we need to rebuild the query text because the result of + * pg_ivm_get_viewdef() will change. + */ + plan = entry->plan; + if (plan && SPI_plan_is_valid(plan) && + SearchPathMatchesCurrentEnvironment(entry->search_path)) + return plan; + + /* + * Otherwise we might as well flush the cached plan now, to free a little + * memory space before we make a new one. + */ + if (plan) + SPI_freeplan(plan); + if (entry->search_path) + pfree(entry->search_path); + + entry->plan = NULL; + entry->search_path = NULL; + + return NULL; +} + +/* + * mv_HashPreparedPlan + * + * Add another plan to our private SPI query plan hashtable. + */ +static void +mv_HashPreparedPlan(MV_QueryKey *key, SPIPlanPtr plan) +{ + MV_QueryHashEntry *entry; + bool found; + + /* + * On the first call initialize the hashtable + */ + if (!mv_query_cache) + mv_InitHashTables(); + + /* + * Add the new plan. We might be overwriting an entry previously found + * invalid by mv_FetchPreparedPlan. + */ + entry = (MV_QueryHashEntry *) hash_search(mv_query_cache, + (void *) key, + HASH_ENTER, &found); + Assert(!found || entry->plan == NULL); + entry->plan = plan; + entry->search_path = GetSearchPathMatcher(TopMemoryContext); +} + +/* + * mv_BuildQueryKey + * + * Construct a hashtable key for a prepared SPI plan for IVM. + */ +static void +mv_BuildQueryKey(MV_QueryKey *key, Oid matview_id, int32 query_type) +{ + /* + * We assume struct MV_QueryKey contains no padding bytes, else we'd need + * to use memset to clear them. + */ + key->matview_id = matview_id; + key->query_type = query_type; +} + /* * AtAbort_IVM * -- 2.43.0 --Multipart=_Fri__29_May_2026_23_14_17_+0900_Te0o73X2VqYK57Gd Content-Type: text/x-diff; name="v37-0008-Add-aggregates-support-in-IVM.patch" Content-Disposition: attachment; filename="v37-0008-Add-aggregates-support-in-IVM.patch" Content-Transfer-Encoding: quoted-printable ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v4] Allow pgbnech to cancel queries during benchmark @ 2023-07-24 12:53 Yugo Nagata <nagata@sraoss.co.jp> 0 siblings, 0 replies; 6+ messages in thread From: Yugo Nagata @ 2023-07-24 12:53 UTC (permalink / raw) Previously, Ctrl+C during benchmark killed pgbench immediately, but queries running at that time were not cancelled. The commit fixes this so that cancel requests are sent for all connections before pgbench exits. In thread #0, setup_cancel_handler is called before the benchmark so that CancelRequested is set when SIGINT is sent. When SIGINT is sent during the benchmark, on non-Windows, thread #0 will be interrupted, return from I/O wait, and send cancel requests to all connections. After queries are cancelled, other threads also be interrupted and pgbench will exit at the end. On Windows, cancel requests are sent in the callback function specified by setup_cancel_hander. --- src/bin/pgbench/pgbench.c | 89 ++++++++++++++++++++ src/bin/pgbench/t/001_pgbench_with_server.pl | 42 +++++++++ 2 files changed, 131 insertions(+) diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c index 713e8a06bb..5adf099b76 100644 --- a/src/bin/pgbench/pgbench.c +++ b/src/bin/pgbench/pgbench.c @@ -596,6 +596,7 @@ typedef enum typedef struct { PGconn *con; /* connection handle to DB */ + PGcancel *cancel; /* query cancel */ int id; /* client No. */ ConnectionStateEnum state; /* state machine's current state. */ ConditionalStack cstack; /* enclosing conditionals state */ @@ -638,6 +639,8 @@ typedef struct * here */ } CState; +CState *client_states; /* status of all clients */ + /* * Thread state */ @@ -837,6 +840,10 @@ static void add_socket_to_set(socket_set *sa, int fd, int idx); static int wait_on_socket_set(socket_set *sa, int64 usecs); static bool socket_has_input(socket_set *sa, int fd, int idx); +#ifdef WIN32 +static void pgbench_cancel_callback(void); +#endif + /* callback used to build rows for COPY during data loading */ typedef void (*initRowMethod) (PQExpBufferData *sql, int64 curr); @@ -3639,6 +3646,7 @@ advanceConnectionState(TState *thread, CState *st, StatsData *agg) st->state = CSTATE_ABORTED; break; } + st->cancel = PQgetCancel(st->con); /* reset now after connection */ now = pg_time_now(); @@ -4670,6 +4678,18 @@ disconnect_all(CState *state, int length) finishCon(&state[i]); } +/* send cancel requests to all connections */ +static void +cancel_all() +{ + for (int i = 0; i < nclients; i++) + { + char errbuf[1]; + if (client_states[i].cancel != NULL) + (void) PQcancel(client_states[i].cancel, errbuf, sizeof(errbuf)); + } +} + /* * Remove old pgbench tables, if any exist */ @@ -7146,6 +7166,9 @@ main(int argc, char **argv) } } + /* enable threads to access the status of all clients */ + client_states = state; + /* other CState initializations */ for (i = 0; i < nclients; i++) { @@ -7358,6 +7381,37 @@ threadRun(void *arg) StatsData last, aggs; + /* + * Query cancellation is handled only in thread #0. + * + * On Windows, a callback function is set in which query cancel requests + * are sent to all benchmark queries running in the backend. + * + * On non-Windows, any callback function is not set. When SIGINT is + * received, CancelRequested is just set, and only thread #0 is interrupted + * and returns from waiting input from the backend. After that, the thread + * sends cancel requests to all benchmark queries. + */ + if (thread->tid == 0) +#ifdef WIN32 + setup_cancel_handler(pgbench_cancel_callback); +#else + setup_cancel_handler(NULL); +#endif + +#ifndef WIN32 + if (thread->tid > 0) + { + sigset_t sigint_sigset; + sigset_t osigset; + sigemptyset(&sigint_sigset); + sigaddset(&sigint_sigset, SIGINT); + + /* Block SIGINT in all threads except one. */ + pthread_sigmask(SIG_BLOCK, &sigint_sigset, &osigset); + } +#endif + /* open log file if requested */ if (use_log) { @@ -7400,6 +7454,7 @@ threadRun(void *arg) pg_fatal("could not create connection for client %d", state[i].id); } + state[i].cancel = PQgetCancel(state[i].con); } } @@ -7427,6 +7482,26 @@ threadRun(void *arg) pg_time_usec_t min_usec; pg_time_usec_t now = 0; /* set this only if needed */ + /* + * If pgbench is cancelled, send cancel requests to all connections + * and exit the benchmark. + * + * Note that only thread #0 can be interrupted by SIGINT while waiting + * the result from the backend. Other threads will return from waiting + * just after queries they running are cancelled by thread #0. + * + * On Windows, cancel requests are sent in the callback function, so + * do nothing but exit the benchmark. + */ + if (CancelRequested) + { +#ifndef WIN32 + if (thread->tid == 0) + cancel_all(); +#endif + goto done; + } + /* * identify which client sockets should be checked for input, and * compute the nearest time (if any) at which we need to wake up. @@ -7650,6 +7725,8 @@ finishCon(CState *st) { PQfinish(st->con); st->con = NULL; + PQfreeCancel(st->cancel); + st->cancel = NULL; } } @@ -7867,3 +7944,15 @@ socket_has_input(socket_set *sa, int fd, int idx) } #endif /* POLL_USING_SELECT */ + +#ifdef WIN32 +/* + * query cancellation callback for Windows + */ +static void +pgbench_cancel_callback(void) +{ + /* send cancel requests to all connections */ + cancel_all(); +} +#endif diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl index 96be529d6b..43eadfbe9f 100644 --- a/src/bin/pgbench/t/001_pgbench_with_server.pl +++ b/src/bin/pgbench/t/001_pgbench_with_server.pl @@ -7,6 +7,7 @@ use warnings; use PostgreSQL::Test::Cluster; use PostgreSQL::Test::Utils; use Test::More; +use Time::HiRes qw(usleep); # Check the initial state of the data generated. Tables for tellers and # branches use NULL for their filler attribute. The table accounts uses @@ -1502,6 +1503,47 @@ update counter set i = i+1 returning i \gset # Clean up $node->safe_psql('postgres', 'DROP TABLE counter;'); +# Test query canceling by sending SIGINT to a running pgbench +SKIP: +{ + skip "sending SIGINT on Windows terminates the test itself", 3 + if $windows_os; + + my ($stdin, $stdout, $stderr, @file); + + @file = $node->_pgbench_make_files( + { + '003_pgbench_cancel' => qq{ +select pg_sleep($PostgreSQL::Test::Utils::timeout_default); + }}); + + local %ENV = $node->_get_env(); + + my $h = IPC::Run::start( + [ 'pgbench', '-c', '2', '-j', '2', + '-T', "$PostgreSQL::Test::Utils::timeout_default", @file ], + \$stdin, \$stdout, \$stderr); + + $node->poll_query_until('postgres', + q{SELECT (SELECT count(*) FROM pg_stat_activity WHERE query ~ '^select pg_sleep') = 2;} + ) or die "timed out"; + + # Send cancel request + $h->signal('INT'); + + my $result = finish $h; + + ok(!$result, 'pgbench failed as expected'); + like( + $stderr, + qr/Run was aborted; the above results are incomplete/, + 'pgbench was canceled'); + + is($node->safe_psql('postgres', + q{SELECT count(*) FROM pg_stat_activity WHERE query ~ '^select pg_sleep'}), + '0', 'all queries were canceled'); +} + # done $node->safe_psql('postgres', 'DROP TABLESPACE regress_pgbench_tap_1_ts'); $node->stop; -- 2.25.1 --Multipart=_Tue__19_Sep_2023_17_30_11_+0900_JATr+KlP9+ALhH+Y-- ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v4] Allow pgbnech to cancel queries during benchmark @ 2023-07-24 12:53 Yugo Nagata <nagata@sraoss.co.jp> 0 siblings, 0 replies; 6+ messages in thread From: Yugo Nagata @ 2023-07-24 12:53 UTC (permalink / raw) Previously, Ctrl+C during benchmark killed pgbench immediately, but queries running at that time were not cancelled. The commit fixes this so that cancel requests are sent for all connections before pgbench exits. In thread #0, setup_cancel_handler is called before the benchmark so that CancelRequested is set when SIGINT is sent. When SIGINT is sent during the benchmark, on non-Windows, thread #0 will be interrupted, return from I/O wait, and send cancel requests to all connections. After queries are cancelled, other threads also be interrupted and pgbench will exit at the end. On Windows, cancel requests are sent in the callback function specified by setup_cancel_hander. --- src/bin/pgbench/pgbench.c | 89 ++++++++++++++++++++ src/bin/pgbench/t/001_pgbench_with_server.pl | 42 +++++++++ 2 files changed, 131 insertions(+) diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c index 713e8a06bb..5adf099b76 100644 --- a/src/bin/pgbench/pgbench.c +++ b/src/bin/pgbench/pgbench.c @@ -596,6 +596,7 @@ typedef enum typedef struct { PGconn *con; /* connection handle to DB */ + PGcancel *cancel; /* query cancel */ int id; /* client No. */ ConnectionStateEnum state; /* state machine's current state. */ ConditionalStack cstack; /* enclosing conditionals state */ @@ -638,6 +639,8 @@ typedef struct * here */ } CState; +CState *client_states; /* status of all clients */ + /* * Thread state */ @@ -837,6 +840,10 @@ static void add_socket_to_set(socket_set *sa, int fd, int idx); static int wait_on_socket_set(socket_set *sa, int64 usecs); static bool socket_has_input(socket_set *sa, int fd, int idx); +#ifdef WIN32 +static void pgbench_cancel_callback(void); +#endif + /* callback used to build rows for COPY during data loading */ typedef void (*initRowMethod) (PQExpBufferData *sql, int64 curr); @@ -3639,6 +3646,7 @@ advanceConnectionState(TState *thread, CState *st, StatsData *agg) st->state = CSTATE_ABORTED; break; } + st->cancel = PQgetCancel(st->con); /* reset now after connection */ now = pg_time_now(); @@ -4670,6 +4678,18 @@ disconnect_all(CState *state, int length) finishCon(&state[i]); } +/* send cancel requests to all connections */ +static void +cancel_all() +{ + for (int i = 0; i < nclients; i++) + { + char errbuf[1]; + if (client_states[i].cancel != NULL) + (void) PQcancel(client_states[i].cancel, errbuf, sizeof(errbuf)); + } +} + /* * Remove old pgbench tables, if any exist */ @@ -7146,6 +7166,9 @@ main(int argc, char **argv) } } + /* enable threads to access the status of all clients */ + client_states = state; + /* other CState initializations */ for (i = 0; i < nclients; i++) { @@ -7358,6 +7381,37 @@ threadRun(void *arg) StatsData last, aggs; + /* + * Query cancellation is handled only in thread #0. + * + * On Windows, a callback function is set in which query cancel requests + * are sent to all benchmark queries running in the backend. + * + * On non-Windows, any callback function is not set. When SIGINT is + * received, CancelRequested is just set, and only thread #0 is interrupted + * and returns from waiting input from the backend. After that, the thread + * sends cancel requests to all benchmark queries. + */ + if (thread->tid == 0) +#ifdef WIN32 + setup_cancel_handler(pgbench_cancel_callback); +#else + setup_cancel_handler(NULL); +#endif + +#ifndef WIN32 + if (thread->tid > 0) + { + sigset_t sigint_sigset; + sigset_t osigset; + sigemptyset(&sigint_sigset); + sigaddset(&sigint_sigset, SIGINT); + + /* Block SIGINT in all threads except one. */ + pthread_sigmask(SIG_BLOCK, &sigint_sigset, &osigset); + } +#endif + /* open log file if requested */ if (use_log) { @@ -7400,6 +7454,7 @@ threadRun(void *arg) pg_fatal("could not create connection for client %d", state[i].id); } + state[i].cancel = PQgetCancel(state[i].con); } } @@ -7427,6 +7482,26 @@ threadRun(void *arg) pg_time_usec_t min_usec; pg_time_usec_t now = 0; /* set this only if needed */ + /* + * If pgbench is cancelled, send cancel requests to all connections + * and exit the benchmark. + * + * Note that only thread #0 can be interrupted by SIGINT while waiting + * the result from the backend. Other threads will return from waiting + * just after queries they running are cancelled by thread #0. + * + * On Windows, cancel requests are sent in the callback function, so + * do nothing but exit the benchmark. + */ + if (CancelRequested) + { +#ifndef WIN32 + if (thread->tid == 0) + cancel_all(); +#endif + goto done; + } + /* * identify which client sockets should be checked for input, and * compute the nearest time (if any) at which we need to wake up. @@ -7650,6 +7725,8 @@ finishCon(CState *st) { PQfinish(st->con); st->con = NULL; + PQfreeCancel(st->cancel); + st->cancel = NULL; } } @@ -7867,3 +7944,15 @@ socket_has_input(socket_set *sa, int fd, int idx) } #endif /* POLL_USING_SELECT */ + +#ifdef WIN32 +/* + * query cancellation callback for Windows + */ +static void +pgbench_cancel_callback(void) +{ + /* send cancel requests to all connections */ + cancel_all(); +} +#endif diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl index 96be529d6b..43eadfbe9f 100644 --- a/src/bin/pgbench/t/001_pgbench_with_server.pl +++ b/src/bin/pgbench/t/001_pgbench_with_server.pl @@ -7,6 +7,7 @@ use warnings; use PostgreSQL::Test::Cluster; use PostgreSQL::Test::Utils; use Test::More; +use Time::HiRes qw(usleep); # Check the initial state of the data generated. Tables for tellers and # branches use NULL for their filler attribute. The table accounts uses @@ -1502,6 +1503,47 @@ update counter set i = i+1 returning i \gset # Clean up $node->safe_psql('postgres', 'DROP TABLE counter;'); +# Test query canceling by sending SIGINT to a running pgbench +SKIP: +{ + skip "sending SIGINT on Windows terminates the test itself", 3 + if $windows_os; + + my ($stdin, $stdout, $stderr, @file); + + @file = $node->_pgbench_make_files( + { + '003_pgbench_cancel' => qq{ +select pg_sleep($PostgreSQL::Test::Utils::timeout_default); + }}); + + local %ENV = $node->_get_env(); + + my $h = IPC::Run::start( + [ 'pgbench', '-c', '2', '-j', '2', + '-T', "$PostgreSQL::Test::Utils::timeout_default", @file ], + \$stdin, \$stdout, \$stderr); + + $node->poll_query_until('postgres', + q{SELECT (SELECT count(*) FROM pg_stat_activity WHERE query ~ '^select pg_sleep') = 2;} + ) or die "timed out"; + + # Send cancel request + $h->signal('INT'); + + my $result = finish $h; + + ok(!$result, 'pgbench failed as expected'); + like( + $stderr, + qr/Run was aborted; the above results are incomplete/, + 'pgbench was canceled'); + + is($node->safe_psql('postgres', + q{SELECT count(*) FROM pg_stat_activity WHERE query ~ '^select pg_sleep'}), + '0', 'all queries were canceled'); +} + # done $node->safe_psql('postgres', 'DROP TABLESPACE regress_pgbench_tap_1_ts'); $node->stop; -- 2.25.1 --Multipart=_Tue__19_Sep_2023_17_30_11_+0900_JATr+KlP9+ALhH+Y-- ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v4] Allow pgbnech to cancel queries during benchmark @ 2023-07-24 12:53 Yugo Nagata <nagata@sraoss.co.jp> 0 siblings, 0 replies; 6+ messages in thread From: Yugo Nagata @ 2023-07-24 12:53 UTC (permalink / raw) Previously, Ctrl+C during benchmark killed pgbench immediately, but queries running at that time were not cancelled. The commit fixes this so that cancel requests are sent for all connections before pgbench exits. In thread #0, setup_cancel_handler is called before the benchmark so that CancelRequested is set when SIGINT is sent. When SIGINT is sent during the benchmark, on non-Windows, thread #0 will be interrupted, return from I/O wait, and send cancel requests to all connections. After queries are cancelled, other threads also be interrupted and pgbench will exit at the end. On Windows, cancel requests are sent in the callback function specified by setup_cancel_hander. --- src/bin/pgbench/pgbench.c | 89 ++++++++++++++++++++ src/bin/pgbench/t/001_pgbench_with_server.pl | 42 +++++++++ 2 files changed, 131 insertions(+) diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c index 713e8a06bb..5adf099b76 100644 --- a/src/bin/pgbench/pgbench.c +++ b/src/bin/pgbench/pgbench.c @@ -596,6 +596,7 @@ typedef enum typedef struct { PGconn *con; /* connection handle to DB */ + PGcancel *cancel; /* query cancel */ int id; /* client No. */ ConnectionStateEnum state; /* state machine's current state. */ ConditionalStack cstack; /* enclosing conditionals state */ @@ -638,6 +639,8 @@ typedef struct * here */ } CState; +CState *client_states; /* status of all clients */ + /* * Thread state */ @@ -837,6 +840,10 @@ static void add_socket_to_set(socket_set *sa, int fd, int idx); static int wait_on_socket_set(socket_set *sa, int64 usecs); static bool socket_has_input(socket_set *sa, int fd, int idx); +#ifdef WIN32 +static void pgbench_cancel_callback(void); +#endif + /* callback used to build rows for COPY during data loading */ typedef void (*initRowMethod) (PQExpBufferData *sql, int64 curr); @@ -3639,6 +3646,7 @@ advanceConnectionState(TState *thread, CState *st, StatsData *agg) st->state = CSTATE_ABORTED; break; } + st->cancel = PQgetCancel(st->con); /* reset now after connection */ now = pg_time_now(); @@ -4670,6 +4678,18 @@ disconnect_all(CState *state, int length) finishCon(&state[i]); } +/* send cancel requests to all connections */ +static void +cancel_all() +{ + for (int i = 0; i < nclients; i++) + { + char errbuf[1]; + if (client_states[i].cancel != NULL) + (void) PQcancel(client_states[i].cancel, errbuf, sizeof(errbuf)); + } +} + /* * Remove old pgbench tables, if any exist */ @@ -7146,6 +7166,9 @@ main(int argc, char **argv) } } + /* enable threads to access the status of all clients */ + client_states = state; + /* other CState initializations */ for (i = 0; i < nclients; i++) { @@ -7358,6 +7381,37 @@ threadRun(void *arg) StatsData last, aggs; + /* + * Query cancellation is handled only in thread #0. + * + * On Windows, a callback function is set in which query cancel requests + * are sent to all benchmark queries running in the backend. + * + * On non-Windows, any callback function is not set. When SIGINT is + * received, CancelRequested is just set, and only thread #0 is interrupted + * and returns from waiting input from the backend. After that, the thread + * sends cancel requests to all benchmark queries. + */ + if (thread->tid == 0) +#ifdef WIN32 + setup_cancel_handler(pgbench_cancel_callback); +#else + setup_cancel_handler(NULL); +#endif + +#ifndef WIN32 + if (thread->tid > 0) + { + sigset_t sigint_sigset; + sigset_t osigset; + sigemptyset(&sigint_sigset); + sigaddset(&sigint_sigset, SIGINT); + + /* Block SIGINT in all threads except one. */ + pthread_sigmask(SIG_BLOCK, &sigint_sigset, &osigset); + } +#endif + /* open log file if requested */ if (use_log) { @@ -7400,6 +7454,7 @@ threadRun(void *arg) pg_fatal("could not create connection for client %d", state[i].id); } + state[i].cancel = PQgetCancel(state[i].con); } } @@ -7427,6 +7482,26 @@ threadRun(void *arg) pg_time_usec_t min_usec; pg_time_usec_t now = 0; /* set this only if needed */ + /* + * If pgbench is cancelled, send cancel requests to all connections + * and exit the benchmark. + * + * Note that only thread #0 can be interrupted by SIGINT while waiting + * the result from the backend. Other threads will return from waiting + * just after queries they running are cancelled by thread #0. + * + * On Windows, cancel requests are sent in the callback function, so + * do nothing but exit the benchmark. + */ + if (CancelRequested) + { +#ifndef WIN32 + if (thread->tid == 0) + cancel_all(); +#endif + goto done; + } + /* * identify which client sockets should be checked for input, and * compute the nearest time (if any) at which we need to wake up. @@ -7650,6 +7725,8 @@ finishCon(CState *st) { PQfinish(st->con); st->con = NULL; + PQfreeCancel(st->cancel); + st->cancel = NULL; } } @@ -7867,3 +7944,15 @@ socket_has_input(socket_set *sa, int fd, int idx) } #endif /* POLL_USING_SELECT */ + +#ifdef WIN32 +/* + * query cancellation callback for Windows + */ +static void +pgbench_cancel_callback(void) +{ + /* send cancel requests to all connections */ + cancel_all(); +} +#endif diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl index 96be529d6b..43eadfbe9f 100644 --- a/src/bin/pgbench/t/001_pgbench_with_server.pl +++ b/src/bin/pgbench/t/001_pgbench_with_server.pl @@ -7,6 +7,7 @@ use warnings; use PostgreSQL::Test::Cluster; use PostgreSQL::Test::Utils; use Test::More; +use Time::HiRes qw(usleep); # Check the initial state of the data generated. Tables for tellers and # branches use NULL for their filler attribute. The table accounts uses @@ -1502,6 +1503,47 @@ update counter set i = i+1 returning i \gset # Clean up $node->safe_psql('postgres', 'DROP TABLE counter;'); +# Test query canceling by sending SIGINT to a running pgbench +SKIP: +{ + skip "sending SIGINT on Windows terminates the test itself", 3 + if $windows_os; + + my ($stdin, $stdout, $stderr, @file); + + @file = $node->_pgbench_make_files( + { + '003_pgbench_cancel' => qq{ +select pg_sleep($PostgreSQL::Test::Utils::timeout_default); + }}); + + local %ENV = $node->_get_env(); + + my $h = IPC::Run::start( + [ 'pgbench', '-c', '2', '-j', '2', + '-T', "$PostgreSQL::Test::Utils::timeout_default", @file ], + \$stdin, \$stdout, \$stderr); + + $node->poll_query_until('postgres', + q{SELECT (SELECT count(*) FROM pg_stat_activity WHERE query ~ '^select pg_sleep') = 2;} + ) or die "timed out"; + + # Send cancel request + $h->signal('INT'); + + my $result = finish $h; + + ok(!$result, 'pgbench failed as expected'); + like( + $stderr, + qr/Run was aborted; the above results are incomplete/, + 'pgbench was canceled'); + + is($node->safe_psql('postgres', + q{SELECT count(*) FROM pg_stat_activity WHERE query ~ '^select pg_sleep'}), + '0', 'all queries were canceled'); +} + # done $node->safe_psql('postgres', 'DROP TABLESPACE regress_pgbench_tap_1_ts'); $node->stop; -- 2.25.1 --Multipart=_Tue__19_Sep_2023_17_30_11_+0900_JATr+KlP9+ALhH+Y-- ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-07-24 12:53 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-07-28 14:03 [PATCH 5/5] A couple more places for incremental sort Tomas Vondra <tomas@2ndquadrant.com> 2019-07-28 14:03 [PATCH 7/8] A couple more places for incremental sort Tomas Vondra <tomas@2ndquadrant.com> 2023-05-31 11:58 [PATCH v37 09/11] Add support for min/max aggregates for IVM Yugo Nagata <nagata@sraoss.co.jp> 2023-07-24 12:53 [PATCH v4] Allow pgbnech to cancel queries during benchmark Yugo Nagata <nagata@sraoss.co.jp> 2023-07-24 12:53 [PATCH v4] Allow pgbnech to cancel queries during benchmark Yugo Nagata <nagata@sraoss.co.jp> 2023-07-24 12:53 [PATCH v4] Allow pgbnech to cancel queries during benchmark Yugo Nagata <nagata@sraoss.co.jp>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox