PostgreSQL: need to routinely rotate a big table that is mentioned in a view

Question

I have a view:

CREATE OR REPLACE VIEW v_Foo0 AS
SELECT lotsofstuff
  FROM Bar INNER JOIN LotsOfOtherJoins

CREATE TABLE old_Foo AS SELECT * FROM v_Foo LIMIT 0;

CREATE OR REPLACE VIEW v_Foo AS
SELECT * FROM v_Foo0
 WHERE stuff NOT IN (SELECT stuff FROM old_Foo);

CREATE TABLE Foo AS SELECT * FROM v_Foo LIMIT 0;

then I have DML which runs periodically in a procedure, and the normal way could be this:

BEGIN TRANSACTION;

INSERT INTO old_Foo SELECT * FROM Foo;

TRUNCATE TABLE Foo;
INSERT INTO Foo SELECT * FROM v_Foo;

...

INSERT INTO Foo 
SELECT * FROM old_Foo
 WHERE (stuff) NOT IN (SELECT stuff FROM old_Foo)

TRUNCATE TABLE old_Foo;

...

COMMIT;

The point is Foo is huge, and being re-built, incrementally, based on new stuff not already in Foo. Then lots of other stuff happens, then whatever from the old_Foo is not already in the rebuilt Foo will get copied into the rebuilt Foo and then old_Foo can be deleted.

But there is one unnecessary copy operation where 10s of millions of rows get shuffled around just to hang on to the old stuff. I would prefer just renaming Foo to old_Foo then rebuild the new Foo:

BEGIN TRANSACTION;

ALTER TABLE Foo RENAME TO old_Foo;

CREATE TABLE Foo AS SELECT * FROM v_Foo LIMIT 0;
INSERT INTO Foo SELECT * FROM v_Foo;

...

INSERT INTO Foo 
SELECT * FROM old_Foo
 WHERE (stuff) NOT IN (SELECT stuff FROM old_Foo)

DROP TABLE old_Foo;

...

COMMIT;

Problem is, I can't rename Foo nor drop old_Foo because they are referenced in the v_Foo views. And I want that view referencing whatever table is called Foo at the time it's executed, not cling to the table that was called Foo when the view was defined.

The reason for having this view is that it's a multi screen page full query, I don't want to put that into some procedure to be redefined every time. I just want that view there and when time to rebuild this Foo comes, I want it to do its thing.

And in the real world there are many tables like Foo, and I just don't want to redefine these views over and over again verbatim like that in a crazy 5000 line procedure.

Been thinking if I could use a partitioned table, but as soon as I change the partition key, I would have to do an UPDATE which would translate into the same copying of the millions of rows into the old_Foo partition. So nothing would be gained.

Answer 1

I second Arkhena's answer – dropping and re-creating a view is no big deal.

If you want to avoid that, you could resort to a function and base the view on that:

CREATE FUNCTION view_fun() RETURNS TABLE (...)
   LANGUAGE sql AS
$$SELECT ... FROM foo WHERE ...$$;

CREATE VIEW foo_view AS SELECT * FROM view_fun();

Since a function like this will not depend on foo, you can then rename the tables, and because the function does not store the parsed query, it will always use the table that is currently called foo.

Answer 2

The simple without re-thinking everything and assuming everything you do is the only way to do it would be to :

• drop the view
• drop the table
• recreate the view

Another way of doing things would be to create another foo table, make all the changes to it (inserting/deleting) and then drop the view/recreate the view to point to that new table, so that you can now drop the foo table and rename your table as foo.

Dropping or creating a view does not take a long time.

I would still advise to do all your queries in the same transaction so that you can rollback easily and someone connecting to the database will always see a table foo and a view v_foo.

Answer 3

Sadly, the method I wanted to avoid, dropping the view, is the only working method. But I built myself a procedure which can do it for any view, dropping it and recreating it afterwards.

So here is what we do:

1. preserve view definition of v_R from the catalog
2. drop the view v_R
3. rename the table R to old_R
4. create table R as select * from old_R limit 0
5. create view v_R as saved definition
6. copy this grants from old_R to R

Here is the procedure:

CREATE OR REPLACE PROCEDURE reports.move_to_old(schemaName_ text, tableName_ text) LANGUAGE PlPgSQL AS $$
DECLARE
  _schemaName text := lower(schemaName_);
  _tableName text := lower(tableName_);
  _viewName text := 'v_'||_tableName;
  _definition text :=(SELECT definition
                        FROM pg_views
                       WHERE schemaName = _schemaName
                         AND viewName = _viewName);
  command text;
BEGIN
  EXECUTE 'DROP VIEW '||_schemaName||'.'||_viewName;
  EXECUTE 'ALTER TABLE '||_schemaName||'.'||_tableName||' RENAME TO old_'||_tableName;
  EXECUTE 'CREATE TABLE '||_schemaName||'.'||_tableName||' AS SELECT * FROM '||_schemaName||'.old_'||_tableName||' LIMIT 0';
  EXECUTE 'CREATE VIEW '||_schemaName||'.'||_viewName||E' AS \n'||_definition;
  FOR command IN
    SELECT 'GRANT '||string_agg(privilege_type, ', ')
           ||' ON '||table_schema||'.'||table_name
           ||' TO '||string_agg(distinct grantee, ', ')
      FROM information_schema.role_table_grants
     WHERE table_name = 'old_'||_tableName
       AND table_schema = _schemaName
      GROUP BY table_schema, grantee, table_name
  LOOP
    EXECUTE command;
  END LOOP;
END;
$$;

And the cool thing is that PostgreSQL as opposed to stupid Oracle can do all of this in a transaction. So here is the test:

start transaction;
create or replace view reports.v_foo as select 1 as num, 'aa'::text as name;
create table reports.foo as select * from reports.v_foo limit 0;
insert into reports.foo select * from reports.v_foo;
select * from reports.foo;
call move_to_old('reports', 'foo');
insert into reports.foo select * from reports.v_foo;
insert into reports.foo select * from reports.v_foo;
select * from reports.foo;
select * from reports.old_foo;
rollback;