show schema.collate in explain(verbose on)
Started by wangsh.fnst@fujitsu.comalmost 4 years ago2 messages
Hi,
The comment of get_collation_name wrote:
* NOTE: since collation name is not unique, be wary of code that uses this
* for anything except preparing error messages.
In file explain.c, the function get_collation_name() is called, but the schema
name won't be outputted.
Since the schema name of relation/function name will be outputted in
explain's result when verbose(on) is specified. I plan to output the schema
name of collation as well as relation/function.
The patch attached, any thought?
Regards
Shenhao Wang
Attachments:
0001-show-schema-name-of-collation-in-explain-verbose.patchapplication/octet-stream; name=0001-show-schema-name-of-collation-in-explain-verbose.patchDownload
From 881d575e0a3070be4cac71e84a844634f9b49901 Mon Sep 17 00:00:00 2001
From: "wangsh.fnst" <wangsh.fnst@fujitsu.com>
Date: Tue, 22 Feb 2022 01:08:58 +0800
Subject: [PATCH] show schema name of collation in explain(verbose)
---
.../postgres_fdw/expected/postgres_fdw.out | 2 +-
src/backend/commands/explain.c | 21 ++++++++++++----
src/backend/utils/cache/lsyscache.c | 24 +++++++++++++++++++
src/include/utils/lsyscache.h | 1 +
src/test/regress/expected/collate.out | 14 ++++++-----
src/test/regress/sql/collate.sql | 2 +-
6 files changed, 52 insertions(+), 12 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 057342083c..a3042cce1d 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -913,7 +913,7 @@ EXPLAIN (VERBOSE, COSTS OFF)
-------------------------------------------------------------------------------
Sort
Output: c1, c2, c3, c4, c5, c6, c7, c8, ((c3)::text)
- Sort Key: ft2.c1, ft2.c3 COLLATE "C"
+ Sort Key: ft2.c1, ft2.c3 COLLATE pg_catalog."C"
-> Foreign Scan on public.ft2
Output: c1, c2, c3, c4, c5, c6, c7, c8, c3
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index b970997c34..efec155d5d 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -102,7 +102,8 @@ static void show_sort_group_keys(PlanState *planstate, const char *qlabel,
Oid *sortOperators, Oid *collations, bool *nullsFirst,
List *ancestors, ExplainState *es);
static void show_sortorder_options(StringInfo buf, Node *sortexpr,
- Oid sortOperator, Oid collation, bool nullsFirst);
+ Oid sortOperator, Oid collation, bool nullsFirst,
+ ExplainState *es);
static void show_tablesample(TableSampleClause *tsc, PlanState *planstate,
List *ancestors, ExplainState *es);
static void show_sort_info(SortState *sortstate, ExplainState *es);
@@ -2567,7 +2568,7 @@ show_sort_group_keys(PlanState *planstate, const char *qlabel,
(Node *) target->expr,
sortOperators[keyno],
collations[keyno],
- nullsFirst[keyno]);
+ nullsFirst[keyno], es);
/* Emit one property-list item per sort key */
result = lappend(result, pstrdup(sortkeybuf.data));
if (keyno < nPresortedKeys)
@@ -2585,7 +2586,8 @@ show_sort_group_keys(PlanState *planstate, const char *qlabel,
*/
static void
show_sortorder_options(StringInfo buf, Node *sortexpr,
- Oid sortOperator, Oid collation, bool nullsFirst)
+ Oid sortOperator, Oid collation,
+ bool nullsFirst, ExplainState *es)
{
Oid sortcoltype = exprType(sortexpr);
bool reverse = false;
@@ -2604,10 +2606,21 @@ show_sortorder_options(StringInfo buf, Node *sortexpr,
if (OidIsValid(collation) && collation != get_typcollation(sortcoltype))
{
char *collname = get_collation_name(collation);
+ char *namespace = NULL;
if (collname == NULL)
elog(ERROR, "cache lookup failed for collation %u", collation);
- appendStringInfo(buf, " COLLATE %s", quote_identifier(collname));
+
+ if (es->verbose)
+ namespace = get_namespace_name_or_temp(
+ get_collation_namespace(collation));
+
+ if (namespace == NULL)
+ appendStringInfo(buf, " COLLATE %s", quote_identifier(collname));
+ else
+ appendStringInfo(buf, " COLLATE %s.%s",
+ quote_identifier(namespace),
+ quote_identifier(collname));
}
/* Print direction if not ASC, or USING if non-default sort operator */
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index feef999863..b64df2702a 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1075,6 +1075,30 @@ get_collation_name(Oid colloid)
return NULL;
}
+/*
+ * get_collation_namespace
+ *
+ * Returns the pg_namespace OID associated with a given collation.
+ */
+Oid
+get_collation_namespace(Oid colloid)
+{
+ HeapTuple tp;
+
+ tp = SearchSysCache1(COLLOID, ObjectIdGetDatum(colloid));
+ if (HeapTupleIsValid(tp))
+ {
+ Form_pg_collation colltup = (Form_pg_collation) GETSTRUCT(tp);
+ Oid result;
+
+ result = colltup->collnamespace;
+ ReleaseSysCache(tp);
+ return result;
+ }
+ else
+ return InvalidOid;
+}
+
bool
get_collation_isdeterministic(Oid colloid)
{
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index b8dd27d4a9..e810055d0c 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -98,6 +98,7 @@ extern void get_atttypetypmodcoll(Oid relid, AttrNumber attnum,
extern Datum get_attoptions(Oid relid, int16 attnum);
extern Oid get_cast_oid(Oid sourcetypeid, Oid targettypeid, bool missing_ok);
extern char *get_collation_name(Oid colloid);
+extern Oid get_collation_namespace(Oid colloid);
extern bool get_collation_isdeterministic(Oid colloid);
extern char *get_constraint_name(Oid conoid);
extern Oid get_constraint_index(Oid conoid);
diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.out
index 246832575c..a369cb7608 100644
--- a/src/test/regress/expected/collate.out
+++ b/src/test/regress/expected/collate.out
@@ -631,14 +631,16 @@ RESET enable_seqscan;
RESET enable_hashjoin;
RESET enable_nestloop;
-- EXPLAIN
-EXPLAIN (COSTS OFF)
+EXPLAIN (COSTS OFF, VERBOSE ON)
SELECT * FROM collate_test10 ORDER BY x, y;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
Sort
- Sort Key: x COLLATE "C", y COLLATE "POSIX"
- -> Seq Scan on collate_test10
-(3 rows)
+ Output: a, x, y
+ Sort Key: collate_test10.x COLLATE pg_catalog."C", collate_test10.y COLLATE pg_catalog."POSIX"
+ -> Seq Scan on collate_tests.collate_test10
+ Output: a, x, y
+(5 rows)
EXPLAIN (COSTS OFF)
SELECT * FROM collate_test10 ORDER BY x DESC, y COLLATE "C" ASC NULLS FIRST;
diff --git a/src/test/regress/sql/collate.sql b/src/test/regress/sql/collate.sql
index c3d40fc195..4105da82f2 100644
--- a/src/test/regress/sql/collate.sql
+++ b/src/test/regress/sql/collate.sql
@@ -236,7 +236,7 @@ RESET enable_nestloop;
-- EXPLAIN
-EXPLAIN (COSTS OFF)
+EXPLAIN (COSTS OFF, VERBOSE ON)
SELECT * FROM collate_test10 ORDER BY x, y;
EXPLAIN (COSTS OFF)
SELECT * FROM collate_test10 ORDER BY x DESC, y COLLATE "C" ASC NULLS FIRST;
--
2.32.0
Re: show schema.collate in explain(verbose on)
"wangsh.fnst@fujitsu.com" <wangsh.fnst@fujitsu.com> writes:
Since the schema name of relation/function name will be outputted in
explain's result when verbose(on) is specified. I plan to output the schema
name of collation as well as relation/function.
While that's not unreasonable in principle, it seems like it'll just be
useless verbosity in pretty much any actual database. In what scenario
would you have duplicate collation names?
regards, tom lane