Rework output format of pg_dependenciesHEADmaster

The existing format of pg_dependencies uses a single-object JSON
structure, with each key value embedding all the knowledge about the
set attributes tracked, like:
{"1 => 5": 1.000000, "5 => 1": 0.423130}

While this is a very compact format, it is confusing to read and it is
difficult to manipulate the values within the object, particularly when
tracking multiple attributes.

The new output format introduced in this commit is a JSON array of
objects, with:
- A key named "degree", with a float value.
- A key named "attributes", with an array of attribute numbers.
- A key named "dependency", with an attribute number.

The values use the same underlying type as previously when printed, with
a new output format that shows now as follows:
[{"degree": 1.000000, "attributes": [1], "dependency": 5},
 {"degree": 0.423130, "attributes": [5], "dependency": 1}]

This new format will become handy for a follow-up set of changes, so as
it becomes possible to inject extended statistics rather than require an
ANALYZE, like in a dump/restore sequence or after pg_upgrade on a new
cluster.

This format has been suggested by Tomas Vondra.  The key names are
defined in the header introduced by 1f927cce4498, to ease the
integration of frontend-specific changes that are still under
discussion.  (Again a personal note: if anybody comes up with better
name for the keys, of course feel free.)

The bulk of the changes come from the regression tests, where
jsonb_pretty() is now used to make the outputs generated easier to
parse.

Author: Corey Huinker <[email protected]>
Reviewed-by: Jian He <[email protected]>
Reviewed-by: Michael Paquier <[email protected]>
Discussion: https://postgr.es/m/CADkLM=dpz3KFnqP-dgJ-zvRvtjsa8UZv8wDAQdqho=qN3kX0Zg@mail.gmail.com

4 files changed, 122 insertions, 28 deletions

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, ", ");
-
-			appendStringInfo(&str, "%d", dependency->attributes[j]);
-		}
-		appendStringInfo(&str, "\": %f", dependency->degree);
+		if (dependency->nattributes <= 1)
+			elog(ERROR, "invalid zero-length nattributes array in MVDependencies");
+
+		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/include/statistics/statistics_format.h b/src/include/statistics/statistics_format.h
index fb0ae58ff3b..0d3ce72d081 100644
--- a/src/include/statistics/statistics_format.h
+++ b/src/include/statistics/statistics_format.h
@@ -29,4 +29,19 @@
 #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/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''');