Here is a small prototype for a query progress indicator.
Past discussions seemed to indicate that the best place to report this
would be in pg_stat_activity. So that's what this does. You can try it
out with any of the following (on a sufficiently large table): VACUUM
(lazy) (also autovacuum), COPY out from table, COPY in from file,
table-rewriting ALTER TABLE (e.g., add column with default), or a very
simple query. Run the command, and stare at pg_stat_activity (perhaps
via "watch") in a separate session.
More can be added, and the exact placement of the counters is debatable,
but those might be details to be worked out later. Note, my emphasis
here is on maintenance commands; I don't plan to do a progress
estimation of complex queries at this time.
Some thoughts:
- Are the interfaces OK?
- Is this going to be too slow to be useful?
- Should there be a separate switch to turn it on (currently
track_activities)?
- How to handle commands that process multiple tables? For example,
lazy VACUUM on a single table is pretty easy to track (count the block
numbers), but what about a database-wide lazy VACUUM?
Other comments?
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 8852326..8280550 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -341,7 +341,8 @@ CREATE VIEW pg_stat_activity AS
S.xact_start,
S.query_start,
S.waiting,
- S.current_query
+ S.current_query,
+ S.query_progress
FROM pg_database D, pg_stat_get_activity(NULL) AS S, pg_authid U
WHERE S.datid = D.oid AND
S.usesysid = U.oid;
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 906d547..67e4fe0 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -35,6 +35,7 @@
#include "miscadmin.h"
#include "optimizer/planner.h"
#include "parser/parse_relation.h"
+#include "pgstat.h"
#include "rewrite/rewriteHandler.h"
#include "storage/fd.h"
#include "tcop/tcopprot.h"
@@ -1424,6 +1425,7 @@ CopyTo(CopyState cstate)
bool *nulls;
HeapScanDesc scandesc;
HeapTuple tuple;
+ int64 cnt = 0;
values = (Datum *) palloc(num_phys_attrs * sizeof(Datum));
nulls = (bool *) palloc(num_phys_attrs * sizeof(bool));
@@ -1439,6 +1441,8 @@ CopyTo(CopyState cstate)
/* Format and send the data */
CopyOneRowTo(cstate, HeapTupleGetOid(tuple), values, nulls);
+
+ pgstat_report_progress(++cnt, cstate->rel->rd_rel->reltuples);
}
heap_endscan(scandesc);
@@ -1695,6 +1699,8 @@ CopyFrom(CopyState cstate)
CommandId mycid = GetCurrentCommandId(true);
int hi_options = 0; /* start with default heap_insert options */
BulkInsertState bistate;
+ off_t file_size;
+ off_t file_cnt = 0;
Assert(cstate->rel);
@@ -1776,6 +1782,8 @@ CopyFrom(CopyState cstate)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("\"%s\" is a directory", cstate->filename)));
+
+ file_size = st.st_size;
}
tupDesc = RelationGetDescr(cstate->rel);
@@ -2051,6 +2059,9 @@ CopyFrom(CopyState cstate)
}
Assert(fieldno == nfields);
+
+ file_cnt += cstate->line_buf.len;
+ pgstat_report_progress(file_cnt, file_size);
}
else
{
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 5f6fe41..0d70e86 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -60,6 +60,7 @@
#include "parser/parse_type.h"
#include "parser/parse_utilcmd.h"
#include "parser/parser.h"
+#include "pgstat.h"
#include "rewrite/rewriteDefine.h"
#include "rewrite/rewriteHandler.h"
#include "storage/bufmgr.h"
@@ -3126,6 +3127,7 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
MemoryContext oldCxt;
List *dropped_attrs = NIL;
ListCell *lc;
+ int64 cnt = 0;
econtext = GetPerTupleExprContext(estate);
@@ -3253,6 +3255,8 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
ResetExprContext(econtext);
+ pgstat_report_progress(++cnt, oldrel->rd_rel->reltuples);
+
CHECK_FOR_INTERRUPTS();
}
@@ -7064,6 +7068,9 @@ copy_relation_data(SMgrRelation src, SMgrRelation dst,
* write; we'll do it ourselves below.
*/
smgrextend(dst, forkNum, blkno, buf, true);
+
+ if (forkNum == MAIN_FORKNUM)
+ pgstat_report_progress(blkno + 1, nblocks);
}
/*
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index 4408db8..2127434 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -350,6 +350,8 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
bool all_visible_according_to_vm = false;
bool all_visible;
+ pgstat_report_progress(blkno, nblocks);
+
/*
* Skip pages that don't require vacuuming according to the visibility
* map. But only if we've seen a streak of at least
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 48599d2..28342bc 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -47,6 +47,7 @@
#include "optimizer/clauses.h"
#include "parser/parse_clause.h"
#include "parser/parsetree.h"
+#include "pgstat.h"
#include "storage/bufmgr.h"
#include "storage/lmgr.h"
#include "storage/smgr.h"
@@ -1181,12 +1182,15 @@ ExecutePlan(EState *estate,
{
TupleTableSlot *slot;
long current_tuple_count;
+ double planned_tuple_count;
/*
* initialize local variables
*/
current_tuple_count = 0;
+ planned_tuple_count = planstate->plan->plan_rows;
+
/*
* Set the direction.
*/
@@ -1246,6 +1250,8 @@ ExecutePlan(EState *estate,
current_tuple_count++;
if (numberTuples && numberTuples == current_tuple_count)
break;
+
+ pgstat_report_progress(current_tuple_count, planned_tuple_count);
}
}
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 894527e..07ad004 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -2328,6 +2328,7 @@ pgstat_bestart(void)
/* Also make sure the last byte in each string area is always 0 */
beentry->st_appname[NAMEDATALEN - 1] = '\0';
beentry->st_activity[pgstat_track_activity_query_size - 1] = '\0';
+ beentry->st_progress = 0.0;
beentry->st_changecount++;
Assert((beentry->st_changecount & 1) == 0);
@@ -2497,6 +2498,34 @@ pgstat_report_waiting(bool waiting)
beentry->st_waiting = waiting;
}
+/* ----------
+ * pgstat_report_progress() -
+ *
+ * Called from various commands to report their progress.
+ *
+ * NB: this *must* be able to survive being called before MyBEEntry has been
+ * initialized.
+ * ----------
+ */
+void
+pgstat_report_progress(double work_done, double work_total)
+{
+ volatile PgBackendStatus *beentry = MyBEEntry;
+
+ if (!pgstat_track_activities || !beentry)
+ return;
+
+ /*
+ * Update my status entry, following the protocol of bumping
+ * st_changecount before and after. We use a volatile pointer here to
+ * ensure the compiler doesn't try to get cute.
+ */
+ beentry->st_changecount++;
+ beentry->st_progress = work_done/work_total;
+ beentry->st_changecount++;
+ Assert((beentry->st_changecount & 1) == 0);
+}
+
/* ----------
* pgstat_read_current_status() -
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 5efad23..2de7e91 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -3814,6 +3814,7 @@ PostgresMain(int argc, char *argv[], const char *username)
set_ps_display("idle", false);
pgstat_report_activity("<IDLE>");
}
+ pgstat_report_progress(0, 0);
ReadyForQuery(whereToSendOutput);
send_ready_for_query = false;
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 8379407..a55dfcf 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -50,6 +50,7 @@ extern Datum pg_stat_get_backend_pid(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_backend_dbid(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_backend_userid(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_backend_activity(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_backend_progress(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_backend_waiting(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_backend_xact_start(PG_FUNCTION_ARGS);
@@ -419,7 +420,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
- tupdesc = CreateTemplateTupleDesc(11, false);
+ tupdesc = CreateTemplateTupleDesc(12, false);
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "datid", OIDOID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "procpid", INT4OID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 3, "usesysid", OIDOID, -1, 0);
@@ -431,6 +432,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
TupleDescInitEntry(tupdesc, (AttrNumber) 9, "backend_start", TIMESTAMPTZOID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 10, "client_addr", INETOID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 11, "client_port", INT4OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 12, "query_progress", FLOAT8OID, -1, 0);
funcctx->tuple_desc = BlessTupleDesc(tupdesc);
@@ -482,8 +484,8 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
if (funcctx->call_cntr < funcctx->max_calls)
{
/* for each row */
- Datum values[11];
- bool nulls[11];
+ Datum values[12];
+ bool nulls[12];
HeapTuple tuple;
PgBackendStatus *beentry;
SockAddr zero_clientaddr;
@@ -611,6 +613,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
nulls[10] = true;
}
}
+ values[11] = Float8GetDatum(beentry->st_progress);
}
else
{
@@ -622,6 +625,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
nulls[8] = true;
nulls[9] = true;
nulls[10] = true;
+ nulls[11] = true;
}
tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
@@ -703,6 +707,24 @@ pg_stat_get_backend_activity(PG_FUNCTION_ARGS)
Datum
+pg_stat_get_backend_progress(PG_FUNCTION_ARGS)
+{
+ int32 beid = PG_GETARG_INT32(0);
+ PgBackendStatus *beentry;
+ double result;
+
+ if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
+ PG_RETURN_NULL();
+ else if (!superuser() && beentry->st_userid != GetUserId())
+ PG_RETURN_NULL();
+
+ result = beentry->st_progress;
+
+ PG_RETURN_FLOAT8(result);
+}
+
+
+Datum
pg_stat_get_backend_waiting(PG_FUNCTION_ARGS)
{
int32 beid = PG_GETARG_INT32(0);
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 6036493..6cd7b62 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3031,7 +3031,7 @@ DATA(insert OID = 2784 ( pg_stat_get_last_autoanalyze_time PGNSP PGUID 12 1 0 0
DESCR("statistics: last auto analyze time for a table");
DATA(insert OID = 1936 ( pg_stat_get_backend_idset PGNSP PGUID 12 1 100 0 f f f t t s 0 0 23 "" _null_ _null_ _null_ _null_ pg_stat_get_backend_idset _null_ _null_ _null_ ));
DESCR("statistics: currently active backend IDs");
-DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,16,1184,1184,1184,869,23}" "{i,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,procpid,usesysid,application_name,current_query,waiting,xact_start,query_start,backend_start,client_addr,client_port}" _null_ pg_stat_get_activity _null_ _null_ _null_ ));
+DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,16,1184,1184,1184,869,23,701}" "{i,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,procpid,usesysid,application_name,current_query,waiting,xact_start,query_start,backend_start,client_addr,client_port,query_progress}" _null_ pg_stat_get_activity _null_ _null_ _null_ ));
DESCR("statistics: information about currently active backends");
DATA(insert OID = 2026 ( pg_backend_pid PGNSP PGUID 12 1 0 0 f f f t f s 0 0 23 "" _null_ _null_ _null_ _null_ pg_backend_pid _null_ _null_ _null_ ));
DESCR("statistics: current backend PID");
@@ -3043,6 +3043,8 @@ DATA(insert OID = 1939 ( pg_stat_get_backend_userid PGNSP PGUID 12 1 0 0 f f f
DESCR("statistics: user ID of backend");
DATA(insert OID = 1940 ( pg_stat_get_backend_activity PGNSP PGUID 12 1 0 0 f f f t f s 1 0 25 "23" _null_ _null_ _null_ _null_ pg_stat_get_backend_activity _null_ _null_ _null_ ));
DESCR("statistics: current query of backend");
+DATA(insert OID = 2614 ( pg_stat_get_backend_progress PGNSP PGUID 12 1 0 0 f f f t f s 1 0 701 "23" _null_ _null_ _null_ _null_ pg_stat_get_backend_progress _null_ _null_ _null_ ));
+DESCR("statistics: progress of current query of backend");
DATA(insert OID = 2853 ( pg_stat_get_backend_waiting PGNSP PGUID 12 1 0 0 f f f t f s 1 0 16 "23" _null_ _null_ _null_ _null_ pg_stat_get_backend_waiting _null_ _null_ _null_ ));
DESCR("statistics: is backend currently waiting for a lock");
DATA(insert OID = 2094 ( pg_stat_get_backend_activity_start PGNSP PGUID 12 1 0 0 f f f t f s 1 0 1184 "23" _null_ _null_ _null_ _null_ pg_stat_get_backend_activity_start _null_ _null_ _null_ ));
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 3dd5f45..98ffbdd 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -610,6 +610,9 @@ typedef struct PgBackendStatus
/* current command string; MUST be null-terminated */
char *st_activity;
+
+ /* progress indicator */
+ double st_progress;
} PgBackendStatus;
/*
@@ -690,6 +693,7 @@ extern void pgstat_report_activity(const char *cmd_str);
extern void pgstat_report_appname(const char *appname);
extern void pgstat_report_xact_timestamp(TimestampTz tstamp);
extern void pgstat_report_waiting(bool waiting);
+extern void pgstat_report_progress(double work_done, double work_total);
extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser);
extern void pgstat_initstats(Relation rel);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
