BUG #5234: ALTER TABLE ... RENAME COLUMN change view definition incorrectly - Mailing list pgsql-bugs

From Sergey Burladyan
Subject BUG #5234: ALTER TABLE ... RENAME COLUMN change view definition incorrectly
Date
Msg-id 200912062241.nB6MfTen069216@wwwmaster.postgresql.org
Whole thread Raw
Responses Re: BUG #5234: ALTER TABLE ... RENAME COLUMN change view definition incorrectly
List pgsql-bugs
The following bug has been logged online:

Bug reference:      5234
Logged by:          Sergey Burladyan
Email address:      eshkinkot@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));
. . .

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: ossp/uuid.h error during configure of 8_3_STABLE
Next
From: "Oleg Yurchenko"
Date:
Subject: BUG #5235: Segmentation fault under high load through JDBC