From 0c61cff57a1dc7685fcac9f09451b261f14cb711 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 29 Nov 2009 18:14:32 +0000 Subject: [PATCH] Make pg_stat_activity.application_name visible to all users, rather than being hidden when current_query is. Relocate it to a column position more consistent with that behavior. Per discussion. --- doc/src/sgml/monitoring.sgml | 16 +++---- src/backend/catalog/system_views.sql | 6 +-- src/backend/utils/adt/pgstatfuncs.c | 71 ++++++++++++++-------------- src/include/catalog/catversion.h | 4 +- src/include/catalog/pg_proc.h | 4 +- src/test/regress/expected/rules.out | 2 +- 6 files changed, 52 insertions(+), 51 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 94fbce63ad..f56fd214ae 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -1,4 +1,4 @@ - + Monitoring Database Activity @@ -235,15 +235,15 @@ postgres: user database host pg_stat_activity One row per server process, showing database OID, database - name, process ID, user OID, user name, current query, - query's waiting status, time at which the current transaction and - current query began execution, time at which the process was - started, client's address and port number, and application name. + name, process ID, user OID, user name, application name, + current query, query's waiting status, time at which the current + transaction and current query began execution, time at which the + process was started, and client's address and port number. The columns that report data on the current query are available unless the parameter track_activities has been turned off. - Furthermore, these columns and the application name are only visible if - the user examining the view is a superuser or the same as the user - owning the process being reported on. + Furthermore, these columns are only visible if the user examining + the view is a superuser or the same as the user owning the process + being reported on. diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 5bf7647816..05608bdbee 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -3,7 +3,7 @@ * * Copyright (c) 1996-2009, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.62 2009/11/28 23:38:07 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.63 2009/11/29 18:14:30 tgl Exp $ */ CREATE VIEW pg_roles AS @@ -333,14 +333,14 @@ CREATE VIEW pg_stat_activity AS S.procpid, S.usesysid, U.rolname AS usename, + S.application_name, S.current_query, S.waiting, S.xact_start, S.query_start, S.backend_start, S.client_addr, - S.client_port, - S.application_name + S.client_port 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/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 6d93c5cf75..5ccd9cdd51 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.55 2009/11/28 23:38:07 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.56 2009/11/29 18:14:30 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -420,14 +420,14 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) TupleDescInitEntry(tupdesc, (AttrNumber) 1, "datid", OIDOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 2, "procpid", INT4OID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 3, "usesysid", OIDOID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 4, "current_query", TEXTOID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 5, "waiting", BOOLOID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 6, "act_start", TIMESTAMPTZOID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 7, "query_start", TIMESTAMPTZOID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 8, "backend_start", TIMESTAMPTZOID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 9, "client_addr", INETOID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 10, "client_port", INT4OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 11, "application_name", TEXTOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 4, "application_name", TEXTOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 5, "current_query", TEXTOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 6, "waiting", BOOLOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 7, "act_start", TIMESTAMPTZOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 8, "query_start", TIMESTAMPTZOID, -1, 0); + 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); funcctx->tuple_desc = BlessTupleDesc(tupdesc); @@ -489,11 +489,15 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) MemSet(nulls, 0, sizeof(nulls)); if (*(int *) (funcctx->user_fctx) > 0) + { /* Get specific pid slot */ beentry = pgstat_fetch_stat_beentry(*(int *) (funcctx->user_fctx)); + } else + { /* Get the next one in the list */ beentry = pgstat_fetch_stat_beentry(funcctx->call_cntr + 1); /* 1-based index */ + } if (!beentry) { int i; @@ -501,8 +505,8 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) for (i = 0; i < sizeof(nulls) / sizeof(nulls[0]); i++) nulls[i] = true; - nulls[3] = false; - values[3] = CStringGetTextDatum(""); + nulls[4] = false; + values[4] = CStringGetTextDatum(""); tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls); SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple)); @@ -512,43 +516,47 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) values[0] = ObjectIdGetDatum(beentry->st_databaseid); values[1] = Int32GetDatum(beentry->st_procpid); values[2] = ObjectIdGetDatum(beentry->st_userid); + if (beentry->st_appname) + values[3] = CStringGetTextDatum(beentry->st_appname); + else + nulls[3] = true; /* Values only available to same user or superuser */ if (superuser() || beentry->st_userid == GetUserId()) { if (*(beentry->st_activity) == '\0') { - values[3] = CStringGetTextDatum(""); + values[4] = CStringGetTextDatum(""); } else { - values[3] = CStringGetTextDatum(beentry->st_activity); + values[4] = CStringGetTextDatum(beentry->st_activity); } - values[4] = BoolGetDatum(beentry->st_waiting); + values[5] = BoolGetDatum(beentry->st_waiting); if (beentry->st_xact_start_timestamp != 0) - values[5] = TimestampTzGetDatum(beentry->st_xact_start_timestamp); + values[6] = TimestampTzGetDatum(beentry->st_xact_start_timestamp); else - nulls[5] = true; + nulls[6] = true; if (beentry->st_activity_start_timestamp != 0) - values[6] = TimestampTzGetDatum(beentry->st_activity_start_timestamp); + values[7] = TimestampTzGetDatum(beentry->st_activity_start_timestamp); else - nulls[6] = true; + nulls[7] = true; if (beentry->st_proc_start_timestamp != 0) - values[7] = TimestampTzGetDatum(beentry->st_proc_start_timestamp); + values[8] = TimestampTzGetDatum(beentry->st_proc_start_timestamp); else - nulls[7] = true; + nulls[8] = true; /* A zeroed client addr means we don't know */ memset(&zero_clientaddr, 0, sizeof(zero_clientaddr)); if (memcmp(&(beentry->st_clientaddr), &zero_clientaddr, sizeof(zero_clientaddr) == 0)) { - nulls[8] = true; nulls[9] = true; + nulls[10] = true; } else { @@ -571,15 +579,15 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) NI_NUMERICHOST | NI_NUMERICSERV); if (ret) { - nulls[8] = true; nulls[9] = true; + nulls[10] = true; } else { clean_ipv6_addr(beentry->st_clientaddr.addr.ss_family, remote_host); - values[8] = DirectFunctionCall1(inet_in, + values[9] = DirectFunctionCall1(inet_in, CStringGetDatum(remote_host)); - values[9] = Int32GetDatum(atoi(remote_port)); + values[10] = Int32GetDatum(atoi(remote_port)); } } else if (beentry->st_clientaddr.addr.ss_family == AF_UNIX) @@ -590,28 +598,21 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) * connections we have no permissions to view, or with * errors. */ - nulls[8] = true; - values[9] = DatumGetInt32(-1); + nulls[9] = true; + values[10] = DatumGetInt32(-1); } else { /* Unknown address type, should never happen */ - nulls[8] = true; nulls[9] = true; + nulls[10] = true; } } - - /* application name */ - if (beentry->st_appname) - values[10] = CStringGetTextDatum(beentry->st_appname); - else - nulls[10] = true; } else { /* No permissions to view data about this session */ - values[3] = CStringGetTextDatum(""); - nulls[4] = true; + values[4] = CStringGetTextDatum(""); nulls[5] = true; nulls[6] = true; nulls[7] = true; diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index d7225ae0c4..ad9c47a0b0 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.553 2009/11/29 03:02:27 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.554 2009/11/29 18:14:30 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200911282 +#define CATALOG_VERSION_NO 200911291 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index f0988892c7..d6191e61e4 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.553 2009/11/28 23:38:07 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.554 2009/11/29 18:14:30 tgl Exp $ * * NOTES * The script catalog/genbki.sh reads this file and generates .bki @@ -2999,7 +2999,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,16,1184,1184,1184,869,23,25}" "{i,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,procpid,usesysid,current_query,waiting,xact_start,query_start,backend_start,client_addr,client_port,application_name}" _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}" "{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_ )); 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"); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index a4025436a9..9420e568ac 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1289,7 +1289,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name); pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline); pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin; - pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.current_query, s.waiting, s.xact_start, s.query_start, s.backend_start, s.client_addr, s.client_port, s.application_name FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_port, application_name), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid)); + pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.application_name, s.current_query, s.waiting, s.xact_start, s.query_start, s.backend_start, s.client_addr, s.client_port FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid)); pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])); pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname; pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_alloc() AS buffers_alloc; -- 2.40.0