On 20.12.2011 19:59, Tomas Vondra wrote:
> On 20.12.2011 11:20, Magnus Hagander wrote:
>> 2011/12/20 Tomas Vondra <t...@fuzzy.cz>:
>>>
>>> I haven't updated the docs yet - let's see if the patch is acceptable at
>>> all first.
>>
>> Again, without having reviewed the code, this looks like a feature
>> we'd want, so please add some docs, and then submit it for the next
>> commitfest!
>
> I've added the docs (see the attachment) and rebased to current head.
>
> Tomas
Fixed a failing regression test (check of pg_stat_database structure).
Tomas
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index a12a9a2..3635c3f 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -691,6 +691,26 @@ postgres: <replaceable>user</> <replaceable>database</>
<replaceable>host</> <re
</row>
<row>
+
<entry><literal><function>pg_stat_get_db_temp_files</function>(<type>oid</type>)</literal></entry>
+ <entry><type>bigint</type></entry>
+ <entry>
+ Nuber of temporary files written for the database. All temporary files
are
+ counted, regardless of why the temporary file was created (sorting or
hash
+ join) or file size (log_temp_file does not affect this).
+ </entry>
+ </row>
+
+ <row>
+
<entry><literal><function>pg_stat_get_db_temp_bytes</function>(<type>oid</type>)</literal></entry>
+ <entry><type>bigint</type></entry>
+ <entry>
+ Amount of data written to temporary files for the database. All
temporary
+ files are counted, regardless of why the temporary file was created
(sorting
+ or hash join) or file size (log_temp_file does not affect this).
+ </entry>
+ </row>
+
+ <row>
<entry><literal><function>pg_stat_get_numscans</function>(<type>oid</type>)</literal></entry>
<entry><type>bigint</type></entry>
<entry>
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index 50ba20c..3a0dca3 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -574,6 +574,8 @@ CREATE VIEW pg_stat_database AS
pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted,
pg_stat_get_db_conflict_all(D.oid) AS conflicts,
+ pg_stat_get_db_temp_files(D.oid) AS temp_files,
+ pg_stat_get_db_temp_bytes(D.oid) AS temp_bytes,
pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset
FROM pg_database D;
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 323d42b..6a2ff1d 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -286,7 +286,7 @@ static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg,
int len);
static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
static void pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int
len);
-
+static void pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len);
/* ------------------------------------------------------------
* Public functions called from postmaster follow
@@ -1339,6 +1339,29 @@ pgstat_report_recovery_conflict(int reason)
pgstat_send(&msg, sizeof(msg));
}
+
+/* --------
+ * pgstat_report_tempfile() -
+ *
+ * Tell the collector about a temporary file.
+ * --------
+ */
+void
+pgstat_report_tempfile(size_t filesize)
+{
+ PgStat_MsgTempFile msg;
+
+ if (pgStatSock == PGINVALID_SOCKET || !pgstat_track_counts)
+ return;
+
+ pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_TEMPFILE);
+ msg.m_databaseid = MyDatabaseId;
+ msg.m_filesize = filesize;
+ pgstat_send(&msg, sizeof(msg));
+}
+
+;
+
/* ----------
* pgstat_ping() -
*
@@ -3185,6 +3208,10 @@ PgstatCollectorMain(int argc, char *argv[])
pgstat_recv_recoveryconflict((PgStat_MsgRecoveryConflict *) &msg, len);
break;
+ case PGSTAT_MTYPE_TEMPFILE:
+
pgstat_recv_tempfile((PgStat_MsgTempFile *) &msg, len);
+ break;
+
default:
break;
}
@@ -3266,6 +3293,8 @@ pgstat_get_db_entry(Oid databaseid, bool create)
result->n_conflict_snapshot = 0;
result->n_conflict_bufferpin = 0;
result->n_conflict_startup_deadlock = 0;
+ result->n_temp_files = 0;
+ result->n_temp_bytes = 0;
result->stat_reset_timestamp = GetCurrentTimestamp();
@@ -4177,6 +4206,8 @@ pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int
len)
dbentry->n_tuples_updated = 0;
dbentry->n_tuples_deleted = 0;
dbentry->last_autovac_time = 0;
+ dbentry->n_temp_bytes = 0;
+ dbentry->n_temp_files = 0;
dbentry->stat_reset_timestamp = GetCurrentTimestamp();
@@ -4403,6 +4434,24 @@ pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict
*msg, int len)
}
/* ----------
+ * pgstat_recv_tempfile() -
+ *
+ * Process as PGSTAT_MTYPE_TEMPFILE message.
+ * ----------
+ */
+static void
+pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len)
+{
+ PgStat_StatDBEntry *dbentry;
+
+ dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
+
+ dbentry->n_temp_bytes += msg->m_filesize;
+ dbentry->n_temp_files += 1;
+
+}
+
+/* ----------
* pgstat_recv_funcstat() -
*
* Count what the backend has done.
diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c
index 43bc43a..7a47ae7 100644
--- a/src/backend/storage/file/fd.c
+++ b/src/backend/storage/file/fd.c
@@ -1088,6 +1088,10 @@ FileClose(File file)
*/
if (vfdP->fdstate & FD_TEMPORARY)
{
+
+ struct stat filestats;
+ int stat_errno;
+
/*
* If we get an error, as could happen within the ereport/elog
calls,
* we'll come right back here during transaction abort. Reset
the
@@ -1101,23 +1105,23 @@ FileClose(File file)
temporary_files_size -= vfdP->fileSize;
vfdP->fileSize = 0;
- if (log_temp_files >= 0)
- {
- struct stat filestats;
- int stat_errno;
+ /* first try the stat() */
+ if (stat(vfdP->fileName, &filestats))
+ stat_errno = errno;
+ else
+ stat_errno = 0;
- /* first try the stat() */
- if (stat(vfdP->fileName, &filestats))
- stat_errno = errno;
- else
- stat_errno = 0;
+ /* in any case do the unlink */
+ if (unlink(vfdP->fileName))
+ elog(LOG, "could not unlink file \"%s\": %m",
vfdP->fileName);
- /* in any case do the unlink */
- if (unlink(vfdP->fileName))
- elog(LOG, "could not unlink file \"%s\": %m",
vfdP->fileName);
+ /* and last report the stat results */
+ if (stat_errno == 0)
+ {
- /* and last report the stat results */
- if (stat_errno == 0)
+ pgstat_report_tempfile(filestats.st_size);
+
+ if (log_temp_files >= 0)
{
if ((filestats.st_size / 1024) >=
log_temp_files)
ereport(LOG,
@@ -1131,12 +1135,6 @@ FileClose(File file)
elog(LOG, "could not stat file \"%s\": %m",
vfdP->fileName);
}
}
- else
- {
- /* easy case, just do the unlink */
- if (unlink(vfdP->fileName))
- elog(LOG, "could not unlink file \"%s\": %m",
vfdP->fileName);
- }
}
/* Unregister it from the resource owner */
diff --git a/src/backend/utils/adt/pgstatfuncs.c
b/src/backend/utils/adt/pgstatfuncs.c
index b4986d8..2804af7 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -79,6 +79,8 @@ extern Datum
pg_stat_get_db_conflict_bufferpin(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_db_conflict_startup_deadlock(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_db_conflict_all(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_db_stat_reset_time(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_db_temp_files(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
@@ -1180,6 +1182,37 @@ pg_stat_get_db_stat_reset_time(PG_FUNCTION_ARGS)
}
Datum
+pg_stat_get_db_temp_files(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatDBEntry *dbentry;
+
+ if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+ result = 0;
+ else
+ result = dbentry->n_temp_files;
+
+ PG_RETURN_INT64(result);
+}
+
+
+Datum
+pg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatDBEntry *dbentry;
+
+ if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+ result = 0;
+ else
+ result = dbentry->n_temp_bytes;
+
+ PG_RETURN_INT64(result);
+}
+
+Datum
pg_stat_get_db_conflict_tablespace(PG_FUNCTION_ARGS)
{
Oid dbid = PG_GETARG_OID(0);
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 355c61a..df68a8d 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2633,6 +2633,10 @@ DATA(insert OID = 3070 ( pg_stat_get_db_conflict_all
PGNSP PGUID 12 1 0 0 0 f f
DESCR("statistics: recovery conflicts in database");
DATA(insert OID = 3074 ( pg_stat_get_db_stat_reset_time PGNSP PGUID 12 1 0 0
0 f f f t f s 1 0 1184 "26" _null_ _null_ _null_ _null_
pg_stat_get_db_stat_reset_time _null_ _null_ _null_ ));
DESCR("statistics: last reset for a database");
+DATA(insert OID = 3079 ( pg_stat_get_db_temp_files PGNSP PGUID 12 1 0 0 0 f f
f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_temp_files
_null_ _null_ _null_ ));
+DESCR("statistics: number of temporary files written");
+DATA(insert OID = 3080 ( pg_stat_get_db_temp_bytes PGNSP PGUID 12 1 0 0 0 f f
f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_temp_bytes
_null_ _null_ _null_ ));
+DESCR("statistics: number of bytes in temporary files written");
DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12
1 0 0 0 f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_
pg_stat_get_bgwriter_timed_checkpoints _null_ _null_ _null_ ));
DESCR("statistics: number of timed checkpoints started by the bgwriter");
DATA(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP
PGUID 12 1 0 0 0 f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_
pg_stat_get_bgwriter_requested_checkpoints _null_ _null_ _null_ ));
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index b8c6d82..df27d80 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -47,7 +47,8 @@ typedef enum StatMsgType
PGSTAT_MTYPE_BGWRITER,
PGSTAT_MTYPE_FUNCSTAT,
PGSTAT_MTYPE_FUNCPURGE,
- PGSTAT_MTYPE_RECOVERYCONFLICT
+ PGSTAT_MTYPE_RECOVERYCONFLICT,
+ PGSTAT_MTYPE_TEMPFILE
} StatMsgType;
/* ----------
@@ -377,6 +378,18 @@ typedef struct PgStat_MsgRecoveryConflict
} PgStat_MsgRecoveryConflict;
/* ----------
+ * PgStat_MsgTempFile Sent by the backend upon creating a temp file
+ * ----------
+ */
+typedef struct PgStat_MsgTempFile
+{
+ PgStat_MsgHdr m_hdr;
+
+ Oid m_databaseid;
+ size_t m_filesize;
+} PgStat_MsgTempFile;
+
+/* ----------
* PgStat_FunctionCounts The actual per-function counts kept by a backend
*
* This struct should contain only actual event counters, because we memcmp
@@ -507,9 +520,11 @@ typedef struct PgStat_StatDBEntry
PgStat_Counter n_conflict_snapshot;
PgStat_Counter n_conflict_bufferpin;
PgStat_Counter n_conflict_startup_deadlock;
+ PgStat_Counter n_temp_files;
+ PgStat_Counter n_temp_bytes;
+
TimestampTz stat_reset_timestamp;
-
/*
* tables and functions must be last in the struct, because we don't
write
* the pointers out to the stats file.
@@ -716,6 +731,7 @@ extern void pgstat_initialize(void);
extern void pgstat_bestart(void);
extern void pgstat_report_activity(const char *cmd_str);
+extern void pgstat_report_tempfile(size_t filesize);
extern void pgstat_report_appname(const char *appname);
extern void pgstat_report_xact_timestamp(TimestampTz tstamp);
extern void pgstat_report_waiting(bool waiting);
diff --git a/src/test/regress/expected/rules.out
b/src/test/regress/expected/rules.out
index 454e1f9..c558066 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1296,7 +1296,7 @@ SELECT viewname, definition FROM pg_views WHERE
schemaname <> 'information_schem
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,
pg_stat_get_vacuum_count(c.oid) AS vacuum_count,
pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count,
pg_stat_get_analyze_count(c.oid) AS analyze_count,
pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count 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_fsync_backend() AS buffers_backend_fsync,
pg_stat_get_buf_alloc() AS buffers_alloc,
pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
- pg_stat_database | SELECT d.oid AS datid, d.datname,
pg_stat_get_db_numbackends(d.oid) AS numbackends,
pg_stat_get_db_xact_commit(d.oid) AS xact_commit,
pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback,
(pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS
blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit,
pg_stat_get_db_tuples_returned(d.oid) AS tup_returned,
pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched,
pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted,
pg_stat_get_db_tuples_updated(d.oid) AS tup_updated,
pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted,
pg_stat_get_db_conflict_all(d.oid) AS conflicts,
pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d;
+ pg_stat_database | SELECT d.oid AS datid, d.datname,
pg_stat_get_db_numbackends(d.oid) AS numbackends,
pg_stat_get_db_xact_commit(d.oid) AS xact_commit,
pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback,
(pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS
blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit,
pg_stat_get_db_tuples_returned(d.oid) AS tup_returned,
pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched,
pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted,
pg_stat_get_db_tuples_updated(d.oid) AS tup_updated,
pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted,
pg_stat_get_db_conflict_all(d.oid) AS conflicts,
pg_stat_get_db_temp_files(d.oid) AS temp_files,
pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes,
pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d;
pg_stat_database_conflicts | SELECT d.oid AS datid, d.datname,
pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace,
pg_stat_get_db_conflict_lock(d.oid) AS confl_lock,
pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot,
pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin,
pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock FROM
pg_database d;
pg_stat_replication | SELECT s.procpid, s.usesysid, u.rolname AS
usename, s.application_name, s.client_addr, s.client_hostname, s.client_port,
s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location,
w.replay_location, w.sync_priority, w.sync_state FROM
pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid,
application_name, current_query, waiting, xact_start, query_start,
backend_start, client_addr, client_hostname, client_port), pg_authid u,
pg_stat_get_wal_senders() w(procpid, state, sent_location, write_location,
flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid
= u.oid) AND (s.procpid = w.procpid));
pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid,
pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname,
pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname,
pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read,
pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE
((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name,
'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~
'^pg_toast'::text));
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
