Hi, While writing the recent ext.stats docs, I became annoyed by the output functions of the new types used by multivariate statistics: they are almost JSON, but not quite. Since they can become largish, I propose that we make a point of ensuring the output of those types is valid JSON, so that they can be processed by JSON tools, particularly by jsonb_pretty(). Note that the internal bytea format does not change; this is only for human consumption (or for external tools that want to examine the values), not for the planner. Also, the input function for those types rejects input, so nothing needs to care about reading these values; ANALYZE is the only valid source for them.
With the proposed attached patch, those columns look more convenient to work with: ialvherre=# select jsonb_pretty(stxndistinct::jsonb), jsonb_pretty( stxdependencies::jsonb) from pg_statistic_ext; jsonb_pretty │ jsonb_pretty ──────────────────────┼──────────────────────────── { ↵│ { ↵ "1, 2": 33178, ↵│ "1 => 2": 1.000000, ↵ "1, 5": 33178, ↵│ "1 => 5": 1.000000, ↵ "2, 5": 27435, ↵│ "5 => 1": 0.422367, ↵ "1, 2, 5": 33178↵│ "5 => 2": 0.482567, ↵ } │ "1, 2 => 5": 1.000000,↵ │ "1, 5 => 2": 1.000000,↵ │ "2, 5 => 1": 0.807367 ↵ │ } Changes: * Removed the external [ ]; the whole string is now a single JSON object. * Removed the bitmapset output artifact. (I introduced that while simplifying the code, but I now think that was a mistake). * The attribute list is the object key. * In ndistinct, the value is now an integer rather than a floating point number. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
commit 49695b5ef165b1736fab65d6443640c5529b6338[m Author: Alvaro Herrera <alvhe...@alvh.no-ip.org> AuthorDate: Thu Apr 20 16:26:27 2017 -0300 CommitDate: Thu Apr 20 16:26:27 2017 -0300 Fix extstats output funcs to emit valid JSON diff --git a/src/backend/statistics/dependencies.c b/src/backend/statistics/dependencies.c index 0890514bf7..cbc2a3c6c3 100644 --- a/src/backend/statistics/dependencies.c +++ b/src/backend/statistics/dependencies.c @@ -696,7 +696,7 @@ pg_dependencies_out(PG_FUNCTION_ARGS) MVDependencies *dependencies = statext_dependencies_deserialize(data); initStringInfo(&str); - appendStringInfoChar(&str, '['); + appendStringInfoChar(&str, '{'); for (i = 0; i < dependencies->ndeps; i++) { @@ -705,7 +705,7 @@ pg_dependencies_out(PG_FUNCTION_ARGS) if (i > 0) appendStringInfoString(&str, ", "); - appendStringInfoChar(&str, '{'); + appendStringInfoChar(&str, '"'); for (j = 0; j < dependency->nattributes; j++) { if (j == dependency->nattributes - 1) @@ -715,11 +715,10 @@ pg_dependencies_out(PG_FUNCTION_ARGS) appendStringInfo(&str, "%d", dependency->attributes[j]); } - appendStringInfo(&str, " : %f", dependency->degree); - appendStringInfoChar(&str, '}'); + appendStringInfo(&str, "\": %f", dependency->degree); } - appendStringInfoChar(&str, ']'); + appendStringInfoChar(&str, '}'); PG_RETURN_CSTRING(str.data); } diff --git a/src/backend/statistics/mvdistinct.c b/src/backend/statistics/mvdistinct.c index b77113fb39..362c912b2c 100644 --- a/src/backend/statistics/mvdistinct.c +++ b/src/backend/statistics/mvdistinct.c @@ -354,21 +354,29 @@ pg_ndistinct_out(PG_FUNCTION_ARGS) StringInfoData str; initStringInfo(&str); - appendStringInfoChar(&str, '['); + appendStringInfoChar(&str, '{'); for (i = 0; i < ndist->nitems; i++) { MVNDistinctItem item = ndist->items[i]; + int x = -1; + bool first = true; if (i > 0) appendStringInfoString(&str, ", "); - appendStringInfoChar(&str, '{'); - outBitmapset(&str, item.attrs); - appendStringInfo(&str, ", %f}", item.ndistinct); + appendStringInfoChar(&str, '"'); + while ((x = bms_next_member(item.attrs, x)) >= 0) + { + if (!first) + appendStringInfoString(&str, ", "); + first = false; + appendStringInfo(&str, "%d", x); + } + appendStringInfo(&str, "\": %d", (int) item.ndistinct); } - appendStringInfoChar(&str, ']'); + appendStringInfoChar(&str, '}'); PG_RETURN_CSTRING(str.data); }
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers