PostgreSQL 9.6 – View Dependencies After Removing Explicit and Transitive Dependencies

postgresqlpostgresql-9.6

Assume I have the following table and view:

CREATE TABLE my_table (a int, b text, c text);
CREATE VIEW my_view AS SELECT * FROM my_table WHERE b = 'foo';

The table has some data:

INSERT INTO my_table VALUES (1, 'foo', 'first');
INSERT INTO my_table VALUES (2, 'foo', 'second');
INSERT INTO my_table VALUES (3, 'foo', 'third');

I realise that column b always has the same value 'foo'. I decide that the entire column is unnecessary, and wish to drop it.

Because I like to be explicit about my dependencies, to make sure I don't accidentally drop more than I should, I avoid using the CASCADE keyword, opting instead to redefine the dependent view manually:

CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_table;

The view now no longer has any explicit dependency on column b, so the column should be safe to delete. 

ALTER TABLE my_table DROP COLUMN b;

However, as I try to drop column b, I get the following error message

ERROR:  cannot drop table my_table column b because other objects depend on it
DETAIL:  view my_view depends on table my_table column b
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Why does this error message appear even after removing the dependency on the column to be dropped?

Best Answer

TL;DR: The view definition statement is only evaluated once, not each time the view is queried.

It appears that I have misunderstood how DDL statements like CREATE OR REPLACE VIEW are evaluated.

I had assumed that the definition of the view (or at least, its SELECT clause) was reevaluated verbatim upon querying the view. From that point of view, the expanded form of 

CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_table;

would be

CREATE OR REPLACE VIEW my_view AS SELECT a, b, c FROM my_table;

before the column b was dropped, but

CREATE OR REPLACE VIEW my_view AS SELECT a, c FROM my_table;

after column b was dropped.

However, after my attempt to drop column b failed, I decided to inspect the interpreted definition of my_view.

postgres=# \d+ my_view
                 View "public.my_view"
 Column |  Type   | Modifiers | Storage  | Description 
--------+---------+-----------+----------+-------------
 a      | integer |           | plain    | 
 b      | text    |           | extended | 
 c      | text    |           | extended | 
View definition:
 SELECT my_table.a,
    my_table.b,
    my_table.c
   FROM my_table;

As it turns out, it seems the view definition is evaluated only once, and that the * wildcard from the SELECT clause was expanded to column list a, b, c upon evaluation. It seems this * wildcard is not re-evaluated as the underlying table has changed, contrary to my initial beliefs.

I assume this means I must redefine the view after the table's column list has changed.

Related Question