On 2024-Jan-18, Alvaro Herrera wrote:
> commands/explain.c (Hmm, I think this is a preexisting bug actually)
>
> 3893 18 : case T_TableFuncScan:
> 3894 18 : Assert(rte->rtekind == RTE_TABLEFUNC);
> 3895 18 : if (rte->tablefunc)
> 3896 0 : if (rte->tablefunc->functype ==
> TFT_XMLTABLE)
> 3897 0 : objectname = "xmltable";
> 3898 : else /* Must be
> TFT_JSON_TABLE */
> 3899 0 : objectname = "json_table";
> 3900 : else
> 3901 18 : objectname = NULL;
> 3902 18 : objecttag = "Table Function Name";
> 3903 18 : break;
Indeed -- the problem seems to be that add_rte_to_flat_rtable is
creating a new RTE and zaps the ->tablefunc pointer for it. So when
EXPLAIN goes to examine the struct, there's a NULL pointer there and
nothing is printed.
One simple fix is to change add_rte_to_flat_rtable so that it doesn't
zero out the tablefunc pointer, but this is straight against what that
function is trying to do, namely to remove substructure. Which means
that we need to preserve the name somewhere else. I added a new member
to RangeTblEntry for this, which perhaps is a little ugly. So here's
the patch for that. (I also added an alias to one XMLTABLE invocation
under EXPLAIN, to show what it looks like when an alias is specified.
Otherwise they're always shown as "XMLTABLE" "xmltable" which is a bit
dumb).
Another possible way out is to decide that we don't want the
"objectname" to be reported here. I admit it's perhaps redundant. In
this case we'd just remove lines 3896-3899 shown above and let it be
NULL.
Thoughts?
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
>From 00d2885ddd0475f787e0d64807f8eb2858c95ff0 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Thu, 18 Jan 2024 18:07:30 +0100
Subject: [PATCH] Show function name in TableFuncScan
Previously we were only showing the user-specified alias, but this is
clearly not the code's intent.
---
src/backend/commands/explain.c | 2 +-
src/backend/nodes/outfuncs.c | 1 +
src/backend/nodes/readfuncs.c | 1 +
src/backend/parser/parse_relation.c | 4 ++--
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/xml_1.out | 22 +++++++++++-----------
src/test/regress/sql/xml.sql | 2 +-
7 files changed, 18 insertions(+), 15 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 3d590a6b9f..4df715e344 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3892,7 +3892,7 @@ ExplainTargetRel(Plan *plan, Index rti, ExplainState *es)
break;
case T_TableFuncScan:
Assert(rte->rtekind == RTE_TABLEFUNC);
- objectname = "xmltable";
+ objectname = rte->tablefunc_name;
objecttag = "Table Function Name";
break;
case T_ValuesScan:
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 296ba84518..b42daaba53 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -531,6 +531,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
break;
case RTE_TABLEFUNC:
WRITE_NODE_FIELD(tablefunc);
+ WRITE_STRING_FIELD(tablefunc_name);
break;
case RTE_VALUES:
WRITE_NODE_FIELD(values_lists);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 1624b34581..925192cb07 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -528,6 +528,7 @@ _readRangeTblEntry(void)
break;
case RTE_TABLEFUNC:
READ_NODE_FIELD(tablefunc);
+ READ_STRING_FIELD(tablefunc_name);
/* The RTE must have a copy of the column type info, if any */
if (local_node->tablefunc)
{
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 34a0ec5901..65e54abdd1 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -2073,17 +2073,17 @@ addRangeTableEntryForTableFunc(ParseState *pstate,
Assert(list_length(tf->coltypmods) == list_length(tf->colnames));
Assert(list_length(tf->colcollations) == list_length(tf->colnames));
- refname = alias ? alias->aliasname : pstrdup("xmltable");
-
rte->rtekind = RTE_TABLEFUNC;
rte->relid = InvalidOid;
rte->subquery = NULL;
rte->tablefunc = tf;
+ rte->tablefunc_name = pstrdup("XMLTABLE");
rte->coltypes = tf->coltypes;
rte->coltypmods = tf->coltypmods;
rte->colcollations = tf->colcollations;
rte->alias = alias;
+ refname = alias ? alias->aliasname : pstrdup("xmltable");
eref = alias ? copyObject(alias) : makeAlias(refname, NIL);
numaliases = list_length(eref->colnames);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index b3181f34ae..7af206553a 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1144,6 +1144,7 @@ typedef struct RangeTblEntry
* Fields valid for a TableFunc RTE (else NULL):
*/
TableFunc *tablefunc;
+ char *tablefunc_name;
/*
* Fields valid for a values RTE (else NIL):
diff --git a/src/test/regress/expected/xml_1.out b/src/test/regress/expected/xml_1.out
index eb9c6f2ed4..eb740ce8b1 100644
--- a/src/test/regress/expected/xml_1.out
+++ b/src/test/regress/expected/xml_1.out
@@ -1004,11 +1004,11 @@ CREATE OR REPLACE VIEW public.xmltableview1 AS
FROM xmldata) x,
LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME/text()'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+----------------------------------------------------
Nested Loop
-> Seq Scan on xmldata
- -> Table Function Scan on "xmltable"
+ -> Table Function Scan on "XMLTABLE" "xmltable"
(3 rows)
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1;
@@ -1018,7 +1018,7 @@ EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1;
Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
-> Seq Scan on public.xmldata
Output: xmldata.data
- -> Table Function Scan on "xmltable"
+ -> Table Function Scan on "XMLTABLE" "xmltable"
Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME/text()'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
(7 rows)
@@ -1162,7 +1162,7 @@ SELECT xmltable.*
Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
-> Seq Scan on public.xmldata
Output: xmldata.data
- -> Table Function Scan on "xmltable"
+ -> Table Function Scan on "XMLTABLE" "xmltable"
Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
(7 rows)
@@ -1174,17 +1174,17 @@ SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan"
(0 rows)
EXPLAIN (VERBOSE, COSTS OFF)
-SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
+SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) AS f WHERE "COUNTRY_NAME" = 'Japan';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop
- Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
+ Output: f."COUNTRY_NAME", f."REGION_ID"
-> Seq Scan on public.xmldata
Output: xmldata.data
- -> Table Function Scan on "xmltable"
- Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
+ -> Table Function Scan on "XMLTABLE" f
+ Output: f."COUNTRY_NAME", f."REGION_ID"
Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer)
- Filter: ("xmltable"."COUNTRY_NAME" = 'Japan'::text)
+ Filter: (f."COUNTRY_NAME" = 'Japan'::text)
(8 rows)
-- should to work with more data
@@ -1278,7 +1278,7 @@ SELECT xmltable.*
Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
-> Seq Scan on public.xmldata
Output: xmldata.data
- -> Table Function Scan on "xmltable"
+ -> Table Function Scan on "XMLTABLE" "xmltable"
Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
Filter: ("xmltable".region_id = 2)
diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql
index 34dc4f1e39..4ff308939c 100644
--- a/src/test/regress/sql/xml.sql
+++ b/src/test/regress/sql/xml.sql
@@ -505,7 +505,7 @@ SELECT xmltable.*
SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
EXPLAIN (VERBOSE, COSTS OFF)
-SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
+SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) AS f WHERE "COUNTRY_NAME" = 'Japan';
-- should to work with more data
INSERT INTO xmldata VALUES('<ROWS>
--
2.39.2
