Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit77bd49a

Browse files
committed
Show shared object statistics in pg_stat_database
This adds a row to the pg_stat_database view with datoid 0 and datnameNULL for those objects that are not in a database. This was addedparticularly for checksums, but we were already tracking more satisticsfor these objects, just not returning it.Also add a checksum_last_failure column that holds the timestamptz ofthe last checksum failure that occurred in a database (or in anon-dataabase file), if any.Author: Julien Rouhaud <rjuju123@gmail.com>
1 parentef6f30f commit77bd49a

File tree

10 files changed

+76
-17
lines changed

10 files changed

+76
-17
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 18 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -2498,20 +2498,22 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
24982498
<row>
24992499
<entry><structfield>datid</structfield></entry>
25002500
<entry><type>oid</type></entry>
2501-
<entry>OID of a database</entry>
2501+
<entry>OID of this database, or 0 for objects belonging to a shared
2502+
relation</entry>
25022503
</row>
25032504
<row>
25042505
<entry><structfield>datname</structfield></entry>
25052506
<entry><type>name</type></entry>
2506-
<entry>Name of this database</entry>
2507+
<entry>Name of this database, or <literal>NULL</literal> for the shared
2508+
objects.</entry>
25072509
</row>
25082510
<row>
25092511
<entry><structfield>numbackends</structfield></entry>
25102512
<entry><type>integer</type></entry>
2511-
<entry>Number of backends currently connected to this database.
2512-
This is theonly column in this view that returns a value reflecting
2513-
current state; all other columns return the accumulated values since
2514-
the last reset.</entry>
2513+
<entry>Number of backends currently connected to this database, or
2514+
<literal>NULL</literal> for theshared objects. This is the only column
2515+
in this view that returns a value reflecting current state; all other
2516+
columns return the accumulated values sincethe last reset.</entry>
25152517
</row>
25162518
<row>
25172519
<entry><structfield>xact_commit</structfield></entry>
@@ -2597,7 +2599,14 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
25972599
<row>
25982600
<entry><structfield>checksum_failures</structfield></entry>
25992601
<entry><type>bigint</type></entry>
2600-
<entry>Number of data page checksum failures detected in this database</entry>
2602+
<entry>Number of data page checksum failures detected in this
2603+
database</entry>
2604+
</row>
2605+
<row>
2606+
<entry><structfield>checksum_last_failure</structfield></entry>
2607+
<entry><type>timestamp with time zone</type></entry>
2608+
<entry>Time at which the last data page checksum failure was detected in
2609+
this database, or on a shared object.</entry>
26012610
</row>
26022611
<row>
26032612
<entry><structfield>blk_read_time</structfield></entry>
@@ -2622,7 +2631,8 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
26222631

26232632
<para>
26242633
The <structname>pg_stat_database</structname> view will contain one row
2625-
for each database in the cluster, showing database-wide statistics.
2634+
for each database in the cluster, plus one for the shared objects, showing
2635+
database-wide statistics.
26262636
</para>
26272637

26282638
<table id="pg-stat-database-conflicts-view" xreflabel="pg_stat_database_conflicts">

‎doc/src/sgml/ref/initdb.sgml

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -218,7 +218,9 @@ PostgreSQL documentation
218218
I/O system that would otherwise be silent. Enabling checksums
219219
may incur a noticeable performance penalty. This option can only
220220
be set during initialization, and cannot be changed later. If
221-
set, checksums are calculated for all objects, in all databases.
221+
set, checksums are calculated for all objects, in all databases. All
222+
checksum failures will be reported in the <xref
223+
linkend="pg-stat-database-view"/> view.
222224
</para>
223225
</listitem>
224226
</varlistentry>

‎doc/src/sgml/ref/pg_basebackup.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -531,7 +531,8 @@ PostgreSQL documentation
531531
By default, checksums are verified and checksum failures will result
532532
in a non-zero exit status. However, the base backup will not be
533533
removed in such a case, as if the <option>--no-clean</option> option
534-
had been used.
534+
had been used. Checksum verifications failures will also be reported
535+
in the <xref linkend="pg-stat-database-view"/> view.
535536
</para>
536537
</listitem>
537538
</varlistentry>

