Skip to content

Commit a183310

Browse files
bmomjianbmomjian
committed
Add start time to pg_stat_activity
Neil Conway
1 parent ddd50a0 commit a183310

File tree

20 files changed

+208
-91
lines changed

20 files changed

+208
-91
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.143 2003/03/13 01:30:28 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.144 2003/03/20 03:34:55 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -5315,7 +5315,7 @@ SELECT LOCALTIMESTAMP;
53155315

53165316
<para>
53175317
There is also the function <function>timeofday()</function>, which for historical
5318-
reasons returns a text string rather than a <type>timestamp</type> value:
5318+
reasons returns a <type>text</type> string rather than a <type>timestamp</type> value:
53195319
<screen>
53205320
SELECT timeofday();
53215321
<lineannotation>Result: </lineannotation><computeroutput>Sat Feb 17 19:07:32.000126 2001 EST</computeroutput>
@@ -5326,7 +5326,11 @@ SELECT timeofday();
53265326
It is important to know that
53275327
<function>CURRENT_TIMESTAMP</function> and related functions return
53285328
the start time of the current transaction; their values do not
5329-
change during the transaction. <function>timeofday()</function>
5329+
change during the transaction. This is considered a feature:
5330+
the intent is to allow a single transaction to have a consistent
5331+
notion of the <quote>current</quote> time, so that multiple
5332+
modifications within the same transaction bear the same
5333+
timestamp. <function>timeofday()</function>
53305334
returns the wall-clock time and does advance during transactions.
53315335
</para>
53325336

‎doc/src/sgml/monitoring.sgml

Lines changed: 49 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/monitoring.sgml,v 1.15 2002/11/11 20:14:03 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/monitoring.sgml,v 1.16 2003/03/20 03:34:55 momjian Exp $
33
-->
44

55
<chapter id="monitoring">
@@ -146,12 +146,13 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
146146
<important>
147147
<para>
148148
Since the variables <varname>STATS_COMMAND_STRING</varname>,
149-
<varname>STATS_BLOCK_LEVEL</varname>,
150-
and <varname>STATS_ROW_LEVEL</varname>
151-
default to <literal>false</>, no statistics are actually collected
152-
in the default configuration. You must turn one or more of them on
153-
before you will get useful results from the statistical display
154-
functions.
149+
<varname>STATS_BLOCK_LEVEL</varname>, and
150+
<varname>STATS_ROW_LEVEL</varname> default to <literal>false</>,
151+
very few statistics are collected in the default
152+
configuration. Enabling one or more of these configuration
153+
variables will significantly enhance the amount of useful data
154+
produced by the statistics collector, at the expense of
155+
additional run-time overhead.
155156
</para>
156157
</important>
157158

@@ -205,11 +206,15 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
205206
<tbody>
206207
<row>
207208
<entry><structname>pg_stat_activity</></entry>
208-
<entry>One row per server process, showing process <acronym>ID</>, database,
209-
user, and current query. The current query column is only available
210-
to superusers; for others it reads as null. (Note that because of
211-
the collector's reporting delay, current query will only be up-to-date
212-
for long-running queries.)</entry>
209+
<entry>One row per server process, showing process
210+
<acronym>ID</>, database, user, current query, and the time at
211+
which the current query began execution. The columns that report
212+
data on the current query are only available if the
213+
<varname>STATS_COMMAND_STRING</varname> configuration option has
214+
been enabled. Furthermore, these columns can only be accessed by
215+
superusers; to other users, they always appear NULL. (Note that
216+
because of the collector's reporting delay, current query will
217+
only be up-to-date for long-running queries.)</entry>
213218
</row>
214219

215220
<row>
@@ -333,10 +338,20 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
333338
</para>
334339

335340
<para>
336-
The <structname>pg_statio_</> views are primarily useful to determine
337-
the effectiveness of the buffer cache. When the number of actual disk
338-
reads is much smaller than the number of buffer hits, then the cache
339-
is satisfying most read requests without invoking a kernel call.
341+
The <structname>pg_statio_</> views are primarily useful to
342+
determine the effectiveness of the buffer cache. When the number
343+
of actual disk reads is much smaller than the number of buffer
344+
hits, then the cache is satisfying most read requests without
345+
invoking a kernel call. However, these statistics do not give the
346+
entire story: due to the way in which <productname>PostgreSQL</>
347+
handles disk I/O, data that is not in the
348+
<productname>PostgreSQL</> buffer cache may still reside in the
349+
kernel's I/O cache, and may therefore still be fetched without
350+
requiring a physical read. Users interested in obtaining more
351+
detailed information on <productname>PostgreSQL</> I/O behavior are
352+
advised to use the <productname>PostgreSQL</> statistics collector
353+
in combination with operating system utilities that allow insight
354+
into the kernel's handling of I/O.
340355
</para>
341356

