From: | "Sergey Burladyan" <eshkinkot(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5234: ALTER TABLE ... RENAME COLUMN change view definition incorrectly |
Date: | 2009-12-06 22:41:29 |
Message-ID: | 200912062241.nB6MfTen069216@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 5234
Logged by: Sergey Burladyan
Email address: eshkinkot(at)gmail(dot)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));
. . .
From | Date | Subject | |
---|---|---|---|
Next Message | Oleg Yurchenko | 2009-12-07 21:43:31 | BUG #5235: Segmentation fault under high load through JDBC |
Previous Message | Tom Lane | 2009-12-04 16:46:57 | Re: ossp/uuid.h error during configure of 8_3_STABLE |