Re: Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
От | Robert Haas |
---|---|
Тема | Re: Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE? |
Дата | |
Msg-id | CA+TgmoaxCWscityNPFx2qFP1HSjdL8y+=2XS0HiDa417pK-Qsw@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE? (Stephen Frost <sfrost@snowman.net>) |
Ответы |
Re: Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Re: Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE? |
Список | pgsql-hackers |
On Mon, May 26, 2014 at 10:39 AM, Stephen Frost <sfrost@snowman.net> wrote: > * ash (ash@commandprompt.com) wrote: >> This came up recently on general list (and I've just hit the same issue today): >> http://www.postgresql.org/message-id/CAB7nPqTLmMn1LTb5WE0v0dO57iP0U73yKwzbZytAXDF1CAWLZg@mail.gmail.com >> >> Why couldn't postgres re-create the dependent views automatically? I >> mean it knows which views depend on the altered column and keeps the >> view definition, no? > > Might be pretty complicated in the end.. > >> Would a patch likely to be accepted? How hard do you feel this might be >> to implement? Any caveat that defeats the purpose of such feature? > > It'd need to be explicitly requested, eg a 'CASCADE' option. Why? Would any sane person NOT want this behavior? I think the question here is whether there's any way to make this work at all, not whether we'd want it if we could get it. Consider: CREATE OR REPLACE VIEW v AS SELECT a + 0 FROM t; If we drop the view, change the column type of t.a, and re-execute the view, + might resolve to a different operator than before (or no operator, at all). Furthermore, the operator to which it resolves will depend on the search path at the time the CREATE OR REPLACE VIEW command is executed. Now, consider the situation in which we want to achieve the same result without having to drop and recreate v. When the column type of t.a is changed, we can use the dependencies to figure out that v might be impacted. We can trace through the rewrite rule to find out where column t.a is referenced. And ... then what? All we know about t.a is that we're applying some operator to it, which is specified by OID.The rewrite rule doesn't tell us the actual *name*by which the operator was referenced in the original view text, nor does it tell us the search path that was in effect at that time. If it did, we could pick the same operator for + that would have been used had t.a been of the new type originally, but as it is, we can't. Now maybe there are options other than trying to reproduce what the original CREATE OR REPLACE statement would have done against the new type. For example, we could look through views that depend on t.a and rewrite each reference to that column to t.a::oldtype. This might lead to odd results with multiple nested casts and generally funny behavior if the column is re-typed multiple times; but maybe there's some way to fix that. Also, it might not really be the semantics you want if you were hoping the type update would truly cascade. But it might still be better than a sharp stick in the eye, which is kinda what we offer today. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
В списке pgsql-hackers по дате отправления:
Следующее
От: Peter EisentrautДата:
Сообщение: Re: [PATCH] Replacement for OSSP-UUID for Linux and BSD