‎src/backend/catalog/system_views.sql

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -816,7 +816,10 @@ CREATE VIEW pg_stat_database AS
816816
SELECT
817817
D.oidAS datid,
818818
D.datnameAS datname,
819-
pg_stat_get_db_numbackends(D.oid)AS numbackends,
819+
CASE
820+
WHEN (D.oid= (0)::oid) THENNULL::integer
821+
ELSE pg_stat_get_db_numbackends(D.oid)
822+
ENDAS numbackends,
820823
pg_stat_get_db_xact_commit(D.oid)AS xact_commit,
821824
pg_stat_get_db_xact_rollback(D.oid)AS xact_rollback,
822825
pg_stat_get_db_blocks_fetched(D.oid)-
@@ -832,10 +835,15 @@ CREATE VIEW pg_stat_database AS
832835
pg_stat_get_db_temp_bytes(D.oid)AS temp_bytes,
833836
pg_stat_get_db_deadlocks(D.oid)AS deadlocks,
834837
pg_stat_get_db_checksum_failures(D.oid)AS checksum_failures,
838+
pg_stat_get_db_checksum_last_failure(D.oid)AS checksum_last_failure,
835839
pg_stat_get_db_blk_read_time(D.oid)AS blk_read_time,
836840
pg_stat_get_db_blk_write_time(D.oid)AS blk_write_time,
837841
pg_stat_get_db_stat_reset_time(D.oid)AS stats_reset
838-
FROM pg_database D;
842+
FROM (
843+
SELECT0ASoid,NULL::nameAS datname
844+
UNION ALL
845+
SELECToid, datnameFROM pg_database
846+
) D;
839847

840848
CREATEVIEWpg_stat_database_conflictsAS
841849
SELECT

‎src/backend/postmaster/pgstat.c

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1523,7 +1523,7 @@ pgstat_report_deadlock(void)
15231523

15241524

15251525
/* --------
1526-
* pgstat_report_checksum_failures_in_db(dboid, failure_count) -
1526+
* pgstat_report_checksum_failures_in_db() -
15271527
*
15281528
*Tell the collector about one or more checksum failures.
15291529
* --------
@@ -1539,6 +1539,8 @@ pgstat_report_checksum_failures_in_db(Oid dboid, int failurecount)
15391539
pgstat_setheader(&msg.m_hdr,PGSTAT_MTYPE_CHECKSUMFAILURE);
15401540
msg.m_databaseid=dboid;
15411541
msg.m_failurecount=failurecount;
1542+
msg.m_failure_time=GetCurrentTimestamp();
1543+
15421544
pgstat_send(&msg,sizeof(msg));
15431545
}
15441546

@@ -4651,6 +4653,7 @@ reset_dbentry_counters(PgStat_StatDBEntry *dbentry)
46514653
dbentry->n_temp_bytes=0;
46524654
dbentry->n_deadlocks=0;
46534655
dbentry->n_checksum_failures=0;
4656+
dbentry->last_checksum_failure=0;
46544657
dbentry->n_block_read_time=0;
46554658
dbentry->n_block_write_time=0;
46564659

@@ -6307,6 +6310,7 @@ pgstat_recv_checksum_failure(PgStat_MsgChecksumFailure *msg, int len)
63076310
dbentry=pgstat_get_db_entry(msg->m_databaseid, true);
63086311

63096312
dbentry->n_checksum_failures+=msg->m_failurecount;
6313+
dbentry->last_checksum_failure=msg->m_failure_time;
63106314
}
63116315

63126316
/* ----------

‎src/backend/replication/basebackup.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1584,9 +1584,9 @@ sendFile(const char *readfilename, const char *tarfilename, struct stat *statbuf
15841584
(errmsg("file \"%s\" has a total of %d checksum verification "
15851585
"failures",readfilename,checksum_failures)));
15861586

1587-
if (dboid!=InvalidOid)
1588-
pgstat_report_checksum_failures_in_db(dboid,checksum_failures);
1587+
pgstat_report_checksum_failures_in_db(dboid,checksum_failures);
15891588
}
1589+
15901590
total_checksum_failures+=checksum_failures;
15911591

15921592
return true;

‎src/backend/utils/adt/pgstatfuncs.c

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1534,6 +1534,24 @@ pg_stat_get_db_checksum_failures(PG_FUNCTION_ARGS)
15341534
PG_RETURN_INT64(result);
15351535
}
15361536

1537+
Datum
1538+
pg_stat_get_db_checksum_last_failure(PG_FUNCTION_ARGS)
1539+
{
1540+
Oiddbid=PG_GETARG_OID(0);
1541+
TimestampTzresult;
1542+
PgStat_StatDBEntry*dbentry;
1543+
1544+
if ((dbentry=pgstat_fetch_stat_dbentry(dbid))==NULL)
1545+
result=0;
1546+
else
1547+
result=dbentry->last_checksum_failure;
1548+
1549+
if (result==0)
1550+
PG_RETURN_NULL();
1551+
else
1552+
PG_RETURN_TIMESTAMPTZ(result);
1553+
}
1554+
15371555
Datum
15381556
pg_stat_get_db_blk_read_time(PG_FUNCTION_ARGS)
15391557
{

‎src/include/catalog/pg_proc.dat

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5285,6 +5285,11 @@
52855285
proname => 'pg_stat_get_db_checksum_failures', provolatile => 's',
52865286
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
52875287
prosrc => 'pg_stat_get_db_checksum_failures' },
5288+
{ oid => '8394',
5289+
descr => 'statistics: when last checksum failure was detected in database',
5290+
proname => 'pg_stat_get_db_checksum_last_failure', provolatile => 's',
5291+
proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
5292+
prosrc => 'pg_stat_get_db_checksum_last_failure' },
52885293
{ oid => '3074', descr => 'statistics: last reset for a database',
52895294
proname => 'pg_stat_get_db_stat_reset_time', provolatile => 's',
52905295
proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',

‎src/include/pgstat.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -541,6 +541,7 @@ typedef struct PgStat_MsgChecksumFailure
541541
PgStat_MsgHdrm_hdr;
542542
Oidm_databaseid;
543543
intm_failurecount;
544+
TimestampTzm_failure_time;
544545
}PgStat_MsgChecksumFailure;
545546

546547

@@ -607,6 +608,7 @@ typedef struct PgStat_StatDBEntry
607608
PgStat_Countern_temp_bytes;
608609
PgStat_Countern_deadlocks;
609610
PgStat_Countern_checksum_failures;
611+
TimestampTzlast_checksum_failure;
610612
PgStat_Countern_block_read_time;/* times in microseconds */
611613
PgStat_Countern_block_write_time;
612614

‎src/test/regress/expected/rules.out

Lines changed: 11 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1805,7 +1805,10 @@ pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints
18051805
pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
18061806
pg_stat_database| SELECT d.oid AS datid,
18071807
d.datname,
1808-
pg_stat_get_db_numbackends(d.oid) AS numbackends,
1808+
CASE
1809+
WHEN (d.oid = (0)::oid) THEN NULL::integer
1810+
ELSE pg_stat_get_db_numbackends(d.oid)
1811+
END AS numbackends,
18091812
pg_stat_get_db_xact_commit(d.oid) AS xact_commit,
18101813
pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback,
18111814
(pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read,
@@ -1820,10 +1823,16 @@ pg_stat_database| SELECT d.oid AS datid,
18201823
pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes,
18211824
pg_stat_get_db_deadlocks(d.oid) AS deadlocks,
18221825
pg_stat_get_db_checksum_failures(d.oid) AS checksum_failures,
1826+
pg_stat_get_db_checksum_last_failure(d.oid) AS checksum_last_failure,
18231827
pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time,
18241828
pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time,
18251829
pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset
1826-
FROM pg_database d;
1830+
FROM ( SELECT 0 AS oid,
1831+
NULL::name AS datname
1832+
UNION ALL
1833+
SELECT pg_database.oid,
1834+
pg_database.datname
1835+
FROM pg_database) d;
18271836
pg_stat_database_conflicts| SELECT d.oid AS datid,
18281837
d.datname,
18291838
pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp