From 1608f3bd8153045da9cb3db269597b92042a4d9c Mon Sep 17 00:00:00 2001 From: Amit Date: Sun, 28 Feb 2016 01:50:07 +0900 Subject: [PATCH 1/3] Provide a way for utility commands to report progress Commands can update a set of parameters in shared memory using: pgstat_progress_update_param(param_index, param_value) Up to 10 independent 64-bit integer values can be published by commands. In addition to those, a command should always report its BackendCommandType and the OID of the relation it is about to begin processing at the beginning of the processing using: pgstat_progress_start_command(cmdtype) pgstat_progress_set_command_target(relid) A view can be defined in system_views.sql that outputs the values returned by pg_stat_get_progress_info(cmdtype), where 'cmdtype' is numeric value as mentioned above. Each such view has columns corresponding to the counters published by respective commands. There is a SQL-callable function pg_stat_reset_local_progress() which when called, resets the progress information of the backend of the session in which its called. It is useful to erase progress info of commands previously run in the session. --- doc/src/sgml/monitoring.sgml | 8 +++ src/backend/postmaster/pgstat.c | 76 ++++++++++++++++++++++++++++ src/backend/utils/adt/pgstatfuncs.c | 93 +++++++++++++++++++++++++++++++++++ src/include/catalog/pg_proc.h | 4 ++ src/include/pgstat.h | 26 ++++++++++ 5 files changed, 207 insertions(+), 0 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 85459d0..45d9ed7 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -1935,6 +1935,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser zero (requires superuser privileges) + + + pg_stat_reset_local_progress()pg_stat_reset_local_progress + void + + Reset command progress parameters of local backend + + diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index da768c6..af5be5f 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -2731,6 +2731,8 @@ pgstat_bestart(void) beentry->st_clienthostname[NAMEDATALEN - 1] = '\0'; beentry->st_appname[NAMEDATALEN - 1] = '\0'; beentry->st_activity[pgstat_track_activity_query_size - 1] = '\0'; + beentry->st_command = COMMAND_INVALID; + MemSet(&beentry->st_progress_param, 0, sizeof(beentry->st_progress_param)); pgstat_increment_changecount_after(beentry); @@ -2851,6 +2853,80 @@ pgstat_report_activity(BackendState state, const char *cmd_str) pgstat_increment_changecount_after(beentry); } +/*----------- + * pgstat_progress_update_param() - + * + * Update index'th member in st_progress_param[] of own backend entry. + *----------- + */ +void +pgstat_progress_update_param(int index, int64 val) +{ + volatile PgBackendStatus *beentry = MyBEEntry; + + if(!beentry) + return; + + if (!pgstat_track_activities) + return; + + pgstat_increment_changecount_before(beentry); + beentry->st_progress_param[index] = val; + pgstat_increment_changecount_after(beentry); +} + +/*----------- + * pgstat_progress_start_command()- + * + * Set st_command in own backend entry. Also, zero-initialize + * st_progress_param array. + *----------- + */ +void +pgstat_progress_start_command(BackendCommandType cmdtype) +{ + volatile PgBackendStatus *beentry = MyBEEntry; + + pgstat_increment_changecount_before(beentry); + beentry->st_command = cmdtype; + MemSet(&beentry->st_progress_param, 0, + sizeof(beentry->st_progress_param)); + pgstat_increment_changecount_after(beentry); +} + +/*----------- + * pgstat_progress_set_command_target()- + * + * Set st_command_target in own backend entry. + *----------- + */ +void +pgstat_progress_set_command_target(Oid relid) +{ + volatile PgBackendStatus *beentry = MyBEEntry; + + pgstat_increment_changecount_before(beentry); + beentry->st_command_target = relid; + pgstat_increment_changecount_after(beentry); +} + +/*-------- + * pgstat_reset_local_progress()- + * + * Reset local backend's progress parameters. Setting st_command to + * COMMAND_INVALID will do. + *-------- + */ +void +pgstat_reset_local_progress() +{ + PgBackendStatus *beentry = MyBEEntry; + + pgstat_increment_changecount_before(beentry); + beentry->st_command = COMMAND_INVALID; + pgstat_increment_changecount_after(beentry); +} + /* ---------- * pgstat_report_appname() - * diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 1b22fcc..ac72def 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -64,6 +64,8 @@ extern Datum pg_stat_get_backend_xact_start(PG_FUNCTION_ARGS); extern Datum pg_stat_get_backend_start(PG_FUNCTION_ARGS); extern Datum pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS); extern Datum pg_stat_get_backend_client_port(PG_FUNCTION_ARGS); +extern Datum pg_stat_get_progress_info(PG_FUNCTION_ARGS); +extern Datum pg_stat_reset_local_progress(PG_FUNCTION_ARGS); extern Datum pg_stat_get_db_numbackends(PG_FUNCTION_ARGS); extern Datum pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS); @@ -525,6 +527,97 @@ pg_stat_get_backend_idset(PG_FUNCTION_ARGS) } /* + * Returns progress parameter values of backends running a given command + */ +Datum +pg_stat_get_progress_info(PG_FUNCTION_ARGS) +{ +#define PG_STAT_GET_PROGRESS_COLS N_PROGRESS_PARAM + 2 + int num_backends = pgstat_fetch_stat_numbackends(); + int curr_backend; + int32 cmdtype = PG_GETARG_INT32(0); + TupleDesc tupdesc; + Tuplestorestate *tupstore; + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + MemoryContext per_query_ctx; + MemoryContext oldcontext; + + /* check to see if caller supports us returning a tuplestore */ + if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("set-valued function called in context that cannot accept a set"))); + if (!(rsinfo->allowedModes & SFRM_Materialize)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("materialize mode required, but it is not " \ + "allowed in this context"))); + + /* Build a tuple descriptor for our result type */ + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; + oldcontext = MemoryContextSwitchTo(per_query_ctx); + + tupstore = tuplestore_begin_heap(true, false, work_mem); + rsinfo->returnMode = SFRM_Materialize; + rsinfo->setResult = tupstore; + rsinfo->setDesc = tupdesc; + MemoryContextSwitchTo(oldcontext); + + for (curr_backend = 1; curr_backend <= num_backends; curr_backend++) + { + LocalPgBackendStatus *local_beentry; + PgBackendStatus *beentry; + Datum values[PG_STAT_GET_PROGRESS_COLS]; + bool nulls[PG_STAT_GET_PROGRESS_COLS]; + int i; + + MemSet(values, 0, sizeof(values)); + MemSet(nulls, 0, sizeof(nulls)); + + local_beentry = pgstat_fetch_stat_local_beentry(curr_backend); + + if (!local_beentry) + continue; + + beentry = &local_beentry->backendStatus; + + /* + * Report values for only those backends which are running the given + * command. XXX - privilege check is maybe dubious. + */ + if (!beentry || + beentry->st_command != cmdtype || + !has_privs_of_role(GetUserId(), beentry->st_userid)) + continue; + + /* Values available to all callers */ + values[0] = Int32GetDatum(beentry->st_procpid); + values[1] = ObjectIdGetDatum(beentry->st_command_target); + for(i = 0; i < N_PROGRESS_PARAM; i++) + values[i+2] = UInt32GetDatum(beentry->st_progress_param[i]); + + tuplestore_putvalues(tupstore, tupdesc, values, nulls); + } + + /* clean up and return the tuplestore */ + tuplestore_donestoring(tupstore); + + return (Datum) 0; +} + +/* Reset local backend's command progress info */ +Datum +pg_stat_reset_local_progress(PG_FUNCTION_ARGS) +{ + pgstat_reset_local_progress(); + + PG_RETURN_VOID(); +} + +/* * Returns activity of PG backends. */ Datum diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index cbbb883..daaa233 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -2710,6 +2710,8 @@ DATA(insert OID = 1936 ( pg_stat_get_backend_idset PGNSP PGUID 12 1 100 0 0 f DESCR("statistics: currently active backend IDs"); DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 f f f f f t s r 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23,28,28,16,25,25,23,16,25}" "{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,ssl,sslversion,sslcipher,sslbits,sslcompression,sslclientdn}" _null_ _null_ pg_stat_get_activity _null_ _null_ _null_ )); DESCR("statistics: information about currently active backends"); +DATA(insert OID = 3318 ( pg_stat_get_progress_info PGNSP PGUID 12 1 100 0 0 f f f f f t s r 1 0 2249 "23" "{23,23,26,20,20,20,20,20,20,20,20,20,20}" "{i,o,o,o,o,o,o,o,o,o,o,o,o}" "{cmdtype,pid,relid,param1,param2,param3,param4,param5,param6,param7,param8,param9,param10}" _null_ _null_ pg_stat_get_progress_info _null_ _null_ _null_ )); +DESCR("statistics: information about progress of backends running maintenance command"); DATA(insert OID = 3099 ( pg_stat_get_wal_senders PGNSP PGUID 12 1 10 0 0 f f f f f t s r 0 0 2249 "" "{23,25,3220,3220,3220,3220,23,25}" "{o,o,o,o,o,o,o,o}" "{pid,state,sent_location,write_location,flush_location,replay_location,sync_priority,sync_state}" _null_ _null_ pg_stat_get_wal_senders _null_ _null_ _null_ )); DESCR("statistics: information about currently active replication"); DATA(insert OID = 3317 ( pg_stat_get_wal_receiver PGNSP PGUID 12 1 0 0 0 f f f f f f s r 0 0 2249 "" "{23,25,3220,23,3220,23,1184,1184,3220,1184,25}" "{o,o,o,o,o,o,o,o,o,o,o}" "{pid,status,receive_start_lsn,receive_start_tli,received_lsn,received_tli,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time,slot_name}" _null_ _null_ pg_stat_get_wal_receiver _null_ _null_ _null_ )); @@ -2849,6 +2851,8 @@ DATA(insert OID = 3776 ( pg_stat_reset_single_table_counters PGNSP PGUID 12 1 0 DESCR("statistics: reset collected statistics for a single table or index in the current database"); DATA(insert OID = 3777 ( pg_stat_reset_single_function_counters PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 2278 "26" _null_ _null_ _null_ _null_ _null_ pg_stat_reset_single_function_counters _null_ _null_ _null_ )); DESCR("statistics: reset collected statistics for a single function in the current database"); +DATA(insert OID = 3319 ( pg_stat_reset_local_progress PGNSP PGUID 12 1 0 0 0 f f f f f f v s 0 0 2278 "" _null_ _null_ _null_ _null_ _null_ pg_stat_reset_local_progress _null_ _null_ _null_ )); +DESCR("statistics: reset progress information of the local backend"); DATA(insert OID = 3163 ( pg_trigger_depth PGNSP PGUID 12 1 0 0 0 f f f f t f s s 0 0 23 "" _null_ _null_ _null_ _null_ _null_ pg_trigger_depth _null_ _null_ _null_ )); DESCR("current trigger depth"); diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 65e968e..dfa6b31 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -696,6 +696,17 @@ typedef enum BackendState } BackendState; /* ---------- + * Command type for progress reporting purposes + * ---------- + */ +typedef enum BackendCommandType +{ + COMMAND_INVALID = 0, +} BackendCommandType; + +#define N_PROGRESS_PARAM 10 + +/* ---------- * Shared-memory data structures * ---------- */ @@ -776,6 +787,17 @@ typedef struct PgBackendStatus /* current command string; MUST be null-terminated */ char *st_activity; + + /* + * Progress parameters of currently running utility command in the + * backend. Different commands store different number of up to + * N_PROGRESS_PARAM values in st_progress_param. However, each command + * must set st_command and st_command_target (OID of the relation it is + * about to begin to process) at the beginning of command processing. + */ + BackendCommandType st_command; + Oid st_command_target; + int64 st_progress_param[N_PROGRESS_PARAM]; } PgBackendStatus; /* @@ -935,6 +957,10 @@ extern void pgstat_report_waiting(bool waiting); extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser); extern const char *pgstat_get_crashed_backend_activity(int pid, char *buffer, int buflen); +extern void pgstat_progress_start_command(BackendCommandType cmdtype); +extern void pgstat_progress_set_command_target(Oid relid); +extern void pgstat_progress_update_param(int index, int64 val); +extern void pgstat_reset_local_progress(void); extern PgStat_TableStatus *find_tabstat_entry(Oid rel_id); extern PgStat_BackendFunctionEntry *find_funcstat_entry(Oid func_id); -- 1.7.1