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