Re: Renaming table is affecting views

> Александр Кайданник wrote:
> I need to swap two tables behind view:
> And when i am renaming table - its affecting view.
> 
> For example:
> 
> CREATE TABLE verybigtable (id integer primary key, names varchar(10));
> 
> CREATE TABLE inactive_verybigtable (id integer primary key, names varchar(10));
> 
> CREATE VIEW showdata AS (SELECT id, names FROM verybigtable);
> 
> 
> postgres=# \d+ showdata
>                        View "public.showdata"
>  Column |         Type          | Modifiers | Storage  | Description
> --------+-----------------------+-----------+----------+-------------
>  id     | integer               |           | plain    |
>  names  | character varying(10) |           | extended |
> View definition:
>  SELECT verybigtable.id, verybigtable.names
>    FROM verybigtable;
> 
> 
> 
> And at moment i need to swipe table behind view.
> 
> ALTER TABLE verybigtable RENAME TO verybigtable_swiping; //giving temporarly name for table
> ALTER TABLE inactive_verybigtable RENAME TO verybigtable; //rename inactive_ table to normal
> ALTER TABLE verybigtable_swiping RENAME TO inactive_verybigtable; //rename temporarly to active
> 
> 
> But, view now also changed.And its problem for me.
> 
> postgres=# \d+ showdata
>                        View "public.showdata"
>  Column |         Type          | Modifiers | Storage  | Description
> --------+-----------------------+-----------+----------+-------------
>  id     | integer               |           | plain    |
>  names  | character varying(10) |           | extended |
> View definition:
>  SELECT verybigtable.id, verybigtable.names
>    FROM inactive_verybigtable verybigtable;
> 
> 
> How to prevent it without recreating view each time ?

What is the problem with recreating the view?

You could use CREATE OR REPLACE VIEW to just change the query.
For more complicated view redefinitions, do them inside a transaction, then they
will not disturb concurrent sessions.

Yous,
Laurenz Albe