Search code examples
pipelinedb

Changing a table into a continuous view - issue with dependent continuous views

We're refactoring the structure of our tables and views and one of the improvements is changing one table (which is updated "manually" from a java class) to a continuous view.

The name of the view has to be the same with the old table and the old data has to be kept, so I think these steps would be logical:

  1. ALTER TABLE old_table RENAME TO old_table_temp
  2. Create the new continuous view
  3. INSERT INTO new_continuous_view SELECT * FROM old_table_temp
  4. DROP old_table_temp

The problem that I have right now is that when renaming the table, all the dependent views will still be depending on the newly named table so I can not drop it. The error looks like this:

analytics=> drop table renamed_table;
ERROR:  cannot drop table renamed_table because other objects depend on it
DETAIL:  continuous view cview1 depends on table renamed_table
continuous view cview2 depends on table renamed_table
continuous view cview3 depends on table renamed_table
continuous view cview4 depends on table renamed_table

Any idea would be appreciated, even if it's a different approach.

Solution

  • This is not possible. Each continuous view is backed by a materialization table (suffixed with _mrel), which stores the transition states of all aggregates being calculated in the continuous view. At read time these transition states are converted into finalized aggregate values. A simple example would be avg where the materialization table stores the sum and count, and at read time we calculate the average by dividing the two.

    These transition states are mostly byte arrays with their internal implementation not exposed to the user and so the only way to create them is to back-fill data into the stream the continuous view is reading from and let our continuous query execution pipeline re-calculate them.

    Modifications to materialization tables are disabled by default but you can enable them by setting the configuration parameter continuous_query_materialization_table_updatable. This might be useful in cases where you want to truncate old data or delete data for a particular group you want to back-fill data for.

    As far as migrating dependent views is concerned, I think the easiest way would be to re-define them on the continuous view. The whole dependency management is internal to PostgreSQL and manually tweaking with it is not recommended.