Swapping Tables With Dependencies in Postgres: Part 1

CREDIT: before getting into the post(s), the solution in Part 2 didn’t come from me or even my team. I spent a lot of time trying to figure out why exactly we were doing things this way though, so I thought I’d write up what I found…

At one point when messing with transferring data into a postgres database I ran into a hard wall that had to do with how postgres implements tables and dependent views. The hope was that a new table could be “swapped” into an existing view by just ALTERing some table names and dropping the old, but that’s not possible without some extra work. Here’s why:

For example, lets say we have two tables: contact and message

CREATE TABLE contact(
    id serial primary key,
    first_name varchar,
    last_name varchar,
    phone_number varchar,
    date_created date
);

CREATE TABLE message(
    id serial primary key,
    content varchar,
    sent timestamp,
    received timestamp,
    recipient_id serial references contact(id)
);

and the following view that references both tables: vw_detailed_message

CREATE VIEW vw_detailed_message AS 
    SELECT message.id,
           content,
           sent,
           received,
           contact.first_name AS recipient_first_name,
           contact.last_name AS recipient_last_name,
           contact.phone_number AS recipient_phone_number
    FROM contact JOIN message ON message.recipient_id = contact.id;

If we look for the view definition stored in postgres using \d+ vw_detailed_message, we’ll find the following:

>> \d+ vw_detailed_message
...
View definition:
 SELECT message.id,
    message.content,
    message.sent,
    message.received,
    contact.first_name AS recipient_first_name,
    contact.last_name AS recipient_last_name,
    contact.phone_number AS recipient_phone_number
   FROM contact
     JOIN message ON message.recipient_id = contact.id;

Which is what we’d expect as it references contact and message to pull the fields we want.

But what happens to the view definition if we alter the name of one of the tables? Let’s try it and see:

>> ALTER TABLE message RENAME TO message_stale;
ALTER TABLE
>> \d+ vw_detailed_message
...
View definition:
 SELECT message_stale.id,
    message_stale.content,
    message_stale.sent,
    message_stale.received,
    contact.first_name AS recipient_first_name,
    contact.last_name AS recipient_last_name,
    contact.phone_number AS recipient_phone_number
   FROM contact
     JOIN message_stale ON message_stale.recipient_id = contact.id;

Now, this looks very similar to what we got earlier, except the reference to the table message has been altered to match the new table name message_stale. And this is where the problem started. Instead of persisting message in the view definition, it is altered to match the new table name! In hindsight, this makes sense as the view could have been left in an undesirable state, but part of me expected some sort of warning or log that referenced the views. So this wall exists with views, but what about materialized views?

We can try the same thing with a materialized view to verify:

-- undo table name change
>> ALTER TABLE message_stale RENAME TO message;
ALTER TABLE

-- create materialized view
>> CREATE MATERIALIZED VIEW mvw_detailed_message as
    SELECT message.id,
           content,
           sent,
           received,
           contact.first_name AS recipient_first_name,
           contact.last_name AS recipient_last_name,
           contact.phone_number AS recipient_phone_number
    FROM contact JOIN message ON message.recipient_id = contact.id;
SELECT 10

-- check definition of materialized view
>> \d+ mvw_detailed_message
...
View definition:
 SELECT message.id,
    message.content,
    message.sent,
    message.received,
    contact.first_name AS recipient_first_name,
    contact.last_name AS recipient_last_name,
    contact.phone_number AS recipient_phone_number
   FROM contact
     JOIN message ON message.recipient_id = contact.id;
Access method: heap

-- change names again
>> ALTER TABLE message RENAME TO message_stale;
ALTER TABLE

-- check view definition once more to check if similar reference update is made behind the scenes
>> \d+ mvw_detailed_message
...
View definition:
 SELECT message_stale.id,
    message_stale.content,
    message_stale.sent,
    message_stale.received,
    contact.first_name AS recipient_first_name,
    contact.last_name AS recipient_last_name,
    contact.phone_number AS recipient_phone_number
   FROM contact
     JOIN message_stale ON message_stale.recipient_id = contact.id;
Access method: heap

So the same change happens with materialized views. No luck then just using plain old views or materialized views on their own.

What about functions?