Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Tom Lane wrote:
>> Oh, I quite agree with that. My concern here has to do with automatically
>> and silently making changes that we can't be very sure will meet the
>> user's expectations. Perhaps what we need is some kind of UI/API design
>> whereby the user can inspect/modify/approve the semantic changes in
>> advance of pushing the red button.
> I think that instead of forcing the user to append a CASCADE keyword at
> the end of the command, it could perhaps return a bunch of commands to
> alter all views. The user would inspect those commands and fix those
> that need fixing, then rerun the whole bunch. I would imagine a UI
> similar to "git rebase", which first gives you a list of things to do,
> which you can edit, and upon save-exit the final list of commands is
> executed. Any error during the execution abort the entire transaction,
> so if the user makes mistakes the thing is started afresh.
I think we might be better off thinking about "what support would we
need to provide to allow a tool for this to be written?" than "how would
we do this entirely inside the backend?". SQL does not deal in user
interfaces very well, and we shouldn't try to make it do so.
I doubt that we need much help from ALTER TABLE itself. I can
envision the tool's "red button" emitting commands like this:
begin; create or replace view v1 as select ... dummy definition ...; create or replace view v2 as select ... dummy
definition...; ... alter table t alter column type ...; create or replace view v1 as select ... new definition ...;
createor replace view v2 as select ... new definition ...; ... commit;
where the "dummy definitions" have no reference to t; they could probably
just be SELECT null::type1 as colname1, null::type2 as colname2, ...
In this way the ALTER TABLE itself would not need any change from current
behavior. (But probably we need to allow CREATE OR REPLACE VIEW to change
the output column types of the view, if it is currently unreferenced.)
We might want to add some capability whereby the transaction could error
out if anyone had changed either the target table or any of the dependent
views since the transaction in which the tool captured their definitions.
But that would be a separate command not part of the ALTER.
What's more interesting is where the tool gets the draft modified view
definitions from, and how it can highlight exactly what the semantic
changes are for the user's benefit. There would likely be value in
adding backend capability for parsing/reverse-listing views against
hypothetical table definitions, but I'm not sure about details.
I concur with Andres' thought that storing original view text doesn't
actually help here. Rather, what we might need is a way to change
what the reverse-lister does with a view. The behavior of ruleutils.c
is pretty well focused on what pg_dump needs, and that may not always
be what we want for this.
regards, tom lane
