This post, is about documenting the issue that looks like something that isn't going away anytime soon.
Consider this:
CREATE TABLE k (id INTEGER, b text);
CREATE TABLE l (id INTEGER);
CREATE VIEW vw_k AS SELECT k.b FROM k JOIN l USING (id);
TABLE vw_k;
ALTER TABLE l RENAME COLUMN id TO id2;
INSERT INTO k(id, b) VALUES (1, 'abc');
INSERT INTO l(id2) VALUES (1);
TABLE vw_k;
DROP VIEW vw_k;
CREATE VIEW vw_k AS SELECT k.b FROM k JOIN l USING (id);
This is the output:
rt_testing=# CREATE TABLE k (id INTEGER, b text);
CREATE TABLE
rt_testing=# CREATE TABLE l (id INTEGER);
CREATE TABLE
rt_testing=# CREATE VIEW vw_k AS SELECT k.b FROM k JOIN l USING (id);
CREATE VIEW
rt_testing=# TABLE vw_k;
b
---
(0 rows)
rt_testing=# ALTER TABLE l RENAME COLUMN id TO id2;
ALTER TABLE
rt_testing=# INSERT INTO k(id, b) VALUES (1, 'abc');
INSERT 0 1
rt_testing=# INSERT INTO l(id2) VALUES (1);
INSERT 0 1
rt_testing=# TABLE vw_k;
b
-----
abc
(1 row)
DROP VIEW
rt_testing=# CREATE VIEW vw_k AS SELECT k.b FROM k JOIN l USING (id);
ERROR: column "id" specified in USING clause does not exist in right table
In fact, this gets difficult to trace because the VIEW continues to work normally even after the column name change. For e.g. New rows inserted to the updated table, continue to show up in the VIEW as if its definition was up to date!
To summarize, whenever changing a column name (used in a USING clause on a dependent VIEW), be careful that PostgreSQL may not necessarily complain about the dependency. You'd need to be careful to make that check yourself. Or else its possible that sometime down the line, when you're trying to restore a production backup, you'd start seeing errors related to changes you made a year back!
