From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "kaydannik(dot)a(at)gmail(dot)com" <kaydannik(dot)a(at)gmail(dot)com>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Renaming table is affecting views |
Date: | 2014-04-18 09:42:15 |
Message-ID: | A737B7A37273E048B164557ADEF4A58B17CF1E7C@ntex2010i.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
> Александр Кайданник 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
From | Date | Subject | |
---|---|---|---|
Next Message | Александр Кайданник | 2014-04-18 10:14:08 | Re: Renaming table is affecting views |
Previous Message | Александр Кайданник | 2014-04-18 09:25:57 | Renaming table is affecting views |