| From: | Rob Bygrave <robin(dot)bygrave(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | alter table alter column ... (larger type) ... when there are dependent views |
| Date: | 2016-05-10 23:59:36 |
| Message-ID: | CAC=ts-HjwKPD-G2RkchnjrNnLptDSnuWW+La8JWH7cKoc3kkzA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
I have read the prior discussions linked from
https://wiki.postgresql.org/wiki/Todo#Views_and_Rules
What I would like to do is put the specific case for handling 3 common
'alter column' changes when that column is referenced in a view.
Take the case of:
create table base_table ( id bigserial, acol varchar(10), bcol int, ccol
varchar(1000));
create or replace view base_view as select id, acol, bcol, ccol from
base_table;
create or replace view dep_view as select id, length(acol), bcol, ccol from
base_view;
I would like to review the 3 specific cases:
alter table base_table alter column acol type varchar(20); -- was
varchar(10)
alter table base_table alter column bcol type bigint; -- was
int
alter table base_table alter column ccol type text; -- was
varchar(1000)
At the moment these 3 statements all require that the views be dropped
first and then re-created last. The first case looks like:
-- for the varchar(10) to varchar(20) change
drop view dep_view;
drop view base_view;
alter table base_table ALTER COLUMN bcol type varchar(20);
create or replace view base_view as select id, acol, bcol from base_table;
create or replace view dep_view as select id, length(acol) from base_view;
In practical terms this need to drop and re-create the views gets
harder/bigger with more view dependencies. With large complex schema's
this becomes a rather large pain.
Having read all the previous discussions on the general topic of altering
tables with dependent views I realise this is a complex and difficult issue
in general but I'd like to see if there was some support for looking at
these 3 more specific changes.
1. making a varchar column larger e.g. varchar(10) -> varchar(20)
2. changing int to bigint
3. changing varchar to text
I have seen that there are people motivated enough to update pg_attribute
directly (update pg_attribute a set a.atttypmod = 20 + 4 ...).
What are the thoughts on support these 3 specific cases?
Thanks, Rob.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David E. Wheeler | 2016-05-11 00:12:37 | Does Type Have = Operator? |
| Previous Message | Andres Freund | 2016-05-10 23:57:35 | Re: asynchronous and vectorized execution |