public inbox for [email protected]
help / color / mirror / Atom feedFrom: shihao zhong <[email protected]>
To: Zsolt Parragi <[email protected]>
Cc: songjinzhou <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: jian he <[email protected]>
Subject: Re: [Patch] New pg_stat_tablespace view
Date: Wed, 1 Apr 2026 21:54:25 -0400
Message-ID: <CAGRkXqSL9MCawhBdARbktrcQDvpHQOzwGCoDDbq0iVpDwk6Q9g@mail.gmail.com> (raw)
In-Reply-To: <CAGRkXqT6dLa4H1ZbR_Tf6zMWA+Kwi8_v-mncYMvHDo1YCd76hA@mail.gmail.com>
References: <CAGRkXqRHsZw3+aeNZgnBYduQSY7qg1O4MBdmyFjK-A+TU1-b-A@mail.gmail.com>
<CAGRkXqRp+mK_uuCEp+wuBvuvFf_1v=MGC6Dd6HgJigJUzZanHg@mail.gmail.com>
<[email protected]>
<CAGRkXqTaxN+cqNz-qaMRWmcXC7SVmgTAb++6MZwpXTZj-EUB0A@mail.gmail.com>
<CAN4CZFPf-VhzkXMsWjncXBVBc9vjw2tasJz99iN6MMCaMLna6g@mail.gmail.com>
<CAGRkXqT6dLa4H1ZbR_Tf6zMWA+Kwi8_v-mncYMvHDo1YCd76hA@mail.gmail.com>
On Fri, Mar 27, 2026 at 2:05 PM shihao zhong <[email protected]> wrote:
>
> On Tue, Mar 24, 2026 at 6:11 PM Zsolt Parragi <[email protected]> wrote:
> >
> > Hello!
> >
> > blk_read_time and blk_write_time doesn't seem to work, they show 0 to
> > me even after some workloads, and I don't see any assignments in the
> > code. The testcase also checks for "blk_read_time >= 0" which
> > trivially succeeds.
> >
> > blocks_fetched is also misleading, it includes both reads and cache
> > hits. pg_stat_database calls this column blocks_read, and properly
> > substracts blocks_hit from it.
> >
> > + rel->pgstat_info->reltablespace = rel->rd_locator.spcOid;
> >
> > Shouldn't this be included in TwoPhasePgStatRecord / pgstat_twophase_postcommit?
> >
> >
>
> Hi Zsolt and Jian,
>
> Thanks for the feedback. I've attached v3, addressing all comments.
> Notably, I've included tuple-level stats in the pg_stat_tablespace
> view to align with the addition of SpaceOid in TwoPhasePgStatRecord.
>
> Thanks,
> Shihao
Rebase with head.
Attachments:
[application/octet-stream] pg_stat_tablespace_final_v4.patch (42.8K, 2-pg_stat_tablespace_final_v4.patch)
download | inline diff:
From 8d21c719ae1fb48046d3a982686972ef9626f867 Mon Sep 17 00:00:00 2001
From: shihao zhong <[email protected]>
Date: Thu, 2 Apr 2026 00:58:03 +0000
Subject: [PATCH] Add pg_stat_tablespace statistics view
Implement pg_stat_tablespace to track block reads, hits, I/O timing, temporary file usage, and tuple operations per tablespace. This allows DBAs to analyze tablespace-level workload hotspots.
The view includes:
- tablespace_id
- tablespace_name
- blks_read
- blks_hit
- blk_read_time
- blk_write_time
- temp_files
- temp_bytes
- tup_returned
- tup_fetched
- tup_inserted
- tup_updated
- tup_deleted
- stats_reset
Includes comprehensive field coverage checks in stats.sql.
---
doc/src/sgml/monitoring.sgml | 198 ++++++++++++++++++
src/backend/catalog/system_views.sql | 19 ++
src/backend/commands/tablespace.c | 4 +
src/backend/storage/buffer/bufmgr.c | 33 ++-
src/backend/storage/file/fd.c | 2 +-
src/backend/utils/activity/Makefile | 1 +
src/backend/utils/activity/meson.build | 1 +
src/backend/utils/activity/pgstat.c | 16 ++
src/backend/utils/activity/pgstat_database.c | 45 +++-
src/backend/utils/activity/pgstat_relation.c | 32 ++-
.../utils/activity/pgstat_tablespace.c | 127 +++++++++++
src/backend/utils/adt/pgstatfuncs.c | 106 +++++++++-
src/include/catalog/pg_proc.dat | 8 +
src/include/pgstat.h | 29 +++
src/include/utils/backend_status.h | 2 +-
src/include/utils/pgstat_internal.h | 8 +
src/include/utils/pgstat_kind.h | 3 +-
src/test/regress/expected/rules.out | 16 ++
src/test/regress/expected/stats.out | 85 +++++++-
src/test/regress/sql/stats.sql | 38 ++++
20 files changed, 759 insertions(+), 14 deletions(-)
create mode 100644 src/backend/utils/activity/pgstat_tablespace.c
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index bb75ed1069b..2f78e88500b 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -535,6 +535,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</entry>
</row>
+ <row>
+ <entry><structname>pg_stat_tablespace</structname><indexterm><primary>pg_stat_tablespace</primary></indexterm></entry>
+ <entry>One row per tablespace, showing statistics about I/O, temporary files, and tuple operations. See
+ <link linkend="monitoring-pg-stat-tablespace-view">
+ <structname>pg_stat_tablespace</structname></link> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_subscription_stats</structname><indexterm><primary>pg_stat_subscription_stats</primary></indexterm></entry>
<entry>One row per subscription, showing statistics about errors and conflicts.
@@ -5256,6 +5264,196 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</sect2>
+ <sect2 id="monitoring-pg-stat-tablespace-view">
+ <title><structname>pg_stat_tablespace</structname></title>
+
+ <indexterm>
+ <primary>pg_stat_tablespace</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_tablespace</structname> view will contain one row
+ for each tablespace, showing statistics about I/O operations, temporary
+ file usage, and tuple operations in that tablespace.
+ </para>
+
+ <table id="pg-stat-tablespace-view" xreflabel="pg_stat_tablespace">
+ <title><structname>pg_stat_tablespace</structname> View</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para>
+ </entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>tablespace_id</structfield> <type>oid</type>
+ </para>
+ <para>
+ OID of the tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>tablespace_name</structfield> <type>name</type>
+ </para>
+ <para>
+ Name of the tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>blk_read_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Time spent reading data blocks by backends in this tablespace, in milliseconds
+ (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>blk_write_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Time spent writing data blocks by backends in this tablespace, in milliseconds
+ (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>blocks_fetched</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of data blocks read from disk in this tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>blocks_hit</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of data blocks found in shared buffer cache in this tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>temp_files</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of temporary files created in this tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>temp_bytes</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total amount of data written to temporary files in this tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>tup_returned</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of live rows fetched by sequential scans and index entries returned by index scans in this tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>tup_fetched</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of live rows fetched by index scans in this tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>tup_inserted</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of rows inserted by queries in this tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>tup_updated</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of rows updated by queries in this tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>tup_deleted</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of rows deleted by queries in this tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Time at which these statistics were last reset
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect2>
+
<sect2 id="monitoring-stats-functions">
<title>Statistics Functions</title>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index e54018004db..c673e15b216 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1128,6 +1128,25 @@ CREATE VIEW pg_stat_replication_slots AS
LATERAL pg_stat_get_replication_slot(slot_name) as s
WHERE r.datoid IS NOT NULL; -- excluding physical slots
+CREATE VIEW pg_stat_tablespace AS
+ SELECT
+ T.oid AS tablespace_id,
+ T.spcname AS tablespace_name,
+ S.blk_read_time,
+ S.blk_write_time,
+ S.blks_hit,
+ S.blks_fetched - S.blks_hit AS blks_read,
+ S.temp_files,
+ S.temp_bytes,
+ S.tup_returned,
+ S.tup_fetched,
+ S.tup_inserted,
+ S.tup_updated,
+ S.tup_deleted,
+ S.stats_reset
+ FROM pg_tablespace T
+ LEFT JOIN LATERAL pg_stat_get_tablespace(T.oid) S ON true;
+
CREATE VIEW pg_stat_database AS
SELECT
D.oid AS datid,
diff --git a/src/backend/commands/tablespace.c b/src/backend/commands/tablespace.c
index d91fcf0facf..0d35957c956 100644
--- a/src/backend/commands/tablespace.c
+++ b/src/backend/commands/tablespace.c
@@ -80,6 +80,7 @@
#include "utils/memutils.h"
#include "utils/rel.h"
#include "utils/varlena.h"
+#include "pgstat.h"
/* GUC variables */
char *default_tablespace = NULL;
@@ -546,6 +547,9 @@ DropTableSpace(DropTableSpaceStmt *stmt)
(void) XLogInsert(RM_TBLSPC_ID, XLOG_TBLSPC_DROP);
}
+ /* Keep cumulative stats system up-to-date */
+ pgstat_drop_tablespace(tablespaceoid);
+
/*
* Note: because we checked that the tablespace was empty, there should be
* no need to worry about flushing shared buffers or free space map
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 5c64570020d..678b3bd1b00 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -1815,6 +1815,7 @@ WaitReadBuffers(ReadBuffersOperation *operation)
!pgaio_wref_check_done(&operation->io_wref))
{
instr_time io_start = pgstat_prepare_io_time(track_io_timing);
+ instr_time io_time;
pgaio_wref_wait(&operation->io_wref);
needed_wait = true;
@@ -1823,8 +1824,14 @@ WaitReadBuffers(ReadBuffersOperation *operation)
* The IO operation itself was already counted earlier, in
* AsyncReadBuffers(), this just accounts for the wait time.
*/
+ INSTR_TIME_SET_CURRENT(io_time);
+ INSTR_TIME_SUBTRACT(io_time, io_start);
+
pgstat_count_io_op_time(io_object, io_context, IOOP_READ,
io_start, 0, 0);
+
+ pgstat_count_tablespace_buffer_read_time(INSTR_TIME_GET_MICROSEC(io_time),
+ operation->smgr->smgr_rlocator.locator.spcOid);
}
else
{
@@ -1941,7 +1948,7 @@ AsyncReadBuffers(ReadBuffersOperation *operation, int *nblocks_progress)
void *io_pages[MAX_IO_COMBINE_LIMIT];
IOContext io_context;
IOObject io_object;
- instr_time io_start;
+ instr_time io_start, io_time;
StartBufferIOResult status;
if (persistence == RELPERSISTENCE_TEMP)
@@ -2144,9 +2151,16 @@ AsyncReadBuffers(ReadBuffersOperation *operation, int *nblocks_progress)
smgrstartreadv(ioh, operation->smgr, forknum,
blocknum,
io_pages, io_buffers_len);
+
+ INSTR_TIME_SET_CURRENT(io_time);
+ INSTR_TIME_SUBTRACT(io_time, io_start);
+
pgstat_count_io_op_time(io_object, io_context, IOOP_READ,
io_start, 1, io_buffers_len * BLCKSZ);
+ pgstat_count_tablespace_buffer_read_time(INSTR_TIME_GET_MICROSEC(io_time),
+ operation->smgr->smgr_rlocator.locator.spcOid);
+
if (persistence == RELPERSISTENCE_TEMP)
pgBufferUsage.local_blks_read += io_buffers_len;
else
@@ -2794,7 +2808,7 @@ ExtendBufferedRelShared(BufferManagerRelation bmr,
{
BlockNumber first_block;
IOContext io_context = IOContextForStrategy(strategy);
- instr_time io_start;
+ instr_time io_start, io_time;
LimitAdditionalPins(&extend_by);
@@ -3018,9 +3032,15 @@ ExtendBufferedRelShared(BufferManagerRelation bmr,
if (!(flags & EB_SKIP_EXTENSION_LOCK))
UnlockRelationForExtension(bmr.rel, ExclusiveLock);
+ INSTR_TIME_SET_CURRENT(io_time);
+ INSTR_TIME_SUBTRACT(io_time, io_start);
+
pgstat_count_io_op_time(IOOBJECT_RELATION, io_context, IOOP_EXTEND,
io_start, 1, extend_by * BLCKSZ);
+ pgstat_count_tablespace_buffer_write_time(INSTR_TIME_GET_MICROSEC(io_time),
+ bmr.rel->rd_locator.spcOid);
+
/* Set BM_VALID, terminate IO, and wake up any waiters */
for (uint32 i = 0; i < extend_by; i++)
{
@@ -4505,7 +4525,7 @@ FlushBuffer(BufferDesc *buf, SMgrRelation reln, IOObject io_object,
{
XLogRecPtr recptr;
ErrorContextCallback errcallback;
- instr_time io_start;
+ instr_time io_start, io_time;
Block bufBlock;
Assert(BufferLockHeldByMeInMode(buf, BUFFER_LOCK_EXCLUSIVE) ||
@@ -4598,9 +4618,16 @@ FlushBuffer(BufferDesc *buf, SMgrRelation reln, IOObject io_object,
* When a strategy is not in use, the write can only be a "regular" write
* of a dirty shared buffer (IOCONTEXT_NORMAL IOOP_WRITE).
*/
+
+ INSTR_TIME_SET_CURRENT(io_time);
+ INSTR_TIME_SUBTRACT(io_time, io_start);
+
pgstat_count_io_op_time(IOOBJECT_RELATION, io_context,
IOOP_WRITE, io_start, 1, BLCKSZ);
+ pgstat_count_tablespace_buffer_write_time(INSTR_TIME_GET_MICROSEC(io_time),
+ reln->smgr_rlocator.locator.spcOid);
+
pgBufferUsage.shared_blks_written++;
/*
diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c
index 01f1bd6e687..03c47aba17f 100644
--- a/src/backend/storage/file/fd.c
+++ b/src/backend/storage/file/fd.c
@@ -1515,7 +1515,7 @@ FileAccess(File file)
static void
ReportTemporaryFileUsage(const char *path, pgoff_t size)
{
- pgstat_report_tempfile(size);
+ pgstat_report_tempfile(size, path);
if (log_temp_files >= 0)
{
diff --git a/src/backend/utils/activity/Makefile b/src/backend/utils/activity/Makefile
index ca3ef89bf59..59b49bf6a81 100644
--- a/src/backend/utils/activity/Makefile
+++ b/src/backend/utils/activity/Makefile
@@ -32,6 +32,7 @@ OBJS = \
pgstat_shmem.o \
pgstat_slru.o \
pgstat_subscription.o \
+ pgstat_tablespace.o \
pgstat_wal.o \
pgstat_xact.o \
wait_event.o \
diff --git a/src/backend/utils/activity/meson.build b/src/backend/utils/activity/meson.build
index 1aa7ece5290..b4e23cef558 100644
--- a/src/backend/utils/activity/meson.build
+++ b/src/backend/utils/activity/meson.build
@@ -17,6 +17,7 @@ backend_sources += files(
'pgstat_shmem.c',
'pgstat_slru.c',
'pgstat_subscription.c',
+ 'pgstat_tablespace.c',
'pgstat_wal.c',
'pgstat_xact.c',
)
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index eb8ccbaa628..dedb04a5516 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -301,6 +301,22 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.reset_timestamp_cb = pgstat_database_reset_timestamp_cb,
},
+ [PGSTAT_KIND_TABLESPACE] = {
+ .name = "tablespace",
+
+ .fixed_amount = false,
+ .write_to_file = true,
+ .accessed_across_databases = true,
+
+ .shared_size = sizeof(PgStatShared_Tablespace),
+ .shared_data_off = offsetof(PgStatShared_Tablespace, stats),
+ .shared_data_len = sizeof(((PgStatShared_Tablespace *) 0)->stats),
+ .pending_size = sizeof(PgStat_StatTabspaceEntry),
+
+ .flush_pending_cb = pgstat_tablespace_flush_cb,
+ .reset_timestamp_cb = pgstat_tablespace_reset_timestamp_cb,
+ },
+
[PGSTAT_KIND_RELATION] = {
.name = "relation",
diff --git a/src/backend/utils/activity/pgstat_database.c b/src/backend/utils/activity/pgstat_database.c
index 933dcb5cae5..d76aea09d32 100644
--- a/src/backend/utils/activity/pgstat_database.c
+++ b/src/backend/utils/activity/pgstat_database.c
@@ -17,9 +17,11 @@
#include "postgres.h"
+#include "miscadmin.h"
#include "storage/standby.h"
#include "utils/pgstat_internal.h"
#include "utils/timestamp.h"
+#include "catalog/pg_tablespace_d.h"
static bool pgstat_should_report_connstat(void);
@@ -214,20 +216,61 @@ pgstat_report_checksum_failures_in_db(Oid dboid, int failurecount)
pgstat_unlock_entry(entry_ref);
}
+/*
+ * Helper function to parse tablespace oid from temporary file path.
+ */
+static Oid
+get_tablespace_from_tempfile_path(const char *path)
+{
+ /*
+ * XXX: We match the file path against known tablespace prefixes to avoid passing
+ * down tablespace OIDs through the entire tuplestore/fd.c stack which would bloat
+ * the Vfd internal structs.
+ */
+ if (path == NULL)
+ return InvalidOid;
+
+ if (strncmp(path, "pg_tblspc/", 10) == 0)
+ {
+ return atooid(path + 10);
+ }
+ else if (strncmp(path, "base/", 5) == 0)
+ {
+ return DEFAULTTABLESPACE_OID;
+ }
+ else if (strncmp(path, "global/", 7) == 0)
+ {
+ return GLOBALTABLESPACE_OID;
+ }
+
+ return InvalidOid;
+}
+
/*
* Report creation of temporary file.
*/
void
-pgstat_report_tempfile(size_t filesize)
+pgstat_report_tempfile(size_t filesize, const char *path)
{
PgStat_StatDBEntry *dbent;
+ PgStat_StatTabspaceEntry *tsent;
+ Oid tablespace_oid;
if (!pgstat_track_counts)
return;
+ tablespace_oid = get_tablespace_from_tempfile_path(path);
+
dbent = pgstat_prep_database_pending(MyDatabaseId);
dbent->temp_bytes += filesize;
dbent->temp_files++;
+
+ if (OidIsValid(tablespace_oid))
+ {
+ tsent = pgstat_prep_tablespace_pending(tablespace_oid);
+ tsent->temp_bytes += filesize;
+ tsent->temp_files++;
+ }
}
/*
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index bc8c43b96aa..e24cc36fdc3 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -20,6 +20,7 @@
#include "access/twophase_rmgr.h"
#include "access/xact.h"
#include "catalog/catalog.h"
+#include "miscadmin.h"
#include "utils/memutils.h"
#include "utils/pgstat_internal.h"
#include "utils/rel.h"
@@ -37,12 +38,13 @@ typedef struct TwoPhasePgStatRecord
PgStat_Counter updated_pre_truncdrop;
PgStat_Counter deleted_pre_truncdrop;
Oid id; /* table's OID */
+ Oid tablespace_oid; /* table's tablespace OID */
bool shared; /* is it a shared catalog? */
bool truncdropped; /* was the relation truncated/dropped? */
} TwoPhasePgStatRecord;
-static PgStat_TableStatus *pgstat_prep_relation_pending(Oid rel_id, bool isshared);
+static PgStat_TableStatus *pgstat_prep_relation_pending(Oid rel_id, Oid tablespace_oid, bool isshared);
static void add_tabstat_xact_level(PgStat_TableStatus *pgstat_info, int nest_level);
static void ensure_tabstat_xact_level(PgStat_TableStatus *pgstat_info);
static void save_truncdrop_counters(PgStat_TableXactStatus *trans, bool is_drop);
@@ -135,7 +137,8 @@ pgstat_assoc_relation(Relation rel)
/* Else find or make the PgStat_TableStatus entry, and update link */
rel->pgstat_info = pgstat_prep_relation_pending(RelationGetRelid(rel),
- rel->rd_rel->relisshared);
+ rel->rd_locator.spcOid,
+ rel->rd_rel->relisshared);
/* don't allow link a stats to multiple relcache entries */
Assert(rel->pgstat_info->relation == NULL);
@@ -707,6 +710,7 @@ AtPrepare_PgStat_Relations(PgStat_SubXactStatus *xact_state)
record.updated_pre_truncdrop = trans->updated_pre_truncdrop;
record.deleted_pre_truncdrop = trans->deleted_pre_truncdrop;
record.id = tabstat->id;
+ record.tablespace_oid = tabstat->tablespace_oid;
record.shared = tabstat->shared;
record.truncdropped = trans->truncdropped;
@@ -750,7 +754,7 @@ pgstat_twophase_postcommit(FullTransactionId fxid, uint16 info,
PgStat_TableStatus *pgstat_info;
/* Find or create a tabstat entry for the rel */
- pgstat_info = pgstat_prep_relation_pending(rec->id, rec->shared);
+ pgstat_info = pgstat_prep_relation_pending(rec->id, rec->tablespace_oid, rec->shared);
/* Same math as in AtEOXact_PgStat, commit case */
pgstat_info->counts.tuples_inserted += rec->tuples_inserted;
@@ -786,7 +790,7 @@ pgstat_twophase_postabort(FullTransactionId fxid, uint16 info,
PgStat_TableStatus *pgstat_info;
/* Find or create a tabstat entry for the rel */
- pgstat_info = pgstat_prep_relation_pending(rec->id, rec->shared);
+ pgstat_info = pgstat_prep_relation_pending(rec->id, rec->tablespace_oid, rec->shared);
/* Same math as in AtEOXact_PgStat, abort case */
if (rec->truncdropped)
@@ -897,6 +901,23 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
dbentry->blocks_fetched += lstats->counts.blocks_fetched;
dbentry->blocks_hit += lstats->counts.blocks_hit;
+ /* The entry was successfully flushed, add the same to tablespace stats */
+ {
+ Oid tsid = (lstats->tablespace_oid == InvalidOid) ? MyDatabaseTableSpace : lstats->tablespace_oid;
+
+ if (OidIsValid(tsid))
+ {
+ PgStat_StatTabspaceEntry *tsentry = pgstat_prep_tablespace_pending(tsid);
+ tsentry->blocks_fetched += lstats->counts.blocks_fetched;
+ tsentry->blocks_hit += lstats->counts.blocks_hit;
+ tsentry->tuples_returned += lstats->counts.tuples_returned;
+ tsentry->tuples_fetched += lstats->counts.tuples_fetched;
+ tsentry->tuples_inserted += lstats->counts.tuples_inserted;
+ tsentry->tuples_updated += lstats->counts.tuples_updated;
+ tsentry->tuples_deleted += lstats->counts.tuples_deleted;
+ }
+ }
+
return true;
}
@@ -920,7 +941,7 @@ pgstat_relation_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts)
* initialized if not exists.
*/
static PgStat_TableStatus *
-pgstat_prep_relation_pending(Oid rel_id, bool isshared)
+pgstat_prep_relation_pending(Oid rel_id, Oid tablespace_oid, bool isshared)
{
PgStat_EntryRef *entry_ref;
PgStat_TableStatus *pending;
@@ -930,6 +951,7 @@ pgstat_prep_relation_pending(Oid rel_id, bool isshared)
rel_id, NULL);
pending = entry_ref->pending;
pending->id = rel_id;
+ pending->tablespace_oid = tablespace_oid;
pending->shared = isshared;
return pending;
diff --git a/src/backend/utils/activity/pgstat_tablespace.c b/src/backend/utils/activity/pgstat_tablespace.c
new file mode 100644
index 00000000000..fec1c7f6ed0
--- /dev/null
+++ b/src/backend/utils/activity/pgstat_tablespace.c
@@ -0,0 +1,127 @@
+/* -------------------------------------------------------------------------
+ *
+ * pgstat_tablespace.c
+ * Implementation of tablespace statistics.
+ *
+ * Copyright (c) 2001-2025, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * src/backend/utils/activity/pgstat_tablespace.c
+ * -------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "utils/pgstat_internal.h"
+#include "utils/timestamp.h"
+
+
+/*
+ * Remove entry for the tablespace being dropped.
+ */
+void
+pgstat_drop_tablespace(Oid tablespaceid)
+{
+ pgstat_drop_transactional(PGSTAT_KIND_TABLESPACE, InvalidOid, tablespaceid);
+}
+
+/*
+ * Fetch tablespace statistics.
+ */
+PgStat_StatTabspaceEntry *
+pgstat_fetch_stat_tabspaceentry(Oid tablespaceid)
+{
+ return (PgStat_StatTabspaceEntry *)
+ pgstat_fetch_entry(PGSTAT_KIND_TABLESPACE, InvalidOid, tablespaceid);
+}
+
+/*
+ * Flush out pending stats for the entry.
+ */
+bool
+pgstat_tablespace_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
+{
+ PgStatShared_Tablespace *sharedent;
+ PgStat_StatTabspaceEntry *pendingent;
+
+ pendingent = (PgStat_StatTabspaceEntry *) entry_ref->pending;
+ sharedent = (PgStatShared_Tablespace *) entry_ref->shared_stats;
+
+ if (!pgstat_lock_entry(entry_ref, nowait))
+ return false;
+
+#define PGSTAT_ACCUM_TABSPACECOUNT(item) \
+ (sharedent)->stats.item += (pendingent)->item
+
+ PGSTAT_ACCUM_TABSPACECOUNT(blocks_fetched);
+ PGSTAT_ACCUM_TABSPACECOUNT(blocks_hit);
+ PGSTAT_ACCUM_TABSPACECOUNT(blk_read_time);
+ PGSTAT_ACCUM_TABSPACECOUNT(blk_write_time);
+ PGSTAT_ACCUM_TABSPACECOUNT(temp_files);
+ PGSTAT_ACCUM_TABSPACECOUNT(temp_bytes);
+ PGSTAT_ACCUM_TABSPACECOUNT(tuples_returned);
+ PGSTAT_ACCUM_TABSPACECOUNT(tuples_fetched);
+ PGSTAT_ACCUM_TABSPACECOUNT(tuples_inserted);
+ PGSTAT_ACCUM_TABSPACECOUNT(tuples_updated);
+ PGSTAT_ACCUM_TABSPACECOUNT(tuples_deleted);
+
+#undef PGSTAT_ACCUM_TABSPACECOUNT
+
+ pgstat_unlock_entry(entry_ref);
+
+ /* Clear pending stats since they have been flushed */
+ memset(pendingent, 0, sizeof(*pendingent));
+
+ return true;
+}
+
+/*
+ * Reset stats reset timestamp.
+ */
+void
+pgstat_tablespace_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts)
+{
+ ((PgStatShared_Tablespace *) header)->stats.stat_reset_timestamp = ts;
+}
+
+/*
+ * Prepare for reporting tablespace stats.
+ */
+PgStat_StatTabspaceEntry *
+pgstat_prep_tablespace_pending(Oid tablespaceid)
+{
+ PgStat_EntryRef *entry_ref;
+
+ Assert(OidIsValid(tablespaceid));
+
+ entry_ref = pgstat_prep_pending_entry(PGSTAT_KIND_TABLESPACE,
+ InvalidOid, tablespaceid, NULL);
+
+ return (PgStat_StatTabspaceEntry *) entry_ref->pending;
+}
+
+/*
+ * Count tablespace buffer write time.
+ */
+void
+pgstat_count_tablespace_buffer_write_time(uint64 duration, Oid tablespace_oid)
+{
+ if (OidIsValid(tablespace_oid))
+ {
+ PgStat_StatTabspaceEntry *tsent = pgstat_prep_tablespace_pending(tablespace_oid);
+ tsent->blk_write_time += duration;
+ }
+}
+
+/*
+ * Count tablespace buffer read time.
+ */
+void
+pgstat_count_tablespace_buffer_read_time(uint64 duration, Oid tablespace_oid)
+{
+ if (OidIsValid(tablespace_oid))
+ {
+ PgStat_StatTabspaceEntry *tsent = pgstat_prep_tablespace_pending(tablespace_oid);
+
+ tsent->blk_read_time += duration;
+ }
+}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 9185a8e6b83..3fb9c662db8 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1965,6 +1965,7 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
XLogPrefetchResetStats();
pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
pgstat_reset_of_kind(PGSTAT_KIND_WAL);
+ pgstat_reset_of_kind(PGSTAT_KIND_TABLESPACE);
PG_RETURN_VOID();
}
@@ -1987,11 +1988,13 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
else if (strcmp(target, "wal") == 0)
pgstat_reset_of_kind(PGSTAT_KIND_WAL);
+ else if (strcmp(target, "tablespace") == 0)
+ pgstat_reset_of_kind(PGSTAT_KIND_TABLESPACE);
else
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("unrecognized reset target: \"%s\"", target),
- errhint("Target must be \"archiver\", \"bgwriter\", \"checkpointer\", \"io\", \"recovery_prefetch\", \"slru\", or \"wal\".")));
+ errhint("Target must be \"archiver\", \"bgwriter\", \"checkpointer\", \"io\", \"recovery_prefetch\", \"slru\", \"wal\", or \"tablespace\".")));
PG_RETURN_VOID();
}
@@ -2348,6 +2351,107 @@ pg_stat_get_subscription_stats(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
}
+/*
+ * Returns tablespace statistics for the given tablespace. If the tablespace
+ * statistics is not available, return all-zeros stats.
+ */
+Datum
+pg_stat_get_tablespace(PG_FUNCTION_ARGS)
+{
+#define PG_STAT_GET_TABLESPACE_COLS 12
+ Oid spcoid = PG_GETARG_OID(0);
+ TupleDesc tupdesc;
+ Datum values[PG_STAT_GET_TABLESPACE_COLS] = {0};
+ bool nulls[PG_STAT_GET_TABLESPACE_COLS] = {0};
+ PgStat_StatTabspaceEntry *tsentry;
+ PgStat_StatTabspaceEntry allzero;
+ int i = 0;
+
+ /* Get tablespace stats */
+ tsentry = pgstat_fetch_stat_tabspaceentry(spcoid);
+
+ /* Initialise attributes information in the tuple descriptor */
+ tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_TABLESPACE_COLS);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "blk_read_time",
+ FLOAT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "blk_write_time",
+ FLOAT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "blks_fetched",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 4, "blks_hit",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 5, "temp_files",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 6, "temp_bytes",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 7, "tup_returned",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 8, "tup_fetched",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 9, "tup_inserted",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 10, "tup_updated",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 11, "tup_deleted",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 12, "stats_reset",
+ TIMESTAMPTZOID, -1, 0);
+
+ TupleDescFinalize(tupdesc);
+ tupdesc = BlessTupleDesc(tupdesc);
+
+ if (!tsentry)
+ {
+ /* If the tablespace is not found, initialise its stats */
+ memset(&allzero, 0, sizeof(PgStat_StatTabspaceEntry));
+ tsentry = &allzero;
+ }
+
+ /* blk_read_time */
+ values[i++] = Float8GetDatum(pg_stat_us_to_ms(tsentry->blk_read_time));
+
+ /* blk_write_time */
+ values[i++] = Float8GetDatum(pg_stat_us_to_ms(tsentry->blk_write_time));
+
+ /* blocks_fetched */
+ values[i++] = Int64GetDatum(tsentry->blocks_fetched);
+
+ /* blocks_hit */
+ values[i++] = Int64GetDatum(tsentry->blocks_hit);
+
+ /* temp_files */
+ values[i++] = Int64GetDatum(tsentry->temp_files);
+
+ /* temp_bytes */
+ values[i++] = Int64GetDatum(tsentry->temp_bytes);
+
+ /* tup_returned */
+ values[i++] = Int64GetDatum(tsentry->tuples_returned);
+
+ /* tup_fetched */
+ values[i++] = Int64GetDatum(tsentry->tuples_fetched);
+
+ /* tup_inserted */
+ values[i++] = Int64GetDatum(tsentry->tuples_inserted);
+
+ /* tup_updated */
+ values[i++] = Int64GetDatum(tsentry->tuples_updated);
+
+ /* tup_deleted */
+ values[i++] = Int64GetDatum(tsentry->tuples_deleted);
+
+ /* stats_reset */
+ if (tsentry->stat_reset_timestamp == 0)
+ nulls[i] = true;
+ else
+ values[i] = TimestampTzGetDatum(tsentry->stat_reset_timestamp);
+
+ Assert(i + 1 == PG_STAT_GET_TABLESPACE_COLS);
+
+ /* Returns the record as Datum */
+ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
+}
+
/*
* Checks for presence of stats for object with provided kind, database oid,
* object oid.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 3579cec5744..d741c4f115e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6096,6 +6096,14 @@
proargnames => '{name,blks_zeroed,blks_hit,blks_read,blks_written,blks_exists,flushes,truncates,stats_reset}',
prosrc => 'pg_stat_get_slru' },
+{ oid => '8459', descr => 'statistics: tablespace statistics',
+ proname => 'pg_stat_get_tablespace', provolatile => 's',
+ proparallel => 'r', prorettype => 'record', proargtypes => 'oid',
+ proallargtypes => '{oid,float8,float8,int8,int8,int8,int8,int8,int8,int8,int8,int8,timestamptz}',
+ proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o}',
+ proargnames => '{tablespaceid,blk_read_time,blk_write_time,blks_fetched,blks_hit,temp_files,temp_bytes,tup_returned,tup_fetched,tup_inserted,tup_updated,tup_deleted,stats_reset}',
+ prosrc => 'pg_stat_get_tablespace' },
+
{ oid => '2978', descr => 'statistics: number of function calls',
proname => 'pg_stat_get_function_calls', provolatile => 's',
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 8e3549c3752..85e480eaa81 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -181,6 +181,7 @@ typedef struct PgStat_TableStatus
{
Oid id; /* table's OID */
bool shared; /* is it a shared catalog? */
+ Oid tablespace_oid; /* tablespace OID */
struct PgStat_TableXactStatus *trans; /* lowest subxact's counts */
PgStat_TableCounts counts; /* event counts to be sent */
Relation relation; /* rel that is using this entry */
@@ -402,6 +403,23 @@ typedef struct PgStat_StatDBEntry
TimestampTz stat_reset_timestamp;
} PgStat_StatDBEntry;
+typedef struct PgStat_StatTabspaceEntry
+{
+ PgStat_Counter blk_read_time; /* times in microseconds */
+ PgStat_Counter blk_write_time;
+ PgStat_Counter blocks_fetched;
+ PgStat_Counter blocks_hit;
+ PgStat_Counter temp_files;
+ PgStat_Counter temp_bytes;
+ PgStat_Counter tuples_returned;
+ PgStat_Counter tuples_fetched;
+ PgStat_Counter tuples_inserted;
+ PgStat_Counter tuples_updated;
+ PgStat_Counter tuples_deleted;
+
+ TimestampTz stat_reset_timestamp;
+} PgStat_StatTabspaceEntry;
+
typedef struct PgStat_StatFuncEntry
{
PgStat_Counter numcalls;
@@ -771,6 +789,17 @@ extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry_ext(bool shared,
extern PgStat_TableStatus *find_tabstat_entry(Oid rel_id);
+/*
+ * Functions in pgstat_tablespace.c
+ */
+
+extern void pgstat_drop_tablespace(Oid tablespaceid);
+extern PgStat_StatTabspaceEntry *pgstat_fetch_stat_tabspaceentry(Oid tablespaceid);
+extern PgStat_StatTabspaceEntry *pgstat_prep_tablespace_pending(Oid tablespaceid);
+extern void pgstat_count_tablespace_buffer_write_time(uint64 duration, Oid tablespace_oid);
+extern void pgstat_count_tablespace_buffer_read_time(uint64 duration, Oid tablespace_oid);
+
+
/*
* Functions in pgstat_replslot.c
*/
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index ddd06304e97..a2c501edf00 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -323,7 +323,7 @@ extern void pgstat_clear_backend_activity_snapshot(void);
extern void pgstat_report_activity(BackendState state, const char *cmd_str);
extern void pgstat_report_query_id(int64 query_id, bool force);
extern void pgstat_report_plan_id(int64 plan_id, bool force);
-extern void pgstat_report_tempfile(size_t filesize);
+extern void pgstat_report_tempfile(size_t filesize, const char *path);
extern void pgstat_report_appname(const char *appname);
extern void pgstat_report_xact_timestamp(TimestampTz tstamp);
extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser);
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index eed4c6b359c..4c36420334a 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -504,6 +504,12 @@ typedef struct PgStatShared_Database
PgStat_StatDBEntry stats;
} PgStatShared_Database;
+typedef struct PgStatShared_Tablespace
+{
+ PgStatShared_Common header;
+ PgStat_StatTabspaceEntry stats;
+} PgStatShared_Tablespace;
+
typedef struct PgStatShared_Relation
{
PgStatShared_Common header;
@@ -744,6 +750,8 @@ extern PgStat_StatDBEntry *pgstat_prep_database_pending(Oid dboid);
extern void pgstat_reset_database_timestamp(Oid dboid, TimestampTz ts);
extern bool pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait);
extern void pgstat_database_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts);
+extern bool pgstat_tablespace_flush_cb(PgStat_EntryRef *entry_ref, bool nowait);
+extern void pgstat_tablespace_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts);
/*
diff --git a/src/include/utils/pgstat_kind.h b/src/include/utils/pgstat_kind.h
index 2d78a029683..2d28efa92d4 100644
--- a/src/include/utils/pgstat_kind.h
+++ b/src/include/utils/pgstat_kind.h
@@ -39,9 +39,10 @@
#define PGSTAT_KIND_LOCK 11
#define PGSTAT_KIND_SLRU 12
#define PGSTAT_KIND_WAL 13
+#define PGSTAT_KIND_TABLESPACE 14
#define PGSTAT_KIND_BUILTIN_MIN PGSTAT_KIND_DATABASE
-#define PGSTAT_KIND_BUILTIN_MAX PGSTAT_KIND_WAL
+#define PGSTAT_KIND_BUILTIN_MAX PGSTAT_KIND_TABLESPACE
#define PGSTAT_KIND_BUILTIN_SIZE (PGSTAT_KIND_BUILTIN_MAX + 1)
/* Custom stats kinds */
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2b3cf6d8569..4e4eac34a61 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2307,6 +2307,22 @@ pg_stat_sys_tables| SELECT relid,
stats_reset
FROM pg_stat_all_tables
WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
+pg_stat_tablespace| SELECT t.oid AS tablespace_id,
+ t.spcname AS tablespace_name,
+ s.blk_read_time,
+ s.blk_write_time,
+ s.blks_hit,
+ (s.blks_fetched - s.blks_hit) AS blks_read,
+ s.temp_files,
+ s.temp_bytes,
+ s.tup_returned,
+ s.tup_fetched,
+ s.tup_inserted,
+ s.tup_updated,
+ s.tup_deleted,
+ s.stats_reset
+ FROM (pg_tablespace t
+ LEFT JOIN LATERAL pg_stat_get_tablespace(t.oid) s(blk_read_time, blk_write_time, blks_fetched, blks_hit, temp_files, temp_bytes, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, stats_reset) ON (true));
pg_stat_user_functions| SELECT p.oid AS funcid,
n.nspname AS schemaname,
p.proname AS funcname,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index ea7f7846895..bab3d465594 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1130,7 +1130,7 @@ SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
-- Test error case for reset_shared with unknown stats type
SELECT pg_stat_reset_shared('unknown');
ERROR: unrecognized reset target: "unknown"
-HINT: Target must be "archiver", "bgwriter", "checkpointer", "io", "recovery_prefetch", "slru", or "wal".
+HINT: Target must be "archiver", "bgwriter", "checkpointer", "io", "recovery_prefetch", "slru", "wal", or "tablespace".
-- Test that reset works for pg_stat_database and pg_stat_database_conflicts
-- Since pg_stat_database stats_reset starts out as NULL, reset it once first so that we
-- have a baseline for comparison. The same for pg_stat_database_conflicts as it shares
@@ -2006,4 +2006,87 @@ SELECT fastpath_exceeded > :fastpath_exceeded_before FROM pg_stat_lock WHERE loc
(1 row)
DROP TABLE part_test;
+-- Test pg_stat_tablespace
+SELECT count(*) > 0 FROM pg_stat_tablespace;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT tablespace_name FROM pg_stat_tablespace WHERE tablespace_name IN ('pg_default', 'pg_global') ORDER BY tablespace_name;
+ tablespace_name
+-----------------
+ pg_default
+ pg_global
+(2 rows)
+
+-- Test block and tuple stats in pg_stat_tablespace
+SET track_io_timing = on;
+CREATE TABLE test_tablespace_stats (a int);
+INSERT INTO test_tablespace_stats SELECT generate_series(1, 100);
+SELECT count(*) FROM test_tablespace_stats;
+ count
+-------
+ 100
+(1 row)
+
+UPDATE test_tablespace_stats SET a = a + 1 WHERE a > 50;
+DELETE FROM test_tablespace_stats WHERE a > 90;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT blks_read > 0 AS has_blks_read, blks_hit > 0 AS has_blks_hit, blk_read_time > 0 AS has_blk_read_time, blk_write_time > 0 AS has_blk_write_time, tup_inserted > 0 AS has_tup_inserted, tup_updated > 0 AS has_tup_updated, tup_deleted > 0 AS has_tup_deleted, tup_returned > 0 AS has_tup_returned FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default';
+ has_blks_read | has_blks_hit | has_blk_read_time | has_blk_write_time | has_tup_inserted | has_tup_updated | has_tup_deleted | has_tup_returned
+---------------+--------------+-------------------+--------------------+------------------+-----------------+-----------------+------------------
+ t | t | t | t | t | t | t | t
+(1 row)
+
+DROP TABLE test_tablespace_stats;
+-- Test temp file stats in pg_stat_tablespace
+-- Use a sort that exceeds work_mem to force temp file usage
+SET work_mem = '64kB';
+SELECT count(*) FROM (SELECT * FROM generate_series(1, 10000) AS s ORDER BY s DESC) AS foo;
+ count
+-------
+ 10000
+(1 row)
+
+RESET work_mem;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+-- We expect temp files to be in pg_default if not specified otherwise
+SELECT temp_files > 0 AS has_temp_files, temp_bytes > 0 AS has_temp_bytes FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default';
+ has_temp_files | has_temp_bytes
+----------------+----------------
+ t | t
+(1 row)
+
+-- Test reset for pg_stat_tablespace
+-- Ensure we have a timestamp to compare
+SELECT pg_stat_reset_shared('tablespace');
+ pg_stat_reset_shared
+----------------------
+
+(1 row)
+
+SELECT stats_reset AS ts_reset_before FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default' \gset
+SELECT pg_stat_reset_shared('tablespace');
+ pg_stat_reset_shared
+----------------------
+
+(1 row)
+
+SELECT stats_reset > :'ts_reset_before'::timestamptz FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default';
+ ?column?
+----------
+ t
+(1 row)
+
-- End of Stats Test
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 65d8968c83e..ad4c8a65347 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -1000,4 +1000,42 @@ SELECT fastpath_exceeded > :fastpath_exceeded_before FROM pg_stat_lock WHERE loc
DROP TABLE part_test;
+-- Test pg_stat_tablespace
+SELECT count(*) > 0 FROM pg_stat_tablespace;
+
+SELECT tablespace_name FROM pg_stat_tablespace WHERE tablespace_name IN ('pg_default', 'pg_global') ORDER BY tablespace_name;
+
+-- Test block and tuple stats in pg_stat_tablespace
+SET track_io_timing = on;
+CREATE TABLE test_tablespace_stats (a int);
+INSERT INTO test_tablespace_stats SELECT generate_series(1, 100);
+SELECT count(*) FROM test_tablespace_stats;
+UPDATE test_tablespace_stats SET a = a + 1 WHERE a > 50;
+DELETE FROM test_tablespace_stats WHERE a > 90;
+
+SELECT pg_stat_force_next_flush();
+
+SELECT blks_read > 0 AS has_blks_read, blks_hit > 0 AS has_blks_hit, blk_read_time > 0 AS has_blk_read_time, blk_write_time > 0 AS has_blk_write_time, tup_inserted > 0 AS has_tup_inserted, tup_updated > 0 AS has_tup_updated, tup_deleted > 0 AS has_tup_deleted, tup_returned > 0 AS has_tup_returned FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default';
+
+DROP TABLE test_tablespace_stats;
+-- Test temp file stats in pg_stat_tablespace
+-- Use a sort that exceeds work_mem to force temp file usage
+SET work_mem = '64kB';
+SELECT count(*) FROM (SELECT * FROM generate_series(1, 10000) AS s ORDER BY s DESC) AS foo;
+
+RESET work_mem;
+SELECT pg_stat_force_next_flush();
+
+-- We expect temp files to be in pg_default if not specified otherwise
+SELECT temp_files > 0 AS has_temp_files, temp_bytes > 0 AS has_temp_bytes FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default';
+
+-- Test reset for pg_stat_tablespace
+-- Ensure we have a timestamp to compare
+SELECT pg_stat_reset_shared('tablespace');
+
+SELECT stats_reset AS ts_reset_before FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default' \gset
+SELECT pg_stat_reset_shared('tablespace');
+
+SELECT stats_reset > :'ts_reset_before'::timestamptz FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default';
+
-- End of Stats Test
--
2.53.0.1185.g05d4b7b318-goog
view thread (8+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: [Patch] New pg_stat_tablespace view
In-Reply-To: <CAGRkXqSL9MCawhBdARbktrcQDvpHQOzwGCoDDbq0iVpDwk6Q9g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox