Hi hackers,
I would like to propose a patch that adds a new cumulative statistics
view, pg_stat_deprecated_features, which tracks how often each
deprecated feature is used across the cluster.
Motivation:
When we deprecate a feature today, we have little visibility into
whether anyone is still using it. For example, MD5 password support
was marked as deprecated in v18 [1], and v19 added warnings when an
MD5 password is set or used to authenticate. Even so, there is still
no straightforward way for an administrator to answer a basic
question: is anything in my cluster still relying on MD5 passwords?
The only signals available are server log warnings (where they exist)
and ad hoc catalog queries, neither of which gives a cluster-wide,
accumulated count of actual usage.
This is useful from two directions.
For users, it makes major-version upgrades easier. Before upgrading to
a release that removes a deprecated feature, an administrator wants to
confirm that their workload does not depend on it. Today that is hard
to confirm, so the removal can turn into a surprise breakage
discovered only after the upgrade. This view lets administrators check
directly, before upgrading, whether and how often each deprecated
feature is still being used.
For hackers, it keeps the existence of deprecated features visible
instead of letting them be forgotten, and it encourages actually
removing them in time. Each deprecated feature we remove lets us
delete the code that supports it, which keeps an ever-growing codebase
more maintainable rather than carrying obsolete code indefinitely.
This proposal is inspired by similar facilities in other systems: SQL
Server's "Deprecated Features" performance object, which counts uses
of features scheduled for removal [2], and Kubernetes'
apiserver_requested_deprecated_apis metric, which reports requests
made to deprecated APIs [3].
Design:
The patch adds a cumulative statistics view with one row per tracked
deprecated feature, showing cluster-wide usage. Depending on the
feature, usage is counted when the SQL using it is parsed, when the
command is executed, or when an authentication exchange relying on it
succeeds. The columns are deliberately kept to a minimal set, shown in
the example below.
Example Usage:
postgres=# SELECT * FROM pg_stat_deprecated_features;
name | usage_count | last_used |
stats_reset
------------------------+-------------+-----------------------------+-------------------------------
md5_password_set | 3 | 2026-06-27 09:02:11.114+09 |
2026-06-27 00:00:00.000+09
md5_password_auth | 1432 | 2026-06-27 10:21:54.018+09 |
2026-06-27 00:00:00.000+09
global_temporary_table | 0 | |
2026-06-27 00:00:00.000+09
local_temporary_table | 27 | 2026-06-27 10:18:42.551+09 |
2026-06-27 00:00:00.000+09
(4 rows)
Why not the custom cumulative statistics API:
- No hooks at the measurement points. Deprecated usage is detected in
the grammar (the GLOBAL/LOCAL keywords are discarded right after
parsing) and in the authentication code, where an extension cannot get
control.
- Being present by default is the point. This data can only serve as
the basis for removal decisions, which core today makes blind, if it
is in every cluster from the start. An extension installed after the
fact cannot recover the usage it missed before it was installed.
Scope of this patch:
To keep the initial submission small and reviewable, this first
version tracks only two areas: MD5 password usage (setting an MD5
password, and authenticating with one) and the deprecated GLOBAL/LOCAL
TEMPORARY table syntax. The view and the infrastructure are designed
so that more features can be added incrementally.
Many other deprecated features could be tracked in the same way, for example:
- VACUUM FULL command
- deprecated functions (getpgusername, obj_description_oid,
pg_get_viewdef_text, the txid_* family)
- deprecated operators (<^, >^, ?#, @@@)
- older DDL spellings (EXECUTE PROCEDURE in CREATE TRIGGER, PROCEDURE=
in CREATE OPERATOR, CREATE ROLE ... IN GROUP, ALTER GROUP)
Rather than adding these now, I would first like to agree on the
overall direction, namely whether this view is something we want and
what criteria decide which deprecated features belong in it. Once we
have that agreement, I will implement the rest accordingly.
The patch is attached as a small series: 0001 adds the view and the
cumulative-statistics infrastructure, and the remaining patches add
counting for MD5 password usage and for the deprecated TEMPORARY table
syntax. The patches are split this way for ease of review, so the 0001
infrastructure patch does not compile on its own, because it defines
an empty set of tracked features. The series builds once the first
counting patch is applied.
Thoughts?
[1] https://www.postgresql.org/message-id/ZwbfpJJol7lDWajL@nathan
[2]
https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-deprecated-features-object
[3]
https://kubernetes.io/docs/reference/using-api/deprecation-policy/#rest-resources-aka-api-objects
--
Best regards,
Shinya Kato
NTT OSS Center
From 5f11a221d2ee661d15cdd6216aa7bd3045969a01 Mon Sep 17 00:00:00 2001
From: Shinya Kato <[email protected]>
Date: Thu, 11 Jun 2026 16:02:03 +0900
Subject: [PATCH v1 1/5] Add pg_stat_deprecated_features view
PostgreSQL has no way to measure whether deprecated features are
still in use before they are removed, forcing such decisions to be
made blind or based on log scraping. Add a new cumulative statistics
kind for deprecated feature usage and expose it through a new
pg_stat_deprecated_features view, with one row per tracked feature
showing a usage count and the time of last use. Statistics are
cluster-wide, persist across clean restarts, and can be reset with
pg_stat_reset_shared('deprecated_features').
This commit only adds the statistics infrastructure, the registry of
tracked features, and the view itself; the per-feature counting hooks
that call pgstat_count_deprecated_feature() are added by subsequent
commits, so all usage counts are zero for now.
Bump catalog version.
Bump PGSTAT_FILE_FORMAT_ID.
Author: Shinya Kato <[email protected]>
Reviewed-by:
Discussion: https://postgr.es/m/
---
doc/src/sgml/monitoring.sgml | 114 ++++++++++++
src/backend/catalog/system_views.sql | 8 +
src/backend/utils/activity/Makefile | 1 +
src/backend/utils/activity/meson.build | 1 +
src/backend/utils/activity/pgstat.c | 17 ++
.../activity/pgstat_deprecated_features.c | 175 ++++++++++++++++++
src/backend/utils/adt/pgstatfuncs.c | 50 ++++-
src/include/catalog/pg_proc.dat | 9 +
src/include/pgstat.h | 27 ++-
src/include/utils/pgstat_internal.h | 20 ++
src/include/utils/pgstat_kind.h | 9 +-
src/test/regress/expected/rules.out | 5 +
src/test/regress/expected/stats.out | 22 ++-
src/test/regress/sql/stats.sql | 8 +
14 files changed, 459 insertions(+), 7 deletions(-)
create mode 100644 src/backend/utils/activity/pgstat_deprecated_features.c
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 08d5b824552..25f993496d8 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -499,6 +499,15 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</entry>
</row>
+ <row>
+ <entry><structname>pg_stat_deprecated_features</structname><indexterm><primary>pg_stat_deprecated_features</primary></indexterm></entry>
+ <entry>
+ One row per deprecated feature, showing cluster-wide usage statistics.
+ See <link linkend="monitoring-pg-stat-deprecated-features-view">
+ <structname>pg_stat_deprecated_features</structname></link> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_io</structname><indexterm><primary>pg_stat_io</primary></indexterm></entry>
<entry>
@@ -3397,6 +3406,105 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</table>
</sect2>
+ <sect2 id="monitoring-pg-stat-deprecated-features-view">
+ <title><structname>pg_stat_deprecated_features</structname></title>
+
+ <indexterm>
+ <primary>pg_stat_deprecated_features</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_deprecated_features</structname> view will contain
+ one row for each deprecated feature whose usage is tracked, showing
+ cluster-wide usage statistics. This allows administrators to determine
+ whether anything still depends on a deprecated feature before it is
+ removed.
+ </para>
+
+ <para>
+ Depending on the feature, usage is counted when SQL making use of the
+ feature is parsed, when a command using the feature is executed, or when
+ an authentication exchange relying on the feature succeeds. For features
+ counted at parse time, re-executing a prepared statement does not
+ increment the counter again. Counting happens regardless of whether a
+ warning about the feature's use is emitted; for example, MD5 password
+ usage is counted even when <xref linkend="guc-md5-password-warnings"/> is
+ disabled.
+ </para>
+
+ <table id="pg-stat-deprecated-features-view" xreflabel="pg_stat_deprecated_features">
+ <title><structname>pg_stat_deprecated_features</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>name</structfield> <type>text</type>
+ </para>
+ <para>
+ Name of the deprecated feature.
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>usage_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times the deprecated feature has been used.
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>last_used</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Time at which the deprecated feature was last used, or
+ <literal>NULL</literal> if it has not been used since these
+ statistics were last reset.
+ </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>
+
+ <para>
+ The tracked deprecated features are:
+
+ <variablelist>
+ </variablelist>
+ </para>
+ </sect2>
+
<sect2 id="monitoring-pg-stat-bgwriter-view">
<title><structname>pg_stat_bgwriter</structname></title>
@@ -5654,6 +5762,12 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<structname>pg_stat_checkpointer</structname> view.
</para>
</listitem>
+ <listitem>
+ <para>
+ <literal>deprecated_features</literal>: Reset all the counters shown
+ in the <structname>pg_stat_deprecated_features</structname> view.
+ </para>
+ </listitem>
<listitem>
<para>
<literal>io</literal>: Reset all the counters shown in the
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 8f129baec90..025b5aa9f02 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1012,6 +1012,14 @@ CREATE VIEW pg_stat_lock AS
l.stats_reset
FROM pg_stat_get_lock() l;
+CREATE VIEW pg_stat_deprecated_features AS
+ SELECT
+ d.name,
+ d.usage_count,
+ d.last_used,
+ d.stats_reset
+ FROM pg_stat_get_deprecated_features() d;
+
CREATE VIEW pg_stat_wal_receiver AS
SELECT
s.pid,
diff --git a/src/backend/utils/activity/Makefile b/src/backend/utils/activity/Makefile
index ca3ef89bf59..c0168d536b5 100644
--- a/src/backend/utils/activity/Makefile
+++ b/src/backend/utils/activity/Makefile
@@ -24,6 +24,7 @@ OBJS = \
pgstat_bgwriter.o \
pgstat_checkpointer.o \
pgstat_database.o \
+ pgstat_deprecated_features.o \
pgstat_function.o \
pgstat_io.o \
pgstat_lock.o \
diff --git a/src/backend/utils/activity/meson.build b/src/backend/utils/activity/meson.build
index 1aa7ece5290..bf5cb21fcc2 100644
--- a/src/backend/utils/activity/meson.build
+++ b/src/backend/utils/activity/meson.build
@@ -9,6 +9,7 @@ backend_sources += files(
'pgstat_bgwriter.c',
'pgstat_checkpointer.c',
'pgstat_database.c',
+ 'pgstat_deprecated_features.c',
'pgstat_function.c',
'pgstat_io.c',
'pgstat_lock.c',
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index c4fa14f138f..919fdc0104f 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -432,6 +432,23 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.snapshot_cb = pgstat_checkpointer_snapshot_cb,
},
+ [PGSTAT_KIND_DEPRECATED_FEATURES] = {
+ .name = "deprecated_features",
+
+ .fixed_amount = true,
+ .write_to_file = true,
+
+ .snapshot_ctl_off = offsetof(PgStat_Snapshot, deprecated_features),
+ .shared_ctl_off = offsetof(PgStat_ShmemControl, deprecated_features),
+ .shared_data_off = offsetof(PgStatShared_DeprecatedFeatures, stats),
+ .shared_data_len = sizeof(((PgStatShared_DeprecatedFeatures *) 0)->stats),
+
+ .flush_static_cb = pgstat_deprecated_features_flush_cb,
+ .init_shmem_cb = pgstat_deprecated_features_init_shmem_cb,
+ .reset_all_cb = pgstat_deprecated_features_reset_all_cb,
+ .snapshot_cb = pgstat_deprecated_features_snapshot_cb,
+ },
+
[PGSTAT_KIND_IO] = {
.name = "io",
diff --git a/src/backend/utils/activity/pgstat_deprecated_features.c b/src/backend/utils/activity/pgstat_deprecated_features.c
new file mode 100644
index 00000000000..47e34b78243
--- /dev/null
+++ b/src/backend/utils/activity/pgstat_deprecated_features.c
@@ -0,0 +1,175 @@
+/* -------------------------------------------------------------------------
+ *
+ * pgstat_deprecated_features.c
+ * Implementation of deprecated feature usage statistics.
+ *
+ * This file contains the implementation of deprecated feature usage
+ * statistics. It is kept separate from pgstat.c to enforce the line
+ * between the statistics access / storage implementation and the details
+ * about individual types of statistics.
+ *
+ * Copyright (c) 2001-2026, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * src/backend/utils/activity/pgstat_deprecated_features.c
+ * -------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "utils/pgstat_internal.h"
+#include "utils/timestamp.h"
+
+
+/*
+ * Names of the deprecated features that we keep usage statistics for.
+ * Entries are one-to-one with the PgStat_DeprecatedFeature enum and must
+ * be kept in the same order, sorted by name.
+ */
+static const char *const pgstat_deprecated_feature_names[] = {
+ /* entries are added by later commits */
+};
+
+StaticAssertDecl(lengthof(pgstat_deprecated_feature_names) == PGSTAT_NUM_DEPRECATED_FEATURES,
+ "pgstat_deprecated_feature_names[] inconsistent with PgStat_DeprecatedFeature");
+
+/*
+ * Deprecated feature usage counts waiting to be flushed out. We assume
+ * this variable inits to zeroes. Entries are one-to-one with
+ * pgstat_deprecated_feature_names[].
+ */
+static PgStat_DeprecatedFeatureStats pending_DeprecatedFeatureStats[PGSTAT_NUM_DEPRECATED_FEATURES];
+static bool have_deprecated_feature_stats = false;
+
+
+/*
+ * Count one use of a deprecated feature.
+ */
+void
+pgstat_count_deprecated_feature(PgStat_DeprecatedFeature feature)
+{
+ PgStat_DeprecatedFeatureStats *pendingent;
+
+ pgstat_assert_is_up();
+
+ /*
+ * The postmaster should never count deprecated feature usage; if it did,
+ * the counts would be duplicated into child processes via fork().
+ */
+ Assert(IsUnderPostmaster || !IsPostmasterEnvironment);
+
+ Assert(feature < PGSTAT_NUM_DEPRECATED_FEATURES);
+
+ pendingent = &pending_DeprecatedFeatureStats[feature];
+ pendingent->usage_count++;
+ pendingent->last_used = GetCurrentTimestamp();
+
+ have_deprecated_feature_stats = true;
+ pgstat_report_fixed = true;
+}
+
+/*
+ * Support function for the SQL-callable pgstat* functions. Returns
+ * a pointer to the deprecated feature usage statistics struct.
+ */
+PgStat_DeprecatedFeatureStats *
+pgstat_fetch_deprecated_features(void)
+{
+ pgstat_snapshot_fixed(PGSTAT_KIND_DEPRECATED_FEATURES);
+
+ return pgStatLocal.snapshot.deprecated_features;
+}
+
+/*
+ * Returns the name of a deprecated feature for an index. The index may be
+ * above PGSTAT_NUM_DEPRECATED_FEATURES, in which case this returns NULL.
+ * This allows writing code that does not know the number of entries in
+ * advance.
+ */
+const char *
+pgstat_get_deprecated_feature_name(int idx)
+{
+ if (idx < 0 || idx >= PGSTAT_NUM_DEPRECATED_FEATURES)
+ return NULL;
+
+ return pgstat_deprecated_feature_names[idx];
+}
+
+/*
+ * Flush out locally pending deprecated feature usage stats entries
+ *
+ * If nowait is true, this function returns true if the lock could not be
+ * acquired. Otherwise return false.
+ */
+bool
+pgstat_deprecated_features_flush_cb(bool nowait)
+{
+ PgStatShared_DeprecatedFeatures *stats_shmem = &pgStatLocal.shmem->deprecated_features;
+ int i;
+
+ if (!have_deprecated_feature_stats)
+ return false;
+
+ if (!nowait)
+ LWLockAcquire(&stats_shmem->lock, LW_EXCLUSIVE);
+ else if (!LWLockConditionalAcquire(&stats_shmem->lock, LW_EXCLUSIVE))
+ return true;
+
+ for (i = 0; i < PGSTAT_NUM_DEPRECATED_FEATURES; i++)
+ {
+ PgStat_DeprecatedFeatureStats *sharedent = &stats_shmem->stats[i];
+ PgStat_DeprecatedFeatureStats *pendingent = &pending_DeprecatedFeatureStats[i];
+
+ sharedent->usage_count += pendingent->usage_count;
+ sharedent->last_used = Max(sharedent->last_used,
+ pendingent->last_used);
+ }
+
+ /* done, clear the pending entry */
+ MemSet(pending_DeprecatedFeatureStats, 0,
+ sizeof(pending_DeprecatedFeatureStats));
+
+ LWLockRelease(&stats_shmem->lock);
+
+ have_deprecated_feature_stats = false;
+
+ return false;
+}
+
+void
+pgstat_deprecated_features_init_shmem_cb(void *stats)
+{
+ PgStatShared_DeprecatedFeatures *stats_shmem = (PgStatShared_DeprecatedFeatures *) stats;
+
+ LWLockInitialize(&stats_shmem->lock, LWTRANCHE_PGSTATS_DATA);
+}
+
+void
+pgstat_deprecated_features_reset_all_cb(TimestampTz ts)
+{
+ PgStatShared_DeprecatedFeatures *stats_shmem = &pgStatLocal.shmem->deprecated_features;
+
+ LWLockAcquire(&stats_shmem->lock, LW_EXCLUSIVE);
+
+ for (int i = 0; i < PGSTAT_NUM_DEPRECATED_FEATURES; i++)
+ {
+ memset(&stats_shmem->stats[i], 0,
+ sizeof(PgStat_DeprecatedFeatureStats));
+ stats_shmem->stats[i].stat_reset_timestamp = ts;
+ }
+
+ LWLockRelease(&stats_shmem->lock);
+}
+
+void
+pgstat_deprecated_features_snapshot_cb(void)
+{
+ PgStatShared_DeprecatedFeatures *stats_shmem = &pgStatLocal.shmem->deprecated_features;
+
+ LWLockAcquire(&stats_shmem->lock, LW_SHARED);
+
+ memcpy(pgStatLocal.snapshot.deprecated_features, &stats_shmem->stats,
+ sizeof(stats_shmem->stats));
+
+ LWLockRelease(&stats_shmem->lock);
+}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 6f9c9c72de5..5041e5cbfd8 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1820,6 +1820,51 @@ pg_stat_get_slru(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+/*
+ * Returns statistics of deprecated feature usage.
+ */
+Datum
+pg_stat_get_deprecated_features(PG_FUNCTION_ARGS)
+{
+#define PG_STAT_GET_DEPRECATED_FEATURES_COLS 4
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ int i;
+ PgStat_DeprecatedFeatureStats *stats;
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ /* request deprecated feature stats from the cumulative stats system */
+ stats = pgstat_fetch_deprecated_features();
+
+ for (i = 0;; i++)
+ {
+ /* for each row */
+ Datum values[PG_STAT_GET_DEPRECATED_FEATURES_COLS] = {0};
+ bool nulls[PG_STAT_GET_DEPRECATED_FEATURES_COLS] = {0};
+ PgStat_DeprecatedFeatureStats stat;
+ const char *name;
+
+ name = pgstat_get_deprecated_feature_name(i);
+
+ if (!name)
+ break;
+
+ stat = stats[i];
+
+ values[0] = PointerGetDatum(cstring_to_text(name));
+ values[1] = Int64GetDatum(stat.usage_count);
+ if (stat.last_used == 0)
+ nulls[2] = true;
+ else
+ values[2] = TimestampTzGetDatum(stat.last_used);
+ values[3] = TimestampTzGetDatum(stat.stat_reset_timestamp);
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+ }
+
+ return (Datum) 0;
+}
+
#define PG_STAT_GET_XACT_RELENTRY_INT64(stat) \
Datum \
CppConcat(pg_stat_get_xact_,stat)(PG_FUNCTION_ARGS) \
@@ -1956,6 +2001,7 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
pgstat_reset_of_kind(PGSTAT_KIND_ARCHIVER);
pgstat_reset_of_kind(PGSTAT_KIND_BGWRITER);
pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER);
+ pgstat_reset_of_kind(PGSTAT_KIND_DEPRECATED_FEATURES);
pgstat_reset_of_kind(PGSTAT_KIND_IO);
pgstat_reset_of_kind(PGSTAT_KIND_LOCK);
XLogPrefetchResetStats();
@@ -1973,6 +2019,8 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
pgstat_reset_of_kind(PGSTAT_KIND_BGWRITER);
else if (strcmp(target, "checkpointer") == 0)
pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER);
+ else if (strcmp(target, "deprecated_features") == 0)
+ pgstat_reset_of_kind(PGSTAT_KIND_DEPRECATED_FEATURES);
else if (strcmp(target, "io") == 0)
pgstat_reset_of_kind(PGSTAT_KIND_IO);
else if (strcmp(target, "lock") == 0)
@@ -1987,7 +2035,7 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("unrecognized reset target: \"%s\"", target),
- errhint("Target must be \"archiver\", \"bgwriter\", \"checkpointer\", \"io\", \"lock\", \"recovery_prefetch\", \"slru\", or \"wal\".")));
+ errhint("Target must be \"archiver\", \"bgwriter\", \"checkpointer\", \"deprecated_features\", \"io\", \"lock\", \"recovery_prefetch\", \"slru\", or \"wal\".")));
PG_RETURN_VOID();
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 384ba908d35..2823f856424 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6069,6 +6069,15 @@
proargnames => '{locktype,waits,wait_time,fastpath_exceeded,stats_reset}',
prosrc => 'pg_stat_get_lock' },
+{ oid => '8987', descr => 'statistics: deprecated feature usage',
+ proname => 'pg_stat_get_deprecated_features', prorows => '0',
+ proretset => 't', provolatile => 'v', proparallel => 'r',
+ prorettype => 'record', proargtypes => '',
+ proallargtypes => '{text,int8,timestamptz,timestamptz}',
+ proargmodes => '{o,o,o,o}',
+ proargnames => '{name,usage_count,last_used,stats_reset}',
+ prosrc => 'pg_stat_get_deprecated_features' },
+
{ oid => '6386', descr => 'statistics: backend IO statistics',
proname => 'pg_stat_get_backend_io', prorows => '5', proretset => 't',
provolatile => 'v', proparallel => 'r', prorettype => 'record',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index dfa2e837638..a43b56f247d 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -218,7 +218,7 @@ typedef struct PgStat_TableXactStatus
* ------------------------------------------------------------
*/
-#define PGSTAT_FILE_FORMAT_ID 0x01A5BCBC
+#define PGSTAT_FILE_FORMAT_ID 0x01A5BCBD
typedef struct PgStat_ArchiverStats
{
@@ -273,6 +273,22 @@ typedef struct PgStat_CheckpointerStats
} PgStat_CheckpointerStats;
+/* Deprecated features counted by pg_stat_deprecated_features */
+typedef enum PgStat_DeprecatedFeature
+{
+ /* entries are added by later commits */
+} PgStat_DeprecatedFeature;
+
+#define PGSTAT_NUM_DEPRECATED_FEATURES 0
+
+typedef struct PgStat_DeprecatedFeatureStats
+{
+ PgStat_Counter usage_count;
+ TimestampTz last_used;
+ TimestampTz stat_reset_timestamp;
+} PgStat_DeprecatedFeatureStats;
+
+
/*
* Types related to counting IO operations
*/
@@ -608,6 +624,15 @@ extern void pgstat_report_checkpointer(void);
extern PgStat_CheckpointerStats *pgstat_fetch_stat_checkpointer(void);
+/*
+ * Functions in pgstat_deprecated_features.c
+ */
+
+extern void pgstat_count_deprecated_feature(PgStat_DeprecatedFeature feature);
+extern const char *pgstat_get_deprecated_feature_name(int idx);
+extern PgStat_DeprecatedFeatureStats *pgstat_fetch_deprecated_features(void);
+
+
/*
* Functions in pgstat_io.c
*/
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 3ca4f454895..24d2630a092 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -453,6 +453,13 @@ typedef struct PgStatShared_Checkpointer
PgStat_CheckpointerStats reset_offset;
} PgStatShared_Checkpointer;
+typedef struct PgStatShared_DeprecatedFeatures
+{
+ /* lock protects ->stats */
+ LWLock lock;
+ PgStat_DeprecatedFeatureStats stats[PGSTAT_NUM_DEPRECATED_FEATURES];
+} PgStatShared_DeprecatedFeatures;
+
/* Shared-memory ready PgStat_IO */
typedef struct PgStatShared_IO
{
@@ -576,6 +583,7 @@ typedef struct PgStat_ShmemControl
PgStatShared_Archiver archiver;
PgStatShared_BgWriter bgwriter;
PgStatShared_Checkpointer checkpointer;
+ PgStatShared_DeprecatedFeatures deprecated_features;
PgStatShared_IO io;
PgStatShared_Lock lock;
PgStatShared_SLRU slru;
@@ -608,6 +616,8 @@ typedef struct PgStat_Snapshot
PgStat_CheckpointerStats checkpointer;
+ PgStat_DeprecatedFeatureStats deprecated_features[PGSTAT_NUM_DEPRECATED_FEATURES];
+
PgStat_IO io;
PgStat_Lock lock;
@@ -744,6 +754,16 @@ extern bool pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait);
extern void pgstat_database_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts);
+/*
+ * Functions in pgstat_deprecated_features.c
+ */
+
+extern bool pgstat_deprecated_features_flush_cb(bool nowait);
+extern void pgstat_deprecated_features_init_shmem_cb(void *stats);
+extern void pgstat_deprecated_features_reset_all_cb(TimestampTz ts);
+extern void pgstat_deprecated_features_snapshot_cb(void);
+
+
/*
* Functions in pgstat_function.c
*/
diff --git a/src/include/utils/pgstat_kind.h b/src/include/utils/pgstat_kind.h
index 2d78a029683..60e6380c9ef 100644
--- a/src/include/utils/pgstat_kind.h
+++ b/src/include/utils/pgstat_kind.h
@@ -35,10 +35,11 @@
#define PGSTAT_KIND_ARCHIVER 7
#define PGSTAT_KIND_BGWRITER 8
#define PGSTAT_KIND_CHECKPOINTER 9
-#define PGSTAT_KIND_IO 10
-#define PGSTAT_KIND_LOCK 11
-#define PGSTAT_KIND_SLRU 12
-#define PGSTAT_KIND_WAL 13
+#define PGSTAT_KIND_DEPRECATED_FEATURES 10
+#define PGSTAT_KIND_IO 11
+#define PGSTAT_KIND_LOCK 12
+#define PGSTAT_KIND_SLRU 13
+#define PGSTAT_KIND_WAL 14
#define PGSTAT_KIND_BUILTIN_MIN PGSTAT_KIND_DATABASE
#define PGSTAT_KIND_BUILTIN_MAX PGSTAT_KIND_WAL
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index a65a5bf0c4f..0f87f482750 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1939,6 +1939,11 @@ pg_stat_database_conflicts| SELECT oid AS datid,
pg_stat_get_db_conflict_logicalslot(oid) AS confl_active_logicalslot,
pg_stat_get_db_stat_reset_time(oid) AS stats_reset
FROM pg_database d;
+pg_stat_deprecated_features| SELECT name,
+ usage_count,
+ last_used,
+ stats_reset
+ FROM pg_stat_get_deprecated_features() d(name, usage_count, last_used, stats_reset);
pg_stat_gssapi| SELECT pid,
gss_auth AS gss_authenticated,
gss_princ AS principal,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index bbb1db3c433..1444b2eec9a 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1119,6 +1119,26 @@ SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpoi
t
(1 row)
+-- The deprecated features view should list the expected features
+SELECT name FROM pg_stat_deprecated_features ORDER BY name COLLATE "C";
+ name
+------
+(0 rows)
+
+-- Test that reset_shared with deprecated_features specified as the stats type works
+SELECT max(stats_reset) AS deprecated_features_reset_ts FROM pg_stat_deprecated_features \gset
+SELECT pg_stat_reset_shared('deprecated_features');
+ pg_stat_reset_shared
+----------------------
+
+(1 row)
+
+SELECT max(stats_reset) > :'deprecated_features_reset_ts'::timestamptz FROM pg_stat_deprecated_features;
+ ?column?
+----------
+ t
+(1 row)
+
-- Test that reset_shared with recovery_prefetch specified as the stats type works
SELECT stats_reset AS recovery_prefetch_reset_ts FROM pg_stat_recovery_prefetch \gset
SELECT pg_stat_reset_shared('recovery_prefetch');
@@ -1164,7 +1184,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", "lock", "recovery_prefetch", "slru", or "wal".
+HINT: Target must be "archiver", "bgwriter", "checkpointer", "deprecated_features", "io", "lock", "recovery_prefetch", "slru", or "wal".
-- 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
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 610fd21fae4..f0711c41a87 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -513,6 +513,14 @@ SELECT stats_reset AS checkpointer_reset_ts FROM pg_stat_checkpointer \gset
SELECT pg_stat_reset_shared('checkpointer');
SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpointer;
+-- The deprecated features view should list the expected features
+SELECT name FROM pg_stat_deprecated_features ORDER BY name COLLATE "C";
+
+-- Test that reset_shared with deprecated_features specified as the stats type works
+SELECT max(stats_reset) AS deprecated_features_reset_ts FROM pg_stat_deprecated_features \gset
+SELECT pg_stat_reset_shared('deprecated_features');
+SELECT max(stats_reset) > :'deprecated_features_reset_ts'::timestamptz FROM pg_stat_deprecated_features;
+
-- Test that reset_shared with recovery_prefetch specified as the stats type works
SELECT stats_reset AS recovery_prefetch_reset_ts FROM pg_stat_recovery_prefetch \gset
SELECT pg_stat_reset_shared('recovery_prefetch');
--
2.47.3
From 7801e223034498a51ca7f60a61de5a9c181b1797 Mon Sep 17 00:00:00 2001
From: Shinya Kato <[email protected]>
Date: Thu, 11 Jun 2026 16:04:30 +0900
Subject: [PATCH v1 2/5] Count md5_password_set in pg_stat_deprecated_features
---
doc/src/sgml/monitoring.sgml | 9 ++++++
src/backend/libpq/crypt.c | 19 +++++++-----
.../activity/pgstat_deprecated_features.c | 2 +-
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 5 ++--
src/test/regress/expected/stats.out | 30 +++++++++++++++++--
src/test/regress/sql/stats.sql | 15 ++++++++++
7 files changed, 68 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 25f993496d8..5195d160dc4 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3501,6 +3501,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
The tracked deprecated features are:
<variablelist>
+ <varlistentry>
+ <term><literal>md5_password_set</literal></term>
+ <listitem>
+ <para>
+ Setting an MD5-encrypted password, e.g., with <command>CREATE
+ ROLE</command> or <command>ALTER ROLE</command>.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
</sect2>
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 28857773d9c..345a9d43698 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -21,6 +21,7 @@
#include "libpq/crypt.h"
#include "libpq/scram.h"
#include "miscadmin.h"
+#include "pgstat.h"
#include "utils/builtins.h"
#include "utils/memutils.h"
#include "utils/syscache.h"
@@ -241,13 +242,17 @@ encrypt_password(PasswordType target_type, const char *role,
MAX_ENCRYPTED_PASSWORD_LEN)));
}
- if (md5_password_warnings &&
- get_password_type(encrypted_password) == PASSWORD_TYPE_MD5)
- ereport(WARNING,
- (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
- errmsg("setting an MD5-encrypted password"),
- errdetail("MD5 password support is deprecated and will be removed in a future release of PostgreSQL."),
- errhint("Refer to the PostgreSQL documentation for details about migrating to another password type.")));
+ if (get_password_type(encrypted_password) == PASSWORD_TYPE_MD5)
+ {
+ pgstat_count_deprecated_feature(PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_SET);
+
+ if (md5_password_warnings)
+ ereport(WARNING,
+ (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
+ errmsg("setting an MD5-encrypted password"),
+ errdetail("MD5 password support is deprecated and will be removed in a future release of PostgreSQL."),
+ errhint("Refer to the PostgreSQL documentation for details about migrating to another password type.")));
+ }
return encrypted_password;
}
diff --git a/src/backend/utils/activity/pgstat_deprecated_features.c b/src/backend/utils/activity/pgstat_deprecated_features.c
index 47e34b78243..489fce29069 100644
--- a/src/backend/utils/activity/pgstat_deprecated_features.c
+++ b/src/backend/utils/activity/pgstat_deprecated_features.c
@@ -27,7 +27,7 @@
* be kept in the same order, sorted by name.
*/
static const char *const pgstat_deprecated_feature_names[] = {
- /* entries are added by later commits */
+ [PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_SET] = "md5_password_set",
};
StaticAssertDecl(lengthof(pgstat_deprecated_feature_names) == PGSTAT_NUM_DEPRECATED_FEATURES,
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 2823f856424..37de65c65f6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6070,7 +6070,7 @@
prosrc => 'pg_stat_get_lock' },
{ oid => '8987', descr => 'statistics: deprecated feature usage',
- proname => 'pg_stat_get_deprecated_features', prorows => '0',
+ proname => 'pg_stat_get_deprecated_features', prorows => '1',
proretset => 't', provolatile => 'v', proparallel => 'r',
prorettype => 'record', proargtypes => '',
proallargtypes => '{text,int8,timestamptz,timestamptz}',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index a43b56f247d..241e7757492 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -276,10 +276,11 @@ typedef struct PgStat_CheckpointerStats
/* Deprecated features counted by pg_stat_deprecated_features */
typedef enum PgStat_DeprecatedFeature
{
- /* entries are added by later commits */
+ PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_SET,
} PgStat_DeprecatedFeature;
-#define PGSTAT_NUM_DEPRECATED_FEATURES 0
+#define PGSTAT_NUM_DEPRECATED_FEATURES \
+ (PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_SET + 1)
typedef struct PgStat_DeprecatedFeatureStats
{
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 1444b2eec9a..9f93a450a53 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1077,6 +1077,29 @@ SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHER
t
(1 row)
+-- Test deprecated feature usage statistics
+-- Setting an MD5-encrypted password must be counted even when
+-- md5_password_warnings is disabled.
+SET md5_password_warnings = off;
+SELECT usage_count AS deprecated_md5_set_count
+ FROM pg_stat_deprecated_features WHERE name = 'md5_password_set' \gset
+CREATE ROLE regress_stats_md5 PASSWORD 'md5912f4d9b58e4731e3e9f6dd9eebbbca5';
+DROP ROLE regress_stats_md5;
+RESET md5_password_warnings;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT usage_count = :deprecated_md5_set_count + 1 AS incremented,
+ last_used IS NOT NULL AS has_last_used
+ FROM pg_stat_deprecated_features WHERE name = 'md5_password_set';
+ incremented | has_last_used
+-------------+---------------
+ t | t
+(1 row)
+
-- Test that reset_shared with archiver specified as the stats type works
SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
SELECT pg_stat_reset_shared('archiver');
@@ -1121,9 +1144,10 @@ SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpoi
-- The deprecated features view should list the expected features
SELECT name FROM pg_stat_deprecated_features ORDER BY name COLLATE "C";
- name
-------
-(0 rows)
+ name
+------------------
+ md5_password_set
+(1 row)
-- Test that reset_shared with deprecated_features specified as the stats type works
SELECT max(stats_reset) AS deprecated_features_reset_ts FROM pg_stat_deprecated_features \gset
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index f0711c41a87..bf88b08c4cf 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -498,6 +498,21 @@ SELECT pg_stat_reset_slru();
SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'commit_timestamp';
SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'notify';
+-- Test deprecated feature usage statistics
+
+-- Setting an MD5-encrypted password must be counted even when
+-- md5_password_warnings is disabled.
+SET md5_password_warnings = off;
+SELECT usage_count AS deprecated_md5_set_count
+ FROM pg_stat_deprecated_features WHERE name = 'md5_password_set' \gset
+CREATE ROLE regress_stats_md5 PASSWORD 'md5912f4d9b58e4731e3e9f6dd9eebbbca5';
+DROP ROLE regress_stats_md5;
+RESET md5_password_warnings;
+SELECT pg_stat_force_next_flush();
+SELECT usage_count = :deprecated_md5_set_count + 1 AS incremented,
+ last_used IS NOT NULL AS has_last_used
+ FROM pg_stat_deprecated_features WHERE name = 'md5_password_set';
+
-- Test that reset_shared with archiver specified as the stats type works
SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
SELECT pg_stat_reset_shared('archiver');
--
2.47.3
From 54bd07a9d54ac0e8e7df80bfae97d6117261f6b3 Mon Sep 17 00:00:00 2001
From: Shinya Kato <[email protected]>
Date: Thu, 11 Jun 2026 16:15:32 +0900
Subject: [PATCH v1 3/5] Count md5_password_auth in pg_stat_deprecated_features
---
doc/src/sgml/monitoring.sgml | 11 +++++++++++
src/backend/libpq/crypt.c | 5 +++++
.../utils/activity/pgstat_deprecated_features.c | 1 +
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 1 +
src/test/authentication/t/001_password.pl | 10 ++++++++++
src/test/regress/expected/stats.out | 7 ++++---
7 files changed, 33 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 5195d160dc4..4e5d5cc7e84 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3501,6 +3501,17 @@ description | Waiting for a newly initialized WAL file to reach durable storage
The tracked deprecated features are:
<variablelist>
+ <varlistentry>
+ <term><literal>md5_password_auth</literal></term>
+ <listitem>
+ <para>
+ Successful authentication against an MD5-encrypted password, via
+ either <literal>md5</literal> or <literal>password</literal>
+ authentication.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>md5_password_set</literal></term>
<listitem>
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 345a9d43698..e2030db48b6 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -309,6 +309,8 @@ md5_crypt_verify(const char *role, const char *shadow_pass,
retval = STATUS_OK;
+ pgstat_count_deprecated_feature(PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_AUTH);
+
oldcontext = MemoryContextSwitchTo(TopMemoryContext);
warning = pstrdup(_("authenticated with an MD5-encrypted password"));
@@ -385,7 +387,10 @@ plain_crypt_verify(const char *role, const char *shadow_pass,
}
if (strlen(crypt_client_pass) == strlen(shadow_pass) &&
timingsafe_bcmp(crypt_client_pass, shadow_pass, strlen(shadow_pass)) == 0)
+ {
+ pgstat_count_deprecated_feature(PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_AUTH);
return STATUS_OK;
+ }
else
{
*logdetail = psprintf(_("Password does not match for user \"%s\"."),
diff --git a/src/backend/utils/activity/pgstat_deprecated_features.c b/src/backend/utils/activity/pgstat_deprecated_features.c
index 489fce29069..770ed195741 100644
--- a/src/backend/utils/activity/pgstat_deprecated_features.c
+++ b/src/backend/utils/activity/pgstat_deprecated_features.c
@@ -27,6 +27,7 @@
* be kept in the same order, sorted by name.
*/
static const char *const pgstat_deprecated_feature_names[] = {
+ [PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_AUTH] = "md5_password_auth",
[PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_SET] = "md5_password_set",
};
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 37de65c65f6..ab4df79e1ef 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6070,7 +6070,7 @@
prosrc => 'pg_stat_get_lock' },
{ oid => '8987', descr => 'statistics: deprecated feature usage',
- proname => 'pg_stat_get_deprecated_features', prorows => '1',
+ proname => 'pg_stat_get_deprecated_features', prorows => '2',
proretset => 't', provolatile => 'v', proparallel => 'r',
prorettype => 'record', proargtypes => '',
proallargtypes => '{text,int8,timestamptz,timestamptz}',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 241e7757492..8c483ea05f6 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -276,6 +276,7 @@ typedef struct PgStat_CheckpointerStats
/* Deprecated features counted by pg_stat_deprecated_features */
typedef enum PgStat_DeprecatedFeature
{
+ PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_AUTH,
PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_SET,
} PgStat_DeprecatedFeature;
diff --git a/src/test/authentication/t/001_password.pl b/src/test/authentication/t/001_password.pl
index fca78fc4d6a..11ae5e6b8d3 100644
--- a/src/test/authentication/t/001_password.pl
+++ b/src/test/authentication/t/001_password.pl
@@ -521,6 +521,16 @@ SKIP:
log_like => [
qr/connection authenticated: identity="md5_role_no_warnings" method=md5/
]);
+
+ # This session is authenticated with an MD5-encrypted password, so the
+ # md5_password_auth deprecated feature counter must be positive once
+ # this session's pending statistics have been flushed.
+ $res = $node->safe_psql(
+ 'postgres',
+ "SELECT pg_stat_force_next_flush();
+SELECT usage_count >= 1 FROM pg_stat_deprecated_features WHERE name = 'md5_password_auth';",
+ connstr => "user=md5_role");
+ is($res, qq(\nt), 'md5_password_auth deprecated feature usage counted');
}
# require_auth succeeds with SCRAM required.
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 9f93a450a53..ecf5f210c59 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1144,10 +1144,11 @@ SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpoi
-- The deprecated features view should list the expected features
SELECT name FROM pg_stat_deprecated_features ORDER BY name COLLATE "C";
- name
-------------------
+ name
+-------------------
+ md5_password_auth
md5_password_set
-(1 row)
+(2 rows)
-- Test that reset_shared with deprecated_features specified as the stats type works
SELECT max(stats_reset) AS deprecated_features_reset_ts FROM pg_stat_deprecated_features \gset
--
2.47.3
From a04aa2eb0c99227e844e1e2a158316fefcd1e11b Mon Sep 17 00:00:00 2001
From: Shinya Kato <[email protected]>
Date: Thu, 11 Jun 2026 16:17:31 +0900
Subject: [PATCH v1 4/5] Count global_temporary_table in
pg_stat_deprecated_features
---
doc/src/sgml/monitoring.sgml | 10 +++++++
src/backend/parser/gram.y | 5 ++++
.../activity/pgstat_deprecated_features.c | 1 +
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 1 +
src/test/regress/expected/stats.out | 30 +++++++++++++++++--
src/test/regress/sql/stats.sql | 11 +++++++
7 files changed, 56 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 4e5d5cc7e84..5b556075006 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3501,6 +3501,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
The tracked deprecated features are:
<variablelist>
+ <varlistentry>
+ <term><literal>global_temporary_table</literal></term>
+ <listitem>
+ <para>
+ Use of the deprecated <literal>GLOBAL</literal> keyword in temporary
+ table creation.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>md5_password_auth</literal></term>
<listitem>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ff4e1388c55..a0325cd8d1e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -58,6 +58,7 @@
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "parser/parser.h"
+#include "pgstat.h"
#include "utils/datetime.h"
#include "utils/xml.h"
@@ -3926,6 +3927,7 @@ OptTemp: TEMPORARY { $$ = RELPERSISTENCE_TEMP; }
| LOCAL TEMP { $$ = RELPERSISTENCE_TEMP; }
| GLOBAL TEMPORARY
{
+ pgstat_count_deprecated_feature(PGSTAT_DEPRECATED_FEATURE_GLOBAL_TEMPORARY_TABLE);
ereport(WARNING,
(errmsg("GLOBAL is deprecated in temporary table creation"),
parser_errposition(@1)));
@@ -3933,6 +3935,7 @@ OptTemp: TEMPORARY { $$ = RELPERSISTENCE_TEMP; }
}
| GLOBAL TEMP
{
+ pgstat_count_deprecated_feature(PGSTAT_DEPRECATED_FEATURE_GLOBAL_TEMPORARY_TABLE);
ereport(WARNING,
(errmsg("GLOBAL is deprecated in temporary table creation"),
parser_errposition(@1)));
@@ -13972,6 +13975,7 @@ OptTempTableName:
}
| GLOBAL TEMPORARY opt_table qualified_name
{
+ pgstat_count_deprecated_feature(PGSTAT_DEPRECATED_FEATURE_GLOBAL_TEMPORARY_TABLE);
ereport(WARNING,
(errmsg("GLOBAL is deprecated in temporary table creation"),
parser_errposition(@1)));
@@ -13980,6 +13984,7 @@ OptTempTableName:
}
| GLOBAL TEMP opt_table qualified_name
{
+ pgstat_count_deprecated_feature(PGSTAT_DEPRECATED_FEATURE_GLOBAL_TEMPORARY_TABLE);
ereport(WARNING,
(errmsg("GLOBAL is deprecated in temporary table creation"),
parser_errposition(@1)));
diff --git a/src/backend/utils/activity/pgstat_deprecated_features.c b/src/backend/utils/activity/pgstat_deprecated_features.c
index 770ed195741..fadffc1d19d 100644
--- a/src/backend/utils/activity/pgstat_deprecated_features.c
+++ b/src/backend/utils/activity/pgstat_deprecated_features.c
@@ -27,6 +27,7 @@
* be kept in the same order, sorted by name.
*/
static const char *const pgstat_deprecated_feature_names[] = {
+ [PGSTAT_DEPRECATED_FEATURE_GLOBAL_TEMPORARY_TABLE] = "global_temporary_table",
[PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_AUTH] = "md5_password_auth",
[PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_SET] = "md5_password_set",
};
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index ab4df79e1ef..6d11271d095 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6070,7 +6070,7 @@
prosrc => 'pg_stat_get_lock' },
{ oid => '8987', descr => 'statistics: deprecated feature usage',
- proname => 'pg_stat_get_deprecated_features', prorows => '2',
+ proname => 'pg_stat_get_deprecated_features', prorows => '3',
proretset => 't', provolatile => 'v', proparallel => 'r',
prorettype => 'record', proargtypes => '',
proallargtypes => '{text,int8,timestamptz,timestamptz}',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 8c483ea05f6..90bc163cc00 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -276,6 +276,7 @@ typedef struct PgStat_CheckpointerStats
/* Deprecated features counted by pg_stat_deprecated_features */
typedef enum PgStat_DeprecatedFeature
{
+ PGSTAT_DEPRECATED_FEATURE_GLOBAL_TEMPORARY_TABLE,
PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_AUTH,
PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_SET,
} PgStat_DeprecatedFeature;
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index ecf5f210c59..50e5e18ef0b 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1078,6 +1078,29 @@ SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHER
(1 row)
-- Test deprecated feature usage statistics
+-- Creating a temporary table with the deprecated GLOBAL keyword must be
+-- counted, in addition to emitting a warning.
+SELECT usage_count AS deprecated_global_temp_count
+ FROM pg_stat_deprecated_features WHERE name = 'global_temporary_table' \gset
+CREATE GLOBAL TEMPORARY TABLE stats_global_temp (a int);
+WARNING: GLOBAL is deprecated in temporary table creation
+LINE 1: CREATE GLOBAL TEMPORARY TABLE stats_global_temp (a int);
+ ^
+DROP TABLE stats_global_temp;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT usage_count = :deprecated_global_temp_count + 1 AS incremented,
+ last_used IS NOT NULL AS has_last_used
+ FROM pg_stat_deprecated_features WHERE name = 'global_temporary_table';
+ incremented | has_last_used
+-------------+---------------
+ t | t
+(1 row)
+
-- Setting an MD5-encrypted password must be counted even when
-- md5_password_warnings is disabled.
SET md5_password_warnings = off;
@@ -1144,11 +1167,12 @@ SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpoi
-- The deprecated features view should list the expected features
SELECT name FROM pg_stat_deprecated_features ORDER BY name COLLATE "C";
- name
--------------------
+ name
+------------------------
+ global_temporary_table
md5_password_auth
md5_password_set
-(2 rows)
+(3 rows)
-- Test that reset_shared with deprecated_features specified as the stats type works
SELECT max(stats_reset) AS deprecated_features_reset_ts FROM pg_stat_deprecated_features \gset
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index bf88b08c4cf..24e196212af 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -500,6 +500,17 @@ SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHER
-- Test deprecated feature usage statistics
+-- Creating a temporary table with the deprecated GLOBAL keyword must be
+-- counted, in addition to emitting a warning.
+SELECT usage_count AS deprecated_global_temp_count
+ FROM pg_stat_deprecated_features WHERE name = 'global_temporary_table' \gset
+CREATE GLOBAL TEMPORARY TABLE stats_global_temp (a int);
+DROP TABLE stats_global_temp;
+SELECT pg_stat_force_next_flush();
+SELECT usage_count = :deprecated_global_temp_count + 1 AS incremented,
+ last_used IS NOT NULL AS has_last_used
+ FROM pg_stat_deprecated_features WHERE name = 'global_temporary_table';
+
-- Setting an MD5-encrypted password must be counted even when
-- md5_password_warnings is disabled.
SET md5_password_warnings = off;
--
2.47.3
From 6db4afe85564972e3914b8e88b547d08af37e132 Mon Sep 17 00:00:00 2001
From: Shinya Kato <[email protected]>
Date: Thu, 11 Jun 2026 16:23:39 +0900
Subject: [PATCH v1 5/5] Count local_temporary_table in
pg_stat_deprecated_features
---
doc/src/sgml/monitoring.sgml | 10 ++++++++
src/backend/parser/gram.y | 14 +++++++++--
.../activity/pgstat_deprecated_features.c | 1 +
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 1 +
src/test/regress/expected/stats.out | 23 ++++++++++++++++++-
src/test/regress/sql/stats.sql | 11 +++++++++
7 files changed, 58 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 5b556075006..f30e7bc9364 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3511,6 +3511,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>local_temporary_table</literal></term>
+ <listitem>
+ <para>
+ Use of the deprecated <literal>LOCAL</literal> keyword in temporary
+ table creation.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>md5_password_auth</literal></term>
<listitem>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a0325cd8d1e..402cc16c828 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3923,8 +3923,16 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
*/
OptTemp: TEMPORARY { $$ = RELPERSISTENCE_TEMP; }
| TEMP { $$ = RELPERSISTENCE_TEMP; }
- | LOCAL TEMPORARY { $$ = RELPERSISTENCE_TEMP; }
- | LOCAL TEMP { $$ = RELPERSISTENCE_TEMP; }
+ | LOCAL TEMPORARY
+ {
+ pgstat_count_deprecated_feature(PGSTAT_DEPRECATED_FEATURE_LOCAL_TEMPORARY_TABLE);
+ $$ = RELPERSISTENCE_TEMP;
+ }
+ | LOCAL TEMP
+ {
+ pgstat_count_deprecated_feature(PGSTAT_DEPRECATED_FEATURE_LOCAL_TEMPORARY_TABLE);
+ $$ = RELPERSISTENCE_TEMP;
+ }
| GLOBAL TEMPORARY
{
pgstat_count_deprecated_feature(PGSTAT_DEPRECATED_FEATURE_GLOBAL_TEMPORARY_TABLE);
@@ -13965,11 +13973,13 @@ OptTempTableName:
}
| LOCAL TEMPORARY opt_table qualified_name
{
+ pgstat_count_deprecated_feature(PGSTAT_DEPRECATED_FEATURE_LOCAL_TEMPORARY_TABLE);
$$ = $4;
$$->relpersistence = RELPERSISTENCE_TEMP;
}
| LOCAL TEMP opt_table qualified_name
{
+ pgstat_count_deprecated_feature(PGSTAT_DEPRECATED_FEATURE_LOCAL_TEMPORARY_TABLE);
$$ = $4;
$$->relpersistence = RELPERSISTENCE_TEMP;
}
diff --git a/src/backend/utils/activity/pgstat_deprecated_features.c b/src/backend/utils/activity/pgstat_deprecated_features.c
index fadffc1d19d..a9b18711825 100644
--- a/src/backend/utils/activity/pgstat_deprecated_features.c
+++ b/src/backend/utils/activity/pgstat_deprecated_features.c
@@ -28,6 +28,7 @@
*/
static const char *const pgstat_deprecated_feature_names[] = {
[PGSTAT_DEPRECATED_FEATURE_GLOBAL_TEMPORARY_TABLE] = "global_temporary_table",
+ [PGSTAT_DEPRECATED_FEATURE_LOCAL_TEMPORARY_TABLE] = "local_temporary_table",
[PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_AUTH] = "md5_password_auth",
[PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_SET] = "md5_password_set",
};
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6d11271d095..ebc4105b1c8 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6070,7 +6070,7 @@
prosrc => 'pg_stat_get_lock' },
{ oid => '8987', descr => 'statistics: deprecated feature usage',
- proname => 'pg_stat_get_deprecated_features', prorows => '3',
+ proname => 'pg_stat_get_deprecated_features', prorows => '4',
proretset => 't', provolatile => 'v', proparallel => 'r',
prorettype => 'record', proargtypes => '',
proallargtypes => '{text,int8,timestamptz,timestamptz}',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 90bc163cc00..a19a12564b6 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -277,6 +277,7 @@ typedef struct PgStat_CheckpointerStats
typedef enum PgStat_DeprecatedFeature
{
PGSTAT_DEPRECATED_FEATURE_GLOBAL_TEMPORARY_TABLE,
+ PGSTAT_DEPRECATED_FEATURE_LOCAL_TEMPORARY_TABLE,
PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_AUTH,
PGSTAT_DEPRECATED_FEATURE_MD5_PASSWORD_SET,
} PgStat_DeprecatedFeature;
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 50e5e18ef0b..eee3563f4ad 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1101,6 +1101,26 @@ SELECT usage_count = :deprecated_global_temp_count + 1 AS incremented,
t | t
(1 row)
+-- Creating a temporary table with the deprecated LOCAL keyword must be
+-- counted, even though it is accepted silently.
+SELECT usage_count AS deprecated_local_temp_count
+ FROM pg_stat_deprecated_features WHERE name = 'local_temporary_table' \gset
+CREATE LOCAL TEMPORARY TABLE stats_local_temp (a int);
+DROP TABLE stats_local_temp;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT usage_count = :deprecated_local_temp_count + 1 AS incremented,
+ last_used IS NOT NULL AS has_last_used
+ FROM pg_stat_deprecated_features WHERE name = 'local_temporary_table';
+ incremented | has_last_used
+-------------+---------------
+ t | t
+(1 row)
+
-- Setting an MD5-encrypted password must be counted even when
-- md5_password_warnings is disabled.
SET md5_password_warnings = off;
@@ -1170,9 +1190,10 @@ SELECT name FROM pg_stat_deprecated_features ORDER BY name COLLATE "C";
name
------------------------
global_temporary_table
+ local_temporary_table
md5_password_auth
md5_password_set
-(3 rows)
+(4 rows)
-- Test that reset_shared with deprecated_features specified as the stats type works
SELECT max(stats_reset) AS deprecated_features_reset_ts FROM pg_stat_deprecated_features \gset
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 24e196212af..9be3c4873cc 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -511,6 +511,17 @@ SELECT usage_count = :deprecated_global_temp_count + 1 AS incremented,
last_used IS NOT NULL AS has_last_used
FROM pg_stat_deprecated_features WHERE name = 'global_temporary_table';
+-- Creating a temporary table with the deprecated LOCAL keyword must be
+-- counted, even though it is accepted silently.
+SELECT usage_count AS deprecated_local_temp_count
+ FROM pg_stat_deprecated_features WHERE name = 'local_temporary_table' \gset
+CREATE LOCAL TEMPORARY TABLE stats_local_temp (a int);
+DROP TABLE stats_local_temp;
+SELECT pg_stat_force_next_flush();
+SELECT usage_count = :deprecated_local_temp_count + 1 AS incremented,
+ last_used IS NOT NULL AS has_last_used
+ FROM pg_stat_deprecated_features WHERE name = 'local_temporary_table';
+
-- Setting an MD5-encrypted password must be counted even when
-- md5_password_warnings is disabled.
SET md5_password_warnings = off;
--
2.47.3
