Index: doc/src/sgml/func.sgml =================================================================== RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/func.sgml,v retrieving revision 1.141 diff -c -r1.141 func.sgml *** doc/src/sgml/func.sgml 19 Feb 2003 03:12:22 -0000 1.141 --- doc/src/sgml/func.sgml 19 Feb 2003 05:21:08 -0000 *************** *** 5309,5327 **** Some examples: SELECT CURRENT_TIME; ! 14:39:53.662522-05 SELECT CURRENT_DATE; ! 2001-12-23 SELECT CURRENT_TIMESTAMP; ! 2001-12-23 14:39:53.662522-05 SELECT CURRENT_TIMESTAMP(2); ! 2001-12-23 14:39:53.66-05 SELECT LOCALTIMESTAMP; ! 2001-12-23 14:39:53.662522 --- 5309,5327 ---- Some examples: SELECT CURRENT_TIME; ! Result: 14:39:53.662522-05 SELECT CURRENT_DATE; ! Result: 2001-12-23 SELECT CURRENT_TIMESTAMP; ! Result: 2001-12-23 14:39:53.662522-05 SELECT CURRENT_TIMESTAMP(2); ! Result: 2001-12-23 14:39:53.66-05 SELECT LOCALTIMESTAMP; ! Result: 2001-12-23 14:39:53.662522 *************** *** 5332,5351 **** ! There is also timeofday(), which for historical ! reasons returns a text string rather than a timestamp value: SELECT timeofday(); ! Sat Feb 17 19:07:32.000126 2001 EST It is important to realize that ! CURRENT_TIMESTAMP and related functions return ! the start time of the current transaction; their values do not ! change during the transaction. timeofday() ! returns the wall clock time and does advance during transactions. --- 5332,5356 ---- ! There is also timeofday(), which for ! historical reasons returns a text string rather than ! a timestamp value: SELECT timeofday(); ! Result: Sat Feb 17 19:07:32.000126 2001 EST It is important to realize that ! CURRENT_TIMESTAMP and related functions ! return the start time of the current transaction; their values do ! not change during the transaction. This is considered a feature: ! the intent is to allow a single transaction to have a consistent ! notion of the current time, so that multiple ! modifications within the same transaction bear the same ! timestamp. timeofday() returns the wall clock ! time and does advance during transactions. Index: doc/src/sgml/monitoring.sgml =================================================================== RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/monitoring.sgml,v retrieving revision 1.15 diff -c -r1.15 monitoring.sgml *** doc/src/sgml/monitoring.sgml 11 Nov 2002 20:14:03 -0000 1.15 --- doc/src/sgml/monitoring.sgml 19 Feb 2003 00:41:35 -0000 *************** *** 146,157 **** Since the variables STATS_COMMAND_STRING, ! STATS_BLOCK_LEVEL, ! and STATS_ROW_LEVEL ! default to false, no statistics are actually collected ! in the default configuration. You must turn one or more of them on ! before you will get useful results from the statistical display ! functions. --- 146,158 ---- Since the variables STATS_COMMAND_STRING, ! STATS_BLOCK_LEVEL, and ! STATS_ROW_LEVEL default to false, ! very few statistics are collected in the default ! configuration. Enabling one or more of these configuration ! variables will significantly enhance the amount of useful data ! produced by the statistics collector, at the expense of ! additional run-time overhead. *************** *** 515,526 **** ! pg_stat_get_backend_activity(integer) text Current query of backend process (NULL if caller is not superuser) pg_stat_reset() --- 516,537 ---- ! pg_stat_get_backend_qry(integer) text Current query of backend process (NULL if caller is not superuser) + + + pg_stat_get_backend_qry_start(integer) + text + + The time at which this backend's currently executing query was + initiated. + + + pg_stat_reset() Index: src/backend/postmaster/pgstat.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/postmaster/pgstat.c,v retrieving revision 1.31 diff -c -r1.31 pgstat.c *** src/backend/postmaster/pgstat.c 24 Oct 2002 23:19:13 -0000 1.31 --- src/backend/postmaster/pgstat.c 19 Feb 2003 01:59:05 -0000 *************** *** 1757,1762 **** --- 1757,1763 ---- beentry->databaseid = msg->m_databaseid; beentry->procpid = msg->m_procpid; beentry->userid = msg->m_userid; + beentry->activity_start = NULL; MemSet(beentry->activity, 0, PGSTAT_ACTIVITY_SIZE); /* *************** *** 2460,2465 **** --- 2461,2468 ---- static void pgstat_recv_activity(PgStat_MsgActivity *msg, int len) { + struct timeval tv; + /* * Here we check explicitly for 0 return, since we don't want to * mangle the activity of an active backend by a delayed packed from a *************** *** 2470,2475 **** --- 2473,2481 ---- strncpy(pgStatBeTable[msg->m_hdr.m_backendid - 1].activity, msg->m_what, PGSTAT_ACTIVITY_SIZE); + + gettimeofday(&tv, NULL); + pgStatBeTable[msg->m_hdr.m_backendid - 1].activity_start = &tv; } Index: src/backend/utils/adt/pgstatfuncs.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/utils/adt/pgstatfuncs.c,v retrieving revision 1.9 diff -c -r1.9 pgstatfuncs.c *** src/backend/utils/adt/pgstatfuncs.c 4 Dec 2002 05:18:34 -0000 1.9 --- src/backend/utils/adt/pgstatfuncs.c 19 Feb 2003 05:15:30 -0000 *************** *** 24,30 **** 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_db_numbackends(PG_FUNCTION_ARGS); extern Datum pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS); --- 24,31 ---- 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_qry(PG_FUNCTION_ARGS); ! extern Datum pg_stat_get_backend_qry_start(PG_FUNCTION_ARGS); extern Datum pg_stat_get_db_numbackends(PG_FUNCTION_ARGS); extern Datum pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS); *************** *** 221,227 **** /* * Built-in function for resetting the counters - * */ Datum pg_stat_reset(PG_FUNCTION_ARGS) --- 222,227 ---- *************** *** 277,283 **** Datum ! pg_stat_get_backend_activity(PG_FUNCTION_ARGS) { PgStat_StatBeEntry *beentry; int32 beid; --- 277,283 ---- Datum ! pg_stat_get_backend_qry(PG_FUNCTION_ARGS) { PgStat_StatBeEntry *beentry; int32 beid; *************** *** 298,303 **** --- 298,334 ---- memcpy(VARDATA(result), beentry->activity, len); PG_RETURN_TEXT_P(result); + } + + + Datum + pg_stat_get_backend_qry_start(PG_FUNCTION_ARGS) + { + PgStat_StatBeEntry *beentry; + int32 beid; + char *output; + + beid = PG_GETARG_INT32(0); + + if (!superuser()) + PG_RETURN_NULL(); + + if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) + PG_RETURN_NULL(); + + /* + * No time recorded for start of current query -- this is the case + * if the user hasn't enabled query-level stats collection. + */ + if (beentry->activity_start == NULL) + PG_RETURN_NULL(); + + output = ctime((time_t *) &beentry->activity_start->tv_sec); + + return DirectFunctionCall3(timestamp_in, + CStringGetDatum(output), + ObjectIdGetDatum(InvalidOid), + Int32GetDatum(-1)); } Index: src/bin/initdb/initdb.sh =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/bin/initdb/initdb.sh,v retrieving revision 1.183 diff -c -r1.183 initdb.sh *** src/bin/initdb/initdb.sh 19 Feb 2003 04:02:54 -0000 1.183 --- src/bin/initdb/initdb.sh 19 Feb 2003 05:21:08 -0000 *************** *** 953,959 **** pg_stat_get_backend_pid(S.backendid) AS procpid, \ pg_stat_get_backend_userid(S.backendid) AS usesysid, \ U.usename AS usename, \ ! pg_stat_get_backend_activity(S.backendid) AS current_query \ FROM pg_database D, \ (SELECT pg_stat_get_backend_idset() AS backendid) AS S, \ pg_shadow U \ --- 953,960 ---- pg_stat_get_backend_pid(S.backendid) AS procpid, \ pg_stat_get_backend_userid(S.backendid) AS usesysid, \ U.usename AS usename, \ ! pg_stat_get_backend_qry(S.backendid) AS current_query, \ ! pg_stat_get_backend_qry_start(S.backendid) AS query_start \ FROM pg_database D, \ (SELECT pg_stat_get_backend_idset() AS backendid) AS S, \ pg_shadow U \ Index: src/include/pgstat.h =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/include/pgstat.h,v retrieving revision 1.12 diff -c -r1.12 pgstat.h *** src/include/pgstat.h 9 Jan 2003 18:00:24 -0000 1.12 --- src/include/pgstat.h 19 Feb 2003 01:02:13 -0000 *************** *** 111,116 **** --- 111,117 ---- Oid userid; int procpid; char activity[PGSTAT_ACTIVITY_SIZE]; + struct timeval *activity_start; } PgStat_StatBeEntry; Index: src/include/catalog/catversion.h =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/include/catalog/catversion.h,v retrieving revision 1.177 diff -c -r1.177 catversion.h *** src/include/catalog/catversion.h 16 Feb 2003 02:30:39 -0000 1.177 --- src/include/catalog/catversion.h 19 Feb 2003 04:24:11 -0000 *************** *** 53,58 **** */ /* yyyymmddN */ ! #define CATALOG_VERSION_NO 200302151 #endif --- 53,58 ---- */ /* yyyymmddN */ ! #define CATALOG_VERSION_NO 200302181 #endif Index: src/include/catalog/pg_proc.h =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/include/catalog/pg_proc.h,v retrieving revision 1.283 diff -c -r1.283 pg_proc.h *** src/include/catalog/pg_proc.h 13 Feb 2003 05:24:02 -0000 1.283 --- src/include/catalog/pg_proc.h 19 Feb 2003 01:07:50 -0000 *************** *** 2737,2744 **** DESCR("Statistics: Database ID of backend"); DATA(insert OID = 1939 ( pg_stat_get_backend_userid PGNSP PGUID 12 f f t f s 1 23 "23" pg_stat_get_backend_userid - _null_ )); DESCR("Statistics: User ID of backend"); ! DATA(insert OID = 1940 ( pg_stat_get_backend_activity PGNSP PGUID 12 f f t f s 1 25 "23" pg_stat_get_backend_activity - _null_ )); DESCR("Statistics: Current query of backend"); DATA(insert OID = 1941 ( pg_stat_get_db_numbackends PGNSP PGUID 12 f f t f s 1 23 "26" pg_stat_get_db_numbackends - _null_ )); DESCR("Statistics: Number of backends in database"); DATA(insert OID = 1942 ( pg_stat_get_db_xact_commit PGNSP PGUID 12 f f t f s 1 20 "26" pg_stat_get_db_xact_commit - _null_ )); --- 2737,2746 ---- DESCR("Statistics: Database ID of backend"); DATA(insert OID = 1939 ( pg_stat_get_backend_userid PGNSP PGUID 12 f f t f s 1 23 "23" pg_stat_get_backend_userid - _null_ )); DESCR("Statistics: User ID of backend"); ! DATA(insert OID = 1940 ( pg_stat_get_backend_qry PGNSP PGUID 12 f f t f s 1 25 "23" pg_stat_get_backend_qry - _null_ )); DESCR("Statistics: Current query of backend"); + DATA(insert OID = 2094 ( pg_stat_get_backend_qry_start PGNSP PGUID 12 f f t f s 1 1114 "23" pg_stat_get_backend_qry_start - _null_)); + DESCR("Statistics: Start time for current query of backend"); DATA(insert OID = 1941 ( pg_stat_get_db_numbackends PGNSP PGUID 12 f f t f s 1 23 "26" pg_stat_get_db_numbackends - _null_ )); DESCR("Statistics: Number of backends in database"); DATA(insert OID = 1942 ( pg_stat_get_db_xact_commit PGNSP PGUID 12 f f t f s 1 20 "26" pg_stat_get_db_xact_commit - _null_ )); *************** *** 2748,2754 **** DATA(insert OID = 1944 ( pg_stat_get_db_blocks_fetched PGNSP PGUID 12 f f t f s 1 20 "26" pg_stat_get_db_blocks_fetched - _null_ )); DESCR("Statistics: Blocks fetched for database"); DATA(insert OID = 1945 ( pg_stat_get_db_blocks_hit PGNSP PGUID 12 f f t f s 1 20 "26" pg_stat_get_db_blocks_hit - _null_ )); ! DESCR("Statistics: Block found in cache for database"); DATA(insert OID = 1946 ( encode PGNSP PGUID 12 f f t f i 2 25 "17 25" binary_encode - _null_ )); DESCR("Convert bytea value into some ascii-only text string"); --- 2750,2756 ---- DATA(insert OID = 1944 ( pg_stat_get_db_blocks_fetched PGNSP PGUID 12 f f t f s 1 20 "26" pg_stat_get_db_blocks_fetched - _null_ )); DESCR("Statistics: Blocks fetched for database"); DATA(insert OID = 1945 ( pg_stat_get_db_blocks_hit PGNSP PGUID 12 f f t f s 1 20 "26" pg_stat_get_db_blocks_hit - _null_ )); ! DESCR("Statistics: Blocks found in cache for database"); DATA(insert OID = 1946 ( encode PGNSP PGUID 12 f f t f i 2 25 "17 25" binary_encode - _null_ )); DESCR("Convert bytea value into some ascii-only text string");