Hi,
After running regression, I ran EXPLAIN on one of the queries in regression
(test create_misc) and got following output
regression=# explain verbose select * into table ramp from road where name
~ '.*Ramp';
QUERY
PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..154.00 rows=841 width=67)
Output: public.road.name, public.road.thepath
-> Append (cost=0.00..154.00 rows=841 width=67)
-> Seq Scan on public.road (cost=0.00..135.05 rows=418 width=67)
Output: public.road.name, public.road.thepath
Filter: (public.road.name ~ '.*Ramp'::text)
-> Seq Scan on public.ihighway road (cost=0.00..14.99 rows=367
width=67)
^^^^^
Output: public.road.name, public.road.thepath
^^^^^^^^^^, ^^^^^^
Filter: (public.road.name ~ '.*Ramp'::text)
^^^^^^^^^^^
-> Seq Scan on public.shighway road (cost=0.00..3.96 rows=56
width=67)
Output: public.road.name, public.road.thepath
Filter: (public.road.name ~ '.*Ramp'::text)
(12 rows)
regression=# \d+ road
Table "public.road"
Column | Type | Modifiers | Storage | Stats target | Description
---------+------+-----------+----------+--------------+-------------
name | text | | extended | |
thepath | path | | extended | |
Indexes:
"rix" btree (name)
Child tables: ihighway,
shighway
Has OIDs: no
Table "road" has children "ihighway" and "shighway" as seen in the \d+
output above. The EXPLAIN output of Seq Scan node on children has
"public.road" as prefix for variables. "public.road" could imply the parent
table "road" and thus can cause confusion, as to what's been referreed, the
columns of parent table or child table. In the EXPLAIN output children
tables have "road" as alias (as against "public.road"). The alias comes
from RangeTblEntry->eref->aliasname. It might be better to have "road" as
prefix in the variable names over "public.road".
The reason why this happens is the code in get_variable()
3865 /* Exceptions occur only if the RTE is alias-less */
3866 if (rte->alias == NULL)
3867 {
3868 if (rte->rtekind == RTE_RELATION)
3869 {
3870 /*
3871 * It's possible that use of the bare refname would find
another
3872 * more-closely-nested RTE, or be ambiguous, in which case
we need
3873 * to specify the schemaname to avoid these errors.
3874 */
3875 if (find_rte_by_refname(rte->eref->aliasname, context) !=
rte)
3876 schemaname =
get_namespace_name(get_rel_namespace(rte->relid));
3877 }
If there is no alias, we find out the schema name and later add it to the
prefix. In the inherited table case, we are actually creating a "kind of"
alias for the children table and thus we should not find out the schema
name and add it to the prefix. This case has been taken care of in
get_from_clause_item(),
6505 else if (rte->rtekind == RTE_RELATION &&
6506 strcmp(rte->eref->aliasname,
get_relation_name(rte->relid)) != 0)
6507 {
6508 /*
6509 * Apparently the rel has been renamed since the rule was
made.
6510 * Emit a fake alias clause so that variable references
will still
6511 * work. This is not a 100% solution but should work in
most
6512 * reasonable situations.
6513 */
6514 appendStringInfo(buf, " %s",
6515 quote_identifier(rte->eref->aliasname));
6516 gavealias = true;
6517 }
I see similar code in ExplainTargetRel()
1778 if (objectname == NULL ||
1779 strcmp(rte->eref->aliasname, objectname) != 0)
1780 appendStringInfo(es->str, " %s",
1781 quote_identifier(rte->eref->aliasname));
Based on this, here is patch to not add schemaname in the prefix for a
variable.
I have run make check. All except inherit.sql passed. The expected output
change is included in the patch.
--
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Enterprise Postgres Company
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 9ad54c5..2e87183 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -3858,21 +3858,26 @@ get_variable(Var *var, int levelsup, bool showstar, deparse_context *context)
return NULL;
}
/* Identify names to use */
schemaname = NULL; /* default assumptions */
refname = rte->eref->aliasname;
/* Exceptions occur only if the RTE is alias-less */
if (rte->alias == NULL)
{
- if (rte->rtekind == RTE_RELATION)
+ /*
+ * If the rel has been renamed since the rule was made, that's
+ * equivalent to having a alias.
+ */
+ if (rte->rtekind == RTE_RELATION &&
+ strcmp(refname, get_relation_name(rte->relid)) == 0)
{
/*
* It's possible that use of the bare refname would find another
* more-closely-nested RTE, or be ambiguous, in which case we need
* to specify the schemaname to avoid these errors.
*/
if (find_rte_by_refname(rte->eref->aliasname, context) != rte)
schemaname = get_namespace_name(get_rel_namespace(rte->relid));
}
else if (rte->rtekind == RTE_JOIN)
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 7e8f572..d47aeea 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1001,58 +1001,58 @@ explain (verbose, costs off) select * from matest0 order by 1-id;
---------------------------------------------------------------------------------
Sort
Output: public.matest0.id, public.matest0.name, ((1 - public.matest0.id))
Sort Key: ((1 - public.matest0.id))
-> Result
Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id)
-> Append
-> Seq Scan on public.matest0
Output: public.matest0.id, public.matest0.name
-> Seq Scan on public.matest1 matest0
- Output: public.matest0.id, public.matest0.name
+ Output: matest0.id, matest0.name
-> Seq Scan on public.matest2 matest0
- Output: public.matest0.id, public.matest0.name
+ Output: matest0.id, matest0.name
-> Seq Scan on public.matest3 matest0
- Output: public.matest0.id, public.matest0.name
+ Output: matest0.id, matest0.name
(14 rows)
select * from matest0 order by 1-id;
id | name
----+--------
6 | Test 6
5 | Test 5
4 | Test 4
3 | Test 3
2 | Test 2
1 | Test 1
(6 rows)
reset enable_indexscan;
set enable_seqscan = off; -- plan with fewest seqscans should be merge
explain (verbose, costs off) select * from matest0 order by 1-id;
- QUERY PLAN
----------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------
Result
Output: public.matest0.id, public.matest0.name, ((1 - public.matest0.id))
-> Merge Append
Sort Key: ((1 - public.matest0.id))
-> Index Scan using matest0i on public.matest0
Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id)
-> Index Scan using matest1i on public.matest1 matest0
- Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id)
+ Output: matest0.id, matest0.name, (1 - matest0.id)
-> Sort
- Output: public.matest0.id, public.matest0.name, ((1 - public.matest0.id))
- Sort Key: ((1 - public.matest0.id))
+ Output: matest0.id, matest0.name, ((1 - matest0.id))
+ Sort Key: ((1 - matest0.id))
-> Seq Scan on public.matest2 matest0
- Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id)
+ Output: matest0.id, matest0.name, (1 - matest0.id)
-> Index Scan using matest3i on public.matest3 matest0
- Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id)
+ Output: matest0.id, matest0.name, (1 - matest0.id)
(15 rows)
select * from matest0 order by 1-id;
id | name
----+--------
6 | Test 6
5 | Test 5
4 | Test 4
3 | Test 3
2 | Test 2
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
