($INBOX_DIR/description missing)help / color / mirror / Atom feed
[PATCH 2/2] Add psql index info commands 14+ messages / 4 participants [nested] [flat]
* [PATCH 2/2] Add psql index info commands @ 2019-07-15 14:11 Nikita Glukhov <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Nikita Glukhov @ 2019-07-15 14:11 UTC (permalink / raw) --- doc/src/sgml/ref/psql-ref.sgml | 28 ++++ src/bin/psql/command.c | 10 ++ src/bin/psql/describe.c | 291 ++++++++++++++++++++++++++++++++++++- src/bin/psql/describe.h | 7 + src/bin/psql/help.c | 2 + src/bin/psql/tab-complete.c | 5 +- src/test/regress/expected/psql.out | 16 ++ src/test/regress/sql/psql.sql | 3 + 8 files changed, 359 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index e690c4d..c4ff542 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1451,6 +1451,34 @@ testdb=> </listitem> </varlistentry> + <varlistentry> + <term><literal>\dip [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> + + <listitem> + <para> + Shows index properties listed in + <xref linkend="functions-info-index-props"/>. + If <replaceable class="parameter">pattern</replaceable> is + specified, only access methods whose names match the pattern are shown. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <literal>\dicp [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>] + </literal> + </term> + + <listitem> + <para> + Shows index column properties listed in + <xref linkend="functions-info-index-column-props"/>. + If <replaceable class="parameter">pattern</replaceable> is + specified, only access methods whose names match the pattern are shown. + </para> + </listitem> + </varlistentry> <varlistentry> <term><literal>\des[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 8cfcc9b..374f2cf 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -827,6 +827,16 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) case 'v': case 'm': case 'i': + if (strncmp(cmd, "dip", 3) == 0) + { + success = describeIndexProperties(pattern, show_system); + break; + } + else if (strncmp(cmd, "dicp", 4) == 0) + { + success = describeIndexColumnProperties(pattern, show_system); + break; + } case 's': case 'E': success = listTables(&cmd[1], pattern, show_verbose, show_system); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index b819b3e..2c1a5c2 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -19,6 +19,7 @@ #include "catalog/pg_cast_d.h" #include "catalog/pg_class_d.h" #include "catalog/pg_default_acl_d.h" +#include "catalog/pg_index.h" #include "common/logging.h" #include "fe_utils/mbprint.h" @@ -47,7 +48,11 @@ static bool describeOneTSConfig(const char *oid, const char *nspname, const char *pnspname, const char *prsname); static void printACLColumn(PQExpBuffer buf, const char *colname); static bool listOneExtensionContents(const char *extname, const char *oid); - +static bool describeOneIndexColumnProperties(const char *oid, + const char *nspname, + const char *idxname, + const char *amname, + const char *tabname); /*---------------- * Handlers for various slash commands displaying some sort of list @@ -6361,3 +6366,287 @@ describeAccessMethodOperatorClasses(const char *access_method_pattern, PQclear(res); return true; } + +/* + * \dip + * Describes index properties. + * + * Takes an optional regexp to select particular index. + */ +bool +describeIndexProperties(const char *pattern, bool showSystem) +{ + PQExpBufferData buf; + PGresult *res; + printQueryOpt myopt = pset.popt; + + static const bool translate_columns[] = {false, false, false, false, false, false, false}; + + initPQExpBuffer(&buf); + + printfPQExpBuffer(&buf, + "SELECT" + " n.nspname AS \"%s\",\n" + " c.relname AS \"%s\",\n" + " am.amname AS \"%s\",\n", + gettext_noop("Schema"), + gettext_noop("Name"), + gettext_noop("Access method")); + appendPQExpBuffer(&buf, + pset.sversion >= 90600 ? + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'clusterable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%3$s\",\n" + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'index_scan')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%4$s\",\n" + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'bitmap_scan')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n" + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'backward_scan')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%6$s\"\n" + : + " CASE WHEN am.amclusterable THEN '%1$s' ELSE '%2$s' END AS \"%3$s\",\n" + " CASE WHEN am.amgettuple <> 0 THEN '%1$s' ELSE '%2$s' END AS \"%4$s\",\n" + " CASE WHEN am.amgetbitmap <> 0 THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n" + " CASE WHEN am.amcanbackward THEN '%1$s' ELSE '%2$s' END AS \"%6$s\"\n", + gettext_noop("yes"), + gettext_noop("no"), + gettext_noop("Clusterable"), + gettext_noop("Index scan"), + gettext_noop("Bitmap scan"), + gettext_noop("Backward scan")); + appendPQExpBufferStr(&buf, + "FROM pg_catalog.pg_class c\n" + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" + " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n" + "WHERE c.relkind='i'\n" + " AND n.nspname !~ 'pg_toast'\n"); + + if (!showSystem && !pattern) + appendPQExpBufferStr(&buf, + " AND n.nspname <> 'pg_catalog'\n" + " AND n.nspname <> 'information_schema'\n"); + + processSQLNamePattern(pset.db, &buf, pattern, true, false, + "n.nspname", "c.relname", NULL, NULL); + + appendPQExpBufferStr(&buf, "ORDER BY 1;"); + res = PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + + myopt.nullPrint = NULL; + myopt.title = _("Index properties"); + myopt.translate_header = true; + myopt.translate_columns = translate_columns; + myopt.n_translate_columns = lengthof(translate_columns); + + printQuery(res, &myopt, pset.queryFout, false, pset.logfile); + + PQclear(res); + return true; +} + +/* + * \dicp + * Describes index index column properties. + * + * Takes an optional regexp to select particular index. + */ +bool +describeIndexColumnProperties(const char *index_pattern, bool showSystem) +{ + PQExpBufferData buf; + PGresult *res; + int i; + + initPQExpBuffer(&buf); + + printfPQExpBuffer(&buf, + "SELECT DISTINCT c.oid,\n" + " n.nspname,\n" + " c.relname,\n" + " am.amname,\n" + " c2.relname\n" + "FROM pg_catalog.pg_class c\n" + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = relnamespace\n" + " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n" + " LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n" + " LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid\n"); + + appendPQExpBufferStr(&buf, "WHERE c.relkind='i'\n"); + + if (!showSystem && !index_pattern) + appendPQExpBufferStr(&buf, "AND n.nspname <> 'pg_catalog'\n" + "AND n.nspname <> 'information_schema'\n"); + + processSQLNamePattern(pset.db, &buf, index_pattern, true, false, + "n.nspname", "c.relname", NULL, + "pg_catalog.pg_table_is_visible(c.oid)"); + + appendPQExpBufferStr(&buf, "ORDER BY 2, 3;"); + + res = PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + + if (PQntuples(res) == 0) + { + if (!pset.quiet) + { + if (index_pattern) + pg_log_error("Did not find any index named \"%s\".", + index_pattern); + else + pg_log_error("Did not find any relations."); + } + PQclear(res); + return false; + } + + for (i = 0; i < PQntuples(res); i++) + { + const char *oid = PQgetvalue(res, i, 0); + const char *nspname = PQgetvalue(res, i, 1); + const char *idxname = PQgetvalue(res, i, 2); + const char *amname = PQgetvalue(res, i, 3); + const char *tabname = PQgetvalue(res, i, 4); + + if (!describeOneIndexColumnProperties(oid, nspname, idxname, amname, + tabname)) + { + PQclear(res); + return false; + } + if (cancel_pressed) + { + PQclear(res); + return false; + } + } + + PQclear(res); + return true; +} + +static bool +describeOneIndexColumnProperties(const char *oid, + const char *nspname, + const char *idxname, + const char *amname, + const char *tabname) +{ + PQExpBufferData buf; + PGresult *res; + printQueryOpt myopt = pset.popt; + char *footers[3] = {NULL, NULL}; + static const bool translate_columns[] = {false, false, false, false, false, + false, false, false, false, false}; + + initPQExpBuffer(&buf); + + printfPQExpBuffer(&buf, + "SELECT\n" + " a.attname AS \"%s\",\n" + " pg_catalog.pg_get_indexdef(i.indexrelid, a.attnum, true) AS \"%s\",\n" + " CASE WHEN pg_catalog.pg_opclass_is_visible(o.oid) THEN '' ELSE n.nspname || '.' END || o.opcname AS \"%s\",\n", + gettext_noop("Column name"), + gettext_noop("Expr"), + gettext_noop("Opclass")); + + if (pset.sversion >= 90600) + appendPQExpBuffer(&buf, + " CASE\n" + " WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') = true \n" + " THEN CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'asc')\n" + " THEN '%1$s' ELSE '%2$s' END \n" + " ELSE NULL" + " END AS \"%3$s\"," + " CASE\n" + " WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') = true \n" + " THEN CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'nulls_first')\n" + " THEN '%1$s' ELSE '%2$s' END \n" + " ELSE NULL" + " END AS \"%4$s\"," + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'distance_orderable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%6$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'returnable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%7$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_array')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%8$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_nulls')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%9$s\"\n", + gettext_noop("yes"), + gettext_noop("no"), + gettext_noop("ASC"), + gettext_noop("Nulls first"), + gettext_noop("Orderable"), + gettext_noop("Distance orderable"), + gettext_noop("Returnable"), + gettext_noop("Search array"), + gettext_noop("Search nulls")); + else + appendPQExpBuffer(&buf, + " CASE WHEN am.amcanorder THEN CASE\n" + " WHEN (i.indoption[a.attnum - 1] & %1$d) = 0\n" /* INDOPTION_DESC */ + " THEN '%2$s' ELSE '%3$s' END\n" + " ELSE NULL END AS \"%4$s\",\n" + " CASE WHEN am.amcanorder THEN CASE\n" + " WHEN (i.indoption[a.attnum - 1] & %5$d) <> 0\n" /* INDOPTION_NULLS_FIRST */ + " THEN '%2$s' ELSE '%3$s' END\n" + " ELSE NULL END AS \"%6$s\",\n" + " CASE WHEN am.amcanorder THEN '%2$s' ELSE '%3$s' END AS \"%7$s\",\n" + " CASE WHEN am.amcanorderbyop THEN '%2$s' ELSE '%3$s' END AS \"%8$s\",\n" + " CASE WHEN am.amsearcharray THEN '%2$s' ELSE '%3$s' END AS \"%9$s\",\n" + " CASE WHEN am.amsearchnulls THEN '%2$s' ELSE '%3$s' END AS \"%10$s\"\n", + INDOPTION_DESC, + gettext_noop("yes"), + gettext_noop("no"), + gettext_noop("ASC"), + INDOPTION_NULLS_FIRST, + gettext_noop("Nulls first"), + gettext_noop("Orderable"), + gettext_noop("Distance orderable"), + gettext_noop("Search array"), + gettext_noop("Search nulls")); + + appendPQExpBuffer(&buf, + "FROM pg_catalog.pg_class c\n" + " LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n" + " LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid\n" + " LEFT JOIN pg_catalog.pg_opclass o ON o.oid = (i.indclass::pg_catalog.oid[])[a.attnum - 1]\n" + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.opcnamespace\n"); + if (pset.sversion < 90600) + appendPQExpBuffer(&buf, + " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n"); + appendPQExpBuffer(&buf, + "WHERE c.oid = %s\n" + "ORDER BY a.attnum", + oid); + + res = PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + if (PQntuples(res) == 0) + { + PQclear(res); + return true; + } + + myopt.nullPrint = NULL; + myopt.title = psprintf(_("Index %s.%s"), nspname, idxname); + footers[0] = psprintf(_("Table: %s"), tabname); + footers[1] = psprintf(_("Access method: %s"), amname); + myopt.footers = footers; + myopt.topt.default_footer = false; + myopt.translate_header = true; + myopt.translate_columns = translate_columns; + myopt.n_translate_columns = lengthof(translate_columns); + + printQuery(res, &myopt, pset.queryFout, false, pset.logfile); + PQclear(res); + return true; +} diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 6622534..01bf163 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -131,4 +131,11 @@ extern bool describeAccessMethodOperatorClasses(const char *access_method_patter const char *opclass_pattern, bool verbose); +/* \dip */ +extern bool describeIndexProperties(const char *pattern, bool showSystem); + +/* \dicp */ +extern bool describeIndexColumnProperties(const char *indexPattern, + bool showSystem); + #endif /* DESCRIBE_H */ diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index dc591c3..dbb873e 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -249,6 +249,8 @@ slashUsage(unsigned short int pager) fprintf(output, _(" \\dFt[+] [PATTERN] list text search templates\n")); fprintf(output, _(" \\dg[S+] [PATTERN] list roles\n")); fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n")); + fprintf(output, _(" \\dip[S] [PATTERN] list indexes with properties\n")); + fprintf(output, _(" \\dicp[S][PATTERN] show index column properties\n")); fprintf(output, _(" \\dl list large objects, same as \\lo_list\n")); fprintf(output, _(" \\dL[S+] [PATTERN] list procedural languages\n")); fprintf(output, _(" \\dm[S+] [PATTERN] list materialized views\n")); diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 5d14a93..0e3a1eff 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1427,8 +1427,9 @@ psql_completion(const char *text, int start, int end) "\\d", "\\da", "\\dA", "\\dAp", "\\dAo", "\\dAp", "\\dAc", "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD", "\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df", - "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL", - "\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt", + "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dicp", "\\dip", + "\\dl", "\\dL", "\\dm", "\\dn", "\\do", "\\dO", + "\\dp", "\\dP", "\\dPi", "\\dPt", "\\drds", "\\dRs", "\\dRp", "\\ds", "\\dS", "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy", "\\e", "\\echo", "\\ef", "\\elif", "\\else", "\\encoding", diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index 7738ee3..6733a49 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -4889,3 +4889,19 @@ List operators of family related to access method brin | oid | | oid_minmax_ops | yes (1 row) +-- check printing info about indexes +\dip pg_am_name_index + Index properties + Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | Backward scan +------------+------------------+---------------+-------------+------------+-------------+--------------- + pg_catalog | pg_am_name_index | btree | yes | yes | yes | yes +(1 row) + +\dicp pg_am_name_index + Index pg_catalog.pg_am_name_index + Column name | Expr | Opclass | ASC | Nulls first | Orderable | Distance orderable | Returnable | Search array | Search nulls +-------------+--------+----------+-----+-------------+-----------+--------------------+------------+--------------+-------------- + amname | amname | name_ops | yes | no | yes | no | yes | yes | yes +Table: pg_am +Access method: btree + diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql index 650a540..578f0de 100644 --- a/src/test/regress/sql/psql.sql +++ b/src/test/regress/sql/psql.sql @@ -1146,3 +1146,6 @@ drop role regress_partitioning_role; \dAp brin uuid_minmax_ops \dAp * pg_catalog.uuid_ops \dAc brin pg*.oid* +-- check printing info about indexes +\dip pg_am_name_index +\dicp pg_am_name_index -- 2.7.4 --------------19B35D84A6D9E14B89902F95-- ^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/2] Add psql index info commands @ 2019-07-15 14:11 Nikita Glukhov <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Nikita Glukhov @ 2019-07-15 14:11 UTC (permalink / raw) --- doc/src/sgml/ref/psql-ref.sgml | 28 ++++ src/bin/psql/command.c | 10 ++ src/bin/psql/describe.c | 291 ++++++++++++++++++++++++++++++++++++- src/bin/psql/describe.h | 7 + src/bin/psql/help.c | 2 + src/bin/psql/tab-complete.c | 5 +- src/test/regress/expected/psql.out | 16 ++ src/test/regress/sql/psql.sql | 3 + 8 files changed, 359 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index e690c4d..c4ff542 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1451,6 +1451,34 @@ testdb=> </listitem> </varlistentry> + <varlistentry> + <term><literal>\dip [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> + + <listitem> + <para> + Shows index properties listed in + <xref linkend="functions-info-index-props"/>. + If <replaceable class="parameter">pattern</replaceable> is + specified, only access methods whose names match the pattern are shown. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <literal>\dicp [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>] + </literal> + </term> + + <listitem> + <para> + Shows index column properties listed in + <xref linkend="functions-info-index-column-props"/>. + If <replaceable class="parameter">pattern</replaceable> is + specified, only access methods whose names match the pattern are shown. + </para> + </listitem> + </varlistentry> <varlistentry> <term><literal>\des[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index e6cb260..36e2efe 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -827,6 +827,16 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) case 'v': case 'm': case 'i': + if (strncmp(cmd, "dip", 3) == 0) + { + success = describeIndexProperties(pattern, show_system); + break; + } + else if (strncmp(cmd, "dicp", 4) == 0) + { + success = describeIndexColumnProperties(pattern, show_system); + break; + } case 's': case 'E': success = listTables(&cmd[1], pattern, show_verbose, show_system); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 9cae8c8..86c3bd3 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -19,6 +19,7 @@ #include "catalog/pg_cast_d.h" #include "catalog/pg_class_d.h" #include "catalog/pg_default_acl_d.h" +#include "catalog/pg_index.h" #include "common/logging.h" #include "fe_utils/mbprint.h" @@ -47,7 +48,11 @@ static bool describeOneTSConfig(const char *oid, const char *nspname, const char *pnspname, const char *prsname); static void printACLColumn(PQExpBuffer buf, const char *colname); static bool listOneExtensionContents(const char *extname, const char *oid); - +static bool describeOneIndexColumnProperties(const char *oid, + const char *nspname, + const char *idxname, + const char *amname, + const char *tabname); /*---------------- * Handlers for various slash commands displaying some sort of list @@ -6361,3 +6366,287 @@ describeAccessMethodOperatorClasses(const char *access_method_pattern, PQclear(res); return true; } + +/* + * \dip + * Describes index properties. + * + * Takes an optional regexp to select particular index. + */ +bool +describeIndexProperties(const char *pattern, bool showSystem) +{ + PQExpBufferData buf; + PGresult *res; + printQueryOpt myopt = pset.popt; + + static const bool translate_columns[] = {false, false, false, false, false, false, false}; + + initPQExpBuffer(&buf); + + printfPQExpBuffer(&buf, + "SELECT" + " n.nspname AS \"%s\",\n" + " c.relname AS \"%s\",\n" + " am.amname AS \"%s\",\n", + gettext_noop("Schema"), + gettext_noop("Name"), + gettext_noop("Access method")); + appendPQExpBuffer(&buf, + pset.sversion >= 90600 ? + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'clusterable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%3$s\",\n" + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'index_scan')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%4$s\",\n" + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'bitmap_scan')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n" + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'backward_scan')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%6$s\"\n" + : + " CASE WHEN am.amclusterable THEN '%1$s' ELSE '%2$s' END AS \"%3$s\",\n" + " CASE WHEN am.amgettuple <> 0 THEN '%1$s' ELSE '%2$s' END AS \"%4$s\",\n" + " CASE WHEN am.amgetbitmap <> 0 THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n" + " CASE WHEN am.amcanbackward THEN '%1$s' ELSE '%2$s' END AS \"%6$s\"\n", + gettext_noop("yes"), + gettext_noop("no"), + gettext_noop("Clusterable"), + gettext_noop("Index scan"), + gettext_noop("Bitmap scan"), + gettext_noop("Backward scan")); + appendPQExpBufferStr(&buf, + "FROM pg_catalog.pg_class c\n" + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" + " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n" + "WHERE c.relkind='i'\n" + " AND n.nspname !~ 'pg_toast'\n"); + + if (!showSystem && !pattern) + appendPQExpBufferStr(&buf, + " AND n.nspname <> 'pg_catalog'\n" + " AND n.nspname <> 'information_schema'\n"); + + processSQLNamePattern(pset.db, &buf, pattern, true, false, + "n.nspname", "c.relname", NULL, NULL); + + appendPQExpBufferStr(&buf, "ORDER BY 1;"); + res = PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + + myopt.nullPrint = NULL; + myopt.title = _("Index properties"); + myopt.translate_header = true; + myopt.translate_columns = translate_columns; + myopt.n_translate_columns = lengthof(translate_columns); + + printQuery(res, &myopt, pset.queryFout, false, pset.logfile); + + PQclear(res); + return true; +} + +/* + * \dicp + * Describes index index column properties. + * + * Takes an optional regexp to select particular index. + */ +bool +describeIndexColumnProperties(const char *index_pattern, bool showSystem) +{ + PQExpBufferData buf; + PGresult *res; + int i; + + initPQExpBuffer(&buf); + + printfPQExpBuffer(&buf, + "SELECT DISTINCT c.oid,\n" + " n.nspname,\n" + " c.relname,\n" + " am.amname,\n" + " c2.relname\n" + "FROM pg_catalog.pg_class c\n" + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = relnamespace\n" + " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n" + " LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n" + " LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid\n"); + + appendPQExpBufferStr(&buf, "WHERE c.relkind='i'\n"); + + if (!showSystem && !index_pattern) + appendPQExpBufferStr(&buf, "AND n.nspname <> 'pg_catalog'\n" + "AND n.nspname <> 'information_schema'\n"); + + processSQLNamePattern(pset.db, &buf, index_pattern, true, false, + "n.nspname", "c.relname", NULL, + "pg_catalog.pg_table_is_visible(c.oid)"); + + appendPQExpBufferStr(&buf, "ORDER BY 2, 3;"); + + res = PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + + if (PQntuples(res) == 0) + { + if (!pset.quiet) + { + if (index_pattern) + pg_log_error("Did not find any index named \"%s\".", + index_pattern); + else + pg_log_error("Did not find any relations."); + } + PQclear(res); + return false; + } + + for (i = 0; i < PQntuples(res); i++) + { + const char *oid = PQgetvalue(res, i, 0); + const char *nspname = PQgetvalue(res, i, 1); + const char *idxname = PQgetvalue(res, i, 2); + const char *amname = PQgetvalue(res, i, 3); + const char *tabname = PQgetvalue(res, i, 4); + + if (!describeOneIndexColumnProperties(oid, nspname, idxname, amname, + tabname)) + { + PQclear(res); + return false; + } + if (cancel_pressed) + { + PQclear(res); + return false; + } + } + + PQclear(res); + return true; +} + +static bool +describeOneIndexColumnProperties(const char *oid, + const char *nspname, + const char *idxname, + const char *amname, + const char *tabname) +{ + PQExpBufferData buf; + PGresult *res; + printQueryOpt myopt = pset.popt; + char *footers[3] = {NULL, NULL}; + static const bool translate_columns[] = {false, false, false, false, false, + false, false, false, false, false}; + + initPQExpBuffer(&buf); + + printfPQExpBuffer(&buf, + "SELECT\n" + " a.attname AS \"%s\",\n" + " pg_catalog.pg_get_indexdef(i.indexrelid, a.attnum, true) AS \"%s\",\n" + " CASE WHEN pg_catalog.pg_opclass_is_visible(o.oid) THEN '' ELSE n.nspname || '.' END || o.opcname AS \"%s\",\n", + gettext_noop("Column name"), + gettext_noop("Expr"), + gettext_noop("Opclass")); + + if (pset.sversion >= 90600) + appendPQExpBuffer(&buf, + " CASE\n" + " WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') = true \n" + " THEN CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'asc')\n" + " THEN '%1$s' ELSE '%2$s' END \n" + " ELSE NULL" + " END AS \"%3$s\"," + " CASE\n" + " WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') = true \n" + " THEN CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'nulls_first')\n" + " THEN '%1$s' ELSE '%2$s' END \n" + " ELSE NULL" + " END AS \"%4$s\"," + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'distance_orderable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%6$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'returnable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%7$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_array')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%8$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_nulls')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%9$s\"\n", + gettext_noop("yes"), + gettext_noop("no"), + gettext_noop("ASC"), + gettext_noop("Nulls first"), + gettext_noop("Orderable"), + gettext_noop("Distance orderable"), + gettext_noop("Returnable"), + gettext_noop("Search array"), + gettext_noop("Search nulls")); + else + appendPQExpBuffer(&buf, + " CASE WHEN am.amcanorder THEN CASE\n" + " WHEN (i.indoption[a.attnum - 1] & %1$d) = 0\n" /* INDOPTION_DESC */ + " THEN '%2$s' ELSE '%3$s' END\n" + " ELSE NULL END AS \"%4$s\",\n" + " CASE WHEN am.amcanorder THEN CASE\n" + " WHEN (i.indoption[a.attnum - 1] & %5$d) <> 0\n" /* INDOPTION_NULLS_FIRST */ + " THEN '%2$s' ELSE '%3$s' END\n" + " ELSE NULL END AS \"%6$s\",\n" + " CASE WHEN am.amcanorder THEN '%2$s' ELSE '%3$s' END AS \"%7$s\",\n" + " CASE WHEN am.amcanorderbyop THEN '%2$s' ELSE '%3$s' END AS \"%8$s\",\n" + " CASE WHEN am.amsearcharray THEN '%2$s' ELSE '%3$s' END AS \"%9$s\",\n" + " CASE WHEN am.amsearchnulls THEN '%2$s' ELSE '%3$s' END AS \"%10$s\"\n", + INDOPTION_DESC, + gettext_noop("yes"), + gettext_noop("no"), + gettext_noop("ASC"), + INDOPTION_NULLS_FIRST, + gettext_noop("Nulls first"), + gettext_noop("Orderable"), + gettext_noop("Distance orderable"), + gettext_noop("Search array"), + gettext_noop("Search nulls")); + + appendPQExpBuffer(&buf, + "FROM pg_catalog.pg_class c\n" + " LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n" + " LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid\n" + " LEFT JOIN pg_catalog.pg_opclass o ON o.oid = (i.indclass::pg_catalog.oid[])[a.attnum - 1]\n" + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.opcnamespace\n"); + if (pset.sversion < 90600) + appendPQExpBuffer(&buf, + " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n"); + appendPQExpBuffer(&buf, + "WHERE c.oid = %s\n" + "ORDER BY a.attnum", + oid); + + res = PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + if (PQntuples(res) == 0) + { + PQclear(res); + return true; + } + + myopt.nullPrint = NULL; + myopt.title = psprintf(_("Index %s.%s"), nspname, idxname); + footers[0] = psprintf(_("Table: %s"), tabname); + footers[1] = psprintf(_("Access method: %s"), amname); + myopt.footers = footers; + myopt.topt.default_footer = false; + myopt.translate_header = true; + myopt.translate_columns = translate_columns; + myopt.n_translate_columns = lengthof(translate_columns); + + printQuery(res, &myopt, pset.queryFout, false, pset.logfile); + PQclear(res); + return true; +} diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 6c87401..f9c3ea6 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -131,4 +131,11 @@ extern bool describeAccessMethodOperatorClasses(const char *access_method_patter const char *opclass_pattern, bool verbose); +/* \dip */ +extern bool describeIndexProperties(const char *pattern, bool showSystem); + +/* \dicp */ +extern bool describeIndexColumnProperties(const char *indexPattern, + bool showSystem); + #endif /* DESCRIBE_H */ diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index dc591c3..dbb873e 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -249,6 +249,8 @@ slashUsage(unsigned short int pager) fprintf(output, _(" \\dFt[+] [PATTERN] list text search templates\n")); fprintf(output, _(" \\dg[S+] [PATTERN] list roles\n")); fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n")); + fprintf(output, _(" \\dip[S] [PATTERN] list indexes with properties\n")); + fprintf(output, _(" \\dicp[S][PATTERN] show index column properties\n")); fprintf(output, _(" \\dl list large objects, same as \\lo_list\n")); fprintf(output, _(" \\dL[S+] [PATTERN] list procedural languages\n")); fprintf(output, _(" \\dm[S+] [PATTERN] list materialized views\n")); diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 5d14a93..0e3a1eff 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1427,8 +1427,9 @@ psql_completion(const char *text, int start, int end) "\\d", "\\da", "\\dA", "\\dAp", "\\dAo", "\\dAp", "\\dAc", "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD", "\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df", - "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL", - "\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt", + "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dicp", "\\dip", + "\\dl", "\\dL", "\\dm", "\\dn", "\\do", "\\dO", + "\\dp", "\\dP", "\\dPi", "\\dPt", "\\drds", "\\dRs", "\\dRp", "\\ds", "\\dS", "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy", "\\e", "\\echo", "\\ef", "\\elif", "\\else", "\\encoding", diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index 4d17be3..2580bae 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -4803,3 +4803,19 @@ List operators of family related to access method brin | oid | | oid_minmax_ops | yes (1 row) +-- check printing info about indexes +\dip pg_am_name_index + Index properties + Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | Backward scan +------------+------------------+---------------+-------------+------------+-------------+--------------- + pg_catalog | pg_am_name_index | btree | yes | yes | yes | yes +(1 row) + +\dicp pg_am_name_index + Index pg_catalog.pg_am_name_index + Column name | Expr | Opclass | ASC | Nulls first | Orderable | Distance orderable | Returnable | Search array | Search nulls +-------------+--------+----------+-----+-------------+-----------+--------------------+------------+--------------+-------------- + amname | amname | name_ops | yes | no | yes | no | yes | yes | yes +Table: pg_am +Access method: btree + diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql index 77941c4..95e7dbe 100644 --- a/src/test/regress/sql/psql.sql +++ b/src/test/regress/sql/psql.sql @@ -1139,3 +1139,6 @@ drop role regress_partitioning_role; \dAp brin uuid_minmax_ops \dAp * pg_catalog.uuid_ops \dAc brin pg*.oid* +-- check printing info about indexes +\dip pg_am_name_index +\dicp pg_am_name_index -- 2.7.4 --------------251D77BCC012992979481C12-- ^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ @ 2021-12-18 20:58 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw) show the size only with \dA++ and \dn++ (for which the single-plus commands have historically not done any slow operations). Also change to show the size only with \db++ and \l++ (for which it's useful to show the ACL without also doing any slow operations). \dt+ and \dP+ are not changed, since showing the table sizes seems to be their primary purpose (??) The idea for plusplus commands were previously discussed here. https://www.postgresql.org/message-id/20190506163359.GA29291%40alvherre.pgsql --- src/bin/psql/command.c | 20 +++++++++++------- src/bin/psql/describe.c | 46 +++++++++++++++++++++++++++++------------ src/bin/psql/describe.h | 8 +++---- 3 files changed, 50 insertions(+), 24 deletions(-) diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 511debbe814..b695e09017d 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -369,6 +369,7 @@ exec_command(const char *cmd, else if (strcmp(cmd, "if") == 0) status = exec_command_if(scan_state, cstack, query_buf); else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 || + strcmp(cmd, "l++") == 0 || strcmp(cmd, "list++") == 0 || strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0) status = exec_command_list(scan_state, active_branch, cmd); else if (strncmp(cmd, "lo_", 3) == 0) @@ -754,6 +755,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; + int verbose = 0; bool show_verbose, show_system; @@ -761,7 +763,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; + + show_verbose = (bool) (verbose != 0); show_system = strchr(cmd, 'S') ? true : false; switch (cmd[1]) @@ -786,7 +791,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) { case '\0': case '+': - success = describeAccessMethods(pattern, show_verbose); + success = describeAccessMethods(pattern, verbose); break; case 'c': success = listOperatorClasses(pattern, pattern2, show_verbose); @@ -812,7 +817,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = describeAggregates(pattern, show_verbose, show_system); break; case 'b': - success = describeTablespaces(pattern, show_verbose); + success = describeTablespaces(pattern, verbose); break; case 'c': if (strncmp(cmd, "dconfig", 7) == 0) @@ -866,7 +871,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = listLanguages(pattern, show_verbose, show_system); break; case 'n': - success = listSchemas(pattern, show_verbose, show_system); + success = listSchemas(pattern, verbose, show_system); break; case 'o': success = exec_command_dfo(scan_state, cmd, pattern, @@ -1943,14 +1948,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; - bool show_verbose; + int verbose = 0; pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; - success = listAllDbs(pattern, show_verbose); + success = listAllDbs(pattern, verbose); free(pattern); } diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 9325a46b8fd..f7f737e8b59 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -139,12 +139,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem) * Takes an optional regexp to select particular access methods */ bool -describeAccessMethods(const char *pattern, bool verbose) +describeAccessMethods(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; printQueryOpt myopt = pset.popt; - static const bool translate_columns[] = {false, true, false, false}; + static const bool translate_columns[] = {false, true, false, false, false}; if (pset.sversion < 90600) { @@ -176,6 +176,11 @@ describeAccessMethods(const char *pattern, bool verbose) " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"", gettext_noop("Handler"), gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 170000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, @@ -214,7 +219,7 @@ describeAccessMethods(const char *pattern, bool verbose) * Takes an optional regexp to select particular tablespaces */ bool -describeTablespaces(const char *pattern, bool verbose) +describeTablespaces(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; @@ -234,12 +239,18 @@ describeTablespaces(const char *pattern, bool verbose) { appendPQExpBufferStr(&buf, ",\n "); printACLColumn(&buf, "spcacl"); + + appendPQExpBuffer(&buf, + ",\n spcoptions AS \"%s\"", + gettext_noop("Options")); + + if (verbose > 1) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"", + gettext_noop("Size")); + appendPQExpBuffer(&buf, - ",\n spcoptions AS \"%s\"" - ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"" ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"", - gettext_noop("Options"), - gettext_noop("Size"), gettext_noop("Description")); } @@ -914,7 +925,7 @@ error_return: * for \l, \list, and -l switch */ bool -listAllDbs(const char *pattern, bool verbose) +listAllDbs(const char *pattern, int verbose) { PGresult *res; PQExpBufferData buf; @@ -961,20 +972,24 @@ listAllDbs(const char *pattern, bool verbose) gettext_noop("ICU Rules")); appendPQExpBufferStr(&buf, " "); printACLColumn(&buf, "d.datacl"); - if (verbose) + if (verbose > 1) appendPQExpBuffer(&buf, ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n" " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n" " ELSE 'No Access'\n" - " END as \"%s\"" + " END as \"%s\"", + gettext_noop("Size")); + + if (verbose > 0) + appendPQExpBuffer(&buf, ",\n t.spcname as \"%s\"" ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"", - gettext_noop("Size"), gettext_noop("Tablespace"), gettext_noop("Description")); + appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_database d\n"); - if (verbose) + if (verbose > 0) appendPQExpBufferStr(&buf, " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n"); @@ -4970,7 +4985,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem) * Describes schemas (namespaces) */ bool -listSchemas(const char *pattern, bool verbose, bool showSystem) +listSchemas(const char *pattern, int verbose, bool showSystem) { PQExpBufferData buf; PGresult *res; @@ -4992,6 +5007,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem) appendPQExpBuffer(&buf, ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"", gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 170000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 554fe867255..d2fd8a72a36 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -13,10 +13,10 @@ extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem); /* \dA */ -extern bool describeAccessMethods(const char *pattern, bool verbose); +extern bool describeAccessMethods(const char *pattern, int verbose); /* \db */ -extern bool describeTablespaces(const char *pattern, bool verbose); +extern bool describeTablespaces(const char *pattern, int verbose); /* \df, \dfa, \dfn, \dft, \dfw, etc. */ extern bool describeFunctions(const char *functypes, const char *func_pattern, @@ -62,7 +62,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose); extern bool listTSTemplates(const char *pattern, bool verbose); /* \l */ -extern bool listAllDbs(const char *pattern, bool verbose); +extern bool listAllDbs(const char *pattern, int verbose); /* \dt, \di, \ds, \dS, etc. */ extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem); @@ -87,7 +87,7 @@ extern bool listCasts(const char *pattern, bool verbose); extern bool listCollations(const char *pattern, bool verbose, bool showSystem); /* \dn */ -extern bool listSchemas(const char *pattern, bool verbose, bool showSystem); +extern bool listSchemas(const char *pattern, int verbose, bool showSystem); /* \dew */ extern bool listForeignDataWrappers(const char *pattern, bool verbose); -- 2.34.1 --mDjj41GS8UJ6l/Dp Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-f-convert-the-other-verbose-to-int-too.patch" ^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH v4 2/4] psql: add convenience commands: \dA+ and \dn+ @ 2021-12-18 20:58 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw) show the size only with ++ in \dn, \dA, \db and (for consistency) \l \dt+ and \dP+ are not changed, since showing the table sizes seems to be their primary purpose. --- src/bin/psql/command.c | 22 ++++++++++++++-------- src/bin/psql/describe.c | 30 ++++++++++++++++++++++-------- src/bin/psql/describe.h | 8 ++++---- 3 files changed, 40 insertions(+), 20 deletions(-) diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index ccd7b48108..c9b0a01f9d 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -362,7 +362,8 @@ exec_command(const char *cmd, else if (strcmp(cmd, "if") == 0) status = exec_command_if(scan_state, cstack, query_buf); else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 || - strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0) + strcmp(cmd, "l+") == 0 || strcmp(cmd, "l++") == 0 || + strcmp(cmd, "list+") == 0 || strcmp(cmd, "list++") == 0) status = exec_command_list(scan_state, active_branch, cmd); else if (strncmp(cmd, "lo_", 3) == 0) status = exec_command_lo(scan_state, active_branch, cmd); @@ -707,6 +708,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; + int verbose = 0; bool show_verbose, show_system; @@ -714,7 +716,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; + + show_verbose = (bool) (verbose != 0); show_system = strchr(cmd, 'S') ? true : false; switch (cmd[1]) @@ -739,7 +744,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) { case '\0': case '+': - success = describeAccessMethods(pattern, show_verbose); + success = describeAccessMethods(pattern, verbose); break; case 'c': success = listOperatorClasses(pattern, pattern2, show_verbose); @@ -766,7 +771,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = describeAggregates(pattern, show_verbose, show_system); break; case 'b': - success = describeTablespaces(pattern, show_verbose); + success = describeTablespaces(pattern, verbose); break; case 'c': success = listConversions(pattern, show_verbose, show_system); @@ -813,7 +818,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = listLanguages(pattern, show_verbose, show_system); break; case 'n': - success = listSchemas(pattern, show_verbose, show_system); + success = listSchemas(pattern, verbose, show_system); break; case 'o': success = exec_command_dfo(scan_state, cmd, pattern, @@ -1856,14 +1861,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; - bool show_verbose; + int verbose = 0; pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; - success = listAllDbs(pattern, show_verbose); + success = listAllDbs(pattern, verbose); if (pattern) free(pattern); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index c28788e84f..c818e657da 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -128,12 +128,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem) * Takes an optional regexp to select particular access methods */ bool -describeAccessMethods(const char *pattern, bool verbose) +describeAccessMethods(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; printQueryOpt myopt = pset.popt; - static const bool translate_columns[] = {false, true, false, false}; + static const bool translate_columns[] = {false, true, false, false, false}; if (pset.sversion < 90600) { @@ -165,6 +165,11 @@ describeAccessMethods(const char *pattern, bool verbose) " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"", gettext_noop("Handler"), gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 150000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, @@ -198,7 +203,7 @@ describeAccessMethods(const char *pattern, bool verbose) * Takes an optional regexp to select particular tablespaces */ bool -describeTablespaces(const char *pattern, bool verbose) +describeTablespaces(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; @@ -218,12 +223,16 @@ describeTablespaces(const char *pattern, bool verbose) { appendPQExpBufferStr(&buf, ",\n "); printACLColumn(&buf, "spcacl"); + + if (verbose > 1) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"", + gettext_noop("Size")); + appendPQExpBuffer(&buf, ",\n spcoptions AS \"%s\"" - ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"" ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"", gettext_noop("Options"), - gettext_noop("Size"), gettext_noop("Description")); } @@ -877,7 +886,7 @@ describeOperators(const char *oper_pattern, * for \l, \list, and -l switch */ bool -listAllDbs(const char *pattern, bool verbose) +listAllDbs(const char *pattern, int verbose) { PGresult *res; PQExpBufferData buf; @@ -898,7 +907,7 @@ listAllDbs(const char *pattern, bool verbose) gettext_noop("Ctype")); appendPQExpBufferStr(&buf, " "); printACLColumn(&buf, "d.datacl"); - if (verbose) + if (verbose > 1) appendPQExpBuffer(&buf, ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n" " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n" @@ -4657,7 +4666,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem) * Describes schemas (namespaces) */ bool -listSchemas(const char *pattern, bool verbose, bool showSystem) +listSchemas(const char *pattern, int verbose, bool showSystem) { PQExpBufferData buf; PGresult *res; @@ -4679,6 +4688,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem) appendPQExpBuffer(&buf, ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"", gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 150000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 71b320f1fc..edeea38580 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -13,10 +13,10 @@ extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem); /* \dA */ -extern bool describeAccessMethods(const char *pattern, bool verbose); +extern bool describeAccessMethods(const char *pattern, int verbose); /* \db */ -extern bool describeTablespaces(const char *pattern, bool verbose); +extern bool describeTablespaces(const char *pattern, int verbose); /* \df, \dfa, \dfn, \dft, \dfw, etc. */ extern bool describeFunctions(const char *functypes, const char *func_pattern, @@ -62,7 +62,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose); extern bool listTSTemplates(const char *pattern, bool verbose); /* \l */ -extern bool listAllDbs(const char *pattern, bool verbose); +extern bool listAllDbs(const char *pattern, int verbose); /* \dt, \di, \ds, \dS, etc. */ extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem); @@ -83,7 +83,7 @@ extern bool listCasts(const char *pattern, bool verbose); extern bool listCollations(const char *pattern, bool verbose, bool showSystem); /* \dn */ -extern bool listSchemas(const char *pattern, bool verbose, bool showSystem); +extern bool listSchemas(const char *pattern, int verbose, bool showSystem); /* \dew */ extern bool listForeignDataWrappers(const char *pattern, bool verbose); -- 2.17.0 --2fEWJT3hVM9yyfvd Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v4-0003-f-convert-the-other-verbose-to-int-too.patch" ^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ @ 2021-12-18 20:58 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw) show the size only with \dA++ and \dn++ (for which the single-plus commands have historically not done any slow operations). Also change to show the size only with \db++ and \l++ (for which it's useful to show the ACL without also doing any slow operations). \dt+ and \dP+ are not changed, since showing the table sizes seems to be their primary purpose. The idea for plusplus commands were previously discussed here. https://www.postgresql.org/message-id/20190506163359.GA29291%40alvherre.pgsql --- src/bin/psql/command.c | 20 +++++++++++------- src/bin/psql/describe.c | 46 +++++++++++++++++++++++++++++------------ src/bin/psql/describe.h | 8 +++---- 3 files changed, 50 insertions(+), 24 deletions(-) diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 955397ee9dc..875b659a26d 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -369,6 +369,7 @@ exec_command(const char *cmd, else if (strcmp(cmd, "if") == 0) status = exec_command_if(scan_state, cstack, query_buf); else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 || + strcmp(cmd, "l++") == 0 || strcmp(cmd, "list++") == 0 || strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0) status = exec_command_list(scan_state, active_branch, cmd); else if (strncmp(cmd, "lo_", 3) == 0) @@ -754,6 +755,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; + int verbose = 0; bool show_verbose, show_system; @@ -761,7 +763,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; + + show_verbose = (bool) (verbose != 0); show_system = strchr(cmd, 'S') ? true : false; switch (cmd[1]) @@ -786,7 +791,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) { case '\0': case '+': - success = describeAccessMethods(pattern, show_verbose); + success = describeAccessMethods(pattern, verbose); break; case 'c': success = listOperatorClasses(pattern, pattern2, show_verbose); @@ -812,7 +817,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = describeAggregates(pattern, show_verbose, show_system); break; case 'b': - success = describeTablespaces(pattern, show_verbose); + success = describeTablespaces(pattern, verbose); break; case 'c': if (strncmp(cmd, "dconfig", 7) == 0) @@ -866,7 +871,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = listLanguages(pattern, show_verbose, show_system); break; case 'n': - success = listSchemas(pattern, show_verbose, show_system); + success = listSchemas(pattern, verbose, show_system); break; case 'o': success = exec_command_dfo(scan_state, cmd, pattern, @@ -1943,14 +1948,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; - bool show_verbose; + int verbose = 0; pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; - success = listAllDbs(pattern, show_verbose); + success = listAllDbs(pattern, verbose); free(pattern); } diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 99e28f607e8..7de604a894d 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -139,12 +139,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem) * Takes an optional regexp to select particular access methods */ bool -describeAccessMethods(const char *pattern, bool verbose) +describeAccessMethods(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; printQueryOpt myopt = pset.popt; - static const bool translate_columns[] = {false, true, false, false}; + static const bool translate_columns[] = {false, true, false, false, false}; if (pset.sversion < 90600) { @@ -176,6 +176,11 @@ describeAccessMethods(const char *pattern, bool verbose) " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"", gettext_noop("Handler"), gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 160000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, @@ -214,7 +219,7 @@ describeAccessMethods(const char *pattern, bool verbose) * Takes an optional regexp to select particular tablespaces */ bool -describeTablespaces(const char *pattern, bool verbose) +describeTablespaces(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; @@ -234,12 +239,18 @@ describeTablespaces(const char *pattern, bool verbose) { appendPQExpBufferStr(&buf, ",\n "); printACLColumn(&buf, "spcacl"); + + appendPQExpBuffer(&buf, + ",\n spcoptions AS \"%s\"", + gettext_noop("Options")); + + if (verbose > 1) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"", + gettext_noop("Size")); + appendPQExpBuffer(&buf, - ",\n spcoptions AS \"%s\"" - ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"" ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"", - gettext_noop("Options"), - gettext_noop("Size"), gettext_noop("Description")); } @@ -914,7 +925,7 @@ error_return: * for \l, \list, and -l switch */ bool -listAllDbs(const char *pattern, bool verbose) +listAllDbs(const char *pattern, int verbose) { PGresult *res; PQExpBufferData buf; @@ -961,20 +972,24 @@ listAllDbs(const char *pattern, bool verbose) gettext_noop("ICU Rules")); appendPQExpBufferStr(&buf, " "); printACLColumn(&buf, "d.datacl"); - if (verbose) + if (verbose > 1) appendPQExpBuffer(&buf, ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n" " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n" " ELSE 'No Access'\n" - " END as \"%s\"" + " END as \"%s\"", + gettext_noop("Size")); + + if (verbose > 0) + appendPQExpBuffer(&buf, ",\n t.spcname as \"%s\"" ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"", - gettext_noop("Size"), gettext_noop("Tablespace"), gettext_noop("Description")); + appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_database d\n"); - if (verbose) + if (verbose > 0) appendPQExpBufferStr(&buf, " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n"); @@ -4973,7 +4988,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem) * Describes schemas (namespaces) */ bool -listSchemas(const char *pattern, bool verbose, bool showSystem) +listSchemas(const char *pattern, int verbose, bool showSystem) { PQExpBufferData buf; PGresult *res; @@ -4995,6 +5010,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem) appendPQExpBuffer(&buf, ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"", gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 160000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 554fe867255..d2fd8a72a36 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -13,10 +13,10 @@ extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem); /* \dA */ -extern bool describeAccessMethods(const char *pattern, bool verbose); +extern bool describeAccessMethods(const char *pattern, int verbose); /* \db */ -extern bool describeTablespaces(const char *pattern, bool verbose); +extern bool describeTablespaces(const char *pattern, int verbose); /* \df, \dfa, \dfn, \dft, \dfw, etc. */ extern bool describeFunctions(const char *functypes, const char *func_pattern, @@ -62,7 +62,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose); extern bool listTSTemplates(const char *pattern, bool verbose); /* \l */ -extern bool listAllDbs(const char *pattern, bool verbose); +extern bool listAllDbs(const char *pattern, int verbose); /* \dt, \di, \ds, \dS, etc. */ extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem); @@ -87,7 +87,7 @@ extern bool listCasts(const char *pattern, bool verbose); extern bool listCollations(const char *pattern, bool verbose, bool showSystem); /* \dn */ -extern bool listSchemas(const char *pattern, bool verbose, bool showSystem); +extern bool listSchemas(const char *pattern, int verbose, bool showSystem); /* \dew */ extern bool listForeignDataWrappers(const char *pattern, bool verbose); -- 2.34.1 --OZ1/qgOAlc2dTU8W Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-f-convert-the-other-verbose-to-int-too.patch" ^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ @ 2021-12-18 20:58 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw) show the size only with ++ in \dn, \dA, \db and (for consistency) \l \dt+ and \dP+ are not changed, since showing the table sizes seems to be their primary purpose. --- src/bin/psql/command.c | 22 ++++++++++++++-------- src/bin/psql/describe.c | 30 ++++++++++++++++++++++-------- src/bin/psql/describe.h | 8 ++++---- 3 files changed, 40 insertions(+), 20 deletions(-) diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index b51d28780b1..cb65283547c 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -366,7 +366,8 @@ exec_command(const char *cmd, else if (strcmp(cmd, "if") == 0) status = exec_command_if(scan_state, cstack, query_buf); else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 || - strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0) + strcmp(cmd, "l+") == 0 || strcmp(cmd, "l++") == 0 || + strcmp(cmd, "list+") == 0 || strcmp(cmd, "list++") == 0) status = exec_command_list(scan_state, active_branch, cmd); else if (strncmp(cmd, "lo_", 3) == 0) status = exec_command_lo(scan_state, active_branch, cmd); @@ -718,6 +719,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; + int verbose = 0; bool show_verbose, show_system; @@ -725,7 +727,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; + + show_verbose = (bool) (verbose != 0); show_system = strchr(cmd, 'S') ? true : false; switch (cmd[1]) @@ -750,7 +755,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) { case '\0': case '+': - success = describeAccessMethods(pattern, show_verbose); + success = describeAccessMethods(pattern, verbose); break; case 'c': success = listOperatorClasses(pattern, pattern2, show_verbose); @@ -777,7 +782,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = describeAggregates(pattern, show_verbose, show_system); break; case 'b': - success = describeTablespaces(pattern, show_verbose); + success = describeTablespaces(pattern, verbose); break; case 'c': if (strncmp(cmd, "dconfig", 7) == 0) @@ -831,7 +836,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = listLanguages(pattern, show_verbose, show_system); break; case 'n': - success = listSchemas(pattern, show_verbose, show_system); + success = listSchemas(pattern, verbose, show_system); break; case 'o': success = exec_command_dfo(scan_state, cmd, pattern, @@ -1911,14 +1916,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; - bool show_verbose; + int verbose = 0; pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; - success = listAllDbs(pattern, show_verbose); + success = listAllDbs(pattern, verbose); if (pattern) free(pattern); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 1a5d924a23f..67163e834bc 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -136,12 +136,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem) * Takes an optional regexp to select particular access methods */ bool -describeAccessMethods(const char *pattern, bool verbose) +describeAccessMethods(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; printQueryOpt myopt = pset.popt; - static const bool translate_columns[] = {false, true, false, false}; + static const bool translate_columns[] = {false, true, false, false, false}; if (pset.sversion < 90600) { @@ -173,6 +173,11 @@ describeAccessMethods(const char *pattern, bool verbose) " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"", gettext_noop("Handler"), gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 160000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, @@ -208,7 +213,7 @@ describeAccessMethods(const char *pattern, bool verbose) * Takes an optional regexp to select particular tablespaces */ bool -describeTablespaces(const char *pattern, bool verbose) +describeTablespaces(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; @@ -228,12 +233,16 @@ describeTablespaces(const char *pattern, bool verbose) { appendPQExpBufferStr(&buf, ",\n "); printACLColumn(&buf, "spcacl"); + + if (verbose > 1) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"", + gettext_noop("Size")); + appendPQExpBuffer(&buf, ",\n spcoptions AS \"%s\"" - ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"" ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"", gettext_noop("Options"), - gettext_noop("Size"), gettext_noop("Description")); } @@ -899,7 +908,7 @@ describeOperators(const char *oper_pattern, * for \l, \list, and -l switch */ bool -listAllDbs(const char *pattern, bool verbose) +listAllDbs(const char *pattern, int verbose) { PGresult *res; PQExpBufferData buf; @@ -932,7 +941,7 @@ listAllDbs(const char *pattern, bool verbose) gettext_noop("Locale Provider")); appendPQExpBufferStr(&buf, " "); printACLColumn(&buf, "d.datacl"); - if (verbose) + if (verbose > 1) appendPQExpBuffer(&buf, ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n" " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n" @@ -4881,7 +4890,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem) * Describes schemas (namespaces) */ bool -listSchemas(const char *pattern, bool verbose, bool showSystem) +listSchemas(const char *pattern, int verbose, bool showSystem) { PQExpBufferData buf; PGresult *res; @@ -4903,6 +4912,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem) appendPQExpBuffer(&buf, ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"", gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 160000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 7872c71f58d..4d889c71368 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -13,10 +13,10 @@ extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem); /* \dA */ -extern bool describeAccessMethods(const char *pattern, bool verbose); +extern bool describeAccessMethods(const char *pattern, int verbose); /* \db */ -extern bool describeTablespaces(const char *pattern, bool verbose); +extern bool describeTablespaces(const char *pattern, int verbose); /* \df, \dfa, \dfn, \dft, \dfw, etc. */ extern bool describeFunctions(const char *functypes, const char *func_pattern, @@ -62,7 +62,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose); extern bool listTSTemplates(const char *pattern, bool verbose); /* \l */ -extern bool listAllDbs(const char *pattern, bool verbose); +extern bool listAllDbs(const char *pattern, int verbose); /* \dt, \di, \ds, \dS, etc. */ extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem); @@ -87,7 +87,7 @@ extern bool listCasts(const char *pattern, bool verbose); extern bool listCollations(const char *pattern, bool verbose, bool showSystem); /* \dn */ -extern bool listSchemas(const char *pattern, bool verbose, bool showSystem); +extern bool listSchemas(const char *pattern, int verbose, bool showSystem); /* \dew */ extern bool listForeignDataWrappers(const char *pattern, bool verbose); -- 2.17.1 --HcAYCG3uE/tztfnV Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-f-convert-the-other-verbose-to-int-too.patch" ^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ @ 2021-12-18 20:58 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw) show the size only with \dA++ and \dn++ (for which the single-plus commands have historically not done any slow operations). Also change to show the size only with \db++ and \l++ (for which it's useful to show the ACL without also doing any slow operations). \dt+ and \dP+ are not changed, since showing the table sizes seems to be their primary purpose (??) The idea for plusplus commands were previously discussed here. https://www.postgresql.org/message-id/20190506163359.GA29291%40alvherre.pgsql --- src/bin/psql/command.c | 20 +++++++++++------- src/bin/psql/describe.c | 46 +++++++++++++++++++++++++++++------------ src/bin/psql/describe.h | 8 +++---- 3 files changed, 50 insertions(+), 24 deletions(-) diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 1300869d797..667beb1b16e 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -369,6 +369,7 @@ exec_command(const char *cmd, else if (strcmp(cmd, "if") == 0) status = exec_command_if(scan_state, cstack, query_buf); else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 || + strcmp(cmd, "l++") == 0 || strcmp(cmd, "list++") == 0 || strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0) status = exec_command_list(scan_state, active_branch, cmd); else if (strncmp(cmd, "lo_", 3) == 0) @@ -754,6 +755,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; + int verbose = 0; bool show_verbose, show_system; @@ -761,7 +763,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; + + show_verbose = (bool) (verbose != 0); show_system = strchr(cmd, 'S') ? true : false; switch (cmd[1]) @@ -786,7 +791,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) { case '\0': case '+': - success = describeAccessMethods(pattern, show_verbose); + success = describeAccessMethods(pattern, verbose); break; case 'c': success = listOperatorClasses(pattern, pattern2, show_verbose); @@ -812,7 +817,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = describeAggregates(pattern, show_verbose, show_system); break; case 'b': - success = describeTablespaces(pattern, show_verbose); + success = describeTablespaces(pattern, verbose); break; case 'c': if (strncmp(cmd, "dconfig", 7) == 0) @@ -866,7 +871,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = listLanguages(pattern, show_verbose, show_system); break; case 'n': - success = listSchemas(pattern, show_verbose, show_system); + success = listSchemas(pattern, verbose, show_system); break; case 'o': success = exec_command_dfo(scan_state, cmd, pattern, @@ -1945,14 +1950,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; - bool show_verbose; + int verbose = 0; pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; - success = listAllDbs(pattern, show_verbose); + success = listAllDbs(pattern, verbose); free(pattern); } diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 45f6a86b872..3be7a5b8dc8 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -139,12 +139,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem) * Takes an optional regexp to select particular access methods */ bool -describeAccessMethods(const char *pattern, bool verbose) +describeAccessMethods(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; printQueryOpt myopt = pset.popt; - static const bool translate_columns[] = {false, true, false, false}; + static const bool translate_columns[] = {false, true, false, false, false}; if (pset.sversion < 90600) { @@ -176,6 +176,11 @@ describeAccessMethods(const char *pattern, bool verbose) " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"", gettext_noop("Handler"), gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 170000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, @@ -214,7 +219,7 @@ describeAccessMethods(const char *pattern, bool verbose) * Takes an optional regexp to select particular tablespaces */ bool -describeTablespaces(const char *pattern, bool verbose) +describeTablespaces(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; @@ -234,12 +239,18 @@ describeTablespaces(const char *pattern, bool verbose) { appendPQExpBufferStr(&buf, ",\n "); printACLColumn(&buf, "spcacl"); + + appendPQExpBuffer(&buf, + ",\n spcoptions AS \"%s\"", + gettext_noop("Options")); + + if (verbose > 1) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"", + gettext_noop("Size")); + appendPQExpBuffer(&buf, - ",\n spcoptions AS \"%s\"" - ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"" ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"", - gettext_noop("Options"), - gettext_noop("Size"), gettext_noop("Description")); } @@ -914,7 +925,7 @@ error_return: * for \l, \list, and -l switch */ bool -listAllDbs(const char *pattern, bool verbose) +listAllDbs(const char *pattern, int verbose) { PGresult *res; PQExpBufferData buf; @@ -961,20 +972,24 @@ listAllDbs(const char *pattern, bool verbose) gettext_noop("ICU Rules")); appendPQExpBufferStr(&buf, " "); printACLColumn(&buf, "d.datacl"); - if (verbose) + if (verbose > 1) appendPQExpBuffer(&buf, ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n" " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n" " ELSE 'No Access'\n" - " END as \"%s\"" + " END as \"%s\"", + gettext_noop("Size")); + + if (verbose > 0) + appendPQExpBuffer(&buf, ",\n t.spcname as \"%s\"" ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"", - gettext_noop("Size"), gettext_noop("Tablespace"), gettext_noop("Description")); + appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_database d\n"); - if (verbose) + if (verbose > 0) appendPQExpBufferStr(&buf, " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n"); @@ -5031,7 +5046,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem) * Describes schemas (namespaces) */ bool -listSchemas(const char *pattern, bool verbose, bool showSystem) +listSchemas(const char *pattern, int verbose, bool showSystem) { PQExpBufferData buf; PGresult *res; @@ -5053,6 +5068,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem) appendPQExpBuffer(&buf, ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"", gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 170000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 24c0884a347..f11579a7bbc 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -13,10 +13,10 @@ extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem); /* \dA */ -extern bool describeAccessMethods(const char *pattern, bool verbose); +extern bool describeAccessMethods(const char *pattern, int verbose); /* \db */ -extern bool describeTablespaces(const char *pattern, bool verbose); +extern bool describeTablespaces(const char *pattern, int verbose); /* \df, \dfa, \dfn, \dft, \dfw, etc. */ extern bool describeFunctions(const char *functypes, const char *func_pattern, @@ -65,7 +65,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose); extern bool listTSTemplates(const char *pattern, bool verbose); /* \l */ -extern bool listAllDbs(const char *pattern, bool verbose); +extern bool listAllDbs(const char *pattern, int verbose); /* \dt, \di, \ds, \dS, etc. */ extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem); @@ -90,7 +90,7 @@ extern bool listCasts(const char *pattern, bool verbose); extern bool listCollations(const char *pattern, bool verbose, bool showSystem); /* \dn */ -extern bool listSchemas(const char *pattern, bool verbose, bool showSystem); +extern bool listSchemas(const char *pattern, int verbose, bool showSystem); /* \dew */ extern bool listForeignDataWrappers(const char *pattern, bool verbose); -- 2.34.1 --X119r+cMzLJwahiZ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-f-convert-the-other-verbose-to-int-too.patch" ^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ @ 2021-12-18 20:58 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw) show the size only with ++ in \dn, \dA, \db and (for consistency) \l \dt+ and \dP+ are not changed, since showing the table sizes seems to be their primary purpose. The idea for plusplus commands were previously discussed here. https://www.postgresql.org/message-id/20190506163359.GA29291%40alvherre.pgsql --- src/bin/psql/command.c | 22 ++++++++++++++-------- src/bin/psql/describe.c | 30 ++++++++++++++++++++++-------- src/bin/psql/describe.h | 8 ++++---- 3 files changed, 40 insertions(+), 20 deletions(-) diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index de6a3a71f8a..40956cc32dc 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -368,7 +368,8 @@ exec_command(const char *cmd, else if (strcmp(cmd, "if") == 0) status = exec_command_if(scan_state, cstack, query_buf); else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 || - strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0) + strcmp(cmd, "l+") == 0 || strcmp(cmd, "l++") == 0 || + strcmp(cmd, "list+") == 0 || strcmp(cmd, "list++") == 0) status = exec_command_list(scan_state, active_branch, cmd); else if (strncmp(cmd, "lo_", 3) == 0) status = exec_command_lo(scan_state, active_branch, cmd); @@ -753,6 +754,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; + int verbose = 0; bool show_verbose, show_system; @@ -760,7 +762,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; + + show_verbose = (bool) (verbose != 0); show_system = strchr(cmd, 'S') ? true : false; switch (cmd[1]) @@ -785,7 +790,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) { case '\0': case '+': - success = describeAccessMethods(pattern, show_verbose); + success = describeAccessMethods(pattern, verbose); break; case 'c': success = listOperatorClasses(pattern, pattern2, show_verbose); @@ -811,7 +816,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = describeAggregates(pattern, show_verbose, show_system); break; case 'b': - success = describeTablespaces(pattern, show_verbose); + success = describeTablespaces(pattern, verbose); break; case 'c': if (strncmp(cmd, "dconfig", 7) == 0) @@ -865,7 +870,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = listLanguages(pattern, show_verbose, show_system); break; case 'n': - success = listSchemas(pattern, show_verbose, show_system); + success = listSchemas(pattern, verbose, show_system); break; case 'o': success = exec_command_dfo(scan_state, cmd, pattern, @@ -1942,14 +1947,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; - bool show_verbose; + int verbose = 0; pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; - success = listAllDbs(pattern, show_verbose); + success = listAllDbs(pattern, verbose); free(pattern); } diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index df166365e81..57666d8c5b8 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -139,12 +139,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem) * Takes an optional regexp to select particular access methods */ bool -describeAccessMethods(const char *pattern, bool verbose) +describeAccessMethods(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; printQueryOpt myopt = pset.popt; - static const bool translate_columns[] = {false, true, false, false}; + static const bool translate_columns[] = {false, true, false, false, false}; if (pset.sversion < 90600) { @@ -176,6 +176,11 @@ describeAccessMethods(const char *pattern, bool verbose) " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"", gettext_noop("Handler"), gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 160000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, @@ -214,7 +219,7 @@ describeAccessMethods(const char *pattern, bool verbose) * Takes an optional regexp to select particular tablespaces */ bool -describeTablespaces(const char *pattern, bool verbose) +describeTablespaces(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; @@ -234,12 +239,16 @@ describeTablespaces(const char *pattern, bool verbose) { appendPQExpBufferStr(&buf, ",\n "); printACLColumn(&buf, "spcacl"); + + if (verbose > 1) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"", + gettext_noop("Size")); + appendPQExpBuffer(&buf, ",\n spcoptions AS \"%s\"" - ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"" ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"", gettext_noop("Options"), - gettext_noop("Size"), gettext_noop("Description")); } @@ -919,7 +928,7 @@ error_return: * for \l, \list, and -l switch */ bool -listAllDbs(const char *pattern, bool verbose) +listAllDbs(const char *pattern, int verbose) { PGresult *res; PQExpBufferData buf; @@ -952,7 +961,7 @@ listAllDbs(const char *pattern, bool verbose) gettext_noop("Locale Provider")); appendPQExpBufferStr(&buf, " "); printACLColumn(&buf, "d.datacl"); - if (verbose) + if (verbose > 1) appendPQExpBuffer(&buf, ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n" " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n" @@ -4954,7 +4963,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem) * Describes schemas (namespaces) */ bool -listSchemas(const char *pattern, bool verbose, bool showSystem) +listSchemas(const char *pattern, int verbose, bool showSystem) { PQExpBufferData buf; PGresult *res; @@ -4976,6 +4985,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem) appendPQExpBuffer(&buf, ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"", gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 160000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index bd051e09cbb..84df35f4623 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -13,10 +13,10 @@ extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem); /* \dA */ -extern bool describeAccessMethods(const char *pattern, bool verbose); +extern bool describeAccessMethods(const char *pattern, int verbose); /* \db */ -extern bool describeTablespaces(const char *pattern, bool verbose); +extern bool describeTablespaces(const char *pattern, int verbose); /* \df, \dfa, \dfn, \dft, \dfw, etc. */ extern bool describeFunctions(const char *functypes, const char *func_pattern, @@ -62,7 +62,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose); extern bool listTSTemplates(const char *pattern, bool verbose); /* \l */ -extern bool listAllDbs(const char *pattern, bool verbose); +extern bool listAllDbs(const char *pattern, int verbose); /* \dt, \di, \ds, \dS, etc. */ extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem); @@ -87,7 +87,7 @@ extern bool listCasts(const char *pattern, bool verbose); extern bool listCollations(const char *pattern, bool verbose, bool showSystem); /* \dn */ -extern bool listSchemas(const char *pattern, bool verbose, bool showSystem); +extern bool listSchemas(const char *pattern, int verbose, bool showSystem); /* \dew */ extern bool listForeignDataWrappers(const char *pattern, bool verbose); -- 2.25.1 --H1spWtNR+x+ondvy Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-f-convert-the-other-verbose-to-int-too.patch" ^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ @ 2021-12-18 20:58 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw) show the size only with \dA++ and \dn++ (for which the single-plus commands have historically not done any slow operations). Also change to show the size only with \db++ and \l++ (for which it's useful to show the ACL without also doing any slow operations). \dt+ and \dP+ are not changed, since showing the table sizes seems to be their primary purpose (??) The idea for plusplus commands were previously discussed here. https://www.postgresql.org/message-id/20190506163359.GA29291%40alvherre.pgsql --- src/bin/psql/command.c | 20 +++++++++++------- src/bin/psql/describe.c | 46 +++++++++++++++++++++++++++++------------ src/bin/psql/describe.h | 8 +++---- 3 files changed, 50 insertions(+), 24 deletions(-) diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 1300869d797..667beb1b16e 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -369,6 +369,7 @@ exec_command(const char *cmd, else if (strcmp(cmd, "if") == 0) status = exec_command_if(scan_state, cstack, query_buf); else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 || + strcmp(cmd, "l++") == 0 || strcmp(cmd, "list++") == 0 || strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0) status = exec_command_list(scan_state, active_branch, cmd); else if (strncmp(cmd, "lo_", 3) == 0) @@ -754,6 +755,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; + int verbose = 0; bool show_verbose, show_system; @@ -761,7 +763,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; + + show_verbose = (bool) (verbose != 0); show_system = strchr(cmd, 'S') ? true : false; switch (cmd[1]) @@ -786,7 +791,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) { case '\0': case '+': - success = describeAccessMethods(pattern, show_verbose); + success = describeAccessMethods(pattern, verbose); break; case 'c': success = listOperatorClasses(pattern, pattern2, show_verbose); @@ -812,7 +817,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = describeAggregates(pattern, show_verbose, show_system); break; case 'b': - success = describeTablespaces(pattern, show_verbose); + success = describeTablespaces(pattern, verbose); break; case 'c': if (strncmp(cmd, "dconfig", 7) == 0) @@ -866,7 +871,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = listLanguages(pattern, show_verbose, show_system); break; case 'n': - success = listSchemas(pattern, show_verbose, show_system); + success = listSchemas(pattern, verbose, show_system); break; case 'o': success = exec_command_dfo(scan_state, cmd, pattern, @@ -1945,14 +1950,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; - bool show_verbose; + int verbose = 0; pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; - success = listAllDbs(pattern, show_verbose); + success = listAllDbs(pattern, verbose); free(pattern); } diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 45f6a86b872..3be7a5b8dc8 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -139,12 +139,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem) * Takes an optional regexp to select particular access methods */ bool -describeAccessMethods(const char *pattern, bool verbose) +describeAccessMethods(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; printQueryOpt myopt = pset.popt; - static const bool translate_columns[] = {false, true, false, false}; + static const bool translate_columns[] = {false, true, false, false, false}; if (pset.sversion < 90600) { @@ -176,6 +176,11 @@ describeAccessMethods(const char *pattern, bool verbose) " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"", gettext_noop("Handler"), gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 170000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, @@ -214,7 +219,7 @@ describeAccessMethods(const char *pattern, bool verbose) * Takes an optional regexp to select particular tablespaces */ bool -describeTablespaces(const char *pattern, bool verbose) +describeTablespaces(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; @@ -234,12 +239,18 @@ describeTablespaces(const char *pattern, bool verbose) { appendPQExpBufferStr(&buf, ",\n "); printACLColumn(&buf, "spcacl"); + + appendPQExpBuffer(&buf, + ",\n spcoptions AS \"%s\"", + gettext_noop("Options")); + + if (verbose > 1) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"", + gettext_noop("Size")); + appendPQExpBuffer(&buf, - ",\n spcoptions AS \"%s\"" - ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"" ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"", - gettext_noop("Options"), - gettext_noop("Size"), gettext_noop("Description")); } @@ -914,7 +925,7 @@ error_return: * for \l, \list, and -l switch */ bool -listAllDbs(const char *pattern, bool verbose) +listAllDbs(const char *pattern, int verbose) { PGresult *res; PQExpBufferData buf; @@ -961,20 +972,24 @@ listAllDbs(const char *pattern, bool verbose) gettext_noop("ICU Rules")); appendPQExpBufferStr(&buf, " "); printACLColumn(&buf, "d.datacl"); - if (verbose) + if (verbose > 1) appendPQExpBuffer(&buf, ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n" " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n" " ELSE 'No Access'\n" - " END as \"%s\"" + " END as \"%s\"", + gettext_noop("Size")); + + if (verbose > 0) + appendPQExpBuffer(&buf, ",\n t.spcname as \"%s\"" ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"", - gettext_noop("Size"), gettext_noop("Tablespace"), gettext_noop("Description")); + appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_database d\n"); - if (verbose) + if (verbose > 0) appendPQExpBufferStr(&buf, " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n"); @@ -5031,7 +5046,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem) * Describes schemas (namespaces) */ bool -listSchemas(const char *pattern, bool verbose, bool showSystem) +listSchemas(const char *pattern, int verbose, bool showSystem) { PQExpBufferData buf; PGresult *res; @@ -5053,6 +5068,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem) appendPQExpBuffer(&buf, ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"", gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 170000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 24c0884a347..f11579a7bbc 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -13,10 +13,10 @@ extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem); /* \dA */ -extern bool describeAccessMethods(const char *pattern, bool verbose); +extern bool describeAccessMethods(const char *pattern, int verbose); /* \db */ -extern bool describeTablespaces(const char *pattern, bool verbose); +extern bool describeTablespaces(const char *pattern, int verbose); /* \df, \dfa, \dfn, \dft, \dfw, etc. */ extern bool describeFunctions(const char *functypes, const char *func_pattern, @@ -65,7 +65,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose); extern bool listTSTemplates(const char *pattern, bool verbose); /* \l */ -extern bool listAllDbs(const char *pattern, bool verbose); +extern bool listAllDbs(const char *pattern, int verbose); /* \dt, \di, \ds, \dS, etc. */ extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem); @@ -90,7 +90,7 @@ extern bool listCasts(const char *pattern, bool verbose); extern bool listCollations(const char *pattern, bool verbose, bool showSystem); /* \dn */ -extern bool listSchemas(const char *pattern, bool verbose, bool showSystem); +extern bool listSchemas(const char *pattern, int verbose, bool showSystem); /* \dew */ extern bool listForeignDataWrappers(const char *pattern, bool verbose); -- 2.34.1 --X119r+cMzLJwahiZ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-f-convert-the-other-verbose-to-int-too.patch" ^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ @ 2021-12-18 20:58 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw) show the size only with ++ in \dn, \dA, \db and (for consistency) \l \dt+ and \dP+ are not changed, since showing the table sizes seems to be their primary purpose. --- src/bin/psql/command.c | 22 ++++++++++++++-------- src/bin/psql/describe.c | 30 ++++++++++++++++++++++-------- src/bin/psql/describe.h | 8 ++++---- 3 files changed, 40 insertions(+), 20 deletions(-) diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index f5904748553..cfae8fd6d12 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -366,7 +366,8 @@ exec_command(const char *cmd, else if (strcmp(cmd, "if") == 0) status = exec_command_if(scan_state, cstack, query_buf); else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 || - strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0) + strcmp(cmd, "l+") == 0 || strcmp(cmd, "l++") == 0 || + strcmp(cmd, "list+") == 0 || strcmp(cmd, "list++") == 0) status = exec_command_list(scan_state, active_branch, cmd); else if (strncmp(cmd, "lo_", 3) == 0) status = exec_command_lo(scan_state, active_branch, cmd); @@ -718,6 +719,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; + int verbose = 0; bool show_verbose, show_system; @@ -725,7 +727,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; + + show_verbose = (bool) (verbose != 0); show_system = strchr(cmd, 'S') ? true : false; switch (cmd[1]) @@ -750,7 +755,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) { case '\0': case '+': - success = describeAccessMethods(pattern, show_verbose); + success = describeAccessMethods(pattern, verbose); break; case 'c': success = listOperatorClasses(pattern, pattern2, show_verbose); @@ -777,7 +782,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = describeAggregates(pattern, show_verbose, show_system); break; case 'b': - success = describeTablespaces(pattern, show_verbose); + success = describeTablespaces(pattern, verbose); break; case 'c': success = listConversions(pattern, show_verbose, show_system); @@ -824,7 +829,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = listLanguages(pattern, show_verbose, show_system); break; case 'n': - success = listSchemas(pattern, show_verbose, show_system); + success = listSchemas(pattern, verbose, show_system); break; case 'o': success = exec_command_dfo(scan_state, cmd, pattern, @@ -1904,14 +1909,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; - bool show_verbose; + int verbose = 0; pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; - success = listAllDbs(pattern, show_verbose); + success = listAllDbs(pattern, verbose); if (pattern) free(pattern); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 8587b19160d..de62822674e 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -128,12 +128,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem) * Takes an optional regexp to select particular access methods */ bool -describeAccessMethods(const char *pattern, bool verbose) +describeAccessMethods(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; printQueryOpt myopt = pset.popt; - static const bool translate_columns[] = {false, true, false, false}; + static const bool translate_columns[] = {false, true, false, false, false}; if (pset.sversion < 90600) { @@ -165,6 +165,11 @@ describeAccessMethods(const char *pattern, bool verbose) " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"", gettext_noop("Handler"), gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 150000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, @@ -198,7 +203,7 @@ describeAccessMethods(const char *pattern, bool verbose) * Takes an optional regexp to select particular tablespaces */ bool -describeTablespaces(const char *pattern, bool verbose) +describeTablespaces(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; @@ -218,12 +223,16 @@ describeTablespaces(const char *pattern, bool verbose) { appendPQExpBufferStr(&buf, ",\n "); printACLColumn(&buf, "spcacl"); + + if (verbose > 1) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"", + gettext_noop("Size")); + appendPQExpBuffer(&buf, ",\n spcoptions AS \"%s\"" - ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"" ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"", gettext_noop("Options"), - gettext_noop("Size"), gettext_noop("Description")); } @@ -877,7 +886,7 @@ describeOperators(const char *oper_pattern, * for \l, \list, and -l switch */ bool -listAllDbs(const char *pattern, bool verbose) +listAllDbs(const char *pattern, int verbose) { PGresult *res; PQExpBufferData buf; @@ -898,7 +907,7 @@ listAllDbs(const char *pattern, bool verbose) gettext_noop("Ctype")); appendPQExpBufferStr(&buf, " "); printACLColumn(&buf, "d.datacl"); - if (verbose) + if (verbose > 1) appendPQExpBuffer(&buf, ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n" " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n" @@ -4665,7 +4674,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem) * Describes schemas (namespaces) */ bool -listSchemas(const char *pattern, bool verbose, bool showSystem) +listSchemas(const char *pattern, int verbose, bool showSystem) { PQExpBufferData buf; PGresult *res; @@ -4687,6 +4696,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem) appendPQExpBuffer(&buf, ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"", gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 150000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index fd6079679c6..5a1b97805df 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -13,10 +13,10 @@ extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem); /* \dA */ -extern bool describeAccessMethods(const char *pattern, bool verbose); +extern bool describeAccessMethods(const char *pattern, int verbose); /* \db */ -extern bool describeTablespaces(const char *pattern, bool verbose); +extern bool describeTablespaces(const char *pattern, int verbose); /* \df, \dfa, \dfn, \dft, \dfw, etc. */ extern bool describeFunctions(const char *functypes, const char *func_pattern, @@ -62,7 +62,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose); extern bool listTSTemplates(const char *pattern, bool verbose); /* \l */ -extern bool listAllDbs(const char *pattern, bool verbose); +extern bool listAllDbs(const char *pattern, int verbose); /* \dt, \di, \ds, \dS, etc. */ extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem); @@ -83,7 +83,7 @@ extern bool listCasts(const char *pattern, bool verbose); extern bool listCollations(const char *pattern, bool verbose, bool showSystem); /* \dn */ -extern bool listSchemas(const char *pattern, bool verbose, bool showSystem); +extern bool listSchemas(const char *pattern, int verbose, bool showSystem); /* \dew */ extern bool listForeignDataWrappers(const char *pattern, bool verbose); -- 2.17.1 --XLWMkxR+mZNQ4WTO Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-f-convert-the-other-verbose-to-int-too.patch" ^ permalink raw reply [nested|flat] 14+ messages in thread
* [PATCH v8 2/4] psql: add convenience commands: \dA+ and \dn+ @ 2021-12-18 20:58 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw) show the size only with ++ in \dn, \dA, \db and (for consistency) \l \dt+ and \dP+ are not changed, since showing the table sizes seems to be their primary purpose. The idea for plusplus commands were previously discussed here. https://www.postgresql.org/message-id/20190506163359.GA29291%40alvherre.pgsql --- src/bin/psql/command.c | 22 ++++++++++++++-------- src/bin/psql/describe.c | 30 ++++++++++++++++++++++-------- src/bin/psql/describe.h | 8 ++++---- 3 files changed, 40 insertions(+), 20 deletions(-) diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index a81bd3307b4..99ee47f436a 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -366,7 +366,8 @@ exec_command(const char *cmd, else if (strcmp(cmd, "if") == 0) status = exec_command_if(scan_state, cstack, query_buf); else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 || - strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0) + strcmp(cmd, "l+") == 0 || strcmp(cmd, "l++") == 0 || + strcmp(cmd, "list+") == 0 || strcmp(cmd, "list++") == 0) status = exec_command_list(scan_state, active_branch, cmd); else if (strncmp(cmd, "lo_", 3) == 0) status = exec_command_lo(scan_state, active_branch, cmd); @@ -717,6 +718,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; + int verbose = 0; bool show_verbose, show_system; @@ -724,7 +726,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; + + show_verbose = (bool) (verbose != 0); show_system = strchr(cmd, 'S') ? true : false; switch (cmd[1]) @@ -749,7 +754,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) { case '\0': case '+': - success = describeAccessMethods(pattern, show_verbose); + success = describeAccessMethods(pattern, verbose); break; case 'c': success = listOperatorClasses(pattern, pattern2, show_verbose); @@ -775,7 +780,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = describeAggregates(pattern, show_verbose, show_system); break; case 'b': - success = describeTablespaces(pattern, show_verbose); + success = describeTablespaces(pattern, verbose); break; case 'c': if (strncmp(cmd, "dconfig", 7) == 0) @@ -829,7 +834,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = listLanguages(pattern, show_verbose, show_system); break; case 'n': - success = listSchemas(pattern, show_verbose, show_system); + success = listSchemas(pattern, verbose, show_system); break; case 'o': success = exec_command_dfo(scan_state, cmd, pattern, @@ -1904,14 +1909,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; - bool show_verbose; + int verbose = 0; pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; - success = listAllDbs(pattern, show_verbose); + success = listAllDbs(pattern, verbose); free(pattern); } diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 327a69487bb..677dd64cda0 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -139,12 +139,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem) * Takes an optional regexp to select particular access methods */ bool -describeAccessMethods(const char *pattern, bool verbose) +describeAccessMethods(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; printQueryOpt myopt = pset.popt; - static const bool translate_columns[] = {false, true, false, false}; + static const bool translate_columns[] = {false, true, false, false, false}; if (pset.sversion < 90600) { @@ -176,6 +176,11 @@ describeAccessMethods(const char *pattern, bool verbose) " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"", gettext_noop("Handler"), gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 160000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, @@ -214,7 +219,7 @@ describeAccessMethods(const char *pattern, bool verbose) * Takes an optional regexp to select particular tablespaces */ bool -describeTablespaces(const char *pattern, bool verbose) +describeTablespaces(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; @@ -234,12 +239,16 @@ describeTablespaces(const char *pattern, bool verbose) { appendPQExpBufferStr(&buf, ",\n "); printACLColumn(&buf, "spcacl"); + + if (verbose > 1) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"", + gettext_noop("Size")); + appendPQExpBuffer(&buf, ",\n spcoptions AS \"%s\"" - ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"" ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"", gettext_noop("Options"), - gettext_noop("Size"), gettext_noop("Description")); } @@ -919,7 +928,7 @@ error_return: * for \l, \list, and -l switch */ bool -listAllDbs(const char *pattern, bool verbose) +listAllDbs(const char *pattern, int verbose) { PGresult *res; PQExpBufferData buf; @@ -952,7 +961,7 @@ listAllDbs(const char *pattern, bool verbose) gettext_noop("Locale Provider")); appendPQExpBufferStr(&buf, " "); printACLColumn(&buf, "d.datacl"); - if (verbose) + if (verbose > 1) appendPQExpBuffer(&buf, ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n" " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n" @@ -4949,7 +4958,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem) * Describes schemas (namespaces) */ bool -listSchemas(const char *pattern, bool verbose, bool showSystem) +listSchemas(const char *pattern, int verbose, bool showSystem) { PQExpBufferData buf; PGresult *res; @@ -4971,6 +4980,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem) appendPQExpBuffer(&buf, ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"", gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 160000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 7872c71f58d..4d889c71368 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -13,10 +13,10 @@ extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem); /* \dA */ -extern bool describeAccessMethods(const char *pattern, bool verbose); +extern bool describeAccessMethods(const char *pattern, int verbose); /* \db */ -extern bool describeTablespaces(const char *pattern, bool verbose); +extern bool describeTablespaces(const char *pattern, int verbose); /* \df, \dfa, \dfn, \dft, \dfw, etc. */ extern bool describeFunctions(const char *functypes, const char *func_pattern, @@ -62,7 +62,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose); extern bool listTSTemplates(const char *pattern, bool verbose); /* \l */ -extern bool listAllDbs(const char *pattern, bool verbose); +extern bool listAllDbs(const char *pattern, int verbose); /* \dt, \di, \ds, \dS, etc. */ extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem); @@ -87,7 +87,7 @@ extern bool listCasts(const char *pattern, bool verbose); extern bool listCollations(const char *pattern, bool verbose, bool showSystem); /* \dn */ -extern bool listSchemas(const char *pattern, bool verbose, bool showSystem); +extern bool listSchemas(const char *pattern, int verbose, bool showSystem); /* \dew */ extern bool listForeignDataWrappers(const char *pattern, bool verbose); -- 2.17.1 --cKDw3XFoqocuprIa Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v8-0003-f-convert-the-other-verbose-to-int-too.patch" ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Compress ReorderBuffer spill files using LZ4 @ 2024-09-23 19:58 Julien Tachoires <[email protected]> 2024-09-24 09:57 ` Re: Compress ReorderBuffer spill files using LZ4 Tomas Vondra <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Julien Tachoires @ 2024-09-23 19:58 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: Amit Kapila <[email protected]>; Tomas Vondra <[email protected]>; [email protected] Hi Tomas, Le lun. 23 sept. 2024 à 18:13, Tomas Vondra <[email protected]> a écrit : > > Hi, > > I've spent a bit more time on this, mostly running tests to get a better > idea of the practical benefits. Thank you for your code review and testing! > Firstly, I think there's a bug in ReorderBufferCompress() - it's legal > for pglz_compress() to return -1. This can happen if the data is not > compressible, and would not fit into the output buffer. The code can't > just do elog(ERROR) in this case, it needs to handle that by storing the > raw data. The attached fixup patch makes this work for me - I'm not > claiming this is the best way to handle this, but it works. > > FWIW I find it strange the tests included in the patch did not trigger > this. That probably means the tests are not quite sufficient. > > > Now, to the testing. Attached are two scripts, testing different cases: > > test-columns.sh - Table with a variable number of 'float8' columns. > > test-toast.sh - Table with a single text column. > > The script always sets up a publication/subscription on two instances, > generates certain amount of data (~1GB for columns, ~3.2GB for TOAST), > waits for it to be replicated to the replica, and measures how much data > was spilled to disk with the different compression methods (off, pglz > and lz4). There's a couple more metrics, but that's irrelevant here. It would be interesting to run the same tests with zstd: in my early testing I found that zstd was able to provide a better compression ratio than lz4, but seemed to use more CPU resources/is slower. > For the "column" test, it looks like this (this is in MB): > > rows columns distribution off pglz lz4 > ======================================================== > 100000 1000 compressible 778 20 9 > random 778 778 16 > -------------------------------------------------------- > 1000000 100 compressible 916 116 62 > random 916 916 67 > > It's very clear that for the "compressible" data (which just copies the > same value into all columns), both pglz and lz4 can significantly reduce > the amount of data. For 1000 columns it's 780MB -> 20MB/9MB, for 100 > columns it's a bit less efficient, but still good. > > For the "random" data (where every column gets a random value, but rows > are copied), it's a very different story - pglz does not help at all, > while lz4 still massively reduces the amount of spilled data. > > I think the explanation is very simple - for pglz, we compress each row > on it's own, there's no concept of streaming/context. If a row is > compressible, it works fine, but when the row gets random, pglz can't > compress it at all. For lz4, this does not matter, because with the > streaming mode it still sees that rows are just repeated, and so can > compress them efficiently. That's correct. > For TOAST test, the results look like this: > > distribution repeats toast off pglz lz4 > =============================================================== > compressible 10000 lz4 14 2 1 > pglz 40 4 3 > 1000 lz4 32 16 9 > pglz 54 17 10 > --------------------------------------------------------- > random 10000 lz4 3305 3305 3157 > pglz 3305 3305 3157 > 1000 lz4 3166 3162 1580 > pglz 3334 3326 1745 > ---------------------------------------------------------- > random2 10000 lz4 3305 3305 3157 > pglz 3305 3305 3158 > 1000 lz4 3160 3156 3010 > pglz 3334 3326 3172 > > The "repeats" value means how long the string is - it's the number of > "md5" hashes added to the string. The number of rows is calculated to > keep the total amount of data the same. The "toast" column tracks what > compression was used for TOAST, I was wondering if it matters. > > This time there are three data distributions - compressible means that > each TOAST value is nicely compressible, "random" means each value is > random (not compressible), but the rows are just copy of the same value > (so on the whole there's a lot of redundancy). And "random2" means each > row is random and unique (so not compressible at all). > > The table shows that with compressible TOAST values, compressing the > spill file is rather useless. The reason is that ReorderBufferCompress > is handling raw TOAST data, which is already compressed. Yes, it may > further reduce the amount of data, but it's negligible when compared to > the original amount of data. > > For the random cases, the spill compression is rather pointless. Yes, > lz4 can reduce it to 1/2 for the shorter strings, but other than that > it's not very useful. It's still interesting to confirm that data already compressed or random data cannot be significantly compressed. > For a while I was thinking this approach is flawed, because it only sees > and compressed changes one by one, and that seeing a batch of changes > would improve this (e.g. we'd see the copied rows). But I realized lz4 > already does that (in the streaming mode at least), and yet it does not > help very much. Presumably that depends on how large the context is. If > the random string is long enough, it won't help. > > So maybe this approach is fine, and doing the compression at a lower > layer (for the whole file), would not really improve this. Even then > we'd only see a limited amount of data. > > Maybe the right answer to this is that compression does not help cases > where most of the replicated data is TOAST, and that it can help cases > with wide (and redundant) rows, or repeated rows. And that lz4 is a > clearly superior choice. (This also raises the question if we want to > support REORDER_BUFFER_STRAT_LZ4_REGULAR. I haven't looked into this, > but doesn't that behave more like pglz, i.e. no context?) I'm working on a new version of this patch set that will include the changes you suggested in your review. About using LZ4 regular API, the goal was to use it when we cannot use the streaming API due to raw data larger than LZ4 ring buffer. But this is something I'm going to delete in the new version because I'm planning to use a similar approach as we do in astreamer_lz4.c: using frames, not blocks. LZ4 frame API looks very similar to ZSTD's streaming API. > FWIW when doing these tests, it made me realize how useful would it be > to track both the "raw" and "spilled" amounts. That is before/after > compression. It'd make calculating compression ratio much easier. Yes, that's why I tried to "fix" the spill_bytes counter. Regards, JT ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Compress ReorderBuffer spill files using LZ4 2024-09-23 19:58 Re: Compress ReorderBuffer spill files using LZ4 Julien Tachoires <[email protected]> @ 2024-09-24 09:57 ` Tomas Vondra <[email protected]> 2024-10-28 14:54 ` Re: Compress ReorderBuffer spill files using LZ4 Julien Tachoires <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Tomas Vondra @ 2024-09-24 09:57 UTC (permalink / raw) To: Julien Tachoires <[email protected]>; +Cc: Amit Kapila <[email protected]>; Tomas Vondra <[email protected]>; [email protected] On 9/23/24 21:58, Julien Tachoires wrote: > Hi Tomas, > > Le lun. 23 sept. 2024 à 18:13, Tomas Vondra <[email protected]> a écrit : >> >> Hi, >> >> I've spent a bit more time on this, mostly running tests to get a better >> idea of the practical benefits. > > Thank you for your code review and testing! > >> Firstly, I think there's a bug in ReorderBufferCompress() - it's legal >> for pglz_compress() to return -1. This can happen if the data is not >> compressible, and would not fit into the output buffer. The code can't >> just do elog(ERROR) in this case, it needs to handle that by storing the >> raw data. The attached fixup patch makes this work for me - I'm not >> claiming this is the best way to handle this, but it works. >> >> FWIW I find it strange the tests included in the patch did not trigger >> this. That probably means the tests are not quite sufficient. >> >> >> Now, to the testing. Attached are two scripts, testing different cases: >> >> test-columns.sh - Table with a variable number of 'float8' columns. >> >> test-toast.sh - Table with a single text column. >> >> The script always sets up a publication/subscription on two instances, >> generates certain amount of data (~1GB for columns, ~3.2GB for TOAST), >> waits for it to be replicated to the replica, and measures how much data >> was spilled to disk with the different compression methods (off, pglz >> and lz4). There's a couple more metrics, but that's irrelevant here. > > It would be interesting to run the same tests with zstd: in my early > testing I found that zstd was able to provide a better compression > ratio than lz4, but seemed to use more CPU resources/is slower. > Oh, I completely forgot about zstd. I don't think it'd substantially change the conclusions, though. It might compress better/worse for some cases, but the overall behavior would remain the same. I can't test this right now, the testmachine is busy with some other stuff. But it should not be difficult to update the test scripts I attached and get results yourself. There's a couple hard-coded paths that need to be updated, ofc. >> For the "column" test, it looks like this (this is in MB): >> >> rows columns distribution off pglz lz4 >> ======================================================== >> 100000 1000 compressible 778 20 9 >> random 778 778 16 >> -------------------------------------------------------- >> 1000000 100 compressible 916 116 62 >> random 916 916 67 >> >> It's very clear that for the "compressible" data (which just copies the >> same value into all columns), both pglz and lz4 can significantly reduce >> the amount of data. For 1000 columns it's 780MB -> 20MB/9MB, for 100 >> columns it's a bit less efficient, but still good. >> >> For the "random" data (where every column gets a random value, but rows >> are copied), it's a very different story - pglz does not help at all, >> while lz4 still massively reduces the amount of spilled data. >> >> I think the explanation is very simple - for pglz, we compress each row >> on it's own, there's no concept of streaming/context. If a row is >> compressible, it works fine, but when the row gets random, pglz can't >> compress it at all. For lz4, this does not matter, because with the >> streaming mode it still sees that rows are just repeated, and so can >> compress them efficiently. > > That's correct. > >> For TOAST test, the results look like this: >> >> distribution repeats toast off pglz lz4 >> =============================================================== >> compressible 10000 lz4 14 2 1 >> pglz 40 4 3 >> 1000 lz4 32 16 9 >> pglz 54 17 10 >> --------------------------------------------------------- >> random 10000 lz4 3305 3305 3157 >> pglz 3305 3305 3157 >> 1000 lz4 3166 3162 1580 >> pglz 3334 3326 1745 >> ---------------------------------------------------------- >> random2 10000 lz4 3305 3305 3157 >> pglz 3305 3305 3158 >> 1000 lz4 3160 3156 3010 >> pglz 3334 3326 3172 >> >> The "repeats" value means how long the string is - it's the number of >> "md5" hashes added to the string. The number of rows is calculated to >> keep the total amount of data the same. The "toast" column tracks what >> compression was used for TOAST, I was wondering if it matters. >> >> This time there are three data distributions - compressible means that >> each TOAST value is nicely compressible, "random" means each value is >> random (not compressible), but the rows are just copy of the same value >> (so on the whole there's a lot of redundancy). And "random2" means each >> row is random and unique (so not compressible at all). >> >> The table shows that with compressible TOAST values, compressing the >> spill file is rather useless. The reason is that ReorderBufferCompress >> is handling raw TOAST data, which is already compressed. Yes, it may >> further reduce the amount of data, but it's negligible when compared to >> the original amount of data. >> >> For the random cases, the spill compression is rather pointless. Yes, >> lz4 can reduce it to 1/2 for the shorter strings, but other than that >> it's not very useful. > > It's still interesting to confirm that data already compressed or > random data cannot be significantly compressed. > >> For a while I was thinking this approach is flawed, because it only sees >> and compressed changes one by one, and that seeing a batch of changes >> would improve this (e.g. we'd see the copied rows). But I realized lz4 >> already does that (in the streaming mode at least), and yet it does not >> help very much. Presumably that depends on how large the context is. If >> the random string is long enough, it won't help. >> >> So maybe this approach is fine, and doing the compression at a lower >> layer (for the whole file), would not really improve this. Even then >> we'd only see a limited amount of data. >> >> Maybe the right answer to this is that compression does not help cases >> where most of the replicated data is TOAST, and that it can help cases >> with wide (and redundant) rows, or repeated rows. And that lz4 is a >> clearly superior choice. (This also raises the question if we want to >> support REORDER_BUFFER_STRAT_LZ4_REGULAR. I haven't looked into this, >> but doesn't that behave more like pglz, i.e. no context?) > > I'm working on a new version of this patch set that will include the > changes you suggested in your review. About using LZ4 regular API, the > goal was to use it when we cannot use the streaming API due to raw > data larger than LZ4 ring buffer. But this is something I'm going to > delete in the new version because I'm planning to use a similar > approach as we do in astreamer_lz4.c: using frames, not blocks. LZ4 > frame API looks very similar to ZSTD's streaming API. > >> FWIW when doing these tests, it made me realize how useful would it be >> to track both the "raw" and "spilled" amounts. That is before/after >> compression. It'd make calculating compression ratio much easier. > > Yes, that's why I tried to "fix" the spill_bytes counter. > But I think the 'fixed' counter only tracks the data after the new compression, right? I'm suggesting to have two counters - one for "raw" data (before compression) and "compressed" (after compression). regards -- Tomas Vondra ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Compress ReorderBuffer spill files using LZ4 2024-09-23 19:58 Re: Compress ReorderBuffer spill files using LZ4 Julien Tachoires <[email protected]> 2024-09-24 09:57 ` Re: Compress ReorderBuffer spill files using LZ4 Tomas Vondra <[email protected]> @ 2024-10-28 14:54 ` Julien Tachoires <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Julien Tachoires @ 2024-10-28 14:54 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: Amit Kapila <[email protected]>; [email protected] Hi Tomas, Please find a new version of this patch set. I think I have addressed all the feedbacks you made. But, since 1bf1140be87230c71d0e7b29939f7e2b3d073aa1 the streaming option is now set to "parallel" by default, making this patch set almost useless. Regards, JT Attachments: [application/octet-stream] v5-0001-Compress-ReorderBuffer-spill-files-using-PGLZ.patch (23.3K, ../../CAFEQCbHQDq6ZfVwRDk8Ym2-EAVueSDLRLop0=3cujKTWqGcRmg@mail.gmail.com/2-v5-0001-Compress-ReorderBuffer-spill-files-using-PGLZ.patch) download | inline diff: From 1d427b7b61f69e0402d093b6dd35096638cfd033 Mon Sep 17 00:00:00 2001 From: Julien Tachoires <[email protected]> Date: Sun, 22 Sep 2024 16:59:10 +0200 Subject: [PATCH 1/6] Compress ReorderBuffer spill files using PGLZ When the content of a large transaction (size exceeding logical_decoding_work_mem) and its sub-transactions has to be reordered during logical decoding, then, all the changes are written on disk in temporary files located in pg_replslot/<slot_name>. This behavior happens only when the subscriber's option "streaming" is set to "off", which is the default value. In this case, decoding of large transactions by multiple replication slots can lead to disk space saturation and high I/O utilization. This patch enables data compression/decompression of these temporary files. Each transaction change that must be written on disk is now compressed using the in-house compression method PGLZ. --- src/backend/replication/logical/Makefile | 1 + src/backend/replication/logical/meson.build | 1 + .../replication/logical/reorderbuffer.c | 356 ++++++++++++++---- .../replication/logical/reorderbuffer_pglz.c | 56 +++ src/include/replication/reorderbuffer.h | 6 + .../replication/reorderbuffer_compression.h | 42 +++ 6 files changed, 392 insertions(+), 70 deletions(-) create mode 100644 src/backend/replication/logical/reorderbuffer_pglz.c create mode 100644 src/include/replication/reorderbuffer_compression.h diff --git a/src/backend/replication/logical/Makefile b/src/backend/replication/logical/Makefile index 1e08bbbd4e..96c733d009 100644 --- a/src/backend/replication/logical/Makefile +++ b/src/backend/replication/logical/Makefile @@ -26,6 +26,7 @@ OBJS = \ proto.o \ relation.o \ reorderbuffer.o \ + reorderbuffer_pglz.o \ slotsync.o \ snapbuild.o \ tablesync.o \ diff --git a/src/backend/replication/logical/meson.build b/src/backend/replication/logical/meson.build index 3d36249d8a..48df907b35 100644 --- a/src/backend/replication/logical/meson.build +++ b/src/backend/replication/logical/meson.build @@ -12,6 +12,7 @@ backend_sources += files( 'proto.c', 'relation.c', 'reorderbuffer.c', + 'reorderbuffer_pglz.c', 'slotsync.c', 'snapbuild.c', 'tablesync.c', diff --git a/src/backend/replication/logical/reorderbuffer.c b/src/backend/replication/logical/reorderbuffer.c index e3a5c7b660..649b196d5f 100644 --- a/src/backend/replication/logical/reorderbuffer.c +++ b/src/backend/replication/logical/reorderbuffer.c @@ -97,11 +97,13 @@ #include "access/xlog_internal.h" #include "catalog/catalog.h" #include "common/int.h" +#include "common/pg_lzcompress.h" #include "lib/binaryheap.h" #include "miscadmin.h" #include "pgstat.h" #include "replication/logical.h" #include "replication/reorderbuffer.h" +#include "replication/reorderbuffer_compression.h" #include "replication/slot.h" #include "replication/snapbuild.h" /* just for SnapBuildSnapDecRefcount */ #include "storage/bufmgr.h" @@ -176,9 +178,10 @@ typedef struct ReorderBufferToastEnt /* Disk serialization support datastructures */ typedef struct ReorderBufferDiskChange { - Size size; - ReorderBufferChange change; - /* data follows */ + ReorderBufferCompressionStrategy comp_strat; /* Compression strategy */ + Size size; /* Ondisk size */ + Size raw_size; /* Raw/uncompressed data size */ + /* ReorderBufferChange + data follow */ } ReorderBufferDiskChange; #define IsSpecInsert(action) \ @@ -215,6 +218,9 @@ static const Size max_changes_in_memory = 4096; /* XXX for restore only */ /* GUC variable */ int debug_logical_replication_streaming = DEBUG_LOGICAL_REP_STREAMING_BUFFERED; +/* Compression strategy for spilled data. */ +int logical_decoding_spill_compression = REORDER_BUFFER_PGLZ_COMPRESSION; + /* --------------------------------------- * primary reorderbuffer support routines * --------------------------------------- @@ -255,6 +261,8 @@ static void ReorderBufferSerializeChange(ReorderBuffer *rb, ReorderBufferTXN *tx int fd, ReorderBufferChange *change); static Size ReorderBufferRestoreChanges(ReorderBuffer *rb, ReorderBufferTXN *txn, TXNEntryFile *file, XLogSegNo *segno); +static bool ReorderBufferReadOndiskChange(ReorderBuffer *rb, ReorderBufferTXN *txn, + TXNEntryFile *file, XLogSegNo *segno); static void ReorderBufferRestoreChange(ReorderBuffer *rb, ReorderBufferTXN *txn, char *data); static void ReorderBufferRestoreCleanup(ReorderBuffer *rb, ReorderBufferTXN *txn); @@ -301,6 +309,24 @@ static void ReorderBufferChangeMemoryUpdate(ReorderBuffer *rb, ReorderBufferTXN *txn, bool addition, Size sz); +/* + * --------------------------------------- + * spill files compression + * --------------------------------------- + */ +static void *ReorderBufferNewCompressorState(MemoryContext context, + int compression_method); +static void ReorderBufferFreeCompressorState(MemoryContext context, + int compression_method, + void *compressor_state); +static void ReorderBufferCompress(ReorderBuffer *rb, + ReorderBufferDiskChange **ondisk, + int compression_method, Size data_size, + void *compressor_state); +static void ReorderBufferDecompress(ReorderBuffer *rb, char *data, + ReorderBufferDiskChange *ondisk, + void *compressor_state); + /* * Allocate a new ReorderBuffer and clean out any old serialized state from * prior ReorderBuffer instances for the same slot. @@ -432,6 +458,8 @@ ReorderBufferGetTXN(ReorderBuffer *rb) /* InvalidCommandId is not zero, so set it explicitly */ txn->command_id = InvalidCommandId; txn->output_plugin_private = NULL; + txn->compressor_state = ReorderBufferNewCompressorState(rb->context, + logical_decoding_spill_compression); return txn; } @@ -469,6 +497,10 @@ ReorderBufferReturnTXN(ReorderBuffer *rb, ReorderBufferTXN *txn) txn->invalidations = NULL; } + ReorderBufferFreeCompressorState(rb->context, + logical_decoding_spill_compression, + txn->compressor_state); + /* Reset the toast hash */ ReorderBufferToastReset(rb, txn); @@ -3806,12 +3838,12 @@ ReorderBufferSerializeChange(ReorderBuffer *rb, ReorderBufferTXN *txn, int fd, ReorderBufferChange *change) { ReorderBufferDiskChange *ondisk; - Size sz = sizeof(ReorderBufferDiskChange); + Size sz = sizeof(ReorderBufferDiskChange) + sizeof(ReorderBufferChange); ReorderBufferSerializeReserve(rb, sz); ondisk = (ReorderBufferDiskChange *) rb->outbuf; - memcpy(&ondisk->change, change, sizeof(ReorderBufferChange)); + memcpy((char *) rb->outbuf + sizeof(ReorderBufferDiskChange), change, sizeof(ReorderBufferChange)); switch (change->action) { @@ -3847,7 +3879,7 @@ ReorderBufferSerializeChange(ReorderBuffer *rb, ReorderBufferTXN *txn, /* make sure we have enough space */ ReorderBufferSerializeReserve(rb, sz); - data = ((char *) rb->outbuf) + sizeof(ReorderBufferDiskChange); + data = ((char *) rb->outbuf) + sizeof(ReorderBufferDiskChange) + sizeof(ReorderBufferChange); /* might have been reallocated above */ ondisk = (ReorderBufferDiskChange *) rb->outbuf; @@ -3879,7 +3911,7 @@ ReorderBufferSerializeChange(ReorderBuffer *rb, ReorderBufferTXN *txn, sizeof(Size) + sizeof(Size); ReorderBufferSerializeReserve(rb, sz); - data = ((char *) rb->outbuf) + sizeof(ReorderBufferDiskChange); + data = ((char *) rb->outbuf) + sizeof(ReorderBufferDiskChange) + sizeof(ReorderBufferChange); /* might have been reallocated above */ ondisk = (ReorderBufferDiskChange *) rb->outbuf; @@ -3909,7 +3941,7 @@ ReorderBufferSerializeChange(ReorderBuffer *rb, ReorderBufferTXN *txn, sz += inval_size; ReorderBufferSerializeReserve(rb, sz); - data = ((char *) rb->outbuf) + sizeof(ReorderBufferDiskChange); + data = ((char *) rb->outbuf) + sizeof(ReorderBufferDiskChange) + sizeof(ReorderBufferChange); /* might have been reallocated above */ ondisk = (ReorderBufferDiskChange *) rb->outbuf; @@ -3931,7 +3963,7 @@ ReorderBufferSerializeChange(ReorderBuffer *rb, ReorderBufferTXN *txn, /* make sure we have enough space */ ReorderBufferSerializeReserve(rb, sz); - data = ((char *) rb->outbuf) + sizeof(ReorderBufferDiskChange); + data = ((char *) rb->outbuf) + sizeof(ReorderBufferDiskChange) + sizeof(ReorderBufferChange); /* might have been reallocated above */ ondisk = (ReorderBufferDiskChange *) rb->outbuf; @@ -3965,7 +3997,7 @@ ReorderBufferSerializeChange(ReorderBuffer *rb, ReorderBufferTXN *txn, /* make sure we have enough space */ ReorderBufferSerializeReserve(rb, sz); - data = ((char *) rb->outbuf) + sizeof(ReorderBufferDiskChange); + data = ((char *) rb->outbuf) + sizeof(ReorderBufferDiskChange) + sizeof(ReorderBufferChange); /* might have been reallocated above */ ondisk = (ReorderBufferDiskChange *) rb->outbuf; @@ -3982,7 +4014,9 @@ ReorderBufferSerializeChange(ReorderBuffer *rb, ReorderBufferTXN *txn, break; } - ondisk->size = sz; + /* Inplace ReorderBuffer content compression before writing it on disk */ + ReorderBufferCompress(rb, &ondisk, logical_decoding_spill_compression, + sz, txn->compressor_state); errno = 0; pgstat_report_wait_start(WAIT_EVENT_REORDER_BUFFER_WRITE); @@ -4011,8 +4045,6 @@ ReorderBufferSerializeChange(ReorderBuffer *rb, ReorderBufferTXN *txn, */ if (txn->final_lsn < change->lsn) txn->final_lsn = change->lsn; - - Assert(ondisk->change.action == change->action); } /* Returns true, if the output plugin supports streaming, false, otherwise. */ @@ -4281,9 +4313,6 @@ ReorderBufferRestoreChanges(ReorderBuffer *rb, ReorderBufferTXN *txn, while (restored < max_changes_in_memory && *segno <= last_segno) { - int readBytes; - ReorderBufferDiskChange *ondisk; - CHECK_FOR_INTERRUPTS(); if (*fd == -1) @@ -4322,60 +4351,15 @@ ReorderBufferRestoreChanges(ReorderBuffer *rb, ReorderBufferTXN *txn, } /* - * Read the statically sized part of a change which has information - * about the total size. If we couldn't read a record, we're at the - * end of this file. + * Read the full change from disk. If ReorderBufferReadOndiskChange + * returns false, then we are at the eof, so, move to the next + * segment. */ - ReorderBufferSerializeReserve(rb, sizeof(ReorderBufferDiskChange)); - readBytes = FileRead(file->vfd, rb->outbuf, - sizeof(ReorderBufferDiskChange), - file->curOffset, WAIT_EVENT_REORDER_BUFFER_READ); - - /* eof */ - if (readBytes == 0) + if (!ReorderBufferReadOndiskChange(rb, txn, file, segno)) { - FileClose(*fd); *fd = -1; - (*segno)++; continue; } - else if (readBytes < 0) - ereport(ERROR, - (errcode_for_file_access(), - errmsg("could not read from reorderbuffer spill file: %m"))); - else if (readBytes != sizeof(ReorderBufferDiskChange)) - ereport(ERROR, - (errcode_for_file_access(), - errmsg("could not read from reorderbuffer spill file: read %d instead of %u bytes", - readBytes, - (uint32) sizeof(ReorderBufferDiskChange)))); - - file->curOffset += readBytes; - - ondisk = (ReorderBufferDiskChange *) rb->outbuf; - - ReorderBufferSerializeReserve(rb, - sizeof(ReorderBufferDiskChange) + ondisk->size); - ondisk = (ReorderBufferDiskChange *) rb->outbuf; - - readBytes = FileRead(file->vfd, - rb->outbuf + sizeof(ReorderBufferDiskChange), - ondisk->size - sizeof(ReorderBufferDiskChange), - file->curOffset, - WAIT_EVENT_REORDER_BUFFER_READ); - - if (readBytes < 0) - ereport(ERROR, - (errcode_for_file_access(), - errmsg("could not read from reorderbuffer spill file: %m"))); - else if (readBytes != ondisk->size - sizeof(ReorderBufferDiskChange)) - ereport(ERROR, - (errcode_for_file_access(), - errmsg("could not read from reorderbuffer spill file: read %d instead of %u bytes", - readBytes, - (uint32) (ondisk->size - sizeof(ReorderBufferDiskChange))))); - - file->curOffset += readBytes; /* * ok, read a full change from disk, now restore it into proper @@ -4388,6 +4372,83 @@ ReorderBufferRestoreChanges(ReorderBuffer *rb, ReorderBufferTXN *txn, return restored; } +/* + * Read a change spilled to disk and decompress it if compressed. + */ +static bool +ReorderBufferReadOndiskChange(ReorderBuffer *rb, ReorderBufferTXN *txn, + TXNEntryFile *file, XLogSegNo *segno) +{ + int readBytes; + ReorderBufferDiskChange *ondisk; + char *header; /* header buffer */ + char *data; /* data buffer */ + + /* + * Read the statically sized part of a change which has information about + * the total size and compression method. If we couldn't read a record, + * we're at the end of this file. + */ + header = (char *) palloc0(sizeof(ReorderBufferDiskChange)); + readBytes = FileRead(file->vfd, header, + sizeof(ReorderBufferDiskChange), + file->curOffset, WAIT_EVENT_REORDER_BUFFER_READ); + + /* eof */ + if (readBytes == 0) + { + + FileClose(file->vfd); + (*segno)++; + pfree(header); + + return false; + } + else if (readBytes < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not read from reorderbuffer spill file: %m"))); + else if (readBytes != sizeof(ReorderBufferDiskChange)) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not read from reorderbuffer spill file: read %d instead of %u bytes", + readBytes, + (uint32) sizeof(ReorderBufferDiskChange)))); + + file->curOffset += readBytes; + + ondisk = (ReorderBufferDiskChange *) header; + + /* Read ondisk data */ + data = (char *) palloc0(ondisk->size - sizeof(ReorderBufferDiskChange)); + readBytes = FileRead(file->vfd, + data, + ondisk->size - sizeof(ReorderBufferDiskChange), + file->curOffset, + WAIT_EVENT_REORDER_BUFFER_READ); + + if (readBytes < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not read from reorderbuffer spill file: %m"))); + else if (readBytes != (ondisk->size - sizeof(ReorderBufferDiskChange))) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not read from reorderbuffer spill file: read %d instead of %u bytes", + readBytes, + (uint32) (ondisk->size - sizeof(ReorderBufferDiskChange))))); + + /* Decompress data */ + ReorderBufferDecompress(rb, data, ondisk, txn->compressor_state); + + pfree(data); + pfree(header); + + file->curOffset += readBytes; + + return true; +} + /* * Convert change from its on-disk format to in-memory format and queue it onto * the TXN's ->changes list. @@ -4400,17 +4461,14 @@ static void ReorderBufferRestoreChange(ReorderBuffer *rb, ReorderBufferTXN *txn, char *data) { - ReorderBufferDiskChange *ondisk; ReorderBufferChange *change; - ondisk = (ReorderBufferDiskChange *) data; - change = ReorderBufferGetChange(rb); /* copy static part */ - memcpy(change, &ondisk->change, sizeof(ReorderBufferChange)); + memcpy(change, data + sizeof(ReorderBufferDiskChange), sizeof(ReorderBufferChange)); - data += sizeof(ReorderBufferDiskChange); + data += sizeof(ReorderBufferDiskChange) + sizeof(ReorderBufferChange); /* restore individual stuff */ switch (change->action) @@ -5338,3 +5396,161 @@ restart: *cmax = ent->cmax; return true; } + +/* + * Allocate a new Compressor State, depending on the compression method. + */ +static void * +ReorderBufferNewCompressorState(MemoryContext context, int compression_method) +{ + switch (compression_method) + { + case REORDER_BUFFER_PGLZ_COMPRESSION: + return pglz_NewCompressorState(context); + break; + case REORDER_BUFFER_NO_COMPRESSION: + default: + return NULL; + break; + } +} + +/* + * Free memory allocated to a Compressor State, depending on the compression + * method. + */ +static void +ReorderBufferFreeCompressorState(MemoryContext context, int compression_method, + void *compressor_state) +{ + switch (compression_method) + { + case REORDER_BUFFER_PGLZ_COMPRESSION: + return pglz_FreeCompressorState(context, compressor_state); + break; + case REORDER_BUFFER_NO_COMPRESSION: + default: + break; + } +} + +/* + * Compress ReorderBuffer content. This function is called in order to compress + * data before spilling on disk. + */ +static void +ReorderBufferCompress(ReorderBuffer *rb, ReorderBufferDiskChange **ondisk, + int compression_method, Size data_size, void *compressor_state) +{ + ReorderBufferDiskChange *hdr = *ondisk; + + switch (compression_method) + { + /* No compression */ + case REORDER_BUFFER_NO_COMPRESSION: + { + hdr->comp_strat = REORDER_BUFFER_STRAT_UNCOMPRESSED; + hdr->size = data_size; + hdr->raw_size = data_size - sizeof(ReorderBufferDiskChange); + + break; + } + /* PGLZ compression */ + case REORDER_BUFFER_PGLZ_COMPRESSION: + { + int32 dst_size = 0; + char *src = (char *) rb->outbuf + sizeof(ReorderBufferDiskChange); + int32 src_size = data_size - sizeof(ReorderBufferDiskChange); + int32 max_size = PGLZ_MAX_OUTPUT(src_size); + PGLZCompressorState *cstate = (PGLZCompressorState *) compressor_state; + + /* + * Make sure the buffer used for data compression has enough + * space. + */ + enlargeStringInfo(cstate->buf, max_size); + + dst_size = pglz_compress(src, src_size, cstate->buf->data, PGLZ_strategy_always); + + cstate->buf->len = dst_size; + + if (dst_size < 0) + ereport(ERROR, + (errcode(ERRCODE_DATA_CORRUPTED), + errmsg_internal("PGLZ compression failed"))); + + ReorderBufferSerializeReserve(rb, (Size) (dst_size + sizeof(ReorderBufferDiskChange))); + + hdr = (ReorderBufferDiskChange *) rb->outbuf; + hdr->comp_strat = REORDER_BUFFER_STRAT_PGLZ; + hdr->size = (Size) dst_size + sizeof(ReorderBufferDiskChange); + hdr->raw_size = (Size) src_size; + + *ondisk = hdr; + + /* Copy back compressed data into the ReorderBuffer */ + memcpy((char *) rb->outbuf + sizeof(ReorderBufferDiskChange), + cstate->buf->data, cstate->buf->len); + + break; + } + default: + /* Other compression methods not yet supported */ + break; + } +} + +/* + * Decompress data read from disk and copy it into the ReorderBuffer. + */ +static void +ReorderBufferDecompress(ReorderBuffer *rb, char *data, + ReorderBufferDiskChange *ondisk, void *compressor_state) +{ + /* + * Make sure the output reorder buffer has enough space to store + * decompressed/raw data. + */ + ReorderBufferSerializeReserve(rb, (Size) (ondisk->raw_size + sizeof(ReorderBufferDiskChange))); + + /* Make a copy of the header read on disk into the ReorderBuffer */ + memcpy(rb->outbuf, (char *) ondisk, sizeof(ReorderBufferDiskChange)); + + switch (ondisk->comp_strat) + { + /* No decompression */ + case REORDER_BUFFER_STRAT_UNCOMPRESSED: + { + /* + * Make a copy of what was read on disk into the reorder + * buffer. + */ + memcpy((char *) rb->outbuf + sizeof(ReorderBufferDiskChange), + data, ondisk->raw_size); + break; + } + /* PGLZ decompression */ + case REORDER_BUFFER_STRAT_PGLZ: + { + char *buf = NULL; + int32 src_size = (int32) ondisk->size - sizeof(ReorderBufferDiskChange); + int32 buf_size = (int32) ondisk->raw_size; + int32 decBytes; + + /* Decompress data directly into the ReorderBuffer */ + buf = (char *) rb->outbuf; + buf += sizeof(ReorderBufferDiskChange); + + decBytes = pglz_decompress(data, src_size, buf, buf_size, false); + + if (decBytes < 0) + ereport(ERROR, + (errcode(ERRCODE_DATA_CORRUPTED), + errmsg_internal("compressed PGLZ data is corrupted"))); + break; + } + default: + /* Other compression methods not yet supported */ + break; + } +} diff --git a/src/backend/replication/logical/reorderbuffer_pglz.c b/src/backend/replication/logical/reorderbuffer_pglz.c new file mode 100644 index 0000000000..1dea81c9fe --- /dev/null +++ b/src/backend/replication/logical/reorderbuffer_pglz.c @@ -0,0 +1,56 @@ +/*------------------------------------------------------------------------- + * + * reorderbuffer_pglz.c + * Functions used for ReorderBuffer compression using PGLZ. + * + * Copyright (c) 2024-2024, PostgreSQL Global Development Group + * + * + * IDENTIFICATION + * src/backend/access/common/reorderbuffer_pglz.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" +#include "replication/reorderbuffer_compression.h" + +/* + * Allocate a new PGLZCompressorState. + */ +void * +pglz_NewCompressorState(MemoryContext context) +{ + PGLZCompressorState *cstate; + MemoryContext oldcontext = MemoryContextSwitchTo(context); + + cstate = (PGLZCompressorState *) + MemoryContextAlloc(context, sizeof(PGLZCompressorState)); + + cstate->buf = makeStringInfo(); + + MemoryContextSwitchTo(oldcontext); + + return (void *) cstate; +} + +/* + * Free PGLZ memory resources and compressor state. + */ +void +pglz_FreeCompressorState(MemoryContext context, void *compressor_state) +{ + PGLZCompressorState *cstate; + MemoryContext oldcontext; + + if (compressor_state == NULL) + return; + + oldcontext = MemoryContextSwitchTo(context); + + cstate = (PGLZCompressorState *) compressor_state; + + destroyStringInfo(cstate->buf); + pfree(compressor_state); + + MemoryContextSwitchTo(oldcontext); +} diff --git a/src/include/replication/reorderbuffer.h b/src/include/replication/reorderbuffer.h index 6ad5a8cb9c..5f231b5f90 100644 --- a/src/include/replication/reorderbuffer.h +++ b/src/include/replication/reorderbuffer.h @@ -26,6 +26,7 @@ /* GUC variables */ extern PGDLLIMPORT int logical_decoding_work_mem; extern PGDLLIMPORT int debug_logical_replication_streaming; +extern PGDLLIMPORT int logical_decoding_spill_compression; /* possible values for debug_logical_replication_streaming */ typedef enum @@ -426,6 +427,11 @@ typedef struct ReorderBufferTXN * Private data pointer of the output plugin. */ void *output_plugin_private; + + /* + * Streaming compression state used for spill files compression. + */ + void *compressor_state; } ReorderBufferTXN; /* so we can define the callbacks used inside struct ReorderBuffer itself */ diff --git a/src/include/replication/reorderbuffer_compression.h b/src/include/replication/reorderbuffer_compression.h new file mode 100644 index 0000000000..9e9565ca7f --- /dev/null +++ b/src/include/replication/reorderbuffer_compression.h @@ -0,0 +1,42 @@ +/*------------------------------------------------------------------------- + * + * reorderbuffer_compression.h + * ReorderBuffer spill files compression. + * + * Copyright (c) 2024-2024, PostgreSQL Global Development Group + * + * src/include/access/reorderbuffer_compression.h + * + *------------------------------------------------------------------------- + */ + +#ifndef REORDERBUFFER_COMPRESSION_H +#define REORDERBUFFER_COMPRESSION_H + +/* ReorderBuffer on disk compression algorithms */ +typedef enum ReorderBufferCompressionMethod +{ + REORDER_BUFFER_NO_COMPRESSION, + REORDER_BUFFER_PGLZ_COMPRESSION, +} ReorderBufferCompressionMethod; + +/* + * Compression strategy applied to ReorderBuffer records spilled on disk + */ +typedef enum ReorderBufferCompressionStrategy +{ + REORDER_BUFFER_STRAT_UNCOMPRESSED, + REORDER_BUFFER_STRAT_PGLZ, +} ReorderBufferCompressionStrategy; + +typedef struct PGLZCompressorState +{ + /* Buffer used to store compressed data */ + StringInfo buf; +} PGLZCompressorState; + +extern void *pglz_NewCompressorState(MemoryContext context); +extern void pglz_FreeCompressorState(MemoryContext context, + void *compressor_state); + +#endif /* REORDERBUFFER_COMPRESSION_H */ -- 2.43.0 [application/octet-stream] v5-0002-Compress-ReorderBuffer-spill-files-using-LZ4.patch (16.1K, ../../CAFEQCbHQDq6ZfVwRDk8Ym2-EAVueSDLRLop0=3cujKTWqGcRmg@mail.gmail.com/3-v5-0002-Compress-ReorderBuffer-spill-files-using-LZ4.patch) download | inline diff: From 3fa3afc0f0ae660b5ab7f6c7851f749adea9e421 Mon Sep 17 00:00:00 2001 From: Julien Tachoires <[email protected]> Date: Tue, 22 Oct 2024 20:40:42 +0200 Subject: [PATCH 2/6] Compress ReorderBuffer spill files using LZ4 --- src/backend/replication/logical/Makefile | 1 + src/backend/replication/logical/meson.build | 1 + .../replication/logical/reorderbuffer.c | 89 +++++- .../replication/logical/reorderbuffer_lz4.c | 268 ++++++++++++++++++ .../replication/reorderbuffer_compression.h | 49 ++++ 5 files changed, 407 insertions(+), 1 deletion(-) create mode 100644 src/backend/replication/logical/reorderbuffer_lz4.c diff --git a/src/backend/replication/logical/Makefile b/src/backend/replication/logical/Makefile index 96c733d009..58dce86258 100644 --- a/src/backend/replication/logical/Makefile +++ b/src/backend/replication/logical/Makefile @@ -27,6 +27,7 @@ OBJS = \ relation.o \ reorderbuffer.o \ reorderbuffer_pglz.o \ + reorderbuffer_lz4.o \ slotsync.o \ snapbuild.o \ tablesync.o \ diff --git a/src/backend/replication/logical/meson.build b/src/backend/replication/logical/meson.build index 48df907b35..dc2f55d0fa 100644 --- a/src/backend/replication/logical/meson.build +++ b/src/backend/replication/logical/meson.build @@ -13,6 +13,7 @@ backend_sources += files( 'relation.c', 'reorderbuffer.c', 'reorderbuffer_pglz.c', + 'reorderbuffer_lz4.c', 'slotsync.c', 'snapbuild.c', 'tablesync.c', diff --git a/src/backend/replication/logical/reorderbuffer.c b/src/backend/replication/logical/reorderbuffer.c index 649b196d5f..5d6f4bbfca 100644 --- a/src/backend/replication/logical/reorderbuffer.c +++ b/src/backend/replication/logical/reorderbuffer.c @@ -219,7 +219,7 @@ static const Size max_changes_in_memory = 4096; /* XXX for restore only */ int debug_logical_replication_streaming = DEBUG_LOGICAL_REP_STREAMING_BUFFERED; /* Compression strategy for spilled data. */ -int logical_decoding_spill_compression = REORDER_BUFFER_PGLZ_COMPRESSION; +int logical_decoding_spill_compression = REORDER_BUFFER_LZ4_COMPRESSION; /* --------------------------------------- * primary reorderbuffer support routines @@ -5408,6 +5408,9 @@ ReorderBufferNewCompressorState(MemoryContext context, int compression_method) case REORDER_BUFFER_PGLZ_COMPRESSION: return pglz_NewCompressorState(context); break; + case REORDER_BUFFER_LZ4_COMPRESSION: + return lz4_NewCompressorState(context); + break; case REORDER_BUFFER_NO_COMPRESSION: default: return NULL; @@ -5428,6 +5431,9 @@ ReorderBufferFreeCompressorState(MemoryContext context, int compression_method, case REORDER_BUFFER_PGLZ_COMPRESSION: return pglz_FreeCompressorState(context, compressor_state); break; + case REORDER_BUFFER_LZ4_COMPRESSION: + return lz4_FreeCompressorState(context, compressor_state); + break; case REORDER_BUFFER_NO_COMPRESSION: default: break; @@ -5494,6 +5500,67 @@ ReorderBufferCompress(ReorderBuffer *rb, ReorderBufferDiskChange **ondisk, break; } + /* LZ4 Compression */ + case REORDER_BUFFER_LZ4_COMPRESSION: + { + Size dst_size = 0; + char *src = (char *) rb->outbuf + sizeof(ReorderBufferDiskChange); + Size src_size = data_size - sizeof(ReorderBufferDiskChange); + StringInfo buf = lz4_GetStringInfoBuffer(compressor_state); + + /* + * LZ4 streaming compression implies keeping a copy of the + * "raw" data in LZ4 input ring buffer. If the "raw" data does + * not fit in this buffer, then we should not try to compress + * it. Let's store it uncompressed. + * + * Even if individual changes larger than 64kB shouldn't + * exist, we still want to be sure that this case is covered + * anyway. + */ + if (unlikely(src_size > LZ4_RING_BUFFER_SIZE)) + return ReorderBufferCompress(rb, ondisk, + REORDER_BUFFER_NO_COMPRESSION, + data_size, compressor_state); + + /* + * Make sure the buffer we'll use to store compressed data has + * enough space. + */ + dst_size = lz4_CompressBound(src_size); + enlargeStringInfo(buf, dst_size); + + /* Use LZ4 streaming compression */ + lz4_StreamingCompressData(rb->context, src, src_size, buf->data, + &dst_size, compressor_state); + buf->len = dst_size; + + /* + * Make sure the ReorderBuffer has enough space to store + * compressed data. Compressed data must be smaller than raw + * data, so, the ReorderBuffer should already have room for + * compressed data, but we do this to avoid buffer overflow + * risks. + */ + ReorderBufferSerializeReserve(rb, (dst_size + sizeof(ReorderBufferDiskChange))); + + hdr = (ReorderBufferDiskChange *) rb->outbuf; + hdr->comp_strat = REORDER_BUFFER_STRAT_LZ4_STREAMING; + hdr->size = dst_size + sizeof(ReorderBufferDiskChange); + hdr->raw_size = src_size; + + /* + * Update ondisk: hdr pointer has potentially changed due to + * ReorderBufferSerializeReserve() + */ + *ondisk = hdr; + + /* Copy back compressed data into the ReorderBuffer */ + memcpy((char *) rb->outbuf + sizeof(ReorderBufferDiskChange), + buf->data, buf->len); + + break; + } default: /* Other compression methods not yet supported */ break; @@ -5549,6 +5616,26 @@ ReorderBufferDecompress(ReorderBuffer *rb, char *data, errmsg_internal("compressed PGLZ data is corrupted"))); break; } + /* LZ4 streaming decompression */ + case REORDER_BUFFER_STRAT_LZ4_STREAMING: + { + char *buf = NULL; + Size src_size = ondisk->size - sizeof(ReorderBufferDiskChange); + Size buf_size = ondisk->raw_size; + + lz4_StreamingDecompressData(rb->context, data, src_size, &buf, + buf_size, compressor_state); + + /* Copy decompressed data into the ReorderBuffer */ + memcpy((char *) rb->outbuf + sizeof(ReorderBufferDiskChange), + buf, buf_size); + + /* + * Not necessary to free buf in this case: it points to the + * decompressed data stored in LZ4 output ring buffer. + */ + break; + } default: /* Other compression methods not yet supported */ break; diff --git a/src/backend/replication/logical/reorderbuffer_lz4.c b/src/backend/replication/logical/reorderbuffer_lz4.c new file mode 100644 index 0000000000..b9a7e717aa --- /dev/null +++ b/src/backend/replication/logical/reorderbuffer_lz4.c @@ -0,0 +1,268 @@ +/*------------------------------------------------------------------------- + * + * reorderbuffer_lz4.c + * Functions for ReorderBuffer compression using LZ4. + * + * Copyright (c) 2024-2024, PostgreSQL Global Development Group + * + * + * IDENTIFICATION + * src/backend/access/common/reorderbuffer_lz4.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#ifdef USE_LZ4 +#include <lz4.h> +#endif + +#include "replication/reorderbuffer_compression.h" + +#define NO_LZ4_SUPPORT() \ + ereport(ERROR, \ + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), \ + errmsg("compression method lz4 not supported"), \ + errdetail("This functionality requires the server to be built with lz4 support."))) + +/* + * Allocate a new LZ4StreamingCompressorState. + */ +void * +lz4_NewCompressorState(MemoryContext context) +{ +#ifndef USE_LZ4 + NO_LZ4_SUPPORT(); + return NULL; /* keep compiler quiet */ +#else + LZ4StreamingCompressorState *cstate; + MemoryContext oldcontext = MemoryContextSwitchTo(context); + + cstate = (LZ4StreamingCompressorState *) + MemoryContextAlloc(context, sizeof(LZ4StreamingCompressorState)); + + cstate->buf = makeStringInfo(); + + /* + * We do not allocate LZ4 ring buffers and streaming handlers at this + * point because we have no guarantee that we will need them later. Let's + * allocate only when we are about to use them. + */ + cstate->lz4_in_buf = NULL; + cstate->lz4_out_buf = NULL; + cstate->lz4_in_buf_offset = 0; + cstate->lz4_out_buf_offset = 0; + cstate->lz4_stream = NULL; + cstate->lz4_stream_decode = NULL; + + MemoryContextSwitchTo(oldcontext); + + return (void *) cstate; +#endif +} + +/* + * Free LZ4 memory resources and the compressor state. + */ +void +lz4_FreeCompressorState(MemoryContext context, void *compressor_state) +{ +#ifndef USE_LZ4 + NO_LZ4_SUPPORT(); +#else + LZ4StreamingCompressorState *cstate; + MemoryContext oldcontext; + + if (compressor_state == NULL) + return; + + oldcontext = MemoryContextSwitchTo(context); + + cstate = (LZ4StreamingCompressorState *) compressor_state; + + destroyStringInfo(cstate->buf); + + if (cstate->lz4_in_buf != NULL) + { + pfree(cstate->lz4_in_buf); + LZ4_freeStream(cstate->lz4_stream); + } + if (cstate->lz4_out_buf != NULL) + { + pfree(cstate->lz4_out_buf); + LZ4_freeStreamDecode(cstate->lz4_stream_decode); + } + + pfree(compressor_state); + + MemoryContextSwitchTo(oldcontext); +#endif +} + +#ifdef USE_LZ4 +/* + * Allocate LZ4 input ring buffer and create the streaming compression handler. + */ +static void +lz4_CreateStreamCompressorState(MemoryContext context, void *compressor_state) +{ + LZ4StreamingCompressorState *cstate; + MemoryContext oldcontext = MemoryContextSwitchTo(context); + + cstate = (LZ4StreamingCompressorState *) compressor_state; + cstate->lz4_in_buf = (char *) palloc0(LZ4_RING_BUFFER_SIZE); + cstate->lz4_stream = LZ4_createStream(); + + MemoryContextSwitchTo(oldcontext); +} +#endif + +#ifdef USE_LZ4 +/* + * Allocate LZ4 output ring buffer and create the streaming decompression + */ +static void +lz4_CreateStreamDecodeCompressorState(MemoryContext context, + void *compressor_state) +{ + LZ4StreamingCompressorState *cstate; + MemoryContext oldcontext = MemoryContextSwitchTo(context); + + cstate = (LZ4StreamingCompressorState *) compressor_state; + cstate->lz4_out_buf = (char *) palloc0(LZ4_RING_BUFFER_SIZE); + cstate->lz4_stream_decode = LZ4_createStreamDecode(); + + MemoryContextSwitchTo(oldcontext); +} +#endif + +/* + * Data compression using LZ4 streaming API. + */ +void +lz4_StreamingCompressData(MemoryContext context, char *src, Size src_size, + char *dst, Size *dst_size, void *compressor_state) +{ +#ifndef USE_LZ4 + NO_LZ4_SUPPORT(); +#else + LZ4StreamingCompressorState *cstate; + int lz4_cmp_size = 0; /* compressed size */ + char *lz4_in_bufPtr; /* input ring buffer pointer */ + + cstate = (LZ4StreamingCompressorState *) compressor_state; + + /* Allocate LZ4 input ring buffer and streaming compression handler */ + if (cstate->lz4_in_buf == NULL) + lz4_CreateStreamCompressorState(context, compressor_state); + + /* Ring buffer offset wraparound */ + if ((cstate->lz4_in_buf_offset + src_size) > LZ4_RING_BUFFER_SIZE) + cstate->lz4_in_buf_offset = 0; + + /* Get the pointer of the next entry in the ring buffer */ + lz4_in_bufPtr = cstate->lz4_in_buf + cstate->lz4_in_buf_offset; + + /* Copy data that should be compressed into LZ4 input ring buffer */ + memcpy(lz4_in_bufPtr, src, src_size); + + /* Use LZ4 streaming compression API */ + lz4_cmp_size = LZ4_compress_fast_continue(cstate->lz4_stream, + lz4_in_bufPtr, dst, src_size, + *dst_size, 1); + + if (lz4_cmp_size <= 0) + ereport(ERROR, + (errcode(ERRCODE_DATA_CORRUPTED), + errmsg_internal("LZ4 compression failed"))); + + /* Move the input ring buffer offset */ + cstate->lz4_in_buf_offset += src_size; + + *dst_size = lz4_cmp_size; +#endif +} + +/* + * Data decompression using LZ4 streaming API. + * LZ4 decompression uses the output ring buffer to store decompressed data, + * thus, we don't need to create a new buffer. We return the pointer to data + * location. + */ +void +lz4_StreamingDecompressData(MemoryContext context, char *src, Size src_size, + char **dst, Size dst_size, void *compressor_state) +{ +#ifndef USE_LZ4 + NO_LZ4_SUPPORT(); +#else + LZ4StreamingCompressorState *cstate; + char *lz4_out_bufPtr; /* output ring buffer pointer */ + int lz4_dec_size; /* decompressed data size */ + + cstate = (LZ4StreamingCompressorState *) compressor_state; + + /* Allocate LZ4 output ring buffer and streaming decompression handler */ + if (cstate->lz4_out_buf == NULL) + lz4_CreateStreamDecodeCompressorState(context, compressor_state); + + /* Ring buffer offset wraparound */ + if ((cstate->lz4_out_buf_offset + dst_size) > LZ4_RING_BUFFER_SIZE) + cstate->lz4_out_buf_offset = 0; + + /* Get current entry pointer in the ring buffer */ + lz4_out_bufPtr = cstate->lz4_out_buf + cstate->lz4_out_buf_offset; + + lz4_dec_size = LZ4_decompress_safe_continue(cstate->lz4_stream_decode, + src, + lz4_out_bufPtr, + src_size, + dst_size); + + Assert(lz4_dec_size == dst_size); + + if (lz4_dec_size < 0) + ereport(ERROR, + (errcode(ERRCODE_DATA_CORRUPTED), + errmsg_internal("compressed LZ4 data is corrupted"))); + else if (lz4_dec_size != dst_size) + ereport(ERROR, + (errcode(ERRCODE_DATA_CORRUPTED), + errmsg_internal("decompressed LZ4 data size differs from original size"))); + + /* Move the output ring buffer offset */ + cstate->lz4_out_buf_offset += lz4_dec_size; + + /* Point to the decompressed data location */ + *dst = lz4_out_bufPtr; +#endif +} + +Size +lz4_CompressBound(Size src_size) +{ +#ifndef USE_LZ4 + NO_LZ4_SUPPORT(); + return -1; +#else + return LZ4_COMPRESSBOUND(src_size); +#endif +} + +/* + * Returns the StringInfo buffer we use to store compressed/decompressed data. + */ +StringInfo +lz4_GetStringInfoBuffer(void *compressor_state) +{ +#ifndef USE_LZ4 + NO_LZ4_SUPPORT(); + return NULL; +#else + LZ4StreamingCompressorState *cstate; + + cstate = (LZ4StreamingCompressorState *) compressor_state; + + return cstate->buf; +#endif +} diff --git a/src/include/replication/reorderbuffer_compression.h b/src/include/replication/reorderbuffer_compression.h index 9e9565ca7f..3dbf47e18e 100644 --- a/src/include/replication/reorderbuffer_compression.h +++ b/src/include/replication/reorderbuffer_compression.h @@ -13,11 +13,16 @@ #ifndef REORDERBUFFER_COMPRESSION_H #define REORDERBUFFER_COMPRESSION_H +#ifdef USE_LZ4 +#include <lz4.h> +#endif + /* ReorderBuffer on disk compression algorithms */ typedef enum ReorderBufferCompressionMethod { REORDER_BUFFER_NO_COMPRESSION, REORDER_BUFFER_PGLZ_COMPRESSION, + REORDER_BUFFER_LZ4_COMPRESSION, } ReorderBufferCompressionMethod; /* @@ -27,6 +32,7 @@ typedef enum ReorderBufferCompressionStrategy { REORDER_BUFFER_STRAT_UNCOMPRESSED, REORDER_BUFFER_STRAT_PGLZ, + REORDER_BUFFER_STRAT_LZ4_STREAMING, } ReorderBufferCompressionStrategy; typedef struct PGLZCompressorState @@ -35,6 +41,49 @@ typedef struct PGLZCompressorState StringInfo buf; } PGLZCompressorState; +#ifdef USE_LZ4 +/* + * We use a fairly small LZ4 ring buffer size (64kB). Using a larger buffer + * size provide better compression ratio, but as long as we have to allocate + * two LZ4 ring buffers per ReorderBufferTXN, we should keep it small. + + * 64kB is also twice the maximum size of a block, which is enough to cover + * changes like UPDATE that will contain data of the old and new version of a + * tuple. + */ +#define LZ4_RING_BUFFER_SIZE (64 * 1024) + +/* + * LZ4 streaming compression/decompression contextes and buffers. + */ +typedef struct LZ4StreamingCompressorState +{ + /* Streaming compression handler */ + LZ4_stream_t *lz4_stream; + /* Streaming decompression handler */ + LZ4_streamDecode_t *lz4_stream_decode; + /* LZ4 in/out ring buffers used for streaming compression */ + char *lz4_in_buf; + int lz4_in_buf_offset; + char *lz4_out_buf; + int lz4_out_buf_offset; + /* Buffer used to store compressed data */ + StringInfo buf; +} LZ4StreamingCompressorState; +#endif + +extern void *lz4_NewCompressorState(MemoryContext context); +extern void lz4_FreeCompressorState(MemoryContext context, + void *compressor_state); +extern void lz4_StreamingCompressData(MemoryContext context, char *src, + Size src_size, char *dst, Size *dst_size, + void *compressor_state); +extern void lz4_StreamingDecompressData(MemoryContext context, char *src, + Size src_size, char **dst, + Size dst_size, void *compressor_state); +extern Size lz4_CompressBound(Size src_size); +extern StringInfo lz4_GetStringInfoBuffer(void *compressor_state); + extern void *pglz_NewCompressorState(MemoryContext context); extern void pglz_FreeCompressorState(MemoryContext context, void *compressor_state); -- 2.43.0 [application/octet-stream] v5-0003-Compress-ReorderBuffer-spill-files-using-ZSTD.patch (17.6K, ../../CAFEQCbHQDq6ZfVwRDk8Ym2-EAVueSDLRLop0=3cujKTWqGcRmg@mail.gmail.com/4-v5-0003-Compress-ReorderBuffer-spill-files-using-ZSTD.patch) download | inline diff: From 27d33e09549d7f96b750750256ed37ec2eb9a04d Mon Sep 17 00:00:00 2001 From: Julien Tachoires <[email protected]> Date: Tue, 22 Oct 2024 22:31:13 +0200 Subject: [PATCH 3/6] Compress ReorderBuffer spill files using ZSTD --- src/backend/replication/logical/Makefile | 1 + src/backend/replication/logical/meson.build | 1 + .../replication/logical/reorderbuffer.c | 58 ++- .../replication/logical/reorderbuffer_zstd.c | 361 ++++++++++++++++++ .../replication/reorderbuffer_compression.h | 54 +++ 5 files changed, 474 insertions(+), 1 deletion(-) create mode 100644 src/backend/replication/logical/reorderbuffer_zstd.c diff --git a/src/backend/replication/logical/Makefile b/src/backend/replication/logical/Makefile index 58dce86258..c71f06b729 100644 --- a/src/backend/replication/logical/Makefile +++ b/src/backend/replication/logical/Makefile @@ -28,6 +28,7 @@ OBJS = \ reorderbuffer.o \ reorderbuffer_pglz.o \ reorderbuffer_lz4.o \ + reorderbuffer_zstd.o \ slotsync.o \ snapbuild.o \ tablesync.o \ diff --git a/src/backend/replication/logical/meson.build b/src/backend/replication/logical/meson.build index dc2f55d0fa..70b8777290 100644 --- a/src/backend/replication/logical/meson.build +++ b/src/backend/replication/logical/meson.build @@ -14,6 +14,7 @@ backend_sources += files( 'reorderbuffer.c', 'reorderbuffer_pglz.c', 'reorderbuffer_lz4.c', + 'reorderbuffer_zstd.c', 'slotsync.c', 'snapbuild.c', 'tablesync.c', diff --git a/src/backend/replication/logical/reorderbuffer.c b/src/backend/replication/logical/reorderbuffer.c index 5d6f4bbfca..17f8208000 100644 --- a/src/backend/replication/logical/reorderbuffer.c +++ b/src/backend/replication/logical/reorderbuffer.c @@ -219,7 +219,7 @@ static const Size max_changes_in_memory = 4096; /* XXX for restore only */ int debug_logical_replication_streaming = DEBUG_LOGICAL_REP_STREAMING_BUFFERED; /* Compression strategy for spilled data. */ -int logical_decoding_spill_compression = REORDER_BUFFER_LZ4_COMPRESSION; +int logical_decoding_spill_compression = REORDER_BUFFER_ZSTD_COMPRESSION; /* --------------------------------------- * primary reorderbuffer support routines @@ -5411,6 +5411,9 @@ ReorderBufferNewCompressorState(MemoryContext context, int compression_method) case REORDER_BUFFER_LZ4_COMPRESSION: return lz4_NewCompressorState(context); break; + case REORDER_BUFFER_ZSTD_COMPRESSION: + return zstd_NewCompressorState(context); + break; case REORDER_BUFFER_NO_COMPRESSION: default: return NULL; @@ -5434,6 +5437,9 @@ ReorderBufferFreeCompressorState(MemoryContext context, int compression_method, case REORDER_BUFFER_LZ4_COMPRESSION: return lz4_FreeCompressorState(context, compressor_state); break; + case REORDER_BUFFER_ZSTD_COMPRESSION: + return zstd_FreeCompressorState(context, compressor_state); + break; case REORDER_BUFFER_NO_COMPRESSION: default: break; @@ -5561,6 +5567,37 @@ ReorderBufferCompress(ReorderBuffer *rb, ReorderBufferDiskChange **ondisk, break; } + /* ZSTD Compression */ + case REORDER_BUFFER_ZSTD_COMPRESSION: + { + Size dst_size = 0; + char *src = (char *) rb->outbuf + sizeof(ReorderBufferDiskChange); + Size src_size = data_size - sizeof(ReorderBufferDiskChange); + StringInfo buf = zstd_GetStringInfoBuffer(compressor_state); + + dst_size = zstd_CompressBound(src_size); + enlargeStringInfo(buf, dst_size); + + /* Use ZSTD streaming compression */ + zstd_StreamingCompressData(rb->context, src, src_size, + buf->data, &dst_size, + compressor_state); + buf->len = dst_size; + + ReorderBufferSerializeReserve(rb, (dst_size + sizeof(ReorderBufferDiskChange))); + + hdr = (ReorderBufferDiskChange *) rb->outbuf; + hdr->comp_strat = REORDER_BUFFER_STRAT_ZSTD_STREAMING; + hdr->size = dst_size + sizeof(ReorderBufferDiskChange); + hdr->raw_size = src_size; + + *ondisk = hdr; + + memcpy((char *) rb->outbuf + sizeof(ReorderBufferDiskChange), + buf->data, buf->len); + + break; + } default: /* Other compression methods not yet supported */ break; @@ -5636,6 +5673,25 @@ ReorderBufferDecompress(ReorderBuffer *rb, char *data, */ break; } + /* ZSTD streaming decompression */ + case REORDER_BUFFER_STRAT_ZSTD_STREAMING: + { + StringInfo buf = zstd_GetStringInfoBuffer(compressor_state); + Size src_size = ondisk->size - sizeof(ReorderBufferDiskChange); + Size buf_size = ondisk->raw_size; + + enlargeStringInfo(buf, buf_size); + + zstd_StreamingDecompressData(rb->context, data, src_size, + buf->data, buf_size, + compressor_state); + buf->len = buf_size; + + /* Copy decompressed data into the ReorderBuffer */ + memcpy((char *) rb->outbuf + sizeof(ReorderBufferDiskChange), + buf->data, buf->len); + break; + } default: /* Other compression methods not yet supported */ break; diff --git a/src/backend/replication/logical/reorderbuffer_zstd.c b/src/backend/replication/logical/reorderbuffer_zstd.c new file mode 100644 index 0000000000..83455a9c8b --- /dev/null +++ b/src/backend/replication/logical/reorderbuffer_zstd.c @@ -0,0 +1,361 @@ +/*------------------------------------------------------------------------- + * + * reorderbuffer_zstd.c + * Functions for ReorderBuffer compression using ZSTD. + * + * Copyright (c) 2024-2024, PostgreSQL Global Development Group + * + * + * IDENTIFICATION + * src/backend/access/common/reorderbuffer_zstd.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#ifdef USE_ZSTD +#include <zstd.h> +#endif + +#include "replication/reorderbuffer_compression.h" + +#define NO_ZSTD_SUPPORT() \ + ereport(ERROR, \ + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), \ + errmsg("compression method zstd not supported"), \ + errdetail("This functionality requires the server to be built with zstd support."))) + +/* + * Allocate a new ZSTDStreamingCompressorState. + */ +void * +zstd_NewCompressorState(MemoryContext context) +{ +#ifndef USE_ZSTD + NO_ZSTD_SUPPORT(); + return NULL; /* keep compiler quiet */ +#else + ZSTDStreamingCompressorState *cstate; + MemoryContext oldcontext = MemoryContextSwitchTo(context); + + cstate = (ZSTDStreamingCompressorState *) + MemoryContextAlloc(context, sizeof(ZSTDStreamingCompressorState)); + + cstate->buf = makeStringInfo(); + + /* + * We do not allocate ZSTD buffers and contexts at this point because we + * have no guarantee that we will need them later. Let's allocate only + * when we are about to use them. + */ + cstate->zstd_c_ctx = NULL; + cstate->zstd_c_in_buf = NULL; + cstate->zstd_c_in_buf_size = 0; + cstate->zstd_c_out_buf = NULL; + cstate->zstd_c_out_buf_size = 0; + cstate->zstd_frame_size = 0; + cstate->zstd_d_ctx = NULL; + cstate->zstd_d_in_buf = NULL; + cstate->zstd_d_in_buf_size = 0; + cstate->zstd_d_out_buf = NULL; + cstate->zstd_d_out_buf_size = 0; + + MemoryContextSwitchTo(oldcontext); + + return (void *) cstate; +#endif +} + +/* + * Free ZSTD memory resources and the compressor state. + */ +void +zstd_FreeCompressorState(MemoryContext context, void *compressor_state) +{ +#ifndef USE_ZSTD + NO_ZSTD_SUPPORT(); +#else + ZSTDStreamingCompressorState *cstate; + MemoryContext oldcontext; + + if (compressor_state == NULL) + return; + + oldcontext = MemoryContextSwitchTo(context); + + cstate = (ZSTDStreamingCompressorState *) compressor_state; + + destroyStringInfo(cstate->buf); + + if (cstate->zstd_c_ctx != NULL) + { + /* Compressor state was used for compression */ + pfree(cstate->zstd_c_in_buf); + pfree(cstate->zstd_c_out_buf); + ZSTD_freeCCtx(cstate->zstd_c_ctx); + } + if (cstate->zstd_d_ctx != NULL) + { + /* Compressor state was used for decompression */ + pfree(cstate->zstd_d_in_buf); + pfree(cstate->zstd_d_out_buf); + ZSTD_freeDCtx(cstate->zstd_d_ctx); + } + + pfree(compressor_state); + + MemoryContextSwitchTo(oldcontext); +#endif +} + +#ifdef USE_ZSTD +/* + * Allocate ZSTD compression buffers and create the ZSTD compression context. + */ +static void +zstd_CreateStreamCompressorState(MemoryContext context, void *compressor_state) +{ + ZSTDStreamingCompressorState *cstate; + MemoryContext oldcontext = MemoryContextSwitchTo(context); + + cstate = (ZSTDStreamingCompressorState *) compressor_state; + cstate->zstd_c_in_buf_size = ZSTD_CStreamInSize(); + cstate->zstd_c_in_buf = (char *) palloc0(cstate->zstd_c_in_buf_size); + cstate->zstd_c_out_buf_size = ZSTD_CStreamOutSize(); + cstate->zstd_c_out_buf = (char *) palloc0(cstate->zstd_c_out_buf_size); + cstate->zstd_c_ctx = ZSTD_createCCtx(); + + if (cstate->zstd_c_ctx == NULL) + ereport(ERROR, + (errcode(ERRCODE_OUT_OF_MEMORY), + errmsg("could not create ZSTD compression context"))); + + /* Set compression level */ + ZSTD_CCtx_setParameter(cstate->zstd_c_ctx, ZSTD_c_compressionLevel, + ZSTD_COMPRESSION_LEVEL); + + MemoryContextSwitchTo(oldcontext); +} +#endif + +#ifdef USE_ZSTD +/* + * Allocate ZSTD decompression buffers and create the ZSTD decompression + * context. + */ +static void +zstd_CreateStreamDecodeCompressorState(MemoryContext context, void *compressor_state) +{ + ZSTDStreamingCompressorState *cstate; + MemoryContext oldcontext = MemoryContextSwitchTo(context); + + cstate = (ZSTDStreamingCompressorState *) compressor_state; + cstate->zstd_d_in_buf_size = ZSTD_DStreamInSize(); + cstate->zstd_d_in_buf = (char *) palloc0(cstate->zstd_d_in_buf_size); + cstate->zstd_d_out_buf_size = ZSTD_DStreamOutSize(); + cstate->zstd_d_out_buf = (char *) palloc0(cstate->zstd_d_out_buf_size); + cstate->zstd_d_ctx = ZSTD_createDCtx(); + + if (cstate->zstd_d_ctx == NULL) + ereport(ERROR, + (errcode(ERRCODE_OUT_OF_MEMORY), + errmsg("could not create ZSTD decompression context"))); + + MemoryContextSwitchTo(oldcontext); +} +#endif + +/* + * Data compression using ZSTD streaming API. + */ +void +zstd_StreamingCompressData(MemoryContext context, char *src, Size src_size, + char *dst, Size *dst_size, void *compressor_state) +{ +#ifndef USE_ZSTD + NO_ZSTD_SUPPORT(); +#else + ZSTDStreamingCompressorState *cstate; + + /* Size of remaining data to be copied from src into ZSTD input buffer */ + Size toCpy = src_size; + char *dst_data; + + cstate = (ZSTDStreamingCompressorState *) compressor_state; + /* Allocate ZSTD buffers and context */ + if (cstate->zstd_c_ctx == NULL) + zstd_CreateStreamCompressorState(context, compressor_state); + + dst_data = dst; + *dst_size = 0; + + /* + * ZSTD streaming compression works with chunks: the source data needs to + * be splitted out in chunks, each of them is then copied into ZSTD input + * buffer. For each chunk, we proceed with compression. Streaming + * compression is not intended to compress the whole input chunk, so we + * have the call ZSTD_compressStream2() multiple times until the entire + * chunk is consumed. + */ + while (toCpy > 0) + { + /* Are we on the last chunk? */ + bool last_chunk = (toCpy < cstate->zstd_c_in_buf_size); + + /* Size of the data copied into ZSTD input buffer */ + Size cpySize = last_chunk ? toCpy : cstate->zstd_c_in_buf_size; + bool finished = false; + ZSTD_inBuffer input; + ZSTD_EndDirective mode = last_chunk ? ZSTD_e_flush : ZSTD_e_continue; + + /* Copy data from src into ZSTD input buffer */ + memcpy(cstate->zstd_c_in_buf, src, cpySize); + + /* + * Close the frame when we are on the last chunk and we've reached max + * frame size. + */ + if (last_chunk && (cstate->zstd_frame_size > ZSTD_MAX_FRAME_SIZE)) + { + mode = ZSTD_e_end; + cstate->zstd_frame_size = 0; + } + + cstate->zstd_frame_size += cpySize; + + input.src = cstate->zstd_c_in_buf; + input.size = cpySize; + input.pos = 0; + + do + { + Size remaining; + ZSTD_outBuffer output; + + output.dst = cstate->zstd_c_out_buf; + output.size = cstate->zstd_c_out_buf_size; + output.pos = 0; + + remaining = ZSTD_compressStream2(cstate->zstd_c_ctx, &output, + &input, mode); + + if (ZSTD_isError(remaining)) + ereport(ERROR, + (errcode(ERRCODE_DATA_CORRUPTED), + errmsg_internal("ZSTD compression failed"))); + + /* Copy back compressed data from ZSTD output buffer */ + memcpy(dst_data, (char *) cstate->zstd_c_out_buf, output.pos); + + dst_data += output.pos; + *dst_size += output.pos; + + /* + * Compression is done when we are working on the last chunk and + * there is nothing left to compress, or, when we reach the end of + * the chunk. + */ + finished = last_chunk ? (remaining == 0) : (input.pos == input.size); + } while (!finished); + + src += cpySize; + toCpy -= cpySize; + } +#endif +} + +/* + * Data decompression using ZSTD streaming API. + */ +void +zstd_StreamingDecompressData(MemoryContext context, char *src, Size src_size, + char *dst, Size dst_size, void *compressor_state) +{ +#ifndef USE_ZSTD + NO_ZSTD_SUPPORT(); +#else + ZSTDStreamingCompressorState *cstate; + + /* Size of remaining data to be copied from src into ZSTD input buffer */ + Size toCpy = src_size; + char *dst_data; + Size decBytes = 0; /* Size of decompressed data */ + + cstate = (ZSTDStreamingCompressorState *) compressor_state; + /* Allocate ZSTD buffers and context */ + if (cstate->zstd_d_ctx == NULL) + zstd_CreateStreamDecodeCompressorState(context, compressor_state); + + dst_data = dst; + + while (toCpy > 0) + { + ZSTD_inBuffer input; + Size cpySize = (toCpy > cstate->zstd_d_in_buf_size) ? cstate->zstd_d_in_buf_size : toCpy; + + /* Copy data from src into ZSTD input buffer */ + memcpy(cstate->zstd_d_in_buf, src, cpySize); + + input.src = cstate->zstd_d_in_buf; + input.size = cpySize; + input.pos = 0; + + while (input.pos < input.size) + { + ZSTD_outBuffer output; + Size ret; + + output.dst = cstate->zstd_d_out_buf; + output.size = cstate->zstd_d_out_buf_size; + output.pos = 0; + + ret = ZSTD_decompressStream(cstate->zstd_d_ctx, &output, &input); + + if (ZSTD_isError(ret)) + ereport(ERROR, + (errcode(ERRCODE_DATA_CORRUPTED), + errmsg_internal("ZSTD decompression failed"))); + + /* Copy back compressed data from ZSTD output buffer */ + memcpy(dst_data, (char *) cstate->zstd_d_out_buf, output.pos); + + dst_data += output.pos; + decBytes += output.pos; + } + + src += cpySize; + toCpy -= cpySize; + } + + Assert(dst_size == decBytes); +#endif +} + +Size +zstd_CompressBound(Size src_size) +{ +#ifndef USE_ZSTD + NO_ZSTD_SUPPORT(); + return -1; +#else + return ZSTD_compressBound(src_size); +#endif +} + +/* + * Returns the StringInfo buffer we use to store compressed/decompressed data. + */ +StringInfo +zstd_GetStringInfoBuffer(void *compressor_state) +{ +#ifndef USE_ZSTD + NO_ZSTD_SUPPORT(); + return NULL; +#else + ZSTDStreamingCompressorState *cstate; + + cstate = (ZSTDStreamingCompressorState *) compressor_state; + + return cstate->buf; +#endif +} diff --git a/src/include/replication/reorderbuffer_compression.h b/src/include/replication/reorderbuffer_compression.h index 3dbf47e18e..240c188f00 100644 --- a/src/include/replication/reorderbuffer_compression.h +++ b/src/include/replication/reorderbuffer_compression.h @@ -17,12 +17,17 @@ #include <lz4.h> #endif +#ifdef USE_ZSTD +#include <zstd.h> +#endif + /* ReorderBuffer on disk compression algorithms */ typedef enum ReorderBufferCompressionMethod { REORDER_BUFFER_NO_COMPRESSION, REORDER_BUFFER_PGLZ_COMPRESSION, REORDER_BUFFER_LZ4_COMPRESSION, + REORDER_BUFFER_ZSTD_COMPRESSION, } ReorderBufferCompressionMethod; /* @@ -33,6 +38,7 @@ typedef enum ReorderBufferCompressionStrategy REORDER_BUFFER_STRAT_UNCOMPRESSED, REORDER_BUFFER_STRAT_PGLZ, REORDER_BUFFER_STRAT_LZ4_STREAMING, + REORDER_BUFFER_STRAT_ZSTD_STREAMING, } ReorderBufferCompressionStrategy; typedef struct PGLZCompressorState @@ -72,6 +78,42 @@ typedef struct LZ4StreamingCompressorState } LZ4StreamingCompressorState; #endif +#ifdef USE_ZSTD +/* + * Low compression level provides high compression speed and decent compression + * rate. Minimum level is 1, maximum is 22. + */ +#define ZSTD_COMPRESSION_LEVEL 1 + +/* + * Maximum volume of data encoded in the current ZSTD frame. When this + * threshold is reached then we close the current frame and start a new one. + */ +#define ZSTD_MAX_FRAME_SIZE (64 * 1024) + +/* + * ZSTD streaming compression/decompression handlers and buffers. + */ +typedef struct ZSTDStreamingCompressorState +{ + /* Compression */ + ZSTD_CCtx *zstd_c_ctx; + Size zstd_c_in_buf_size; + char *zstd_c_in_buf; + Size zstd_c_out_buf_size; + char *zstd_c_out_buf; + Size zstd_frame_size; + /* Decompression */ + ZSTD_DCtx *zstd_d_ctx; + Size zstd_d_in_buf_size; + char *zstd_d_in_buf; + Size zstd_d_out_buf_size; + char *zstd_d_out_buf; + /* Buffer used to store compressed/decompressed data */ + StringInfo buf; +} ZSTDStreamingCompressorState; +#endif + extern void *lz4_NewCompressorState(MemoryContext context); extern void lz4_FreeCompressorState(MemoryContext context, void *compressor_state); @@ -88,4 +130,16 @@ extern void *pglz_NewCompressorState(MemoryContext context); extern void pglz_FreeCompressorState(MemoryContext context, void *compressor_state); +extern void *zstd_NewCompressorState(MemoryContext context); +extern void zstd_FreeCompressorState(MemoryContext context, + void *compressor_state); +extern void zstd_StreamingCompressData(MemoryContext context, char *src, + Size src_size, char *dst, Size *dst_size, + void *compressor_state); +extern void zstd_StreamingDecompressData(MemoryContext context, char *src, + Size src_size, char *dst, + Size dst_size, void *compressor_state); +extern Size zstd_CompressBound(Size src_size); +extern StringInfo zstd_GetStringInfoBuffer(void *compressor_state); + #endif /* REORDERBUFFER_COMPRESSION_H */ -- 2.43.0 [application/octet-stream] v5-0005-Track-data-size-written-to-disk-during-logical-decod.patch (16.3K, ../../CAFEQCbHQDq6ZfVwRDk8Ym2-EAVueSDLRLop0=3cujKTWqGcRmg@mail.gmail.com/5-v5-0005-Track-data-size-written-to-disk-during-logical-decod.patch) download | inline diff: From e6892c7ef32cd79780808517a4bd21e392d905b8 Mon Sep 17 00:00:00 2001 From: Julien Tachoires <[email protected]> Date: Thu, 24 Oct 2024 12:55:09 +0200 Subject: [PATCH 5/6] Track data size written to disk during logical decoding The pg_stat_replication_slots view now exposes a new counter named "spill_write_bytes". This counter is in charge of tracking the amount of data written to disk during logical decoding. This counter differs from "spill_bytes" as the data can be compressed before being written to disk. When data compression is not enabled (default), "spill_bytes" and "spill_write_bytes" are equal. --- contrib/test_decoding/expected/stats.out | 12 +++---- doc/src/sgml/monitoring.sgml | 22 ++++++++++--- src/backend/catalog/system_views.sql | 1 + src/backend/replication/logical/logical.c | 5 ++- .../replication/logical/reorderbuffer.c | 11 +++++-- src/backend/utils/activity/pgstat_replslot.c | 1 + src/backend/utils/adt/pgstatfuncs.c | 31 ++++++++++--------- src/include/catalog/pg_proc.dat | 6 ++-- src/include/pgstat.h | 1 + src/include/replication/reorderbuffer.h | 4 ++- src/test/regress/expected/rules.out | 3 +- 11 files changed, 64 insertions(+), 33 deletions(-) diff --git a/contrib/test_decoding/expected/stats.out b/contrib/test_decoding/expected/stats.out index 78d36429c8..caad65b64c 100644 --- a/contrib/test_decoding/expected/stats.out +++ b/contrib/test_decoding/expected/stats.out @@ -78,17 +78,17 @@ SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, -- verify accessing/resetting stats for non-existent slot does something reasonable SELECT * FROM pg_stat_get_replication_slot('do-not-exist'); - slot_name | spill_txns | spill_count | spill_bytes | stream_txns | stream_count | stream_bytes | total_txns | total_bytes | stats_reset ---------------+------------+-------------+-------------+-------------+--------------+--------------+------------+-------------+------------- - do-not-exist | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | + slot_name | spill_txns | spill_count | spill_bytes | spill_write_bytes | stream_txns | stream_count | stream_bytes | total_txns | total_bytes | stats_reset +--------------+------------+-------------+-------------+-------------------+-------------+--------------+--------------+------------+-------------+------------- + do-not-exist | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | (1 row) SELECT pg_stat_reset_replication_slot('do-not-exist'); ERROR: replication slot "do-not-exist" does not exist SELECT * FROM pg_stat_get_replication_slot('do-not-exist'); - slot_name | spill_txns | spill_count | spill_bytes | stream_txns | stream_count | stream_bytes | total_txns | total_bytes | stats_reset ---------------+------------+-------------+-------------+-------------+--------------+--------------+------------+-------------+------------- - do-not-exist | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | + slot_name | spill_txns | spill_count | spill_bytes | spill_write_bytes | stream_txns | stream_count | stream_bytes | total_txns | total_bytes | stats_reset +--------------+------------+-------------+-------------+-------------------+-------------+--------------+--------------+------------+-------------+------------- + do-not-exist | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | (1 row) -- spilling the xact diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 331315f8d3..d251e4cfa8 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -1561,10 +1561,24 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structfield>spill_bytes</structfield> <type>bigint</type> </para> <para> - Amount of decoded transaction data spilled to disk while performing - decoding of changes from WAL for this slot. This and other spill - counters can be used to gauge the I/O which occurred during logical - decoding and allow tuning <literal>logical_decoding_work_mem</literal>. + Amount of decoded transaction data requiring to be eventually compressed + and spilled to disk while performing decoding of changes from WAL for + this slot. This and other spill counters can be used to gauge the I/O + which occurred during logical decoding and allow tuning + <literal>logical_decoding_work_mem</literal>. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>spill_write_bytes</structfield> <type>bigint</type> + </para> + <para> + Amount of decoded transaction data eventually compressed and spilled + to disk while performing decoding of changes from WAL for this slot. + When using data compression, <literal>spill_write_bytes</literal> + should be significantly smaller than <literal>spill_bytes</literal> + which represents uncompressed data size. </para></entry> </row> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index a087223677..7ab046cb70 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1030,6 +1030,7 @@ CREATE VIEW pg_stat_replication_slots AS s.spill_txns, s.spill_count, s.spill_bytes, + s.spill_write_bytes, s.stream_txns, s.stream_count, s.stream_bytes, diff --git a/src/backend/replication/logical/logical.c b/src/backend/replication/logical/logical.c index 54fbbe6fea..951318a994 100644 --- a/src/backend/replication/logical/logical.c +++ b/src/backend/replication/logical/logical.c @@ -1942,11 +1942,12 @@ UpdateDecodingStats(LogicalDecodingContext *ctx) if (rb->spillBytes <= 0 && rb->streamBytes <= 0 && rb->totalBytes <= 0) return; - elog(DEBUG2, "UpdateDecodingStats: updating stats %p %lld %lld %lld %lld %lld %lld %lld %lld", + elog(DEBUG2, "UpdateDecodingStats: updating stats %p %lld %lld %lld %lld %lld %lld %lld %lld %lld", rb, (long long) rb->spillTxns, (long long) rb->spillCount, (long long) rb->spillBytes, + (long long) rb->spillWriteBytes, (long long) rb->streamTxns, (long long) rb->streamCount, (long long) rb->streamBytes, @@ -1956,6 +1957,7 @@ UpdateDecodingStats(LogicalDecodingContext *ctx) repSlotStat.spill_txns = rb->spillTxns; repSlotStat.spill_count = rb->spillCount; repSlotStat.spill_bytes = rb->spillBytes; + repSlotStat.spill_write_bytes = rb->spillWriteBytes; repSlotStat.stream_txns = rb->streamTxns; repSlotStat.stream_count = rb->streamCount; repSlotStat.stream_bytes = rb->streamBytes; @@ -1967,6 +1969,7 @@ UpdateDecodingStats(LogicalDecodingContext *ctx) rb->spillTxns = 0; rb->spillCount = 0; rb->spillBytes = 0; + rb->spillWriteBytes = 0; rb->streamTxns = 0; rb->streamCount = 0; rb->streamBytes = 0; diff --git a/src/backend/replication/logical/reorderbuffer.c b/src/backend/replication/logical/reorderbuffer.c index 9407466393..2b8839a96d 100644 --- a/src/backend/replication/logical/reorderbuffer.c +++ b/src/backend/replication/logical/reorderbuffer.c @@ -257,7 +257,7 @@ static void ReorderBufferExecuteInvalidations(uint32 nmsgs, SharedInvalidationMe */ static void ReorderBufferCheckMemoryLimit(ReorderBuffer *rb); static void ReorderBufferSerializeTXN(ReorderBuffer *rb, ReorderBufferTXN *txn); -static void ReorderBufferSerializeChange(ReorderBuffer *rb, ReorderBufferTXN *txn, +static Size ReorderBufferSerializeChange(ReorderBuffer *rb, ReorderBufferTXN *txn, int fd, ReorderBufferChange *change); static Size ReorderBufferRestoreChanges(ReorderBuffer *rb, ReorderBufferTXN *txn, TXNEntryFile *file, XLogSegNo *segno); @@ -3756,6 +3756,7 @@ ReorderBufferSerializeTXN(ReorderBuffer *rb, ReorderBufferTXN *txn) XLogSegNo curOpenSegNo = 0; Size spilled = 0; Size size = txn->size; + Size written = 0; elog(DEBUG2, "spill %u changes in XID %u to disk", (uint32) txn->nentries_mem, txn->xid); @@ -3807,7 +3808,7 @@ ReorderBufferSerializeTXN(ReorderBuffer *rb, ReorderBufferTXN *txn) errmsg("could not open file \"%s\": %m", path))); } - ReorderBufferSerializeChange(rb, txn, fd, change); + written += ReorderBufferSerializeChange(rb, txn, fd, change); dlist_delete(&change->node); ReorderBufferReturnChange(rb, change, false); @@ -3822,6 +3823,7 @@ ReorderBufferSerializeTXN(ReorderBuffer *rb, ReorderBufferTXN *txn) { rb->spillCount += 1; rb->spillBytes += size; + rb->spillWriteBytes += written; /* don't consider already serialized transactions */ rb->spillTxns += (rbtxn_is_serialized(txn) || rbtxn_is_serialized_clear(txn)) ? 0 : 1; @@ -3842,7 +3844,7 @@ ReorderBufferSerializeTXN(ReorderBuffer *rb, ReorderBufferTXN *txn) /* * Serialize individual change to disk. */ -static void +static Size ReorderBufferSerializeChange(ReorderBuffer *rb, ReorderBufferTXN *txn, int fd, ReorderBufferChange *change) { @@ -4054,6 +4056,9 @@ ReorderBufferSerializeChange(ReorderBuffer *rb, ReorderBufferTXN *txn, */ if (txn->final_lsn < change->lsn) txn->final_lsn = change->lsn; + + /* Return the size of data written on disk */ + return ondisk->size - sizeof(ReorderBufferDiskChange); } /* Returns true, if the output plugin supports streaming, false, otherwise. */ diff --git a/src/backend/utils/activity/pgstat_replslot.c b/src/backend/utils/activity/pgstat_replslot.c index ddf2ab9928..66d6e36ebf 100644 --- a/src/backend/utils/activity/pgstat_replslot.c +++ b/src/backend/utils/activity/pgstat_replslot.c @@ -91,6 +91,7 @@ pgstat_report_replslot(ReplicationSlot *slot, const PgStat_StatReplSlotEntry *re REPLSLOT_ACC(spill_txns); REPLSLOT_ACC(spill_count); REPLSLOT_ACC(spill_bytes); + REPLSLOT_ACC(spill_write_bytes); REPLSLOT_ACC(stream_txns); REPLSLOT_ACC(stream_count); REPLSLOT_ACC(stream_bytes); diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index f7b50e0b5a..f7488b2a58 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1907,7 +1907,7 @@ pg_stat_get_archiver(PG_FUNCTION_ARGS) Datum pg_stat_get_replication_slot(PG_FUNCTION_ARGS) { -#define PG_STAT_GET_REPLICATION_SLOT_COLS 10 +#define PG_STAT_GET_REPLICATION_SLOT_COLS 11 text *slotname_text = PG_GETARG_TEXT_P(0); NameData slotname; TupleDesc tupdesc; @@ -1926,17 +1926,19 @@ pg_stat_get_replication_slot(PG_FUNCTION_ARGS) INT8OID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 4, "spill_bytes", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 5, "stream_txns", + TupleDescInitEntry(tupdesc, (AttrNumber) 5, "spill_write_bytes", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 6, "stream_count", + TupleDescInitEntry(tupdesc, (AttrNumber) 6, "stream_txns", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 7, "stream_bytes", + TupleDescInitEntry(tupdesc, (AttrNumber) 7, "stream_count", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 8, "total_txns", + TupleDescInitEntry(tupdesc, (AttrNumber) 8, "stream_bytes", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 9, "total_bytes", + TupleDescInitEntry(tupdesc, (AttrNumber) 9, "total_txns", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 10, "stats_reset", + TupleDescInitEntry(tupdesc, (AttrNumber) 10, "total_bytes", + INT8OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 11, "stats_reset", TIMESTAMPTZOID, -1, 0); BlessTupleDesc(tupdesc); @@ -1956,16 +1958,17 @@ pg_stat_get_replication_slot(PG_FUNCTION_ARGS) values[1] = Int64GetDatum(slotent->spill_txns); values[2] = Int64GetDatum(slotent->spill_count); values[3] = Int64GetDatum(slotent->spill_bytes); - values[4] = Int64GetDatum(slotent->stream_txns); - values[5] = Int64GetDatum(slotent->stream_count); - values[6] = Int64GetDatum(slotent->stream_bytes); - values[7] = Int64GetDatum(slotent->total_txns); - values[8] = Int64GetDatum(slotent->total_bytes); + values[4] = Int64GetDatum(slotent->spill_write_bytes); + values[5] = Int64GetDatum(slotent->stream_txns); + values[6] = Int64GetDatum(slotent->stream_count); + values[7] = Int64GetDatum(slotent->stream_bytes); + values[8] = Int64GetDatum(slotent->total_txns); + values[9] = Int64GetDatum(slotent->total_bytes); if (slotent->stat_reset_timestamp == 0) - nulls[9] = true; + nulls[10] = true; else - values[9] = TimestampTzGetDatum(slotent->stat_reset_timestamp); + values[10] = TimestampTzGetDatum(slotent->stat_reset_timestamp); /* Returns the record as Datum */ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls))); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1ec0d6f6b5..6b95dbeebf 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5584,9 +5584,9 @@ { oid => '6169', descr => 'statistics: information about replication slot', proname => 'pg_stat_get_replication_slot', provolatile => 's', proparallel => 'r', prorettype => 'record', proargtypes => 'text', - proallargtypes => '{text,text,int8,int8,int8,int8,int8,int8,int8,int8,timestamptz}', - proargmodes => '{i,o,o,o,o,o,o,o,o,o,o}', - proargnames => '{slot_name,slot_name,spill_txns,spill_count,spill_bytes,stream_txns,stream_count,stream_bytes,total_txns,total_bytes,stats_reset}', + proallargtypes => '{text,text,int8,int8,int8,int8,int8,int8,int8,int8,int8,timestamptz}', + proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o}', + proargnames => '{slot_name,slot_name,spill_txns,spill_count,spill_bytes,spill_write_bytes,stream_txns,stream_count,stream_bytes,total_txns,total_bytes,stats_reset}', prosrc => 'pg_stat_get_replication_slot' }, { oid => '6230', descr => 'statistics: check if a stats object exists', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index df53fa2d4f..733cd74f86 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -403,6 +403,7 @@ typedef struct PgStat_StatReplSlotEntry PgStat_Counter spill_txns; PgStat_Counter spill_count; PgStat_Counter spill_bytes; + PgStat_Counter spill_write_bytes; PgStat_Counter stream_txns; PgStat_Counter stream_count; PgStat_Counter stream_bytes; diff --git a/src/include/replication/reorderbuffer.h b/src/include/replication/reorderbuffer.h index 5f231b5f90..2b585db7c5 100644 --- a/src/include/replication/reorderbuffer.h +++ b/src/include/replication/reorderbuffer.h @@ -659,7 +659,9 @@ struct ReorderBuffer */ int64 spillTxns; /* number of transactions spilled to disk */ int64 spillCount; /* spill-to-disk invocation counter */ - int64 spillBytes; /* amount of data spilled to disk */ + int64 spillBytes; /* amount of data spilled to disk, before + * compression */ + int64 spillWriteBytes; /* amount of data actually written to disk */ /* Statistics about transactions streamed to the decoding output plugin */ int64 streamTxns; /* number of transactions streamed */ diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 2b47013f11..26ff02f89d 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2095,6 +2095,7 @@ pg_stat_replication_slots| SELECT s.slot_name, s.spill_txns, s.spill_count, s.spill_bytes, + s.spill_write_bytes, s.stream_txns, s.stream_count, s.stream_bytes, @@ -2102,7 +2103,7 @@ pg_stat_replication_slots| SELECT s.slot_name, s.total_bytes, s.stats_reset FROM pg_replication_slots r, - LATERAL pg_stat_get_replication_slot((r.slot_name)::text) s(slot_name, spill_txns, spill_count, spill_bytes, stream_txns, stream_count, stream_bytes, total_txns, total_bytes, stats_reset) + LATERAL pg_stat_get_replication_slot((r.slot_name)::text) s(slot_name, spill_txns, spill_count, spill_bytes, spill_write_bytes, stream_txns, stream_count, stream_bytes, total_txns, total_bytes, stats_reset) WHERE (r.datoid IS NOT NULL); pg_stat_slru| SELECT name, blks_zeroed, -- 2.43.0 [application/octet-stream] v5-0004-Set-ReorderBuffer-compression-via-subscription-opt.patch (65.9K, ../../CAFEQCbHQDq6ZfVwRDk8Ym2-EAVueSDLRLop0=3cujKTWqGcRmg@mail.gmail.com/6-v5-0004-Set-ReorderBuffer-compression-via-subscription-opt.patch) download | inline diff: From ede0f93dd44542a1ac401d073e7988c4e61822ce Mon Sep 17 00:00:00 2001 From: Julien Tachoires <[email protected]> Date: Tue, 22 Oct 2024 23:12:44 +0200 Subject: [PATCH 4/6] Set ReorderBuffer compression via subscription opt The CREATE/ALTER SUBSCRIPTION commands now support a new option named "spill_compression" that will be used to select the compression method applied to the logical changes spilled on disk during decoding. The default value is "off", meaning no compression will be applied. Supported values are: "off", "on", "pglz", "lz4", and "zstd". --- doc/src/sgml/ref/create_subscription.sgml | 24 +++ src/backend/catalog/pg_subscription.c | 6 + src/backend/catalog/system_views.sql | 3 +- src/backend/commands/subscriptioncmds.c | 31 +++- .../libpqwalreceiver/libpqwalreceiver.c | 5 + src/backend/replication/logical/logical.c | 4 + .../replication/logical/reorderbuffer.c | 74 ++++++++- src/backend/replication/logical/worker.c | 13 +- src/backend/replication/pgoutput/pgoutput.c | 28 ++++ src/bin/pg_dump/pg_dump.c | 18 +- src/bin/pg_dump/pg_dump.h | 1 + src/bin/pg_dump/t/002_pg_dump.pl | 4 +- src/bin/psql/describe.c | 7 +- src/bin/psql/tab-complete.in.c | 6 +- src/include/catalog/pg_subscription.h | 4 + src/include/replication/logical.h | 2 + src/include/replication/pgoutput.h | 1 + .../replication/reorderbuffer_compression.h | 4 + src/include/replication/walreceiver.h | 1 + src/test/regress/expected/subscription.out | 156 +++++++++--------- src/test/regress/sql/subscription.sql | 4 + 21 files changed, 305 insertions(+), 91 deletions(-) diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml index 6cf7d4f9a1..175965c4a4 100644 --- a/doc/src/sgml/ref/create_subscription.sgml +++ b/doc/src/sgml/ref/create_subscription.sgml @@ -435,6 +435,30 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl </para> </listitem> </varlistentry> + + <varlistentry id="sql-createsubscription-params-with-spill-compression"> + <term><literal>spill_compression</literal> (<type>enum</type>)</term> + <listitem> + <para> + Specifies whether the decoded changes that eventually need to be + temporarily written on disk by the publisher are compressed or not. + Default value is <literal>off</literal> meaning no data compression + involved. Setting <literal>spill_compression</literal> to + <literal>on</literal> or <literal>pglz</literal> means that the + decoded changes are compressed using the internal + <literal>PGLZ</literal> compression algorithm. + </para> + + <para> + If the <productname>PostgreSQL</productname> server running the + publisher node supports the external compression libraries + <productname>LZ4</productname> or + <productname>Zstandard</productname>, + <literal>spill_compression</literal> can be set respectively to + <literal>lz4</literal> or <literal>zstd</literal>. + </para> + </listitem> + </varlistentry> </variablelist></para> </listitem> diff --git a/src/backend/catalog/pg_subscription.c b/src/backend/catalog/pg_subscription.c index 89bf5ec933..e2bd881ab0 100644 --- a/src/backend/catalog/pg_subscription.c +++ b/src/backend/catalog/pg_subscription.c @@ -141,6 +141,12 @@ GetSubscription(Oid subid, bool missing_ok) /* Is the subscription owner a superuser? */ sub->ownersuperuser = superuser_arg(sub->owner); + /* Get spill_compression */ + datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, + tup, + Anum_pg_subscription_subspillcompression); + sub->spill_compression = TextDatumGetCString(datum); + ReleaseSysCache(tup); return sub; diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 3456b821bc..a087223677 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1358,7 +1358,8 @@ REVOKE ALL ON pg_subscription FROM public; GRANT SELECT (oid, subdbid, subskiplsn, subname, subowner, subenabled, subbinary, substream, subtwophasestate, subdisableonerr, subpasswordrequired, subrunasowner, subfailover, - subslotname, subsynccommit, subpublications, suborigin) + subslotname, subsynccommit, subpublications, suborigin, + subspillcompression) ON pg_subscription TO public; CREATE VIEW pg_stat_subscription_stats AS diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c index 03e97730e7..2c9f1eae4b 100644 --- a/src/backend/commands/subscriptioncmds.c +++ b/src/backend/commands/subscriptioncmds.c @@ -40,6 +40,7 @@ #include "replication/logicallauncher.h" #include "replication/logicalworker.h" #include "replication/origin.h" +#include "replication/reorderbuffer_compression.h" #include "replication/slot.h" #include "replication/walreceiver.h" #include "replication/walsender.h" @@ -73,6 +74,7 @@ #define SUBOPT_FAILOVER 0x00002000 #define SUBOPT_LSN 0x00004000 #define SUBOPT_ORIGIN 0x00008000 +#define SUBOPT_SPILL_COMPRESSION 0x00010000 /* check if the 'val' has 'bits' set */ #define IsSet(val, bits) (((val) & (bits)) == (bits)) @@ -100,6 +102,7 @@ typedef struct SubOpts bool failover; char *origin; XLogRecPtr lsn; + char *spill_compression; } SubOpts; static List *fetch_table_list(WalReceiverConn *wrconn, List *publications); @@ -164,6 +167,8 @@ parse_subscription_options(ParseState *pstate, List *stmt_options, opts->failover = false; if (IsSet(supported_opts, SUBOPT_ORIGIN)) opts->origin = pstrdup(LOGICALREP_ORIGIN_ANY); + if (IsSet(supported_opts, SUBOPT_SPILL_COMPRESSION)) + opts->spill_compression = "off"; /* Parse options */ foreach(lc, stmt_options) @@ -357,6 +362,18 @@ parse_subscription_options(ParseState *pstate, List *stmt_options, opts->specified_opts |= SUBOPT_LSN; opts->lsn = lsn; } + else if (IsSet(supported_opts, SUBOPT_SPILL_COMPRESSION) && + strcmp(defel->defname, "spill_compression") == 0) + { + if (IsSet(opts->specified_opts, SUBOPT_SPILL_COMPRESSION)) + errorConflictingDefElem(defel, pstate); + + opts->specified_opts |= SUBOPT_SPILL_COMPRESSION; + opts->spill_compression = defGetString(defel); + + ReorderBufferValidateCompressionMethod(opts->spill_compression, + ERROR); + } else ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), @@ -563,7 +580,8 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt, SUBOPT_SYNCHRONOUS_COMMIT | SUBOPT_BINARY | SUBOPT_STREAMING | SUBOPT_TWOPHASE_COMMIT | SUBOPT_DISABLE_ON_ERR | SUBOPT_PASSWORD_REQUIRED | - SUBOPT_RUN_AS_OWNER | SUBOPT_FAILOVER | SUBOPT_ORIGIN); + SUBOPT_RUN_AS_OWNER | SUBOPT_FAILOVER | SUBOPT_ORIGIN | + SUBOPT_SPILL_COMPRESSION); parse_subscription_options(pstate, stmt->options, supported_opts, &opts); /* @@ -683,6 +701,8 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt, publicationListToArray(publications); values[Anum_pg_subscription_suborigin - 1] = CStringGetTextDatum(opts.origin); + values[Anum_pg_subscription_subspillcompression - 1] = + CStringGetTextDatum(opts.spill_compression); tup = heap_form_tuple(RelationGetDescr(rel), values, nulls); @@ -1165,7 +1185,7 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt, SUBOPT_DISABLE_ON_ERR | SUBOPT_PASSWORD_REQUIRED | SUBOPT_RUN_AS_OWNER | SUBOPT_FAILOVER | - SUBOPT_ORIGIN); + SUBOPT_ORIGIN | SUBOPT_SPILL_COMPRESSION); parse_subscription_options(pstate, stmt->options, supported_opts, &opts); @@ -1332,6 +1352,13 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt, replaces[Anum_pg_subscription_suborigin - 1] = true; } + if (IsSet(opts.specified_opts, SUBOPT_SPILL_COMPRESSION)) + { + values[Anum_pg_subscription_subspillcompression - 1] = + CStringGetTextDatum(opts.spill_compression); + replaces[Anum_pg_subscription_subspillcompression - 1] = true; + } + update_tuple = true; break; } diff --git a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c index 97f957cd87..20f5e4a83e 100644 --- a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c +++ b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c @@ -620,6 +620,11 @@ libpqrcv_startstreaming(WalReceiverConn *conn, PQserverVersion(conn->streamConn) >= 140000) appendStringInfoString(&cmd, ", binary 'true'"); + if (options->proto.logical.spill_compression && + PQserverVersion(conn->streamConn) >= 180000) + appendStringInfo(&cmd, ", spill_compression '%s'", + options->proto.logical.spill_compression); + appendStringInfoChar(&cmd, ')'); } else diff --git a/src/backend/replication/logical/logical.c b/src/backend/replication/logical/logical.c index 3fe1774a1e..54fbbe6fea 100644 --- a/src/backend/replication/logical/logical.c +++ b/src/backend/replication/logical/logical.c @@ -36,6 +36,7 @@ #include "replication/decode.h" #include "replication/logical.h" #include "replication/reorderbuffer.h" +#include "replication/reorderbuffer_compression.h" #include "replication/slotsync.h" #include "replication/snapbuild.h" #include "storage/proc.h" @@ -298,6 +299,9 @@ StartupDecodingContext(List *output_plugin_options, ctx->fast_forward = fast_forward; + /* No spill files compression by default */ + ctx->spill_compression_method = REORDER_BUFFER_NO_COMPRESSION; + MemoryContextSwitchTo(old_context); return ctx; diff --git a/src/backend/replication/logical/reorderbuffer.c b/src/backend/replication/logical/reorderbuffer.c index 17f8208000..9407466393 100644 --- a/src/backend/replication/logical/reorderbuffer.c +++ b/src/backend/replication/logical/reorderbuffer.c @@ -219,7 +219,7 @@ static const Size max_changes_in_memory = 4096; /* XXX for restore only */ int debug_logical_replication_streaming = DEBUG_LOGICAL_REP_STREAMING_BUFFERED; /* Compression strategy for spilled data. */ -int logical_decoding_spill_compression = REORDER_BUFFER_ZSTD_COMPRESSION; +int logical_decoding_spill_compression = REORDER_BUFFER_NO_COMPRESSION; /* --------------------------------------- * primary reorderbuffer support routines @@ -438,6 +438,15 @@ ReorderBufferFree(ReorderBuffer *rb) ReorderBufferCleanupSerializedTXNs(NameStr(MyReplicationSlot->data.name)); } +/* Returns spill files compression method */ +static inline uint8 +ReorderBufferSpillCompressionMethod(ReorderBuffer *rb) +{ + LogicalDecodingContext *ctx = rb->private_data; + + return ctx->spill_compression_method; +} + /* * Get an unused, possibly preallocated, ReorderBufferTXN. */ @@ -459,7 +468,7 @@ ReorderBufferGetTXN(ReorderBuffer *rb) txn->command_id = InvalidCommandId; txn->output_plugin_private = NULL; txn->compressor_state = ReorderBufferNewCompressorState(rb->context, - logical_decoding_spill_compression); + ReorderBufferSpillCompressionMethod(rb)); return txn; } @@ -498,7 +507,7 @@ ReorderBufferReturnTXN(ReorderBuffer *rb, ReorderBufferTXN *txn) } ReorderBufferFreeCompressorState(rb->context, - logical_decoding_spill_compression, + ReorderBufferSpillCompressionMethod(rb), txn->compressor_state); /* Reset the toast hash */ @@ -4015,7 +4024,7 @@ ReorderBufferSerializeChange(ReorderBuffer *rb, ReorderBufferTXN *txn, } /* Inplace ReorderBuffer content compression before writing it on disk */ - ReorderBufferCompress(rb, &ondisk, logical_decoding_spill_compression, + ReorderBufferCompress(rb, &ondisk, ReorderBufferSpillCompressionMethod(rb), sz, txn->compressor_state); errno = 0; @@ -5697,3 +5706,60 @@ ReorderBufferDecompress(ReorderBuffer *rb, char *data, break; } } + +/* + * According to a given compression method (as string representation), returns + * the corresponding ReorderBufferCompressionMethod + */ +ReorderBufferCompressionMethod +ReorderBufferParseCompressionMethod(const char *method) +{ + if (pg_strcasecmp(method, "on") == 0) + return REORDER_BUFFER_PGLZ_COMPRESSION; + else if (pg_strcasecmp(method, "pglz") == 0) + return REORDER_BUFFER_PGLZ_COMPRESSION; + else if (pg_strcasecmp(method, "off") == 0) + return REORDER_BUFFER_NO_COMPRESSION; +#ifdef USE_LZ4 + else if (pg_strcasecmp(method, "lz4") == 0) + return REORDER_BUFFER_LZ4_COMPRESSION; +#endif +#ifdef USE_ZSTD + else if (pg_strcasecmp(method, "zstd") == 0) + return REORDER_BUFFER_ZSTD_COMPRESSION; +#endif + else + return REORDER_BUFFER_INVALID_COMPRESSION; +} + +/* + * Check whether the passed compression method is valid and report errors at + * elevel. + * + * As this validation is intended to be executed on subscriber side, then we + * actually don't know if the server running the publisher supports external + * compression libraries. We only check if the compression method is + * potentially supported. The real validation is done by the publisher when + * the replication starts, an error is then triggered if the compression method + * is not supported. + */ +void +ReorderBufferValidateCompressionMethod(const char *method, int elevel) +{ + bool valid = false; + char methods[5][5] = {"on", "off", "pglz", "lz4", "zstd"}; + + for (int i = 0; i < 5; i++) + { + if (pg_strcasecmp(method, methods[i]) == 0) + { + valid = true; + break; + } + } + + if (!valid) + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("compression method \"%s\" not valid", method))); +} diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c index 925dff9cc4..32b38b94dd 100644 --- a/src/backend/replication/logical/worker.c +++ b/src/backend/replication/logical/worker.c @@ -4021,7 +4021,8 @@ maybe_reread_subscription(void) newsub->passwordrequired != MySubscription->passwordrequired || strcmp(newsub->origin, MySubscription->origin) != 0 || newsub->owner != MySubscription->owner || - !equal(newsub->publications, MySubscription->publications)) + !equal(newsub->publications, MySubscription->publications) || + strcmp(newsub->spill_compression, MySubscription->spill_compression) != 0) { if (am_parallel_apply_worker()) ereport(LOG, @@ -4469,6 +4470,16 @@ set_stream_options(WalRcvStreamOptions *options, MyLogicalRepWorker->parallel_apply = false; } + if (server_version >= 180000 && + MySubscription->stream == LOGICALREP_STREAM_OFF && + MySubscription->spill_compression != NULL) + { + options->proto.logical.spill_compression = + pstrdup(MySubscription->spill_compression); + } + else + options->proto.logical.spill_compression = NULL; + options->proto.logical.twophase = false; options->proto.logical.origin = pstrdup(MySubscription->origin); } diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c index 00e7024563..521b646bb6 100644 --- a/src/backend/replication/pgoutput/pgoutput.c +++ b/src/backend/replication/pgoutput/pgoutput.c @@ -27,6 +27,7 @@ #include "replication/logicalproto.h" #include "replication/origin.h" #include "replication/pgoutput.h" +#include "replication/reorderbuffer_compression.h" #include "utils/builtins.h" #include "utils/inval.h" #include "utils/lsyscache.h" @@ -283,11 +284,13 @@ parse_output_parameters(List *options, PGOutputData *data) bool streaming_given = false; bool two_phase_option_given = false; bool origin_option_given = false; + bool spill_compression_option_given = false; data->binary = false; data->streaming = LOGICALREP_STREAM_OFF; data->messages = false; data->two_phase = false; + data->spill_compression_method = REORDER_BUFFER_NO_COMPRESSION; foreach(lc, options) { @@ -396,6 +399,28 @@ parse_output_parameters(List *options, PGOutputData *data) errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("unrecognized origin value: \"%s\"", origin)); } + else if (strcmp(defel->defname, "spill_compression") == 0) + { + uint8 method; + char *method_str; + + if (spill_compression_option_given) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + spill_compression_option_given = true; + + method_str = defGetString(defel); + method = ReorderBufferParseCompressionMethod(method_str); + + if (method == REORDER_BUFFER_INVALID_COMPRESSION) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid spill files compression method: \"%s\"", + method_str)); + + data->spill_compression_method = method; + } else elog(ERROR, "unrecognized pgoutput option: %s", defel->defname); } @@ -508,6 +533,9 @@ pgoutput_startup(LogicalDecodingContext *ctx, OutputPluginOptions *opt, data->publications = NIL; publications_valid = false; + /* Init spill files compression method */ + ctx->spill_compression_method = data->spill_compression_method; + /* * Register callback for pg_publication if we didn't already do that * during some previous call in this process. diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index d8c6330732..0b9e31ff2f 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -4850,6 +4850,7 @@ getSubscriptions(Archive *fout) int i_suboriginremotelsn; int i_subenabled; int i_subfailover; + int i_subspillcompression; int i, ntups; @@ -4922,10 +4923,17 @@ getSubscriptions(Archive *fout) if (fout->remoteVersion >= 170000) appendPQExpBufferStr(query, - " s.subfailover\n"); + " s.subfailover,\n"); else appendPQExpBuffer(query, - " false AS subfailover\n"); + " false AS subfailover,\n"); + + if (fout->remoteVersion >= 180000) + appendPQExpBufferStr(query, + " s.subspillcompression\n"); + else + appendPQExpBuffer(query, + " 'off' AS subspillcompression\n"); appendPQExpBufferStr(query, "FROM pg_subscription s\n"); @@ -4965,6 +4973,7 @@ getSubscriptions(Archive *fout) i_suboriginremotelsn = PQfnumber(res, "suboriginremotelsn"); i_subenabled = PQfnumber(res, "subenabled"); i_subfailover = PQfnumber(res, "subfailover"); + i_subspillcompression = PQfnumber(res, "subspillcompression"); subinfo = pg_malloc(ntups * sizeof(SubscriptionInfo)); @@ -5011,6 +5020,8 @@ getSubscriptions(Archive *fout) pg_strdup(PQgetvalue(res, i, i_subenabled)); subinfo[i].subfailover = pg_strdup(PQgetvalue(res, i, i_subfailover)); + subinfo[i].subspillcompression = + pg_strdup(PQgetvalue(res, i, i_subspillcompression)); /* Decide whether we want to dump it */ selectDumpableObject(&(subinfo[i].dobj), fout); @@ -5259,6 +5270,9 @@ dumpSubscription(Archive *fout, const SubscriptionInfo *subinfo) if (pg_strcasecmp(subinfo->suborigin, LOGICALREP_ORIGIN_ANY) != 0) appendPQExpBuffer(query, ", origin = %s", subinfo->suborigin); + if (strcmp(subinfo->subspillcompression, "off") != 0) + appendPQExpBuffer(query, ", spill_compression = %s", subinfo->subspillcompression); + appendPQExpBufferStr(query, ");\n"); /* diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 9f907ed5ad..ecbf2c2e27 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -673,6 +673,7 @@ typedef struct _SubscriptionInfo char *suborigin; char *suboriginremotelsn; char *subfailover; + char *subspillcompression; } SubscriptionInfo; /* diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index ac60829d68..4b7f75db8f 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -3001,9 +3001,9 @@ my %tests = ( create_order => 50, create_sql => 'CREATE SUBSCRIPTION sub2 CONNECTION \'dbname=doesnotexist\' PUBLICATION pub1 - WITH (connect = false, origin = none, streaming = off);', + WITH (connect = false, origin = none, spill_compression = on, streaming = off);', regexp => qr/^ - \QCREATE SUBSCRIPTION sub2 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub2', streaming = off, origin = none);\E + \QCREATE SUBSCRIPTION sub2 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub2', streaming = off, origin = none, spill_compression = on);\E /xm, like => { %full_runs, section_post_data => 1, }, }, diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 363a66e718..9735d0b099 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -6543,7 +6543,7 @@ describeSubscriptions(const char *pattern, bool verbose) printQueryOpt myopt = pset.popt; static const bool translate_columns[] = {false, false, false, false, false, false, false, false, false, false, false, false, false, false, - false}; + false, false}; if (pset.sversion < 100000) { @@ -6623,6 +6623,11 @@ describeSubscriptions(const char *pattern, bool verbose) appendPQExpBuffer(&buf, ", subskiplsn AS \"%s\"\n", gettext_noop("Skip LSN")); + + if (pset.sversion >= 180000) + appendPQExpBuffer(&buf, + ", subspillcompression AS \"%s\"\n", + gettext_noop("Spill files compression")); } /* Only display subscriptions in current database. */ diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index 1be0056af7..20969ec562 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -2280,7 +2280,8 @@ match_previous_words(int pattern_id, else if (Matches("ALTER", "SUBSCRIPTION", MatchAny, MatchAnyN, "SET", "(")) COMPLETE_WITH("binary", "disable_on_error", "failover", "origin", "password_required", "run_as_owner", "slot_name", - "streaming", "synchronous_commit", "two_phase"); + "spill_compression", "streaming", "synchronous_commit", + "two_phase"); /* ALTER SUBSCRIPTION <name> SKIP ( */ else if (Matches("ALTER", "SUBSCRIPTION", MatchAny, MatchAnyN, "SKIP", "(")) COMPLETE_WITH("lsn"); @@ -3675,7 +3676,8 @@ match_previous_words(int pattern_id, COMPLETE_WITH("binary", "connect", "copy_data", "create_slot", "disable_on_error", "enabled", "failover", "origin", "password_required", "run_as_owner", "slot_name", - "streaming", "synchronous_commit", "two_phase"); + "spill_compression", "streaming", "synchronous_commit", + "two_phase"); /* CREATE TRIGGER --- is allowed inside CREATE SCHEMA, so use TailMatches */ diff --git a/src/include/catalog/pg_subscription.h b/src/include/catalog/pg_subscription.h index b25f3fea56..2171a1f7f0 100644 --- a/src/include/catalog/pg_subscription.h +++ b/src/include/catalog/pg_subscription.h @@ -113,6 +113,9 @@ CATALOG(pg_subscription,6100,SubscriptionRelationId) BKI_SHARED_RELATION BKI_ROW /* Only publish data originating from the specified origin */ text suborigin BKI_DEFAULT(LOGICALREP_ORIGIN_ANY); + + /* Spill files compression algorithm */ + text subspillcompression BKI_FORCE_NOT_NULL; #endif } FormData_pg_subscription; @@ -157,6 +160,7 @@ typedef struct Subscription List *publications; /* List of publication names to subscribe to */ char *origin; /* Only publish data originating from the * specified origin */ + char *spill_compression; /* Spill files compression algorithm */ } Subscription; /* Disallow streaming in-progress transactions. */ diff --git a/src/include/replication/logical.h b/src/include/replication/logical.h index aff38e8d04..75c17866c3 100644 --- a/src/include/replication/logical.h +++ b/src/include/replication/logical.h @@ -112,6 +112,8 @@ typedef struct LogicalDecodingContext /* Do we need to process any change in fast_forward mode? */ bool processing_required; + /* Compression method used to compress spill files */ + uint8 spill_compression_method; } LogicalDecodingContext; diff --git a/src/include/replication/pgoutput.h b/src/include/replication/pgoutput.h index 89f94e1147..eabcca62af 100644 --- a/src/include/replication/pgoutput.h +++ b/src/include/replication/pgoutput.h @@ -33,6 +33,7 @@ typedef struct PGOutputData bool messages; bool two_phase; bool publish_no_origin; + uint8 spill_compression_method; } PGOutputData; #endif /* PGOUTPUT_H */ diff --git a/src/include/replication/reorderbuffer_compression.h b/src/include/replication/reorderbuffer_compression.h index 240c188f00..1df508be91 100644 --- a/src/include/replication/reorderbuffer_compression.h +++ b/src/include/replication/reorderbuffer_compression.h @@ -24,6 +24,7 @@ /* ReorderBuffer on disk compression algorithms */ typedef enum ReorderBufferCompressionMethod { + REORDER_BUFFER_INVALID_COMPRESSION, REORDER_BUFFER_NO_COMPRESSION, REORDER_BUFFER_PGLZ_COMPRESSION, REORDER_BUFFER_LZ4_COMPRESSION, @@ -117,6 +118,9 @@ typedef struct ZSTDStreamingCompressorState extern void *lz4_NewCompressorState(MemoryContext context); extern void lz4_FreeCompressorState(MemoryContext context, void *compressor_state); +extern ReorderBufferCompressionMethod ReorderBufferParseCompressionMethod(const char *method); +extern void ReorderBufferValidateCompressionMethod(const char *method, + int elevel); extern void lz4_StreamingCompressData(MemoryContext context, char *src, Size src_size, char *dst, Size *dst_size, void *compressor_state); diff --git a/src/include/replication/walreceiver.h b/src/include/replication/walreceiver.h index 132e789948..b759b5807d 100644 --- a/src/include/replication/walreceiver.h +++ b/src/include/replication/walreceiver.h @@ -186,6 +186,7 @@ typedef struct * prepare time */ char *origin; /* Only publish data originating from the * specified origin */ + char *spill_compression; /* Spill files compression algo */ } logical; } proto; } WalRcvStreamOptions; diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out index 1443e1d929..029d42f358 100644 --- a/src/test/regress/expected/subscription.out +++ b/src/test/regress/expected/subscription.out @@ -116,18 +116,18 @@ CREATE SUBSCRIPTION regress_testsub4 CONNECTION 'dbname=regress_doesnotexist' PU WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. \dRs+ regress_testsub4 - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | none | t | f | f | off | dbname=regress_doesnotexist | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------+------------------------- + regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | none | t | f | f | off | dbname=regress_doesnotexist | 0/0 | off (1 row) ALTER SUBSCRIPTION regress_testsub4 SET (origin = any); \dRs+ regress_testsub4 - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------+------------------------- + regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 | off (1 row) DROP SUBSCRIPTION regress_testsub3; @@ -145,10 +145,10 @@ ALTER SUBSCRIPTION regress_testsub CONNECTION 'foobar'; ERROR: invalid connection string syntax: missing "=" after "foobar" in connection info string \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 | off (1 row) ALTER SUBSCRIPTION regress_testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false); @@ -157,10 +157,10 @@ ALTER SUBSCRIPTION regress_testsub SET (slot_name = 'newname'); ALTER SUBSCRIPTION regress_testsub SET (password_required = false); ALTER SUBSCRIPTION regress_testsub SET (run_as_owner = true); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | f | t | f | off | dbname=regress_doesnotexist2 | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | f | t | f | off | dbname=regress_doesnotexist2 | 0/0 | off (1 row) ALTER SUBSCRIPTION regress_testsub SET (password_required = true); @@ -176,10 +176,10 @@ ERROR: unrecognized subscription parameter: "create_slot" -- ok ALTER SUBSCRIPTION regress_testsub SKIP (lsn = '0/12345'); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/12345 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/12345 | off (1 row) -- ok - with lsn = NONE @@ -188,10 +188,10 @@ ALTER SUBSCRIPTION regress_testsub SKIP (lsn = NONE); ALTER SUBSCRIPTION regress_testsub SKIP (lsn = '0/0'); ERROR: invalid WAL location (LSN): 0/0 \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/0 | off (1 row) BEGIN; @@ -222,11 +222,15 @@ ALTER SUBSCRIPTION regress_testsub_foo SET (synchronous_commit = local); ALTER SUBSCRIPTION regress_testsub_foo SET (synchronous_commit = foobar); ERROR: invalid value for parameter "synchronous_commit": "foobar" HINT: Available values: local, remote_write, remote_apply, on, off. +ALTER SUBSCRIPTION regress_testsub_foo SET (spill_compression = pglz); +ALTER SUBSCRIPTION regress_testsub_foo SET (spill_compression = off); +ALTER SUBSCRIPTION regress_testsub_foo SET (spill_compression = foobar); +ERROR: compression method "foobar" not valid \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ----------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | local | dbname=regress_doesnotexist2 | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +---------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+----------+------------------------- + regress_testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | local | dbname=regress_doesnotexist2 | 0/0 | off (1 row) -- rename back to keep the rest simple @@ -255,19 +259,19 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | t | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub} | t | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 | off (1 row) ALTER SUBSCRIPTION regress_testsub SET (binary = false); ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 | off (1 row) DROP SUBSCRIPTION regress_testsub; @@ -279,27 +283,27 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | on | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | on | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 | off (1 row) ALTER SUBSCRIPTION regress_testsub SET (streaming = parallel); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 | off (1 row) ALTER SUBSCRIPTION regress_testsub SET (streaming = false); ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 | off (1 row) -- fail - publication already exists @@ -314,10 +318,10 @@ ALTER SUBSCRIPTION regress_testsub ADD PUBLICATION testpub1, testpub2 WITH (refr ALTER SUBSCRIPTION regress_testsub ADD PUBLICATION testpub1, testpub2 WITH (refresh = false); ERROR: publication "testpub1" is already in subscription "regress_testsub" \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-----------------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub,testpub1,testpub2} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+-----------------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub,testpub1,testpub2} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 | off (1 row) -- fail - publication used more than once @@ -332,10 +336,10 @@ ERROR: publication "testpub3" is not in subscription "regress_testsub" -- ok - delete publications ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub1, testpub2 WITH (refresh = false); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 | off (1 row) DROP SUBSCRIPTION regress_testsub; @@ -371,19 +375,19 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | p | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | p | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 | off (1 row) -- we can alter streaming when two_phase enabled ALTER SUBSCRIPTION regress_testsub SET (streaming = true); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 | off (1 row) ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); @@ -393,10 +397,10 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 | off (1 row) ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); @@ -409,18 +413,18 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 | off (1 row) ALTER SUBSCRIPTION regress_testsub SET (disable_on_error = true); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | t | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN | Spill files compression +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------+------------------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | t | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 | off (1 row) ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql index 007c9e7037..368696f430 100644 --- a/src/test/regress/sql/subscription.sql +++ b/src/test/regress/sql/subscription.sql @@ -140,6 +140,10 @@ ALTER SUBSCRIPTION regress_testsub RENAME TO regress_testsub_foo; ALTER SUBSCRIPTION regress_testsub_foo SET (synchronous_commit = local); ALTER SUBSCRIPTION regress_testsub_foo SET (synchronous_commit = foobar); +ALTER SUBSCRIPTION regress_testsub_foo SET (spill_compression = pglz); +ALTER SUBSCRIPTION regress_testsub_foo SET (spill_compression = off); +ALTER SUBSCRIPTION regress_testsub_foo SET (spill_compression = foobar); + \dRs+ -- rename back to keep the rest simple -- 2.43.0 [application/octet-stream] v5-0006-Add-ReorderBuffer-ondisk-compression-TAP-tests.patch (5.6K, ../../CAFEQCbHQDq6ZfVwRDk8Ym2-EAVueSDLRLop0=3cujKTWqGcRmg@mail.gmail.com/7-v5-0006-Add-ReorderBuffer-ondisk-compression-TAP-tests.patch) download | inline diff: From 864ef6b3ed5fcbb90af71682a45a5118b3b61845 Mon Sep 17 00:00:00 2001 From: Julien Tachoires <[email protected]> Date: Mon, 28 Oct 2024 13:40:28 +0100 Subject: [PATCH 6/6] Add ReorderBuffer ondisk compression TAP tests --- src/test/subscription/Makefile | 2 + src/test/subscription/meson.build | 7 +- .../t/034_reorderbuffer_compression.pl | 107 ++++++++++++++++++ 3 files changed, 115 insertions(+), 1 deletion(-) create mode 100644 src/test/subscription/t/034_reorderbuffer_compression.pl diff --git a/src/test/subscription/Makefile b/src/test/subscription/Makefile index ce1ca43009..9341f1493c 100644 --- a/src/test/subscription/Makefile +++ b/src/test/subscription/Makefile @@ -16,6 +16,8 @@ include $(top_builddir)/src/Makefile.global EXTRA_INSTALL = contrib/hstore export with_icu +export with_lz4 +export with_zstd check: $(prove_check) diff --git a/src/test/subscription/meson.build b/src/test/subscription/meson.build index c591cd7d61..772eeb817f 100644 --- a/src/test/subscription/meson.build +++ b/src/test/subscription/meson.build @@ -5,7 +5,11 @@ tests += { 'sd': meson.current_source_dir(), 'bd': meson.current_build_dir(), 'tap': { - 'env': {'with_icu': icu.found() ? 'yes' : 'no'}, + 'env': { + 'with_icu': icu.found() ? 'yes' : 'no', + 'with_lz4': lz4.found() ? 'yes' : 'no', + 'with_zstd': zstd.found() ? 'yes' : 'no', + }, 'tests': [ 't/001_rep_changes.pl', 't/002_types.pl', @@ -40,6 +44,7 @@ tests += { 't/031_column_list.pl', 't/032_subscribe_use_index.pl', 't/033_run_as_table_owner.pl', + 't/034_reorderbuffer_compression.pl', 't/100_bugs.pl', ], }, diff --git a/src/test/subscription/t/034_reorderbuffer_compression.pl b/src/test/subscription/t/034_reorderbuffer_compression.pl new file mode 100644 index 0000000000..57def74bf9 --- /dev/null +++ b/src/test/subscription/t/034_reorderbuffer_compression.pl @@ -0,0 +1,107 @@ + +# Copyright (c) 2024, PostgreSQL Global Development Group + +# Test ReorderBuffer compression +use strict; +use warnings FATAL => 'all'; +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +sub test_reorderbuffer_compression +{ + my ($node_publisher, $node_subscriber, $appname, $compression, $compression_rate) = @_; + + # Set subscriber's spill_compression option + $node_subscriber->safe_psql('postgres', + "ALTER SUBSCRIPTION tap_sub SET (spill_compression = $compression)"); + + # Make sure the table is empty + $node_publisher->safe_psql('postgres', 'TRUNCATE test_tab'); + + # Reset replication slot stats + $node_publisher->safe_psql('postgres', + "SELECT pg_stat_reset_replication_slot('tap_sub')"); + + # Insert 1 million rows in the table + $node_publisher->safe_psql('postgres', + "INSERT INTO test_tab SELECT i, 'Message number #'||i::TEXT FROM generate_series(1, 1000000) as i" + ); + + $node_publisher->wait_for_catchup($appname); + + # Check if table content is replicated + my $result = + $node_subscriber->safe_psql('postgres', + "SELECT count(*) FROM test_tab"); + is($result, qq(1000000), 'check data was copied to subscriber'); + + # Check if the transaction was spilled on disk + my $res_stats = + $node_publisher->safe_psql('postgres', + "SELECT spill_txns FROM pg_catalog.pg_stat_get_replication_slot('tap_sub');"); + is($res_stats, qq(1), 'check if the transaction was spilled on disk'); + + # Check the compression ratio + my $res_comp_rate = + $node_publisher->safe_psql('postgres', + "SELECT ((1 - spill_write_bytes::FLOAT / spill_bytes::FLOAT) * 100)::INT FROM pg_catalog.pg_stat_get_replication_slot('tap_sub');"); + ok($res_comp_rate >= $compression_rate, "check if the compression rate (spill_compression = '$compression') is greater than or equal to $compression_rate"); +} + +# Create publisher node +my $node_publisher = PostgreSQL::Test::Cluster->new('publisher'); +$node_publisher->init(allows_streaming => 'logical'); +$node_publisher->append_conf('postgresql.conf', + 'logical_decoding_work_mem = 64'); +$node_publisher->start; + +# Create subscriber node +my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber'); +$node_subscriber->init; +$node_subscriber->start; + +# Setup structure on publisher +$node_publisher->safe_psql('postgres', + "CREATE TABLE test_tab (a int primary key, b text)"); + +# Setup structure on subscriber +$node_subscriber->safe_psql('postgres', + "CREATE TABLE test_tab (a int primary key, b text)"); + +# Setup logical replication +my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres'; +$node_publisher->safe_psql('postgres', + "CREATE PUBLICATION tap_pub FOR TABLE test_tab"); + +my $appname = 'tap_sub'; + +$node_subscriber->safe_psql('postgres', + "CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub WITH (streaming = off)" +); + +# No data compression expected, compression ratio is 0 +test_reorderbuffer_compression($node_publisher, $node_subscriber, $appname, + 'off', 0); +# Compression ratio greater than or equal to 30% for pglz +test_reorderbuffer_compression($node_publisher, $node_subscriber, $appname, + 'pglz', 30); +SKIP: +{ + skip "LZ4 not supported by this build", 2 if ($ENV{with_lz4} ne 'yes'); + # Compression ratio greater than or equal to 70% for lz4 + test_reorderbuffer_compression($node_publisher, $node_subscriber, $appname, + 'lz4', 70); +} +SKIP: +{ + skip "ZSTD not supported by this build", 2 if ($ENV{with_zstd} ne 'yes'); + # Compression ratio greater than or equal to 80% for zstd + test_reorderbuffer_compression($node_publisher, $node_subscriber, $appname, + 'zstd', 80); +} + +$node_subscriber->stop; +$node_publisher->stop; + +done_testing(); -- 2.43.0 ^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2024-10-28 14:54 UTC | newest] Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-07-15 14:11 [PATCH 2/2] Add psql index info commands Nikita Glukhov <[email protected]> 2019-07-15 14:11 [PATCH 2/2] Add psql index info commands Nikita Glukhov <[email protected]> 2021-12-18 20:58 [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]> 2021-12-18 20:58 [PATCH v4 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]> 2021-12-18 20:58 [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]> 2021-12-18 20:58 [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]> 2021-12-18 20:58 [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]> 2021-12-18 20:58 [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]> 2021-12-18 20:58 [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]> 2021-12-18 20:58 [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]> 2021-12-18 20:58 [PATCH v8 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]> 2024-09-23 19:58 Re: Compress ReorderBuffer spill files using LZ4 Julien Tachoires <[email protected]> 2024-09-24 09:57 ` Re: Compress ReorderBuffer spill files using LZ4 Tomas Vondra <[email protected]> 2024-10-28 14:54 ` Re: Compress ReorderBuffer spill files using LZ4 Julien Tachoires <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox