Re: Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?

Hi Hackers,

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?

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?

Thanks.
--
Alex

ash,

* ash (ash(at)commandprompt(dot)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.

Thanks,

Stephen

On Mon, May 26, 2014 at 06:25:09PM +0400, ash wrote:
> Hi Hackers,
>
> 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?

Also worth considering: functions which take any part of the view as a
parameter.

Not, IMHO, worth considering, are functions like this:

CREATE OR REPLACE FUNCTION make_task_impossible_for_alex()
RETURNS int8
LANGUAGE plpgsql
AS $$
DECLARE
foo text[] := ARRAY['list','of','views','here'];
BEGIN
EXECUTE 'SELECT COUNT(*) FROM %', foo[floor(random()*array_upper(foo,1))];
END;
$$;

That counts pretty strictly as pilot error, not least because it makes
things like you want to write not just hard, but impossible.

> Would a patch likely to be accepted? How hard do you feel this might be
> to implement?

In the general case, impossible. In most sane cases, mostly a matter
of chasing down dependencies, which is harder than it first appears,
as anyone who's worked on that part of pg_dump can tell you.

> Any caveat

Locking. Given that, you'd want this behavior only with CASCADE, per
Stephen's response.

> that defeats the purpose of such feature?

Probably not. I'd certainly like to have the feature :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david(at)fetter(dot)org> writes:

> On Mon, May 26, 2014 at 06:25:09PM +0400, ash wrote:
>> Hi Hackers,
>>
>> 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?
>
> Also worth considering: functions which take any part of the view as a
> parameter.

Sorry, I don't get it: do you suggest we should re-create dependent
functions too?

I don't think that's feasible, but there is certainly a use case for
silently re-defining the views together with alteration of the joined
table.

--
Alex

On Tue, May 27, 2014 at 12:37:32AM +0400, ash wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > On Mon, May 26, 2014 at 06:25:09PM +0400, ash wrote:
> >> Hi Hackers,
> >>
> >> 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?
> >
> > Also worth considering: functions which take any part of the view
> > as a parameter.
>
> Sorry, I don't get it: do you suggest we should re-create dependent
> functions too?

I'd throw an error in cases where such functions had an obvious and
deterministic dependency on the views, ideally having gone through all
such functions first and enumerated them in the error message.

> I don't think that's feasible, but there is certainly a use case for
> silently re-defining the views together with alteration of the joined
> table.

Indeed.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david(at)fetter(dot)org> writes:
>> >
>> > Also worth considering: functions which take any part of the view
>> > as a parameter.
>>
>> Sorry, I don't get it: do you suggest we should re-create dependent
>> functions too?
>
> I'd throw an error in cases where such functions had an obvious and
> deterministic dependency on the views, ideally having gone through all
> such functions first and enumerated them in the error message.

Then it would also make sense to start with checking function dependency
on the tables themselves, not only the joining views:

psql=> CREATE TABLE t(id INT);
CREATE TABLE
psql=> CREATE FUNCTION func1() RETURNS SETOF INT AS $$ SELECT id FROM t; $$ LANGUAGE SQL;
CREATE FUNCTION
psql=> ALTER TABLE t ALTER COLUMN id TYPE BIGINT;
ALTER TABLE
-- Would complain on func1 right away

psql=> SELECT func1();
ERROR: return type mismatch in function declared to return integer
DETAIL: Actual return type is bigint.
CONTEXT: SQL function "func1" during startup

psql=> CREATE FUNCTION func2() RETURNS SETOF INT AS $$ SELECT id FROM t; $$ LANGUAGE SQL;
ERROR: return type mismatch in function declared to return integer
DETAIL: Actual return type is bigint.
CONTEXT: SQL function "func2"

--
Alex

On Mon, May 26, 2014 at 10:39 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * ash (ash(at)commandprompt(dot)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

Alexander Shulgin wrote
> Hi Hackers,
>
> This came up recently on general list (and I've just hit the same issue
> today):
> http://www.postgresql.org/message-id/

> CAB7nPqTLmMn1LTb5WE0v0dO57iP0U73yKwzbZytAXDF1CAWLZg(at)(dot)gmail

>
> 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?
>
> 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?
>
> Thanks.

Would it be possible to handle the specific case of varchar(n) to
varchar/text by just ignoring the error?

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Re-create-dependent-views-on-ALTER-TABLE-ALTER-COLUMN-TYPE-tp5804972p5805191.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>
>> 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.

This could be a showstopper indeed. We can look up view def in pg_views
view, but it doesn't include any schema references unless they were
explicit in the CREATE VIEW statement.

On the other hand, pg_dump *can* work around this: if you dump a view
that has been defined when a specific search_path was in effect, you'll
get correct definition in the schema dump.

So why can't we try to learn from pg_dump?

> 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.

No, casting back to oldtype totally defeats the purpose, at least for my
usecase.

--
Alex

David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
>
> Would it be possible to handle the specific case of varchar(n) to
> varchar/text by just ignoring the error?

Simply for the reference, my case is INT to BIGINT.

--
Alex

On Tue, May 27, 2014 at 11:20 PM, ash <ash(at)commandprompt(dot)com> wrote:
>> 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.
>
> This could be a showstopper indeed. We can look up view def in pg_views
> view, but it doesn't include any schema references unless they were
> explicit in the CREATE VIEW statement.
>
> On the other hand, pg_dump *can* work around this: if you dump a view
> that has been defined when a specific search_path was in effect, you'll
> get correct definition in the schema dump.
>
> So why can't we try to learn from pg_dump?

Well, pg_dump is trying to do something different than what you're
trying to do here. pg_dump wants to make sure that the view, when fed
back into psql, creates the same view that exists now, regardless of
whether that's what the user created originally. For example, if a
view is created referring to table foo, and table foo is later renamed
to bar, then pg_dump wants to (and does) dump a statement referring to
bar, not foo - even if there's a *new* table called foo against which
the view could have been defined. Similarly, pg_dump will
schema-qualify functions and operators, or not, based on whether
that's necessary to reference the exact same operators that were
selected when the original CREATE VIEW command was run, regardless of
whether the original references were schema-qualified. None of that
involves answering hypothetical questions; but what you want to do
does, and that I think is the problem in a nutshell.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Mon, May 26, 2014 at 10:39 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > It'd need to be explicitly requested, eg a 'CASCADE' option.
>
> Why? Would any sane person NOT want this behavior?

[...]

> 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.

This.

> 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.

I hadn't even considered the idea that we would go through and try to
change everything which referenced that view to now be the new type- but
in that case, I'd want to know that there were other changes which were
happening beyond the single view which I was updating. Perhaps a NOTICE
would be enough, but it doesn't feel correct to me. Also consider
MatViews which would need to be rewritten for the new type, or pl/pgsql
functions which we couldn't possibly fix entirely (we're going to change
the variable's type definition because it selects out a column from this
view?) and so they'd just break instead.

Thanks,

Stephen

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>
> Well, pg_dump is trying to do something different than what you're
> trying to do here. pg_dump wants to make sure that the view, when fed
> back into psql, creates the same view that exists now, regardless of
> whether that's what the user created originally. For example, if a
> view is created referring to table foo, and table foo is later renamed
> to bar, then pg_dump wants to (and does) dump a statement referring to
> bar, not foo - even if there's a *new* table called foo against which
> the view could have been defined. Similarly, pg_dump will
> schema-qualify functions and operators, or not, based on whether
> that's necessary to reference the exact same operators that were
> selected when the original CREATE VIEW command was run, regardless of
> whether the original references were schema-qualified.

Sorry, I don't see how any of the above is a problem in my use case.
Should a table has been renamed, naturally we want to re-create the view
referring to the *old* table, but under its *new name*. The same goes
with schema-qualifying objects.

> None of that involves answering hypothetical questions; but what you
> want to do does, and that I think is the problem in a nutshell.

In a nutshell I'd like PostgreSQL to just re-parse the *current* view
definition. Should that throw an error, user intervention will be
required anyway, but most of the time it should just work.

--
Alex

Stephen Frost <sfrost(at)snowman(dot)net> writes:
>
> I hadn't even considered the idea that we would go through and try to
> change everything which referenced that view to now be the new type- but
> in that case, I'd want to know that there were other changes which were
> happening beyond the single view which I was updating. Perhaps a NOTICE
> would be enough, but it doesn't feel correct to me.

> Also consider MatViews which would need to be rewritten for the new
> type

That might be costly but not impossible. A user would need to do that
anyway, though manually.

> or pl/pgsql functions which we couldn't possibly fix entirely
> (we're going to change the variable's type definition because it
> selects out a column from this view?) and so they'd just break
> instead.

I'm not suggesting that we try to *fix* functions, but if we can detect
function breakage by re-parsing them it would be nice to alert the user
of any problems at the instant of running ALTER TABLE statement, not
when the user tries to actually run the function (see my mail upthread
for sample scenario.)

--
Alex

* ash (ash(at)commandprompt(dot)com) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > I hadn't even considered the idea that we would go through and try to
> > change everything which referenced that view to now be the new type- but
> > in that case, I'd want to know that there were other changes which were
> > happening beyond the single view which I was updating. Perhaps a NOTICE
> > would be enough, but it doesn't feel correct to me.
>
> > Also consider MatViews which would need to be rewritten for the new
> > type
>
> That might be costly but not impossible. A user would need to do that
> anyway, though manually.

I was pointing out why it should need to be explicitly requested, but
all-in-all, I don't really see this proposal going anywhere. It's a
neat idea, and if there's a sensible way to do it, then the user should
have to explicitly request it, imv.

> > or pl/pgsql functions which we couldn't possibly fix entirely
> > (we're going to change the variable's type definition because it
> > selects out a column from this view?) and so they'd just break
> > instead.
>
> I'm not suggesting that we try to *fix* functions, but if we can detect
> function breakage by re-parsing them it would be nice to alert the user
> of any problems at the instant of running ALTER TABLE statement, not
> when the user tries to actually run the function (see my mail upthread
> for sample scenario.)

We're not going to re-parse every function in the system, like, ever. I
might be willing to buy off on "too bad" in those cases (it's not like
we're going and fixing them today for ALTER TABLE .. TYPE cases anyway)
but the point is that cascading the change to a column's type through
all of its dependencies is likely to cause breakage somewhere in even
modestly complex systems and we shouldn't just start doing that
automatically.

Thanks,

Stephen

Stephen Frost <sfrost(at)snowman(dot)net> writes:

> * ash (ash(at)commandprompt(dot)com) wrote:
>> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>>
>> > Also consider MatViews which would need to be rewritten for the new
>> > type
>>
>> That might be costly but not impossible. A user would need to do that
>> anyway, though manually.
>
> I was pointing out why it should need to be explicitly requested, but
> all-in-all, I don't really see this proposal going anywhere. It's a
> neat idea, and if there's a sensible way to do it, then the user should
> have to explicitly request it, imv.

Ah, understood.

>> > or pl/pgsql functions which we couldn't possibly fix entirely
>> > (we're going to change the variable's type definition because it
>> > selects out a column from this view?) and so they'd just break
>> > instead.
>>
>> I'm not suggesting that we try to *fix* functions, but if we can detect
>> function breakage by re-parsing them it would be nice to alert the user
>> of any problems at the instant of running ALTER TABLE statement, not
>> when the user tries to actually run the function (see my mail upthread
>> for sample scenario.)
>
> We're not going to re-parse every function in the system, like, ever.

Well, only every *affected* function, which might be pretty minimal in
the usual case.

> I might be willing to buy off on "too bad" in those cases (it's not
> like we're going and fixing them today for ALTER TABLE .. TYPE cases
> anyway) but the point is that cascading the change to a column's type
> through all of its dependencies is likely to cause breakage somewhere
> in even modestly complex systems and we shouldn't just start doing
> that automatically.

What I am suggesting is that we try to detect such breakage at the time
the user runs ALTER TABLE (issuing NOTICE or ERROR at user discretion.)
If changing column type of a table breaks some functions down the way,
the user will hit it anyway, but better know it soon than when he wants
to *run* that function.

--
Alex

* ash (ash(at)commandprompt(dot)com) wrote:
> What I am suggesting is that we try to detect such breakage at the time
> the user runs ALTER TABLE (issuing NOTICE or ERROR at user discretion.)
> If changing column type of a table breaks some functions down the way,
> the user will hit it anyway, but better know it soon than when he wants
> to *run* that function.

Sure, if we had the information about what would break then we could
tell the user about it. We don't and that's not likely to ever
change...

Thanks,

Stephen

ash <ash(at)commandprompt(dot)com> writes:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> We're not going to re-parse every function in the system, like, ever.

> Well, only every *affected* function, which might be pretty minimal in
> the usual case.

We don't store dependency information for function bodies, so there's
no way to do this except by reparsing everything in sight.

A larger issue with the idea is that a function might fail reparsing
for reasons having nothing to do with the proposed ALTER TABLE.
For instance, it's not at all unusual for functions to contain references
to tables that don't normally exist, but are created when the function is
to be called (or maybe even by the function itself). Because of this
problem, "reparsing", in the sense of detecting semantic rather than
purely syntactic problems in a function body, is something that we don't
actually do *at all*, ever, except when the function is actually executed.
(This is part of the reason why there's no dependency info.)
Pavel Stehule has made some efforts towards improving that situation
for plpgsql functions:
https://commitfest.postgresql.org/action/patch_view?id=884
but that patch remains pretty controversial and may never get committed.
Even if it does get in, it wouldn't move the goalposts for any other PL.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> We don't store dependency information for function bodies, so there's
> no way to do this except by reparsing everything in sight.
>
> A larger issue with the idea is that a function might fail reparsing
> for reasons having nothing to do with the proposed ALTER TABLE.
> For instance, it's not at all unusual for functions to contain references
> to tables that don't normally exist, but are created when the function is
> to be called (or maybe even by the function itself). Because of this
> problem, "reparsing", in the sense of detecting semantic rather than
> purely syntactic problems in a function body, is something that we don't
> actually do *at all*, ever, except when the function is actually executed.
> (This is part of the reason why there's no dependency info.)
> Pavel Stehule has made some efforts towards improving that situation
> for plpgsql functions:
> https://commitfest.postgresql.org/action/patch_view?id=884
> but that patch remains pretty controversial and may never get committed.
> Even if it does get in, it wouldn't move the goalposts for any other PL.

OK, forget functions, I now realize it's not feasible to consider.

Can we get back to re-defining views at least?

--
Alex

ash wrote:
>
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> >
> > We don't store dependency information for function bodies, so there's
> > no way to do this except by reparsing everything in sight.

> OK, forget functions, I now realize it's not feasible to consider.
>
> Can we get back to re-defining views at least?

Hi Alex,

I think it's reasonable to try and fix the problems for views (and other
objects -- there are other things that can depend on table definitions;
composite types come to mind) and ignore functions bodies, since you can
already get into trouble by using ALTER TABLE today and it's known to be
an unsolvable problem.

Now -- do we need to do anything about tables used as return types or
argument types for functions?

alvherre=# create table qux (a int, b text);
CREATE TABLE
alvherre=# create or replace function test_qux(a qux) returns void language plpgsql as $$ begin raise notice 'the qux we got is %', $1; end; $$;
CREATE FUNCTION
alvherre=# insert into qux values (1, 'one');
INSERT 0 1
alvherre=# select * from qux, test_qux(qux.*);
NOTICE: the qux we got is (1,one)
a | b | test_qux
---+-----+----------
1 | one |
(1 fila)

alvherre=# alter table qux add column c timestamptz;
ALTER TABLE
alvherre=# update qux set c = now();
UPDATE 1
alvherre=# select * from qux, test_qux(qux.*);
NOTICE: the qux we got is (1,one,)
a | b | c | test_qux
---+-----+-------------------------------+----------
1 | one | 2014-05-28 12:08:28.210895-04 |
(1 fila)

Notice how the NOTICE has a final comma, meaning the tuple descriptor is
aware that there is a third column -- but the value in the table is not
null per the UPDATE, so the fact that there's nothing after the comma
means this is not being handled correctly. If I close the session and
start a fresh one, the result is saner:

alvherre=# select * from qux, test_qux(qux.*);
NOTICE: the qux we got is (1,one,"2014-05-28 12:08:28.210895-04")
a | b | c | test_qux
---+-----+-------------------------------+----------
1 | one | 2014-05-28 12:08:28.210895-04 |
(1 fila)

Maybe we're missing a function cache invalidation or something like
that.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

* ash (ash(at)commandprompt(dot)com) wrote:
> OK, forget functions, I now realize it's not feasible to consider.

I never meant to imply that it was but rather to point out that we might
have users who actually want to get an error when they're changing a
type definition which goes beyond the scope of the explicit action (and
therefore could very well have more side-effects than they realize),
rather than just doing it for them.

> Can we get back to re-defining views at least?

I'm still not convinced you'll be able to do it in a sensible and
reliable way, but you're certainly welcome to continue exploring.

Thanks,

Stephen

On Wed, May 28, 2014 at 8:22 AM, ash <ash(at)commandprompt(dot)com> wrote:
>> None of that involves answering hypothetical questions; but what you
>> want to do does, and that I think is the problem in a nutshell.
>
> In a nutshell I'd like PostgreSQL to just re-parse the *current* view
> definition. Should that throw an error, user intervention will be
> required anyway, but most of the time it should just work.

What exactly do you mean by "re-parse the current view definition"?
The only form of the view definition we actually have is already
parsed into an internal form (see pg_rewrite) which, for the reasons
I've attempted to explain, is not easy to adapt to new column types.
I suppose we could deparse that definition and then reparse the
results, but that could lead to some very surprising consequences
(some of which are security-relevant).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:

> On Wed, May 28, 2014 at 8:22 AM, ash <ash(at)commandprompt(dot)com> wrote:
>>> None of that involves answering hypothetical questions; but what you
>>> want to do does, and that I think is the problem in a nutshell.
>>
>> In a nutshell I'd like PostgreSQL to just re-parse the *current* view
>> definition. Should that throw an error, user intervention will be
>> required anyway, but most of the time it should just work.
>
> What exactly do you mean by "re-parse the current view definition"?

I mean do what the user will have to do in this situation anyway:

BEGIN;
DROP VIEW ...;
ALTER TABLE ...;
CREATE VIEW ...;
COMMIT;

Should this fail, the user will have to work around it, but most of the
time it could just work.

> The only form of the view definition we actually have is already
> parsed into an internal form (see pg_rewrite) which, for the reasons
> I've attempted to explain, is not easy to adapt to new column types.
> I suppose we could deparse that definition and then reparse the
> results, but that could lead to some very surprising consequences
> (some of which are security-relevant).

Like?

--
Alex

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> What exactly do you mean by "re-parse the current view definition"?
> The only form of the view definition we actually have is already
> parsed into an internal form (see pg_rewrite) which, for the reasons
> I've attempted to explain, is not easy to adapt to new column types.
> I suppose we could deparse that definition and then reparse the
> results, but that could lead to some very surprising consequences
> (some of which are security-relevant).

Note that even if we had the original CREATE VIEW text, as well as
the creation-time settings of all relevant GUCs (search_path being
the most obvious, but not the only one), reparsing like this could
still be risky; it's not the conversion to internal form that's the
real issue here.

A simple example is that
x || 'foo'
means something quite different if x is a tsvector than if x is text,
and something different from those if x is an array, and something
different yet again if x is bit varying. Some of those meanings
are close enough that the user might have wanted the substitution,
but others perhaps not.

Or for another example, if we have foo(x) calling foo(int),
and x's type is changed from int4 to int8, should we insert a
cast so that the same foo() is still called? Or should we allow
the called function to be replaced by foo(int8), foo(numeric),
foo(float8), if one of those exist?

I see the OP's point that the user is likely to try a naive manual
conversion first, but at least then it's on his head to be aware
of whether he got the semantics he wants.

regards, tom lane

On Mon, Jun 2, 2014 at 8:52 AM, ash <ash(at)commandprompt(dot)com> wrote:
>> On Wed, May 28, 2014 at 8:22 AM, ash <ash(at)commandprompt(dot)com> wrote:
>>>> None of that involves answering hypothetical questions; but what you
>>>> want to do does, and that I think is the problem in a nutshell.
>>>
>>> In a nutshell I'd like PostgreSQL to just re-parse the *current* view
>>> definition. Should that throw an error, user intervention will be
>>> required anyway, but most of the time it should just work.
>>
>> What exactly do you mean by "re-parse the current view definition"?
>
> I mean do what the user will have to do in this situation anyway:
>
> BEGIN;
> DROP VIEW ...;
> ALTER TABLE ...;
> CREATE VIEW ...;
> COMMIT;
>
> Should this fail, the user will have to work around it, but most of the
> time it could just work.

You're either missing or choosing to ignore the point that I'm making,
which is that we *don't have* the text form of the view anywhere. If
you try to get implement what you're proposing, I'm fairly certain
that you'll find that you can't. I agree that it would be nice if
there were to make that just work; I've wished for it myself - but I
don't see a reasonable way to implement it.

>> The only form of the view definition we actually have is already
>> parsed into an internal form (see pg_rewrite) which, for the reasons
>> I've attempted to explain, is not easy to adapt to new column types.
>> I suppose we could deparse that definition and then reparse the
>> results, but that could lead to some very surprising consequences
>> (some of which are security-relevant).
>
> Like?

Tom's email covers this point, so I won't repeat what he said.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Jun 2, 2014 at 8:52 AM, ash <ash(at)commandprompt(dot)com> wrote:
>> Should this fail, the user will have to work around it, but most of the
>> time it could just work.

> You're either missing or choosing to ignore the point that I'm making,
> which is that we *don't have* the text form of the view anywhere.

Even if we did, I don't think it'd affect this decision.

The real problem in my mind is one of user expectations. If the database
silently does something behind your back, people expect that that action
will be *right* and they don't have to worry about it. I don't think
that automatically reparsing views has much chance of clearing that bar.
In much simpler, non-extensible SQL systems it could probably work, but
for better or worse Postgres has gone all-in on datatype extensibility.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, Jun 2, 2014 at 8:52 AM, ash <ash(at)commandprompt(dot)com> wrote:
>>> Should this fail, the user will have to work around it, but most of the
>>> time it could just work.
>
>> You're either missing or choosing to ignore the point that I'm making,
>> which is that we *don't have* the text form of the view anywhere.
>
> Even if we did, I don't think it'd affect this decision.
>
> The real problem in my mind is one of user expectations. If the database
> silently does something behind your back, people expect that that action
> will be *right* and they don't have to worry about it. I don't think
> that automatically reparsing views has much chance of clearing that bar.
> In much simpler, non-extensible SQL systems it could probably work, but
> for better or worse Postgres has gone all-in on datatype extensibility.

Alright, I think I can let it go now. It's just that the behavior was
very counter-intuitive to me (and I guess a lot others) at first.

Thanks all for your time and in-depth explanation!

--
Alex

On Mon, Jun 2, 2014 at 10:21 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, Jun 2, 2014 at 8:52 AM, ash <ash(at)commandprompt(dot)com> wrote:
>>> Should this fail, the user will have to work around it, but most of the
>>> time it could just work.
>
>> You're either missing or choosing to ignore the point that I'm making,
>> which is that we *don't have* the text form of the view anywhere.
>
> Even if we did, I don't think it'd affect this decision.
>
> The real problem in my mind is one of user expectations. If the database
> silently does something behind your back, people expect that that action
> will be *right* and they don't have to worry about it. I don't think
> that automatically reparsing views has much chance of clearing that bar.

I agree, but I think it's important to note that Alex's complaint is
not unique - the way things work now is a real source of frustration
for users. In a previous job, I wrote a schema-upgrade script that
dropped all of the views in reverse creation order, applied the schema
updates, and then recreated all the views. This worked, but it was a
lot of hassle that I would have preferred to avoid, and in a
higher-volume application, simultaneously grabbing exclusive locks on
a large number of critical views would have been a non-starter. In
the job before that, I did the same thing manually, which was no fun
at all. This was actually what posted me to write one of my first
patches, committed by Bruce as
ff1ea2173a92dea975d399a4ca25723f83762e55.

From a technical standpoint, I'm not very sure what to do to further
improve the situation - which I will broadly characterize as "view
dependency hell" - but if I did have such an idea I might be willing
to take a modest risk of user confusion if it seemed likely to also
reduce user frustration.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Jun 2, 2014 at 10:21 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> The real problem in my mind is one of user expectations. If the database
>> silently does something behind your back, people expect that that action
>> will be *right* and they don't have to worry about it. I don't think
>> that automatically reparsing views has much chance of clearing that bar.

> I agree, but I think it's important to note that Alex's complaint is
> not unique - the way things work now is a real source of frustration
> for users.

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.

regards, tom lane

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Mon, Jun 2, 2014 at 10:21 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > The real problem in my mind is one of user expectations. If the database
> > silently does something behind your back, people expect that that action
> > will be *right* and they don't have to worry about it. I don't think
> > that automatically reparsing views has much chance of clearing that bar.
[...]
> >From a technical standpoint, I'm not very sure what to do to further
> improve the situation - which I will broadly characterize as "view
> dependency hell" - but if I did have such an idea I might be willing
> to take a modest risk of user confusion if it seemed likely to also
> reduce user frustration.

Tom's point goes back to what I was trying to drive at originally-
people should have to ask for this. Perhaps we can provide a way for
them to ask which is explicit enough that they understand "this might
not do exactly what you think it does", akin to what happens today with
a drop-and-recreate-everything approach. 'CASCADE' might not be
sufficient to meet that, maybe 'CASCADE REBUILD' or something?

Of course, there is a question about if it's worth it to keep around the
exact text of each CREATE VIEW and build all this infrastructure for
something which will only work properly in a specific subset of cases
and in many others could break silently, essentially installing a very
handy looking foot-gun. Not sure I like that either.

Thanks,

Stephen

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > I agree, but I think it's important to note that Alex's complaint is
> > not unique - the way things work now is a real source of frustration
> > for users.
>
> 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.

No clue how we'd manage to do that with just core, but could we provide
something which would make it easier for pgAdmin-or-similar to do that?

We could at least spit out NOTICE's and then see if the user decides to
commit the change but I'm not sure how well that'd really work.

In general, I like the idea..

Thanks,

Stephen

On 06/02/2014 10:40 AM, Stephen Frost wrote:
> Tom's point goes back to what I was trying to drive at originally-
> people should have to ask for this. Perhaps we can provide a way for
> them to ask which is explicit enough that they understand "this might
> not do exactly what you think it does", akin to what happens today with
> a drop-and-recreate-everything approach. 'CASCADE' might not be
> sufficient to meet that, maybe 'CASCADE REBUILD' or something?

I think CASCADE is sufficient; what else could a user mean by ALTER
TABLE ... CASCADE?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 2014-06-02 10:48:02 -0700, Josh Berkus wrote:
> On 06/02/2014 10:40 AM, Stephen Frost wrote:
> > Tom's point goes back to what I was trying to drive at originally-
> > people should have to ask for this. Perhaps we can provide a way for
> > them to ask which is explicit enough that they understand "this might
> > not do exactly what you think it does", akin to what happens today with
> > a drop-and-recreate-everything approach. 'CASCADE' might not be
> > sufficient to meet that, maybe 'CASCADE REBUILD' or something?
>
> I think CASCADE is sufficient; what else could a user mean by ALTER
> TABLE ... CASCADE?

"Please also change the table's children".

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2014-06-02 13:40:32 -0400, Stephen Frost wrote:
> Of course, there is a question about if it's worth it to keep around the
> exact text of each CREATE VIEW and build all this infrastructure for
> something which will only work properly in a specific subset of cases
> and in many others could break silently, essentially installing a very
> handy looking foot-gun. Not sure I like that either.

I don't think any solution that requires that is going to be
workable. It'll break in far too many cases that currently are handled
transparently. Starting with RENAME, followed by lots of other
stuff. It'd also be very confusing because pg_dump doesn't use it...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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.

If you have a complex maze of views, I think this (or something similar
that gives enough leeway to the user) is the only way to enable a
nontrivial alteration of one of the tables or views at the bottom.
There is no way that we're going to allow automatic schema changes
otherwise.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Jun 2, 2014 at 1:40 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>> On Mon, Jun 2, 2014 at 10:21 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> > The real problem in my mind is one of user expectations. If the database
>> > silently does something behind your back, people expect that that action
>> > will be *right* and they don't have to worry about it. I don't think
>> > that automatically reparsing views has much chance of clearing that bar.
> [...]
>> >From a technical standpoint, I'm not very sure what to do to further
>> improve the situation - which I will broadly characterize as "view
>> dependency hell" - but if I did have such an idea I might be willing
>> to take a modest risk of user confusion if it seemed likely to also
>> reduce user frustration.
>
> Tom's point goes back to what I was trying to drive at originally-
> people should have to ask for this.

Well, my point is that we don't yet know what "this" is, so trying to
decide on whether users should be forced to request it or in exactly
what manner seems like putting the cart before the horse. We may well
need some syntax, but it's too soon to decide what it is.

FWIW, I don't think reparsing the original view-text is even remotely
plausible. The fact that views stay glued to the same objects even of
those objects are renamed is a pretty handy property of the current
system, and any sort of reparse-from-scratch technique would give that
up. Deparse-and-reparse might be better, but I'll bet that has too
many problems to be viable, too (even if I haven't yet thought of what
they are). For better or for worse, I think the best we're likely to
be able to do is somehow manipulate the already-parsed rewrite rule.
I don't have any great ideas about how to do that, either, but it
seems less problematic than going back to the SQL representation.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Alvaro Herrera <alvherre(at)2ndquadrant(dot)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 ...;
create or 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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> FWIW, I don't think reparsing the original view-text is even remotely
> plausible. The fact that views stay glued to the same objects even of
> those objects are renamed is a pretty handy property of the current
> system, and any sort of reparse-from-scratch technique would give that
> up.

Agreed.

> Deparse-and-reparse might be better, but I'll bet that has too
> many problems to be viable, too (even if I haven't yet thought of what
> they are). For better or for worse, I think the best we're likely to
> be able to do is somehow manipulate the already-parsed rewrite rule.
> I don't have any great ideas about how to do that, either, but it
> seems less problematic than going back to the SQL representation.

I think deparse-and-reparse is exactly what we have to do, mainly because,
if you subscribe to the idea that the user should see and approve semantic
changes, what else are we going to show her except SQL? If she wants to
adjust the changes, it's even less plausible that the working
representation is not SQL text. We might well produce the initial draft
form by manipulating the parsed querytree before deparsing, though.

regards, tom lane

On Mon, Jun 2, 2014 at 2:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Deparse-and-reparse might be better, but I'll bet that has too
>> many problems to be viable, too (even if I haven't yet thought of what
>> they are). For better or for worse, I think the best we're likely to
>> be able to do is somehow manipulate the already-parsed rewrite rule.
>> I don't have any great ideas about how to do that, either, but it
>> seems less problematic than going back to the SQL representation.
>
> I think deparse-and-reparse is exactly what we have to do, mainly because,
> if you subscribe to the idea that the user should see and approve semantic
> changes, what else are we going to show her except SQL? If she wants to
> adjust the changes, it's even less plausible that the working
> representation is not SQL text. We might well produce the initial draft
> form by manipulating the parsed querytree before deparsing, though.

So I think the scenario we're talking about, simplified down to
basics, is something like this:

CREATE TABLE foo (a int);
CREATE VIEW bar AS SELECT a FROM foo;
ALTER TABLE foo ALTER COLUMN a SET DATA TYPE bigint;

If we wanted to make that last statement succeed instead of failing,
what would we want it to do? I can see two answers. Answer #1 is
that the column type of bar.a changes from int to bigint and the view
definition is still SELECT a FROM foo. In that case, showing the user
the SQL does not help them see and approve semantic changes because
the SQL is completely unchanged. Answer #2 is that the column type of
bar.a remains int4 and therefore the view definition mutates to
something like SELECT a::int4 AS a FROM foo. In that case, showing
the user the SQL does help the user understand what is happening ...
but, as you say, you'd probably generate the new parse tree by
manipulating the existing stored rule. And if you then deparsed it,
how would that help? It's not like you can dump out the revised view
definition and let the user edit it and put it back in. The view has
to get modified as part of the same action as changing the table's
column type, or you can't do anything we can't do already. Frankly, I
don't think showing that particular thing to the user is necessary
anyway; it's not like the semantics of pushing a cast on top of every
use of the column within a related view are particularly hard to
understand. And, anyway, whatever we do here has to be simple to
invoke or we lose most of the advantage.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Jun 2, 2014 at 2:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I think deparse-and-reparse is exactly what we have to do, mainly because,
>> if you subscribe to the idea that the user should see and approve semantic
>> changes, what else are we going to show her except SQL? If she wants to
>> adjust the changes, it's even less plausible that the working
>> representation is not SQL text. We might well produce the initial draft
>> form by manipulating the parsed querytree before deparsing, though.

> So I think the scenario we're talking about, simplified down to
> basics, is something like this:

> CREATE TABLE foo (a int);
> CREATE VIEW bar AS SELECT a FROM foo;
> ALTER TABLE foo ALTER COLUMN a SET DATA TYPE bigint;

> If we wanted to make that last statement succeed instead of failing,
> what would we want it to do?

My argument is that that command sequence, if issued exactly like that,
SHOULD fail. It is not the backend's task to fix this case, and any
smarts you try to put into ALTER TABLE to make it work are certain
to do the wrong thing a distressingly high percentage of the time.
Rather, it should be possible to build a client-side tool that can help
users with such changes.

> I can see two answers. Answer #1 is
> that the column type of bar.a changes from int to bigint and the view
> definition is still SELECT a FROM foo. In that case, showing the user
> the SQL does not help them see and approve semantic changes because
> the SQL is completely unchanged.

Yeah, we need some way of highlighting the semantic differences, and just
printing ruleutils.c output doesn't do that. But if the user is going to
put in a change to whatever choice the tool makes by default here,
I would expect that change to consist of adding (or removing) an explicit
cast in the SQL-text view definition. We can't make people learn some
random non-SQL notation for this.

Perhaps the displayed output of the tool could look something like

CREATE VIEW bar AS
SELECT
a -- this view output column will now be of type int8 not int4
FROM foo;

Or something else; I don't claim to be a good UI designer. But in the
end, this is 90% a UI problem, and that means that raw SQL is seriously
poorly suited to solve it directly.

regards, tom lane

On Mon, Jun 02, 2014 at 01:29:25PM -0400, Robert Haas wrote:
> I agree, but I think it's important to note that Alex's complaint is
> not unique - the way things work now is a real source of frustration
> for users. In a previous job, I wrote a schema-upgrade script that
> dropped all of the views in reverse creation order, applied the schema
> updates, and then recreated all the views. This worked, but it was a
> lot of hassle that I would have preferred to avoid, and in a
> higher-volume application, simultaneously grabbing exclusive locks on
> a large number of critical views would have been a non-starter. In
> the job before that, I did the same thing manually, which was no fun
> at all. This was actually what posted me to write one of my first
> patches, committed by Bruce as
> ff1ea2173a92dea975d399a4ca25723f83762e55.

Would it be sufficient to automatically pass the type change through
only if nothing in the view actually references it in a function,
operator, group by, order by, etc? That is, it only appears in the
SELECT list unadorned? Or is that too limiting?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer

On Mon, Jun 2, 2014 at 10:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I can see two answers. Answer #1 is
>> that the column type of bar.a changes from int to bigint and the view
>> definition is still SELECT a FROM foo. In that case, showing the user
>> the SQL does not help them see and approve semantic changes because
>> the SQL is completely unchanged.
>
> Yeah, we need some way of highlighting the semantic differences, and just
> printing ruleutils.c output doesn't do that. But if the user is going to
> put in a change to whatever choice the tool makes by default here,
> I would expect that change to consist of adding (or removing) an explicit
> cast in the SQL-text view definition. We can't make people learn some
> random non-SQL notation for this.
>
> Perhaps the displayed output of the tool could look something like
>
> CREATE VIEW bar AS
> SELECT
> a -- this view output column will now be of type int8 not int4
> FROM foo;
>
> Or something else; I don't claim to be a good UI designer. But in the
> end, this is 90% a UI problem, and that means that raw SQL is seriously
> poorly suited to solve it directly.

I guess I don't agree that is 90% a UI problem. There's currently no
mechanism whatsoever by means of which a user can change the data type
of a column upon which a view depends. If we had such a mechanism,
then perhaps someone could build a UI providing the sort of user
feedback you're suggesting to help them use it more safely. But isn't
the core server support the first thing?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tuesday, June 3, 2014, Robert Haas [via PostgreSQL] <
ml-node+s1045698n5805857h91(at)n5(dot)nabble(dot)com> wrote:

> On Mon, Jun 2, 2014 at 10:00 PM, Tom Lane <[hidden email]
> <http://user/SendEmail.jtp?type=node&node=5805857&i=0>> wrote:
>
> >> I can see two answers. Answer #1 is
> >> that the column type of bar.a changes from int to bigint and the view
> >> definition is still SELECT a FROM foo. In that case, showing the user
> >> the SQL does not help them see and approve semantic changes because
> >> the SQL is completely unchanged.
> >
> > Yeah, we need some way of highlighting the semantic differences, and
> just
> > printing ruleutils.c output doesn't do that. But if the user is going
> to
> > put in a change to whatever choice the tool makes by default here,
> > I would expect that change to consist of adding (or removing) an
> explicit
> > cast in the SQL-text view definition. We can't make people learn some
> > random non-SQL notation for this.
> >
> > Perhaps the displayed output of the tool could look something like
> >
> > CREATE VIEW bar AS
> > SELECT
> > a -- this view output column will now be of type int8 not
> int4
> > FROM foo;
> >
> > Or something else; I don't claim to be a good UI designer. But in the
> > end, this is 90% a UI problem, and that means that raw SQL is seriously
> > poorly suited to solve it directly.
>
> I guess I don't agree that is 90% a UI problem. There's currently no
> mechanism whatsoever by means of which a user can change the data type
> of a column upon which a view depends. If we had such a mechanism,
> then perhaps someone could build a UI providing the sort of user
> feedback you're suggesting to help them use it more safely. But isn't
> the core server support the first thing?
>
>
The current mechanism is DROP VIEWs -> ALTER TABLE -> CREATE VIEWs

The UI would prompt the user for the desired ALTER TABLE
parameters, calculate the DROP/CREATE commands, then issue all three sets
as a single transaction.

Having a more surgical REWRITE RULE command to alter a view without
dropping it may provide for performance improvements but, conceptually, the
current mechanism should be sufficient to allow for this tool to be
developed.

The main thing that core could do to help is to store as text of the
original create view command - though it may be sufficient to reverse
engineer from the rule. Having both available would give any tools more
options.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Re-create-dependent-views-on-ALTER-TABLE-ALTER-COLUMN-TYPE-tp5804972p5805864.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Jun 2, 2014 at 10:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Or something else; I don't claim to be a good UI designer. But in the
>> end, this is 90% a UI problem, and that means that raw SQL is seriously
>> poorly suited to solve it directly.

> I guess I don't agree that is 90% a UI problem. There's currently no
> mechanism whatsoever by means of which a user can change the data type
> of a column upon which a view depends.

Sure there is: I already illustrated it. You can temporarily set the
view to some dummy definition that doesn't reference the target table,
then do the ALTER COLUMN TYPE, then redefine the view the way you want.
Wrap it up in a transaction, and it's even transparent.

Now, what that doesn't do is let you change the output column type(s)
of the view, but I'd argue that entirely independently of this problem
it'd be reasonable for CREATE OR REPLACE VIEW to allow changing a column
type if the view is unreferenced (ie, basically the same conditions under
which a table column type can be changed today).

If you want to argue that this is unnecessarily complex, you can do so,
but claiming that it's not possible is simply false. I stand by the point
that what we lack is a sane UI for helping in complex cases --- and
nothing done behind-the-scenes in ALTER TABLE is going to qualify as
a sane UI. The complexity in this approach would be easily hidden in
a support tool, which will have much bigger problems to solve than whether
its eventual command to the backend requires multiple SQL steps.

> If we had such a mechanism,
> then perhaps someone could build a UI providing the sort of user
> feedback you're suggesting to help them use it more safely. But isn't
> the core server support the first thing?

I'm guessing you did not read
http://www.postgresql.org/message-id/18723.1401734537@sss.pgh.pa.us

regards, tom lane

On Tue, Jun 3, 2014 at 10:14 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> If we had such a mechanism,
>> then perhaps someone could build a UI providing the sort of user
>> feedback you're suggesting to help them use it more safely. But isn't
>> the core server support the first thing?
>
> I'm guessing you did not read
> http://www.postgresql.org/message-id/18723.1401734537@sss.pgh.pa.us

Argh, sorry, I saw that go by and it went past my eyes but obviously I
didn't really absorb it. I guess we could do it that way. But it
seems like quite a hassle to me; I think we're going to continue to
get complaints here until this is Easy. And if it can't be made Easy,
then we're going to continue to get complaints forever.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Jun 3, 2014 at 10:14 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I'm guessing you did not read
>> http://www.postgresql.org/message-id/18723.1401734537@sss.pgh.pa.us

> Argh, sorry, I saw that go by and it went past my eyes but obviously I
> didn't really absorb it. I guess we could do it that way. But it
> seems like quite a hassle to me; I think we're going to continue to
> get complaints here until this is Easy. And if it can't be made Easy,
> then we're going to continue to get complaints forever.

Well, my vision of it is that it *is* easy, if you're using the tool
(or, perhaps, one of several tools), and you have a case that doesn't
really require careful semantic review. But trying to build this sort
of thing into the backend is the wrong approach: it's going to lead
to unpleasant compromises and/or surprises. And we'd still have to
build that tool someday.

regards, tom lane