Re: Renaming table is affecting views

Thanks for help Laurenz, but in fact - there is over 400 huge views, and 3
tables to swipe.
Thats why we want to swipe table, not replacing views.

2014-04-18 12:42 GMT+03:00 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>:

> > Александр Кайданник 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
>