PostgreSQL – How to Rename Table and Replace with a View

migrationpostgresqlview

I have a very central table in our database that is used by a range of applications, it has rules attached to it, triggers and all the dependencies that you can imagine. Now I would like to modify the table without causing any problems with the dependencies. I have previously been successful with doing the following but in a much less complex case:

alter table reconciliations rename to matches;
create view reconciliations as select * from matches;

What this achieves is that I could now modify the new "matches" table and for example add a column or rows, which do not need to get presented in the "reconciliations" view (by adding a where clause to filter them out).

I am on Postgres 9.5 so the view is automatically updatable. Initial tests shows that there are no immediate problems with this, so I am asking this question to know what kind of problems I should be looking for. Performance is not a big issue.

Best Answer

If you do this in production, be aware of prepared statements. Those have already been parsed, rewritten (and the query plan cached). The effect kicks until prepared statements are deallocated (which can take a long time).

To check for prepared statements (of the same session only!):

TABLE pg_prepared_statements;

The effect also extends to plpgsql functions that handle SQL commands like prepared statements internally.

Normally, prepared statements are forced to be re-planned after any change to involved objects. But your actions circumvent this security mechanism.

Also, most queries will just keep working. But not all.

Demo

CREATE TEMP TABLE foo (id int);
INSERT INTO foo VALUES (4);
PREPARE x AS SELECT foo FROM foo WHERE id > $1;  -- uses row type

This works:

ALTER TABLE foo ADD COLUMN t text;
EXECUTE x(3);  -- automatically re-planned

But this does not:

ALTER TABLE foo RENAME TO foo1;
CREATE VIEW foo AS TABLE foo1;
ALTER TABLE foo1 ADD COLUMN t text;

EXECUTE x(3);  -- not re-planned!

ERROR:  cached plan must not change result type

The manual on PREPARE:

Although the main point of a prepared statement is to avoid repeated parse analysis and planning of the statement, PostgreSQL will force re-analysis and re-planning of the statement before using it whenever database objects used in the statement have undergone definitional (DDL) changes since the previous use of the prepared statement. Also, if the value of search_path changes from one use to the next, the statement will be re-parsed using the new search_path. (This latter behavior is new as of PostgreSQL 9.3.) These rules make use of a prepared statement semantically almost equivalent to re-submitting the same query text over and over, but with a performance benefit if no object definitions are changed, especially if the best plan remains the same across uses. An example of a case where the semantic equivalence is not perfect is that if the statement refers to a table by an unqualified name, and then a new table of the same name is created in a schema appearing earlier in the search_path, no automatic re-parse will occur since no object used in the statement changed. However, if some other change forces a re-parse, the new table will be referenced in subsequent uses.

The above is another example where the "semantic equivalence is not perfect".

Related Question