On Sun, Dec 6, 2009 at 5:41 PM, Sergey Burladyan <eshkin...@gmail.com> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 5234
> Logged by: Sergey Burladyan
> Email address: eshkin...@gmail.com
> PostgreSQL version: 8.3.8
> Operating system: Debian GNU/Linux 5.0.3 (lenny) + testing
> Description: ALTER TABLE ... RENAME COLUMN change view definition
> incorrectly
> Details:
>
> reported by Weed at http://www.sql.ru/forum/actualthread.aspx?tid=717835
> (Russian)
>
> Example:
> create table a (i int, v text);
> create table b (j int, v text);
> create view v_using as select * from a left join b using (v);
> alter table a rename v to o;
> \d v_using
>
> CREATE TABLE
> CREATE TABLE
> CREATE VIEW
> ALTER TABLE
> View "public.v_using"
> Column | Type | Modifiers
> --------+---------+-----------
> v | text |
> i | integer |
> j | integer |
> View definition:
> SELECT a.o AS v, a.i, b.j
> FROM a
> LEFT JOIN b USING (v);
>
> View is still working, but it text definition is incorrect:
> t1=> select * from v_using ;
> v | i | j
> ---+---+---
> (0 rows)
>
> t1=> SELECT a.o AS v, a.i, b.j
> t1-> FROM a
> t1-> LEFT JOIN b USING (v);
> ERROR: 42703: column "v" specified in USING clause does not exist in left
> table
> LOCATION: transformFromClauseItem, parse_clause.c:813
>
> If you dump database in this state, when you cannot restore this dump
> without manual fix:
> $ pg_dump -Fc -f dump t1
> $ pg_restore dump | grep -A2 VIEW
> -- Name: v_using; Type: VIEW; Schema: public; Owner: seb
> --
>
> CREATE VIEW v_using AS
> SELECT a.o AS v, a.i, b.j FROM (a LEFT JOIN b USING (v));
>
> $ LANG=C sudo -u postgres pg_restore -c -d t1 dump
> . . .
> pg_restore: [archiver (db)] could not execute query: ERROR: column "v"
> specified in USING clause does not exist in left table
> Command was: CREATE VIEW v_using AS
> SELECT a.o AS v, a.i, b.j FROM (a LEFT JOIN b USING (v));
The problem here seems to be that get_from_clause_item() is a bit
naive about the JoinExpr representation. usingClause is stored as a
text argument, but get_from_clause_item() is assuming that the schema
definitions have not changed since the parse tree was created.
isNatural has the same problem.
I'm not an expert on this area of the code, but can we just ignore
isNatural and usingClause when deparsing? The attached patch fixes
the bug for me, although I couldn't swear as to whether it breaks
anything else.
...Robert
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index cdbdea2..54a2c87 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -6024,70 +6024,36 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
get_from_clause_item(j->larg, query, context);
- if (j->isNatural)
+ switch (j->jointype)
{
- if (!PRETTY_INDENT(context))
- appendStringInfoChar(buf, ' ');
- switch (j->jointype)
- {
- case JOIN_INNER:
- appendContextKeyword(context, "NATURAL JOIN ",
- -PRETTYINDENT_JOIN,
- PRETTYINDENT_JOIN, 0);
- break;
- case JOIN_LEFT:
- appendContextKeyword(context, "NATURAL LEFT JOIN ",
- -PRETTYINDENT_JOIN,
- PRETTYINDENT_JOIN, 0);
- break;
- case JOIN_FULL:
- appendContextKeyword(context, "NATURAL FULL JOIN ",
- -PRETTYINDENT_JOIN,
- PRETTYINDENT_JOIN, 0);
- break;
- case JOIN_RIGHT:
- appendContextKeyword(context, "NATURAL RIGHT JOIN ",
- -PRETTYINDENT_JOIN,
- PRETTYINDENT_JOIN, 0);
- break;
- default:
- elog(ERROR, "unrecognized join type: %d",
- (int) j->jointype);
- }
- }
- else
- {
- switch (j->jointype)
- {
- case JOIN_INNER:
- if (j->quals)
- appendContextKeyword(context, " JOIN ",
- -PRETTYINDENT_JOIN,
- PRETTYINDENT_JOIN, 2);
- else
- appendContextKeyword(context, " CROSS JOIN ",
- -PRETTYINDENT_JOIN,
- PRETTYINDENT_JOIN, 1);
- break;
- case JOIN_LEFT:
- appendContextKeyword(context, " LEFT JOIN ",
- -PRETTYINDENT_JOIN,
- PRETTYINDENT_JOIN, 2);
- break;
- case JOIN_FULL:
- appendContextKeyword(context, " FULL JOIN ",
+ case JOIN_INNER:
+ if (j->quals)
+ appendContextKeyword(context, " JOIN ",
-PRETTYINDENT_JOIN,
PRETTYINDENT_JOIN, 2);
- break;
- case JOIN_RIGHT:
- appendContextKeyword(context, " RIGHT JOIN ",
+ else
+ appendContextKeyword(context, " CROSS JOIN ",
-PRETTYINDENT_JOIN,
- PRETTYINDENT_JOIN, 2);
- break;
- default:
- elog(ERROR, "unrecognized join type: %d",
- (int) j->jointype);
- }
+ PRETTYINDENT_JOIN, 1);
+ break;
+ case JOIN_LEFT:
+ appendContextKeyword(context, " LEFT JOIN ",
+ -PRETTYINDENT_JOIN,
+ PRETTYINDENT_JOIN, 2);
+ break;
+ case JOIN_FULL:
+ appendContextKeyword(context, " FULL JOIN ",
+ -PRETTYINDENT_JOIN,
+ PRETTYINDENT_JOIN, 2);
+ break;
+ case JOIN_RIGHT:
+ appendContextKeyword(context, " RIGHT JOIN ",
+ -PRETTYINDENT_JOIN,
+ PRETTYINDENT_JOIN, 2);
+ break;
+ default:
+ elog(ERROR, "unrecognized join type: %d",
+ (int) j->jointype);
}
if (need_paren_on_right)
@@ -6098,31 +6064,14 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
context->indentLevel -= PRETTYINDENT_JOIN_ON;
- if (!j->isNatural)
+ if (j->quals)
{
- if (j->usingClause)
- {
- ListCell *col;
-
- appendStringInfo(buf, " USING (");
- foreach(col, j->usingClause)
- {
- if (col != list_head(j->usingClause))
- appendStringInfo(buf, ", ");
- appendStringInfoString(buf,
- quote_identifier(strVal(lfirst(col))));
- }
+ appendStringInfo(buf, " ON ");
+ if (!PRETTY_PAREN(context))
+ appendStringInfoChar(buf, '(');
+ get_rule_expr(j->quals, context, false);
+ if (!PRETTY_PAREN(context))
appendStringInfoChar(buf, ')');
- }
- else if (j->quals)
- {
- appendStringInfo(buf, " ON ");
- if (!PRETTY_PAREN(context))
- appendStringInfoChar(buf, '(');
- get_rule_expr(j->quals, context, false);
- if (!PRETTY_PAREN(context))
- appendStringInfoChar(buf, ')');
- }
}
if (!PRETTY_PAREN(context) || j->alias != NULL)
appendStringInfoChar(buf, ')');
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
