Обсуждение: alter table alter column ... (larger type) ... when there are dependent views
I have read the prior discussions linked from https://wiki.postgresql.org/wiki/Todo#Views_and_Rules
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:
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;
I have seen that there are people motivated enough to update pg_attribute directly (update pg_attribute a set a.atttypmod = 20 + 4 ...).
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;
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 intalter 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.
Re: alter table alter column ... (larger type) ... when there are dependent views
От
Euler Taveira
Дата:
On 10-05-2016 20:59, Rob Bygrave wrote: > 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 > It seems an useful feature request. I've already crossed that bridge while maintaining such a dynamic database schemas. I don't see why we couldn't implement this feature. I'll take a stab at it for the first commitfest. > I have seen that there are people motivated enough to update > pg_attribute directly (update pg_attribute a set a.atttypmod = 20 + 4 ...). > Yuk, this definitely bypass the ATExecAlterColumnType(). It is a hack and must be done with care. -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte24x7 e Treinamento
Euler Taveira <euler@timbira.com.br> writes:
> On 10-05-2016 20:59, Rob Bygrave wrote:
>> 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
> It seems an useful feature request.
FWIW, I think thinking of it like that will almost certainly fall foul
of Polya's Inventor's Paradox. A solution, if practical at all, will
very likely be simpler as well as more useful if it's datatype
independent.
You should look at the code in ALTER TABLE that tries to rebuild index
definitions during ALTER COLUMN TYPE, and see if that can be adapted
to updating views.
regards, tom lane
Re: alter table alter column ... (larger type) ... when there are dependent views
От
Robert Haas
Дата:
On Tue, May 10, 2016 at 11:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > You should look at the code in ALTER TABLE that tries to rebuild index > definitions during ALTER COLUMN TYPE, and see if that can be adapted > to updating views. I think the problems are almost entirely different. In the case of ALTER TABLE, we just need to know whether the modified data will still index in the same way. There are rules for that. In the case of views, the problem has more to do with potential POLA violations. What the user will want (I think) is for the dependent view to end up in the same state that it would have ended up in if the CREATE VIEW command had been issued after the ALTER TABLE command. But I'm pretty sure we lack the information to do that in all cases. We could try some hack, though. We could say that when you alter the table (with some special option), the view definition gets modified by inserting a cast. That is, if v.a gets changed from varchar(20) to varchar(40), we rewrite the view definition so that wherever there was previously a reference to v.a, it gets replaced with a reference to (v.a::varchar(40)). That might lead to hideous results in the face of multiple ALTER TABLE commands, though, and it's doubtful whether it is really the behavior the user wants. What the user actually wants, I think, is for the type of the view column to change from varchar(20) to varchar(40) when the underlying table is altered. That, however, seems like a Pandora's box. At least inserting casts would let the ALTER TABLE succeed, and then you could fix the view afterward with CREATE OR REPLACE VIEW. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, May 10, 2016 at 11:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You should look at the code in ALTER TABLE that tries to rebuild index
>> definitions during ALTER COLUMN TYPE, and see if that can be adapted
>> to updating views.
> I think the problems are almost entirely different. In the case of
> ALTER TABLE, we just need to know whether the modified data will still
> index in the same way. There are rules for that.
Not sure I buy that argument; we really only try to make a similar index
on the new column. An example is that you can use ALTER COLUMN TYPE
to change a text column to timestamp, and if there's an index on the
column it just gets replaced by one using timestamp_ops, never mind that
the sort order and equality rules will be substantially different.
> In the case of
> views, the problem has more to do with potential POLA violations.
Indeed, but that's true for indexes as well.
> ... What the user actually wants, I
> think, is for the type of the view column to change from varchar(20)
> to varchar(40) when the underlying table is altered. That, however,
> seems like a Pandora's box.
Well, for one thing it would require recursive updates of indirectly
dependent views. I think that's perfectly do-able, if you have something
that does what you want on the directly dependent view in the first place.
But it certainly raises the stakes in terms of the amount of damage an
ill-considered ALTER could do.
In any case, it would be a good idea to try to sketch out a spec for
the behavior you want before any code gets written.
regards, tom lane