On 29.10.2012 04:58, Satoshi Nagayasu wrote:
> 2012/10/24 1:12, Alvaro Herrera wrote:
>> Satoshi Nagayasu escribi�:
>>
>>> With this patch, walwriter process and each backend process
>>> would sum up dirty writes, and send it to the stat collector.
>>> So, the value could be saved in the stat file, and could be
>>> kept on restarting.
>>>
>>> The statistics could be retreive with using
>>> pg_stat_get_xlog_dirty_writes() function, and could be reset
>>> with calling pg_stat_reset_shared('walwriter').
>>>
>>> Now, I have one concern.
>>>
>>> The reset time could be captured in globalStats.stat_reset_timestamp,
>>> but this value is the same with the bgwriter one.
>>>
>>> So, once pg_stat_reset_shared('walwriter') is called,
>>> stats_reset column in pg_stat_bgwriter does represent
>>> the reset time for walwriter, not for bgwriter.
>>>
>>> How should we handle this? Should we split this value?
>>> And should we have new system view for walwriter?
>>
>> I think the answer to the two last questions is yes. It doesn't seem to
>> make sense, to me, to have a single reset timings for what are
>> effectively two separate things.
>>
>> Please submit an updated patch to next CF. I'm marking this one
>> returned with feedback. Thanks.
>>
>
> I attached the latest one, which splits the reset_time
> for bgwriter and walwriter, and provides new system view,
> called pg_stat_walwriter, to show the dirty write counter
> and the reset time.
I've done a quick review of the v4 patch:
1) applies fine on HEAD, compiles fine
2) "make installcheck" fails because of a difference in the 'rules'
test suite (there's a new view "pg_stat_walwriter" - see the
attached patch for a fixed version or expected/rules.out)
3) I do agree with Alvaro that using the same struct for two separate
components (bgwriter and walwriter) seems a bit awkward. For example
you need to have two separate stat_reset fields, the reset code
becomes much more verbose (because you need to list individual
fields) etc.
So I'd vote to either split this into two structures or keeping it
as a single structure (although with two views on top of it).
4) Are there any other fields that might be interesting? Right now
there's just "dirty_writes" but I guess there are other values. E.g.
how much data was actually written etc.?
Tomas
diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index e376452..334ce4c 100644
--- a/contrib/pgbench/pgbench.c
+++ b/contrib/pgbench/pgbench.c
@@ -135,6 +135,11 @@ int unlogged_tables = 0;
double sample_rate = 0.0;
/*
+ * logging steps (seconds between log messages)
+ */
+int log_step_seconds = 5;
+
+/*
* tablespace selection
*/
char *tablespace = NULL;
@@ -1362,6 +1367,11 @@ init(bool is_no_vacuum)
char sql[256];
int i;
+ /* used to track elapsed time and estimate of the remaining time */
+ instr_time start, diff;
+ double elapsed_sec, remaining_sec;
+ int log_interval = 1;
+
if ((con = doConnect()) == NULL)
exit(1);
@@ -1430,6 +1440,8 @@ init(bool is_no_vacuum)
}
PQclear(res);
+ INSTR_TIME_SET_CURRENT(start);
+
for (i = 0; i < naccounts * scale; i++)
{
int j = i + 1;
@@ -1441,10 +1453,27 @@ init(bool is_no_vacuum)
exit(1);
}
- if (j % 100000 == 0)
- fprintf(stderr, "%d of %d tuples (%d%%) done.\n",
- j, naccounts * scale,
- (int) (((int64) j * 100) / (naccounts *
scale)));
+ /* let's not call the timing for each row, but only each 100
rows */
+ if (j % 100 == 0 || j == scale * naccounts)
+ {
+ INSTR_TIME_SET_CURRENT(diff);
+ INSTR_TIME_SUBTRACT(diff, start);
+
+ elapsed_sec = INSTR_TIME_GET_DOUBLE(diff);
+ remaining_sec = (scale * naccounts - j) * elapsed_sec /
j;
+
+ /* have we reached the next interval? */
+ if (elapsed_sec >= log_interval * log_step_seconds) {
+
+ fprintf(stderr, "%d of %d tuples (%d%%) done
(elapsed %.2f s, remaining %.2f s).\n",
+ j, naccounts * scale,
+ (int) (((int64) j * 100) /
(naccounts * scale)), elapsed_sec, remaining_sec);
+
+ /* skip to the next interval */
+ log_interval =
(int)ceil(elapsed_sec/log_step_seconds);
+ }
+ }
+
}
if (PQputline(con, "\\.\n"))
{
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index b7df8ce..bf9acc5 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1919,6 +1919,13 @@ include 'filename'
results in most cases.
</para>
+ <para>
+ When you see pg_stat_walwriter.dirty_write, which means number
+ of buffer flushing at buffer full, is continuously increasing
+ in your running server, you may need to enlarge this buffer
+ size.
+ </para>
+
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 39ccfbb..3117f91 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -278,6 +278,14 @@ postgres: <replaceable>user</> <replaceable>database</>
<replaceable>host</> <re
</row>
<row>
+
<entry><structname>pg_stat_walwriter</><indexterm><primary>pg_stat_walwriter</primary></indexterm></entry>
+ <entry>One row only, showing statistics about the wal writer
+ process's activity. See <xref linkend="pg-stat-walwriter-view">
+ for details.
+ </entry>
+ </row>
+
+ <row>
<entry><structname>pg_stat_database</><indexterm><primary>pg_stat_database</primary></indexterm></entry>
<entry>One row per database, showing database-wide statistics. See
<xref linkend="pg-stat-database-view"> for details.
@@ -735,6 +743,39 @@ postgres: <replaceable>user</> <replaceable>database</>
<replaceable>host</> <re
single row, containing global data for the cluster.
</para>
+ <table id="pg-stat-walwriter-view" xreflabel="pg_stat_walwriter">
+ <title><structname>pg_stat_walwriter</structname> View</title>
+
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Column</entry>
+ <entry>Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>dirty_writes</></entry>
+ <entry><type>bigint</type></entry>
+ <entry>Number of dirty writes, which means flushing wal buffers
+ because of its full.</entry>
+ </row>
+ <row>
+ <entry><structfield>stats_reset</></entry>
+ <entry><type>timestamp with time zone</type></entry>
+ <entry>Time at which these statistics were last reset</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <structname>pg_stat_walwriter</structname> view will always have a
+ single row, containing global data for the cluster.
+ </para>
+
<table id="pg-stat-database-view" xreflabel="pg_stat_database">
<title><structname>pg_stat_database</structname> View</title>
<tgroup cols="3">
diff --git a/src/backend/access/transam/xlog.c
b/src/backend/access/transam/xlog.c
index 2618c8d..0bf92fa 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -1338,6 +1338,7 @@ AdvanceXLInsertBuffer(bool new_segment)
WriteRqst.Write = OldPageRqstPtr;
WriteRqst.Flush = 0;
XLogWrite(WriteRqst, false, false);
+ WalWriterStats.m_xlog_dirty_writes++;
LWLockRelease(WALWriteLock);
TRACE_POSTGRESQL_WAL_BUFFER_WRITE_DIRTY_DONE();
}
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index 607a72f..40f0c34 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -671,6 +671,11 @@ CREATE VIEW pg_stat_bgwriter AS
pg_stat_get_buf_alloc() AS buffers_alloc,
pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
+CREATE VIEW pg_stat_walwriter AS
+ SELECT
+ pg_stat_get_xlog_dirty_writes() AS dirty_writes,
+ pg_stat_get_wal_stat_reset_time() AS stats_reset;
+
CREATE VIEW pg_user_mappings AS
SELECT
U.oid AS umid,
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index be3adf1..5be78c6 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -125,6 +125,15 @@ char *pgstat_stat_tmpname = NULL;
*/
PgStat_MsgBgWriter BgWriterStats;
+/*
+ * WalWriter global statistics counter.
+ * Despite its name, this counter is actually used not only in walwriter,
+ * but also in each backend process to sum up xlog dirty writes.
+ * Those processes would increment this counter in each XLogWrite call,
+ * then send it to the stat collector process.
+ */
+PgStat_MsgWalWriter WalWriterStats;
+
/* ----------
* Local data
* ----------
@@ -279,6 +288,7 @@ static void pgstat_recv_autovac(PgStat_MsgAutovacStart
*msg, int len);
static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
+static void pgstat_recv_walwriter(PgStat_MsgWalWriter *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);
@@ -762,6 +772,9 @@ pgstat_report_stat(bool force)
/* Now, send function statistics */
pgstat_send_funcstats();
+
+ /* Now, send wal buffer flush statistics */
+ pgstat_send_walwriter();
}
/*
@@ -1188,11 +1201,13 @@ pgstat_reset_shared_counters(const char *target)
if (strcmp(target, "bgwriter") == 0)
msg.m_resettarget = RESET_BGWRITER;
+ else if (strcmp(target, "walwriter") == 0)
+ msg.m_resettarget = RESET_WALWRITER;
else
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("unrecognized reset target: \"%s\"",
target),
- errhint("Target must be \"bgwriter\".")));
+ errhint("Target must be \"bgwriter\" or
\"walwriter\".")));
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_RESETSHAREDCOUNTER);
pgstat_send(&msg, sizeof(msg));
@@ -2988,6 +3003,38 @@ pgstat_send_bgwriter(void)
MemSet(&BgWriterStats, 0, sizeof(BgWriterStats));
}
+/* ----------
+ * pgstat_send_walwriter() -
+ *
+ * Send walwriter statistics to the collector
+ * ----------
+ */
+void
+pgstat_send_walwriter(void)
+{
+ /* We assume this initializes to zeroes */
+ static const PgStat_MsgBgWriter all_zeroes;
+
+ /*
+ * This function can be called even if nothing at all has happened. In
+ * this case, avoid sending a completely empty message to the stats
+ * collector.
+ */
+ if (memcmp(&WalWriterStats, &all_zeroes, sizeof(PgStat_MsgWalWriter))
== 0)
+ return;
+
+ /*
+ * Prepare and send the message
+ */
+ pgstat_setheader(&WalWriterStats.m_hdr, PGSTAT_MTYPE_WALWRITER);
+ pgstat_send(&WalWriterStats, sizeof(WalWriterStats));
+
+ /*
+ * Clear out the statistics buffer, so it can be re-used.
+ */
+ MemSet(&WalWriterStats, 0, sizeof(WalWriterStats));
+}
+
/* ----------
* PgstatCollectorMain() -
@@ -3209,6 +3256,10 @@ PgstatCollectorMain(int argc, char *argv[])
pgstat_recv_bgwriter((PgStat_MsgBgWriter *) &msg, len);
break;
+ case PGSTAT_MTYPE_WALWRITER:
+
pgstat_recv_walwriter((PgStat_MsgWalWriter *) &msg, len);
+ break;
+
case PGSTAT_MTYPE_FUNCSTAT:
pgstat_recv_funcstat((PgStat_MsgFuncstat *) &msg, len);
break;
@@ -3638,7 +3689,8 @@ pgstat_read_statsfile(Oid onlydb, bool permanent)
* Set the current timestamp (will be kept only in case we can't load an
* existing statsfile).
*/
- globalStats.stat_reset_timestamp = GetCurrentTimestamp();
+ globalStats.stat_bgw_reset_timestamp = GetCurrentTimestamp();
+ globalStats.stat_wal_reset_timestamp = GetCurrentTimestamp();
/*
* Try to open the status file. If it doesn't exist, the backends simply
@@ -4381,8 +4433,23 @@
pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len)
if (msg->m_resettarget == RESET_BGWRITER)
{
/* Reset the global background writer statistics for the
cluster. */
- memset(&globalStats, 0, sizeof(globalStats));
- globalStats.stat_reset_timestamp = GetCurrentTimestamp();
+ globalStats.timed_checkpoints = 0;
+ globalStats.requested_checkpoints = 0;
+ globalStats.checkpoint_write_time = 0;
+ globalStats.checkpoint_sync_time = 0;
+ globalStats.buf_written_checkpoints = 0;
+ globalStats.buf_written_clean = 0;
+ globalStats.maxwritten_clean = 0;
+ globalStats.buf_written_backend = 0;
+ globalStats.buf_fsync_backend = 0;
+ globalStats.buf_alloc = 0;
+ globalStats.stat_bgw_reset_timestamp = GetCurrentTimestamp();
+ }
+ else if (msg->m_resettarget == RESET_WALWRITER)
+ {
+ /* Reset the global walwriter statistics for the cluster. */
+ globalStats.xlog_dirty_writes = 0;
+ globalStats.stat_wal_reset_timestamp = GetCurrentTimestamp();
}
/*
@@ -4536,6 +4603,18 @@ pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len)
}
/* ----------
+ * pgstat_recv_walwriter() -
+ *
+ * Process a WALWRITER message.
+ * ----------
+ */
+static void
+pgstat_recv_walwriter(PgStat_MsgWalWriter *msg, int len)
+{
+ globalStats.xlog_dirty_writes += msg->m_xlog_dirty_writes;
+}
+
+/* ----------
* pgstat_recv_recoveryconflict() -
*
* Process a RECOVERYCONFLICT message.
diff --git a/src/backend/postmaster/walwriter.c
b/src/backend/postmaster/walwriter.c
index c3e15ef..cd294fb 100644
--- a/src/backend/postmaster/walwriter.c
+++ b/src/backend/postmaster/walwriter.c
@@ -290,6 +290,8 @@ WalWriterMain(void)
else if (left_till_hibernate > 0)
left_till_hibernate--;
+ pgstat_send_walwriter();
+
/*
* Sleep until we are signaled or WalWriterDelay has elapsed.
If we
* haven't done anything useful for quite some time, lengthen
the
diff --git a/src/backend/utils/adt/pgstatfuncs.c
b/src/backend/utils/adt/pgstatfuncs.c
index 7d4059f..fc25dda 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -97,6 +97,7 @@ extern Datum
pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_buf_fsync_backend(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_buf_alloc(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_wal_stat_reset_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_xact_numscans(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_xact_tuples_returned(PG_FUNCTION_ARGS);
@@ -118,6 +119,8 @@ extern Datum pg_stat_reset_shared(PG_FUNCTION_ARGS);
extern Datum pg_stat_reset_single_table_counters(PG_FUNCTION_ARGS);
extern Datum pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_xlog_dirty_writes(PG_FUNCTION_ARGS);
+
/* Global bgwriter statistics, from bgwriter.c */
extern PgStat_MsgBgWriter bgwriterStats;
@@ -1443,7 +1446,7 @@ pg_stat_get_checkpoint_sync_time(PG_FUNCTION_ARGS)
Datum
pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS)
{
- PG_RETURN_TIMESTAMPTZ(pgstat_fetch_global()->stat_reset_timestamp);
+ PG_RETURN_TIMESTAMPTZ(pgstat_fetch_global()->stat_bgw_reset_timestamp);
}
Datum
@@ -1465,6 +1468,12 @@ pg_stat_get_buf_alloc(PG_FUNCTION_ARGS)
}
Datum
+pg_stat_get_wal_stat_reset_time(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_TIMESTAMPTZ(pgstat_fetch_global()->stat_wal_reset_timestamp);
+}
+
+Datum
pg_stat_get_xact_numscans(PG_FUNCTION_ARGS)
{
Oid relid = PG_GETARG_OID(0);
@@ -1701,3 +1710,9 @@ pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+
+Datum
+pg_stat_get_xlog_dirty_writes(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_INT64(pgstat_fetch_global()->xlog_dirty_writes);
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index f935eb1..d6edb5a 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2702,6 +2702,8 @@ DATA(insert OID = 3063 ( pg_stat_get_buf_fsync_backend
PGNSP PGUID 12 1 0 0 0 f
DESCR("statistics: number of backend buffer writes that did their own fsync");
DATA(insert OID = 2859 ( pg_stat_get_buf_alloc PGNSP PGUID 12
1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_
pg_stat_get_buf_alloc _null_ _null_ _null_ ));
DESCR("statistics: number of buffer allocations");
+DATA(insert OID = 2860 ( pg_stat_get_wal_stat_reset_time PGNSP PGUID 12 1 0 0
0 f f f f t f s 0 0 1184 "" _null_ _null_ _null_ _null_
pg_stat_get_wal_stat_reset_time _null_ _null_ _null_ ));
+DESCR("statistics: last reset for the wal");
DATA(insert OID = 2978 ( pg_stat_get_function_calls PGNSP PGUID 12
1 0 0 0 f f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_
pg_stat_get_function_calls _null_ _null_ _null_ ));
DESCR("statistics: number of function calls");
@@ -2746,6 +2748,9 @@ DESCR("statistics: reset collected statistics for a
single table or index in the
DATA(insert OID = 3777 ( pg_stat_reset_single_function_counters PGNSP
PGUID 12 1 0 0 0 f f f f f f v 1 0 2278 "26" _null_ _null_ _null_ _null_
pg_stat_reset_single_function_counters _null_ _null_ _null_ ));
DESCR("statistics: reset collected statistics for a single function in the
current database");
+DATA(insert OID = 3766 ( pg_stat_get_xlog_dirty_writes PGNSP PGUID 12 1 0 0
0 f f f f f f v 0 0 20 "" _null_ _null_ _null_ _null_
pg_stat_get_xlog_dirty_writes _null_ _null_ _null_ ));
+DESCR("statistics: get xlog dirty buffer write statistics");
+
DATA(insert OID = 3163 ( pg_trigger_depth PGNSP
PGUID 12 1 0 0 0 f f f f t f s 0 0 23 "" _null_ _null_ _null_ _null_
pg_trigger_depth _null_ _null_ _null_ ));
DESCR("current trigger depth");
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 613c1c2..f71c538 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -45,6 +45,7 @@ typedef enum StatMsgType
PGSTAT_MTYPE_VACUUM,
PGSTAT_MTYPE_ANALYZE,
PGSTAT_MTYPE_BGWRITER,
+ PGSTAT_MTYPE_WALWRITER,
PGSTAT_MTYPE_FUNCSTAT,
PGSTAT_MTYPE_FUNCPURGE,
PGSTAT_MTYPE_RECOVERYCONFLICT,
@@ -102,7 +103,8 @@ typedef struct PgStat_TableCounts
/* Possible targets for resetting cluster-wide shared values */
typedef enum PgStat_Shared_Reset_Target
{
- RESET_BGWRITER
+ RESET_BGWRITER,
+ RESET_WALWRITER
} PgStat_Shared_Reset_Target;
/* Possible object types for resetting single counters */
@@ -372,6 +374,17 @@ typedef struct PgStat_MsgBgWriter
} PgStat_MsgBgWriter;
/* ----------
+ * PgStat_MsgWalWriter Sent by the walwriter to update
statistics.
+ * ----------
+ */
+typedef struct PgStat_MsgWalWriter
+{
+ PgStat_MsgHdr m_hdr;
+
+ PgStat_Counter m_xlog_dirty_writes;
+} PgStat_MsgWalWriter;
+
+/* ----------
* PgStat_MsgRecoveryConflict Sent by the backend upon recovery conflict
* ----------
*/
@@ -499,6 +512,7 @@ typedef union PgStat_Msg
PgStat_MsgVacuum msg_vacuum;
PgStat_MsgAnalyze msg_analyze;
PgStat_MsgBgWriter msg_bgwriter;
+ PgStat_MsgWalWriter msg_walwriter;
PgStat_MsgFuncstat msg_funcstat;
PgStat_MsgFuncpurge msg_funcpurge;
PgStat_MsgRecoveryConflict msg_recoveryconflict;
@@ -622,7 +636,10 @@ typedef struct PgStat_GlobalStats
PgStat_Counter buf_written_backend;
PgStat_Counter buf_fsync_backend;
PgStat_Counter buf_alloc;
- TimestampTz stat_reset_timestamp;
+ TimestampTz stat_bgw_reset_timestamp;
+
+ PgStat_Counter xlog_dirty_writes;
+ TimestampTz stat_wal_reset_timestamp;
} PgStat_GlobalStats;
@@ -730,6 +747,8 @@ extern char *pgstat_stat_filename;
*/
extern PgStat_MsgBgWriter BgWriterStats;
+extern PgStat_MsgWalWriter WalWriterStats;
+
/*
* Updated by pgstat_count_buffer_*_time macros
*/
@@ -858,6 +877,7 @@ extern void pgstat_twophase_postabort(TransactionId xid,
uint16 info,
void *recdata, uint32 len);
extern void pgstat_send_bgwriter(void);
+extern void pgstat_send_walwriter(void);
/* ----------
* Support functions for the SQL-callable functions to
diff --git a/src/test/regress/expected/rules.out
b/src/test/regress/expected/rules.out
index a235571..1612cc0 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1305,6 +1305,7 @@ SELECT viewname, definition FROM pg_views WHERE
schemaname <> 'information_schem
pg_stat_user_functions | SELECT p.oid AS funcid, n.nspname AS
schemaname, p.proname AS funcname, pg_stat_get_function_calls(p.oid) AS calls,
pg_stat_get_function_total_time(p.oid) AS total_time,
pg_stat_get_function_self_time(p.oid) AS self_time FROM (pg_proc p LEFT JOIN
pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid)
AND (pg_stat_get_function_calls(p.oid) IS NOT NULL));
pg_stat_user_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 <> ALL (ARRAY['pg_catalog'::name,
'information_schema'::name])) AND (pg_stat_all_indexes.schemaname !~
'^pg_toast'::text));
pg_stat_user_tables | SELECT pg_stat_all_tables.relid,
pg_stat_all_tables.schemaname, pg_stat_all_tables.relname,
pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read,
pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch,
pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd,
pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd,
pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup,
pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum,
pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze,
pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count,
pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count FROM
pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname <> ALL
(ARRAY['pg_catalog'::name, 'information_schema'::name])) AND
(pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
+ pg_stat_walwriter | SELECT pg_stat_get_xlog_dirty_writes() AS
dirty_writes, pg_stat_get_wal_stat_reset_time() AS stats_reset;
pg_stat_xact_all_tables | SELECT c.oid AS relid, n.nspname AS
schemaname, c.relname, pg_stat_get_xact_numscans(c.oid) AS seq_scan,
pg_stat_get_xact_tuples_returned(c.oid) AS seq_tup_read,
(sum(pg_stat_get_xact_numscans(i.indexrelid)))::bigint AS idx_scan,
((sum(pg_stat_get_xact_tuples_fetched(i.indexrelid)))::bigint +
pg_stat_get_xact_tuples_fetched(c.oid)) AS idx_tup_fetch,
pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins,
pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd,
pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del,
pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd 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_xact_sys_tables | SELECT pg_stat_xact_all_tables.relid,
pg_stat_xact_all_tables.schemaname, pg_stat_xact_all_tables.relname,
pg_stat_xact_all_tables.seq_scan, pg_stat_xact_all_tables.seq_tup_read,
pg_stat_xact_all_tables.idx_scan, pg_stat_xact_all_tables.idx_tup_fetch,
pg_stat_xact_all_tables.n_tup_ins, pg_stat_xact_all_tables.n_tup_upd,
pg_stat_xact_all_tables.n_tup_del, pg_stat_xact_all_tables.n_tup_hot_upd FROM
pg_stat_xact_all_tables WHERE ((pg_stat_xact_all_tables.schemaname = ANY
(ARRAY['pg_catalog'::name, 'information_schema'::name])) OR
(pg_stat_xact_all_tables.schemaname ~ '^pg_toast'::text));
pg_stat_xact_user_functions | SELECT p.oid AS funcid, n.nspname AS
schemaname, p.proname AS funcname, pg_stat_get_xact_function_calls(p.oid) AS
calls, pg_stat_get_xact_function_total_time(p.oid) AS total_time,
pg_stat_get_xact_function_self_time(p.oid) AS self_time FROM (pg_proc p LEFT
JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <>
(12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL));
@@ -1339,7 +1340,7 @@ SELECT viewname, definition FROM pg_views WHERE
schemaname <> 'information_schem
shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM
shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor
= shoelace.sl_color))));
street | SELECT r.name, r.thepath, c.cname FROM ONLY
road r, real_city c WHERE (c.outline ## r.thepath);
toyemp | SELECT emp.name, emp.age, emp.location, (12
* emp.salary) AS annualsal FROM emp;
-(60 rows)
+(61 rows)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
