Обсуждение: Renaming table is affecting views
Hello.
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 ?
Thanks
> Александр Кайданник 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
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.albe@wien.gv.at>:
What is the problem with recreating the view?> Александр Кайданник 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 ?
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
On 18 April 2014 10:25, Александр Кайданник <kaydannik.a@gmail.com> wrote: > I need to swap two tables behind view: What version you running? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
PostgreSQL 9.3.2
2014-04-18 14:26 GMT+03:00 Simon Riggs <simon@2ndquadrant.com>:
On 18 April 2014 10:25, Александр Кайданник <kaydannik.a@gmail.com> wrote:What version you running?
> I need to swap two tables behind view:
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services