v13-0002-Refactor-output-format-of-pg_dependencies.patch
text/x-patch
Filename: v13-0002-Refactor-output-format-of-pg_dependencies.patch
Type: text/x-patch
Part: 1
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v13-0002
Subject: Refactor output format of pg_dependencies.
| File | + | − |
|---|---|---|
| doc/src/sgml/perform.sgml | 3 | 3 |
| src/backend/utils/adt/pg_dependencies.c | 16 | 15 |
| src/include/statistics/statistics_format.h | 18 | 2 |
| src/test/regress/expected/stats_ext.out | 86 | 9 |
| src/test/regress/sql/stats_ext.sql | 4 | 3 |
From 1a29c0948cb9f1d261b96c71c820c5e113d09599 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Tue, 11 Nov 2025 14:15:27 +0900
Subject: [PATCH v13 2/7] Refactor output format of pg_dependencies.
The existing format of pg_dependencies uses a single-object JSON structure
where each key is itself a comma-separated list of attnums. While this
is a very compact format, it's confusing to read and is difficult to
manipulate values within the object. This wasn't a concern until
statistics import functions were introduced, enabling users to inject
hypothetical statistics into an object to observe their effect on the
query planner.
The new format is an array of objects, each object must have the keys
"attributes", which must contain an array of attnums, "dependency",
which must be an integer, and "degree", which must be a float.
The change in format is adequately described from the changes to
src/test/regress/expected/stats_ext.out so description here is
redundant.
---
src/include/statistics/statistics_format.h | 20 ++++-
src/backend/utils/adt/pg_dependencies.c | 31 +++----
src/test/regress/expected/stats_ext.out | 95 ++++++++++++++++++++--
src/test/regress/sql/stats_ext.sql | 7 +-
doc/src/sgml/perform.sgml | 6 +-
5 files changed, 127 insertions(+), 32 deletions(-)
diff --git a/src/include/statistics/statistics_format.h b/src/include/statistics/statistics_format.h
index ba97c0880be..40655b9ec3b 100644
--- a/src/include/statistics/statistics_format.h
+++ b/src/include/statistics/statistics_format.h
@@ -20,11 +20,27 @@
*
* [{"ndistinct": 11, "attributes": [3,4]},
* {"ndistinct": 11, "attributes": [3,6]},
- * {"ndistinct": 11, "attributes": [4,6]},
- * {"ndistinct": 11, "attributes": [3,4,6]}]
+ * ... ]
+ *
* ----------
*/
#define PG_NDISTINCT_KEY_ATTRIBUTES "attributes"
#define PG_NDISTINCT_KEY_NDISTINCT "ndistinct"
+
+/* ----------
+ * pg_dependencies in human-readable format is a JSON array made of elements
+ * with a predefined set of keys, like:
+ *
+ * [{"degree": 1.000000, "attributes": [3], "dependency": 4},
+ * {"degree": 1.000000, "attributes": [3], "dependency": 6},
+ * ... ]
+ *
+ * ----------
+ */
+
+#define PG_DEPENDENCIES_KEY_ATTRIBUTES "attributes"
+#define PG_DEPENDENCIES_KEY_DEPENDENCY "dependency"
+#define PG_DEPENDENCIES_KEY_DEGREE "degree"
+
#endif /* STATISTICS_FORMAT_H */
diff --git a/src/backend/utils/adt/pg_dependencies.c b/src/backend/utils/adt/pg_dependencies.c
index a0a9440fd5c..87181aa00e9 100644
--- a/src/backend/utils/adt/pg_dependencies.c
+++ b/src/backend/utils/adt/pg_dependencies.c
@@ -16,6 +16,7 @@
#include "lib/stringinfo.h"
#include "statistics/extended_stats_internal.h"
+#include "statistics/statistics_format.h"
#include "utils/fmgrprotos.h"
/*
@@ -46,34 +47,34 @@ pg_dependencies_out(PG_FUNCTION_ARGS)
{
bytea *data = PG_GETARG_BYTEA_PP(0);
MVDependencies *dependencies = statext_dependencies_deserialize(data);
- int i,
- j;
StringInfoData str;
initStringInfo(&str);
- appendStringInfoChar(&str, '{');
+ appendStringInfoChar(&str, '[');
- for (i = 0; i < dependencies->ndeps; i++)
+ for (int i = 0; i < dependencies->ndeps; i++)
{
MVDependency *dependency = dependencies->deps[i];
if (i > 0)
appendStringInfoString(&str, ", ");
- appendStringInfoChar(&str, '"');
- for (j = 0; j < dependency->nattributes; j++)
- {
- if (j == dependency->nattributes - 1)
- appendStringInfoString(&str, " => ");
- else if (j > 0)
- appendStringInfoString(&str, ", ");
+ if (dependency->nattributes <= 1)
+ elog(ERROR, "invalid zero-length nattributes array in MVDependencies");
- appendStringInfo(&str, "%d", dependency->attributes[j]);
- }
- appendStringInfo(&str, "\": %f", dependency->degree);
+ appendStringInfo(&str, "{\"" PG_DEPENDENCIES_KEY_ATTRIBUTES "\": [%d",
+ dependency->attributes[0]);
+
+ for (int j = 1; j < dependency->nattributes - 1; j++)
+ appendStringInfo(&str, ", %d", dependency->attributes[j]);
+
+ appendStringInfo(&str, "], \"" PG_DEPENDENCIES_KEY_DEPENDENCY "\": %d, "
+ "\"" PG_DEPENDENCIES_KEY_DEGREE "\": %f}",
+ dependency->attributes[dependency->nattributes - 1],
+ dependency->degree);
}
- appendStringInfoChar(&str, '}');
+ appendStringInfoChar(&str, ']');
PG_RETURN_CSTRING(str.data);
}
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index e9379afe39e..5a4077f8ed5 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -196,7 +196,8 @@ Statistics objects:
"public.ab1_a_b_stats" ON a, b FROM ab1; STATISTICS 0
ANALYZE ab1;
-SELECT stxname, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct, stxddependencies, stxdmcv, stxdinherit
+SELECT stxname, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct,
+ jsonb_pretty(d.stxddependencies::text::jsonb) AS stxddependencies, stxdmcv, stxdinherit
FROM pg_statistic_ext s LEFT JOIN pg_statistic_ext_data d ON (d.stxoid = s.oid)
WHERE s.stxname = 'ab1_a_b_stats';
stxname | stxdndistinct | stxddependencies | stxdmcv | stxdinherit
@@ -1433,10 +1434,48 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies;
ANALYZE functional_dependencies;
-- print the detected dependencies
-SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
- dependencies
-------------------------------------------------------------------------------------------------------------
- {"3 => 4": 1.000000, "3 => 6": 1.000000, "4 => 6": 1.000000, "3, 4 => 6": 1.000000, "3, 6 => 4": 1.000000}
+SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
+ dependencies
+-----------------------------
+ [ +
+ { +
+ "degree": 1.000000,+
+ "attributes": [ +
+ 3 +
+ ], +
+ "dependency": 4 +
+ }, +
+ { +
+ "degree": 1.000000,+
+ "attributes": [ +
+ 3 +
+ ], +
+ "dependency": 6 +
+ }, +
+ { +
+ "degree": 1.000000,+
+ "attributes": [ +
+ 4 +
+ ], +
+ "dependency": 6 +
+ }, +
+ { +
+ "degree": 1.000000,+
+ "attributes": [ +
+ 3, +
+ 4 +
+ ], +
+ "dependency": 6 +
+ }, +
+ { +
+ "degree": 1.000000,+
+ "attributes": [ +
+ 3, +
+ 6 +
+ ], +
+ "dependency": 4 +
+ } +
+ ]
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
@@ -1775,10 +1814,48 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
CREATE STATISTICS func_deps_stat (dependencies) ON (a * 2), upper(b), (c + 1) FROM functional_dependencies;
ANALYZE functional_dependencies;
-- print the detected dependencies
-SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
- dependencies
-------------------------------------------------------------------------------------------------------------------------
- {"-1 => -2": 1.000000, "-1 => -3": 1.000000, "-2 => -3": 1.000000, "-1, -2 => -3": 1.000000, "-1, -3 => -2": 1.000000}
+SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
+ dependencies
+-----------------------------
+ [ +
+ { +
+ "degree": 1.000000,+
+ "attributes": [ +
+ -1 +
+ ], +
+ "dependency": -2 +
+ }, +
+ { +
+ "degree": 1.000000,+
+ "attributes": [ +
+ -1 +
+ ], +
+ "dependency": -3 +
+ }, +
+ { +
+ "degree": 1.000000,+
+ "attributes": [ +
+ -2 +
+ ], +
+ "dependency": -3 +
+ }, +
+ { +
+ "degree": 1.000000,+
+ "attributes": [ +
+ -1, +
+ -2 +
+ ], +
+ "dependency": -3 +
+ }, +
+ { +
+ "degree": 1.000000,+
+ "attributes": [ +
+ -1, +
+ -3 +
+ ], +
+ "dependency": -2 +
+ } +
+ ]
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = 2 AND upper(b) = ''1''');
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index fc4aee6d839..94e2139c504 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -125,7 +125,8 @@ ALTER TABLE ab1 ALTER a SET STATISTICS -1;
ALTER STATISTICS ab1_a_b_stats SET STATISTICS 0;
\d ab1
ANALYZE ab1;
-SELECT stxname, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct, stxddependencies, stxdmcv, stxdinherit
+SELECT stxname, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct,
+ jsonb_pretty(d.stxddependencies::text::jsonb) AS stxddependencies, stxdmcv, stxdinherit
FROM pg_statistic_ext s LEFT JOIN pg_statistic_ext_data d ON (d.stxoid = s.oid)
WHERE s.stxname = 'ab1_a_b_stats';
ALTER STATISTICS ab1_a_b_stats SET STATISTICS -1;
@@ -708,7 +709,7 @@ CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_depen
ANALYZE functional_dependencies;
-- print the detected dependencies
-SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
+SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
@@ -844,7 +845,7 @@ CREATE STATISTICS func_deps_stat (dependencies) ON (a * 2), upper(b), (c + 1) FR
ANALYZE functional_dependencies;
-- print the detected dependencies
-SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
+SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = 2 AND upper(b) = ''1''');
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index b2dc2d27a77..014a542daf5 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -1488,9 +1488,9 @@ ANALYZE zipcodes;
SELECT stxname, stxkeys, stxddependencies
FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
WHERE stxname = 'stts';
- stxname | stxkeys | stxddependencies
----------+---------+------------------------------------------
- stts | 1 5 | {"1 => 5": 1.000000, "5 => 1": 0.423130}
+ stxname | stxkeys | stxddependencies
+---------+---------+----------------------------------------------------------------------------------------------------------------------
+ stts | 1 5 | [{"attributes": [1], "dependency": 5, "degree": 1.000000}, {"attributes": [5], "dependency": 1, "degree": 0.423130}]
(1 row)
</programlisting>
Here it can be seen that column 1 (zip code) fully determines column
--
2.51.1