342357
<para>
@@ -401,7 +416,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
401416
<entry><function>pg_stat_get_db_blocks_hit</function>(<type>oid</type>)</entry>
402417
<entry><type>bigint</type></entry>
403418
<entry>
404-
Number of disk block requests found in cache for database
419+
Number of disk block fetch requests found in cache for database
405420
</entry>
406421
</row>
407422

@@ -478,7 +493,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
478493
<entry><type>set of integer</type></entry>
479494
<entry>
480495
Set of currently active backend IDs (from 1 to N where N is the
481-
number of active backends). See usage example below.
496+
number of active backends). See usage example below
482497
</entry>
483498
</row>
484499

@@ -518,15 +533,27 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
518533
<entry><function>pg_stat_get_backend_activity</function>(<type>integer</type>)</entry>
519534
<entry><type>text</type></entry>
520535
<entry>
521-
Current query of backend process (NULL if caller is not superuser)
536+
Current query of backend process (NULL if caller is not
537+
superuser, or <varname>STATS_COMMAND_STRING</varname> is not enabled)
522538
</entry>
523539
</row>
524540

541+
<row>
542+
<entry><function>pg_stat_get_backend_activity_start</function>(<type>integer</type>)</entry>
543+
<entry><type>text</type></entry>
544+
<entry>
545+
The time at which the specified backend's currently executing query was
546+
initiated (NULL if caller is not superuser, or
547+
<varname>STATS_COMMAND_STRING</varname> is not enabled)
548+
</entry>
549+
</row>
550+
551+
525552
<row>
526553
<entry><function>pg_stat_reset</function>()</entry>
527554
<entry><type>boolean</type></entry>
528555
<entry>
529-
Reset all currently collected statistics.
556+
Reset all currently collected statistics
530557
</entry>
531558
</row>
532559
</tbody>
@@ -535,7 +562,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
535562

536563
<note>
537564
<para>
538-
Blocks_fetched minus blocks_hit gives the number of kernel
565+
<literal>blocks_fetched</literal> minus
566+
<literal>blocks_hit</literal> gives the number of kernel
539567
<function>read()</> calls issued for the table, index, or
540568
database; but the actual number of physical reads is usually
541569
lower due to kernel-level buffering.

‎doc/src/sgml/runtime.sgml

Lines changed: 22 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v 1.170 2003/03/04 21:51:19 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v 1.171 2003/03/20 03:34:55 momjian Exp $
33
-->
44

55
<Chapter Id="runtime">
@@ -1182,16 +1182,31 @@ env PGOPTIONS='-c geqo=off' psql
11821182

11831183
<varlistentry>
11841184
<term><varname>STATS_COMMAND_STRING</varname> (<type>boolean</type>)</term>
1185+
<listitem>
1186+
<para>
1187+
Enables the collection of statistics on the currently
1188+
executing command of each backend, along with the time at
1189+
which that query began execution. This option is off by
1190+
default. Note that even when enabled, this information is only
1191+
visible to the superuser, so it should not represent a
1192+
security risk. This data can be accessed via the
1193+
<structname>pg_stat_activity</structname> system view; refer
1194+
to the &cite-admin; for more information.
1195+
</para>
1196+
</listitem>
1197+
</varlistentry>
1198+
1199+
<varlistentry>
11851200
<term><varname>STATS_BLOCK_LEVEL</varname> (<type>boolean</type>)</term>
11861201
<term><varname>STATS_ROW_LEVEL</varname> (<type>boolean</type>)</term>
11871202
<listitem>
11881203
<para>
1189-
Determines what information backends send to the statistics
1190-
collector process: current commands, block-level activity
1191-
statistics, or row-level activity statistics. All default to
1192-
off. Enabling statistics collection costs a small amount of
1193-
time per query, but is invaluable for debugging and
1194-
performance tuning.
1204+
Enables the collection of block-level and row-level statistics
1205+
on database activity, respectively. These options are off by
1206+
default. This data can be accessed via the
1207+
<structname>pg_stat</structname> and
1208+
<structname>pg_statio</structname> family of system views;
1209+
refer to the &cite-admin; for more information.
11951210
</para>
11961211
</listitem>
11971212
</varlistentry>

‎src/backend/catalog/heap.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.239 2003/01/08 22:06:20 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.240 2003/03/20 03:34:55 momjian Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -961,7 +961,8 @@ RemoveAttributeById(Oid relid, AttrNumber attnum)
961961
attStruct->attstattarget = 0;
962962

963963
/* Change the column name to something that isn't likely to conflict */
964-
snprintf(newattname, NAMEDATALEN, "........pg.dropped.%d........", attnum);
964+
snprintf(newattname, sizeof(newattname),
965+
"........pg.dropped.%d........", attnum);
965966
namestrcpy(&(attStruct->attname), newattname);
966967

967968
simple_heap_update(attr_rel, &tuple->t_self, tuple);

‎src/backend/catalog/namespace.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
* Portions Copyright (c) 1994, Regents of the University of California
1414
*
1515
* IDENTIFICATION
16-
* $Header: /cvsroot/pgsql/src/backend/catalog/namespace.c,v 1.48 2003/03/06 22:54:49 tgl Exp $
16+
* $Header: /cvsroot/pgsql/src/backend/catalog/namespace.c,v 1.49 2003/03/20 03:34:55 momjian Exp $
1717
*
1818
*-------------------------------------------------------------------------
1919
*/
@@ -1598,7 +1598,7 @@ InitTempTableNamespace(void)
15981598
elog(ERROR, "%s: not authorized to create temp tables",
15991599
DatabaseName);
16001600

1601-
snprintf(namespaceName, NAMEDATALEN, "pg_temp_%d", MyBackendId);
1601+
snprintf(namespaceName, sizeof(namespaceName), "pg_temp_%d", MyBackendId);
16021602

16031603
namespaceId = GetSysCacheOid(NAMESPACENAME,
16041604
CStringGetDatum(namespaceName),

‎src/backend/commands/cluster.c

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $Header: /cvsroot/pgsql/src/backend/commands/cluster.c,v 1.106 2003/03/03 04:37:37 tgl Exp $
14+
* $Header: /cvsroot/pgsql/src/backend/commands/cluster.c,v 1.107 2003/03/20 03:34:55 momjian Exp $
1515
*
1616
*-------------------------------------------------------------------------
1717
*/
@@ -405,7 +405,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid)
405405
* namespace from the old, or we will have problems with the TEMP
406406
* status of temp tables.
407407
*/
408-
snprintf(NewHeapName, NAMEDATALEN, "pg_temp_%u", tableOid);
408+
snprintf(NewHeapName, sizeof(NewHeapName), "pg_temp_%u", tableOid);
409409

410410
OIDNewHeap = make_new_heap(tableOid, NewHeapName);
411411
/*
@@ -625,7 +625,8 @@ rebuild_indexes(Oid OIDOldHeap, List *indexes)
625625
Relation pg_index;
626626

627627
/* Create the new index under a temporary name */
628-
snprintf(newIndexName, NAMEDATALEN, "pg_temp_%u", attrs->indexOID);
628+
snprintf(newIndexName, sizeof(newIndexName),
629+
"pg_temp_%u", attrs->indexOID);
629630

630631
/*
631632
* The new index will have primary and constraint status set to

‎src/backend/commands/sequence.c

Lines changed: 13 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/commands/sequence.c,v 1.91 2003/02/13 05:25:24 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/sequence.c,v 1.92 2003/03/20 03:34:55 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -406,7 +406,7 @@ nextval(PG_FUNCTION_ARGS)
406406
{
407407
char buf[100];
408408

409-
snprintf(buf, 100, INT64_FORMAT, maxv);
409+
snprintf(buf, sizeof(buf), INT64_FORMAT, maxv);
410410
elog(ERROR, "%s.nextval: reached MAXVALUE (%s)",
411411
sequence->relname, buf);
412412
}
@@ -427,7 +427,7 @@ nextval(PG_FUNCTION_ARGS)
427427
{
428428
char buf[100];
429429

430-
snprintf(buf, 100, INT64_FORMAT, minv);
430+
snprintf(buf, sizeof(buf), INT64_FORMAT, minv);
431431
elog(ERROR, "%s.nextval: reached MINVALUE (%s)",
432432
sequence->relname, buf);
433433
}
@@ -569,9 +569,9 @@ do_setval(RangeVar *sequence, int64 next, bool iscalled)
569569
bufm[100],
570570
bufx[100];
571571

572-
snprintf(bufv, 100, INT64_FORMAT, next);
573-
snprintf(bufm, 100, INT64_FORMAT, seq->min_value);
574-
snprintf(bufx, 100, INT64_FORMAT, seq->max_value);
572+
snprintf(bufv, sizeof(bufv), INT64_FORMAT, next);
573+
snprintf(bufm, sizeof(bufm), INT64_FORMAT, seq->min_value);
574+
snprintf(bufx, sizeof(bufx), INT64_FORMAT, seq->max_value);
575575
elog(ERROR, "%s.setval: value %s is out of bounds (%s,%s)",
576576
sequence->relname, bufv, bufm, bufx);
577577
}
@@ -861,8 +861,8 @@ init_params(CreateSeqStmt *seq, Form_pg_sequence new)
861861
char bufm[100],
862862
bufx[100];
863863

864-
snprintf(bufm, 100, INT64_FORMAT, new->min_value);
865-
snprintf(bufx, 100, INT64_FORMAT, new->max_value);
864+
snprintf(bufm, sizeof(bufm), INT64_FORMAT, new->min_value);
865+
snprintf(bufx, sizeof(bufx), INT64_FORMAT, new->max_value);
866866
elog(ERROR, "DefineSequence: MINVALUE (%s) must be less than MAXVALUE (%s)",
867867
bufm, bufx);
868868
}
@@ -882,8 +882,8 @@ init_params(CreateSeqStmt *seq, Form_pg_sequence new)
882882
char bufs[100],
883883
bufm[100];
884884

885-
snprintf(bufs, 100, INT64_FORMAT, new->last_value);
886-
snprintf(bufm, 100, INT64_FORMAT, new->min_value);
885+
snprintf(bufs, sizeof(bufs), INT64_FORMAT, new->last_value);
886+
snprintf(bufm, sizeof(bufm), INT64_FORMAT, new->min_value);
887887
elog(ERROR, "DefineSequence: START value (%s) can't be less than MINVALUE (%s)",
888888
bufs, bufm);
889889
}
@@ -892,8 +892,8 @@ init_params(CreateSeqStmt *seq, Form_pg_sequence new)
892892
char bufs[100],
893893
bufm[100];
894894

895-
snprintf(bufs, 100, INT64_FORMAT, new->last_value);
896-
snprintf(bufm, 100, INT64_FORMAT, new->max_value);
895+
snprintf(bufs, sizeof(bufs), INT64_FORMAT, new->last_value);
896+
snprintf(bufm, sizeof(bufm), INT64_FORMAT, new->max_value);
897897
elog(ERROR, "DefineSequence: START value (%s) can't be greater than MAXVALUE (%s)",
898898
bufs, bufm);
899899
}
@@ -904,7 +904,7 @@ init_params(CreateSeqStmt *seq, Form_pg_sequence new)
904904
{
905905
char buf[100];
906906

907-
snprintf(buf, 100, INT64_FORMAT, new->cache_value);
907+
snprintf(buf, sizeof(buf), INT64_FORMAT, new->cache_value);
908908
elog(ERROR, "DefineSequence: CACHE (%s) can't be <= 0",
909909
buf);
910910
}

‎src/backend/commands/tablecmds.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v 1.67 2003/02/13 05:19:59 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v 1.68 2003/03/20 03:34:55 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -3875,8 +3875,8 @@ AlterTableCreateToastTable(Oid relOid, bool silent)
38753875
/*
38763876
* Create the toast table and its index
38773877
*/
3878-
snprintf(toast_relname, NAMEDATALEN, "pg_toast_%u", relOid);
3879-
snprintf(toast_idxname, NAMEDATALEN, "pg_toast_%u_index", relOid);
3878+
snprintf(toast_relname, sizeof(toast_relname), "pg_toast_%u", relOid);
3879+
snprintf(toast_idxname, sizeof(toast_idxname), "pg_toast_%u_index", relOid);
38803880

38813881
/* this is pretty painful... need a tuple descriptor */
38823882
tupdesc = CreateTemplateTupleDesc(3, false);

‎src/backend/executor/execMain.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -26,7 +26,7 @@
2626
*
2727
*
2828
* IDENTIFICATION
29-
* $Header: /cvsroot/pgsql/src/backend/executor/execMain.c,v 1.202 2003/03/11 19:40:22 tgl Exp $
29+
* $Header: /cvsroot/pgsql/src/backend/executor/execMain.c,v 1.203 2003/03/20 03:34:55 momjian Exp $
3030
*
3131
*-------------------------------------------------------------------------
3232
*/
@@ -603,7 +603,7 @@ InitPlan(QueryDesc *queryDesc)
603603
erm = (execRowMark *) palloc(sizeof(execRowMark));
604604
erm->relation = relation;
605605
erm->rti = rti;
606-
snprintf(erm->resname, 32, "ctid%u", rti);
606+
snprintf(erm->resname, sizeof(erm->resname), "ctid%u", rti);
607607
estate->es_rowMark = lappend(estate->es_rowMark, erm);
608608
}
609609
}

0 commit comments

Comments
 (0)