Handling view dependencies in ALTER TABLE statements
829 views
Skip to first unread message
Daniel Gustafsson
Sep 5, 2016, 9:57:43 AM9/5/16
to Greenplum Developers
The question of how to handle dependent views during table alteration in
PostgreSQL (and Greenplum) arise every now and again; unfortunately still
without getting closer to a good solution. Now the question arose again and
here are my $0.02 on it.
The problem statement is simple: altering an object which is referenced in a
view is prohibited, the view must be dropped before the alter table statement
is allowed. This in combination with an installation having hundreds or even
thousands of views makes it hard for a DBA to perform requested table changes.
The gist of what we all want is: For the database to automatically re-create
the views which contain references to an attribute which is being altered such
that the view produce the same result.
A naive implementation (which is often suggested) is to simply save the query
text making up the view, and then drop/recreate it on either side of the ALTER
TABLE statement. PostgreSQL doesn’t use, or store, the textual representation
of the view definition but instead work on an object level (this is why rename
on a column which is part of a view works, the object is unchanged). Making an
external app which saves the text in a table is however trivial, the query text
can even be saved as a comment on the view object itself to make it easy to
retrieve it even without involving extra tables:
create view v as select c from t;
comment on view v is $view$ select c from t $view$;
select description from pg_description d
join pg_class c on (d.objoid = c.oid) where c.relname = 'v';
This can then be used in the following sequence to automate the process needed
to allow alter table:
begin;
drop view v;
alter table t alter column c type .. ;
create view v as .. ;
commit;
The allure of this solution is that it works quite often, for a certain value
of "works". The main problem with it is that when it doesn’t work it doesn’t
necessarily fail. See below for an example what can happen:
Lets start with setting up our table and a view which performs operations on
the projected column.
create table tt (a integer, b text) distributed by (a);
create view ttv as select trim(both ((b || 'a')::text), $$'$$) from tt;
insert into tt values (1,'a'), (2, NULL), (3, ‘’);
Querying our view gives us the following result:
# select * from ttv;
btrim
-------
aa
a
(3 rows)
Now let’s say that a team member want to make use of the new cool text search
feature merged as part of 8.3; he/she subsequently alters the type of tt.b to
tsvector (it’s an example, not reality). If we were to apply the “drop and
recreate" solution to this situation it works without even a NOTICE being
raised:
begin;
drop view ttv;
alter table tt alter column b type tsvector using b::tsvector;
create view ttv as select trim(both ((b || 'a')::text), $$'$$) from tt;
commit;
Success, we can now start playing with full text search. If we again query our
view however, the results are however not what we’d expect unless we knew about
the change of the underlying table.
# select * from ttv;
btrim
-------
a
a
(3 rows)
While this example is quite contrived it illustrates the problems which can
(and thus will) arise from this. Since abstraction of base relations is a
selling point for views, breaking it subtly like this is dangerous. In
summary, this can be proven not to work even when it "works”.
If we extend this by trying to trace down the dependencies and update the view
accordingly to solve the problems, we still can’t guarantee correctness. If we
change the view from integer to bigint, perhaps there is a stored procedure
which subtly breaks since it expects a smaller integer range? Another caveat
is if we we find an operator in the view, how to handle “SELECT foo + 0” when
foo changes? The amount of corner cases is increasing the more one pokes at
this.
Another thing to consider is that anything we do should handle the case of
materialized views even though we currently don’t support them. If an alter
table ends up automatically triggering a very expensive matview rewrite we
might be causing harm that the user didn’t intend.
Now, not doing anything is obviously not a very good solution either. What I
propose we do instead is to supply functionality to extract a detailed report
of all the views which will need to be changed for the table alteration. The
owners of the views will then have to supply an updated query which can be used
to recreate the view when running the alter table statement. While this is a
technical problem, the solution is mainly a UX issue.
This is a far cry from the vision statement in the beginning of this email but
it’s at least something we can achieve that we a) know works and b) quickly.
Thoughts? Alternative solutions
cheers ./daniel
PostgreSQL (and Greenplum) arise every now and again; unfortunately still
without getting closer to a good solution. Now the question arose again and
here are my $0.02 on it.
The problem statement is simple: altering an object which is referenced in a
view is prohibited, the view must be dropped before the alter table statement
is allowed. This in combination with an installation having hundreds or even
thousands of views makes it hard for a DBA to perform requested table changes.
The gist of what we all want is: For the database to automatically re-create
the views which contain references to an attribute which is being altered such
that the view produce the same result.
A naive implementation (which is often suggested) is to simply save the query
text making up the view, and then drop/recreate it on either side of the ALTER
TABLE statement. PostgreSQL doesn’t use, or store, the textual representation
of the view definition but instead work on an object level (this is why rename
on a column which is part of a view works, the object is unchanged). Making an
external app which saves the text in a table is however trivial, the query text
can even be saved as a comment on the view object itself to make it easy to
retrieve it even without involving extra tables:
create view v as select c from t;
comment on view v is $view$ select c from t $view$;
select description from pg_description d
join pg_class c on (d.objoid = c.oid) where c.relname = 'v';
This can then be used in the following sequence to automate the process needed
to allow alter table:
begin;
drop view v;
alter table t alter column c type .. ;
create view v as .. ;
commit;
The allure of this solution is that it works quite often, for a certain value
of "works". The main problem with it is that when it doesn’t work it doesn’t
necessarily fail. See below for an example what can happen:
Lets start with setting up our table and a view which performs operations on
the projected column.
create table tt (a integer, b text) distributed by (a);
create view ttv as select trim(both ((b || 'a')::text), $$'$$) from tt;
insert into tt values (1,'a'), (2, NULL), (3, ‘’);
Querying our view gives us the following result:
# select * from ttv;
btrim
-------
aa
a
(3 rows)
Now let’s say that a team member want to make use of the new cool text search
feature merged as part of 8.3; he/she subsequently alters the type of tt.b to
tsvector (it’s an example, not reality). If we were to apply the “drop and
recreate" solution to this situation it works without even a NOTICE being
raised:
begin;
drop view ttv;
alter table tt alter column b type tsvector using b::tsvector;
create view ttv as select trim(both ((b || 'a')::text), $$'$$) from tt;
commit;
Success, we can now start playing with full text search. If we again query our
view however, the results are however not what we’d expect unless we knew about
the change of the underlying table.
# select * from ttv;
btrim
-------
a
a
(3 rows)
While this example is quite contrived it illustrates the problems which can
(and thus will) arise from this. Since abstraction of base relations is a
selling point for views, breaking it subtly like this is dangerous. In
summary, this can be proven not to work even when it "works”.
If we extend this by trying to trace down the dependencies and update the view
accordingly to solve the problems, we still can’t guarantee correctness. If we
change the view from integer to bigint, perhaps there is a stored procedure
which subtly breaks since it expects a smaller integer range? Another caveat
is if we we find an operator in the view, how to handle “SELECT foo + 0” when
foo changes? The amount of corner cases is increasing the more one pokes at
this.
Another thing to consider is that anything we do should handle the case of
materialized views even though we currently don’t support them. If an alter
table ends up automatically triggering a very expensive matview rewrite we
might be causing harm that the user didn’t intend.
Now, not doing anything is obviously not a very good solution either. What I
propose we do instead is to supply functionality to extract a detailed report
of all the views which will need to be changed for the table alteration. The
owners of the views will then have to supply an updated query which can be used
to recreate the view when running the alter table statement. While this is a
technical problem, the solution is mainly a UX issue.
This is a far cry from the vision statement in the beginning of this email but
it’s at least something we can achieve that we a) know works and b) quickly.
Thoughts? Alternative solutions
cheers ./daniel
Robert Eckhardt
Sep 5, 2016, 10:56:27 AM9/5/16
to Daniel Gustafsson, Greenplum Developers
A few questions.
Is the DDL shown fir the view in tools such as pgAdmin an accurate representation of what the SQL to generate the view is/was since it was itself de-parsed from the parsed query rather than being the query itself? In other words, can we provide the end user the actual query or an equivalent?
Would it be possible to also provide a complete dependency list for a table including views, UDFs and any other object I can't think of right now?
What do you propose the end user receives when 1) attempting to query the view, 2) being told they need to upgrade the view?
Rob
Robert Eckhardt
Sep 5, 2016, 11:00:44 AM9/5/16
to Daniel Gustafsson, Greenplum Developers
Was there ever a consensus in Postgres as to how to make this less painful?
Ivan Novick
Sep 5, 2016, 11:30:30 AM9/5/16
to Daniel Gustafsson, Greenplum Developers
Hi Daniel,
A couple thoughts, and definitely plus 1 to make this more easy for our users.
>> The gist of what we all want is: For the database to automatically re-create
>> the views which contain references to an attribute which is being altered such
>> that the view produce the same result.
>> the views which contain references to an attribute which is being altered such
>> that the view produce the same result.
Agreed
>> PostgreSQL doesn’t use, or store, the textual representation
>> of the view definition but instead work on an object level (this is why rename
>> on a column which is part of a view works, the object is unchanged).
Is this the root problem. If PostgreSQL would store the original text submitted by the user (compressed?) in the catalog would that make solving the problem in database seamlessly easier?
Just thinking if this can be worked in multiple parts: 1) Make an argument that for various reasons it would be useful to store the original view text in the catalog. 2) work on a feature to use the original view text. For example invalidate all views that are modified based on a dependcy. Allow users to recompile original view code or update it so it works.
Cheers,
Ivan
--
Daniel Gustafsson
Sep 5, 2016, 11:33:03 AM9/5/16
to Robert Eckhardt, Greenplum Developers
> On 05 Sep 2016, at 17:00, Robert Eckhardt <reck...@pivotal.io> wrote:
>
> Was there ever a consensus in Postgres as to how to make this less painful?
None yet. There has been a lot of discussion on the mailinglist over the years
>
> Was there ever a consensus in Postgres as to how to make this less painful?
but no working solution has been identified.
> On Mon, Sep 5, 2016 at 10:56 AM, Robert Eckhardt <reck...@pivotal.io <mailto:reck...@pivotal.io>> wrote:
> A few questions.
>
> Is the DDL shown fir the view in tools such as pgAdmin an accurate representation of what the SQL to generate the view is/was since it was itself de-parsed from the parsed query rather than being the query itself? In other words, can we provide the end user the actual query or an equivalent?
this representation is that while it’s guaranteed to be functionally equivalent
it’s not per se equivalent. Editing and using this DDL instead of what is in
the user codebase immediately cause the question of which version is the
master.
> Would it be possible to also provide a complete dependency list for a table including views, UDFs and any other object I can't think of right now?
> What do you propose the end user receives when 1) attempting to query the view, 2) being told they need to upgrade the view?
dependencies at the time of altering the underlying the table.
Daniel Gustafsson
Sep 5, 2016, 12:19:55 PM9/5/16
to Ivan Novick, Greenplum Developers
> On 05 Sep 2016, at 17:30, Ivan Novick <ino...@pivotal.io> wrote:
>
> Hi Daniel,
>
> A couple thoughts, and definitely plus 1 to make this more easy for our users.
>
> >> The gist of what we all want is: For the database to automatically re-create
> >> the views which contain references to an attribute which is being altered such
> >> that the view produce the same result.
> Agreed
>
> >> PostgreSQL doesn’t use, or store, the textual representation
> >> of the view definition but instead work on an object level (this is why rename
> >> on a column which is part of a view works, the object is unchanged).
> Is this the root problem. If PostgreSQL would store the original text submitted by the user (compressed?) in the catalog would that make solving the problem in database seamlessly easier?
I would argue that it doesn’t (see below).
>
> Hi Daniel,
>
> A couple thoughts, and definitely plus 1 to make this more easy for our users.
>
> >> The gist of what we all want is: For the database to automatically re-create
> >> the views which contain references to an attribute which is being altered such
> >> that the view produce the same result.
> Agreed
>
> >> PostgreSQL doesn’t use, or store, the textual representation
> >> of the view definition but instead work on an object level (this is why rename
> >> on a column which is part of a view works, the object is unchanged).
> Is this the root problem. If PostgreSQL would store the original text submitted by the user (compressed?) in the catalog would that make solving the problem in database seamlessly easier?
> Just thinking if this can be worked in multiple parts: 1) Make an argument that for various reasons it would be useful to store the original view text in the catalog. 2) work on a feature to use the original view text. For example invalidate all views that are modified based on a dependcy. Allow users to recompile original view code or update it so it works.
This would imply that SQL should be used as an interface for this, something I
doubt will be very user friendly. What I would propose is to create a good API
for working with these dependencies and conflicts instead and rather create a
good external tool using said API. If there are 100 views that have a conflict
with a table change, fixing all these in the space of a transaction in SQL
doesnt sound like a good UX.
A few other issues I see with storing and thus using the view text in the
database (only storing without using is rather pointless):
- Upstream PostgreQSL will likely Not do this causing divergence if we put
it in core
- Having the master code in the database makes development harder since
tooling such as version control and unit testing doesn’t cover it
- What if the view query is stored in a .sql file as well and is edited
there as well as in the database and the edits conflict? Multiple masters
are hard
Dave Cramer
Sep 5, 2016, 1:00:23 PM9/5/16
to Daniel Gustafsson, Ivan Novick, Greenplum Developers
At one point upstream had a TODO for this feature, I'm not sure they wouldn't accept the feature. Now the implementation might be a different story
Daniel Gustafsson
Sep 6, 2016, 1:25:16 AM9/6/16
to Dave Cramer, Ivan Novick, Greenplum Developers
> On 05 Sep 2016, at 19:00, Dave Cramer <dcr...@pivotal.io> wrote:
>
> At one point upstream had a TODO for this feature, I'm not sure they wouldn't
> accept the feature. Now the implementation might be a different story
The item is still in the TODO on the wiki and there has been significant amount
>
> At one point upstream had a TODO for this feature, I'm not sure they wouldn't
> accept the feature. Now the implementation might be a different story
of discussion around it over the years, all of which boils down to: very few
cases can be solved automagically such that no subtle breakage can be expected
in the consumers of the view, developer/dba interaction will be required anyway.
If we can figure out a good API for how to deal with this in a safe way I think
that could be of interest to upstream as well though.
cheers ./daniel
Yandong Yao
Sep 6, 2016, 11:25:06 AM9/6/16
to Daniel Gustafsson, Dave Cramer, Ivan Novick, Greenplum Developers
Could we take back a bit and just mark view is invalid if underlying table changes impact the view, without provide any feature to revalidate it automatically (maybe make auto revalidation as an option if customer really want such behavior)?
One real customer case is that DBA own the physical tables (maybe a few view also), and many application developers create their views (thousands of views) according to their application requirements. When DBA want to change schema of physical table, DBA has to drop all views and recreate them. If DBA has the option to change physical table and invalid all views impacted by the changes, then view owner could change their view by themselves later at their convenient time.
--
Best Regards,
Yandong
Robert Eckhardt
Sep 7, 2016, 1:39:31 PM9/7/16
to Yandong Yao, Daniel Gustafsson, Dave Cramer, Ivan Novick, Greenplum Developers
On Tue, Sep 6, 2016 at 11:25 AM, Yandong Yao <yy...@pivotal.io> wrote:
Could we take back a bit and just mark view is invalid if underlying table changes impact the view, without provide any feature to revalidate it automatically (maybe make auto revalidation as an option if customer really want such behavior)?
I think the question here is what we want to provide to the view owner and to the person trying to leverage the view. In my opinion we ought to provide the maintainer the original SQL view declaration (currently not stored) and what was changed in the underlying table.
One real customer case is that DBA own the physical tables (maybe a few view also), and many application developers create their views (thousands of views) according to their application requirements. When DBA want to change schema of physical table, DBA has to drop all views and recreate them. If DBA has the option to change physical table and invalid all views impacted by the changes, then view owner could change their view by themselves later at their convenient time.
If I am to be completely honest this is a feature that would just piss me off as a user. If I had something relying on a view and it stopped working I'd be irritated. Whatever we do we need to keep that annoyance in mind.
Ivan Novick
Sep 7, 2016, 4:10:19 PM9/7/16
to Robert Eckhardt, Yandong Yao, Daniel Gustafsson, Dave Cramer, Greenplum Developers
It sounds like there are a few fundamental PostgreSQL things we are pushing to get added as infrastructure that could enable the features we want:
1) Ability to store original view text in the database
2) Ability to invalidate a view instead of giving a dependency error (Would need a place in the catalog to mark views as valid/invalid)
3) Ability to re-compile the view and attempt to make it valid again.
Above is basically what the customers/users of other database systems like Oracle/TD/IBM are asking for in PG based databases like GPDB.
Cheers,
Ivan
Yandong Yao
Sep 7, 2016, 7:03:51 PM9/7/16
to Robert Eckhardt, Daniel Gustafsson, Dave Cramer, Ivan Novick, Greenplum Developers
On Thu, Sep 8, 2016 at 1:39 AM, Robert Eckhardt <reck...@pivotal.io> wrote:
On Tue, Sep 6, 2016 at 11:25 AM, Yandong Yao <yy...@pivotal.io> wrote:Could we take back a bit and just mark view is invalid if underlying table changes impact the view, without provide any feature to revalidate it automatically (maybe make auto revalidation as an option if customer really want such behavior)?I think the question here is what we want to provide to the view owner and to the person trying to leverage the view. In my opinion we ought to provide the maintainer the original SQL view declaration (currently not stored) and what was changed in the underlying table.One real customer case is that DBA own the physical tables (maybe a few view also), and many application developers create their views (thousands of views) according to their application requirements. When DBA want to change schema of physical table, DBA has to drop all views and recreate them. If DBA has the option to change physical table and invalid all views impacted by the changes, then view owner could change their view by themselves later at their convenient time.If I am to be completely honest this is a feature that would just piss me off as a user. If I had something relying on a view and it stopped working I'd be irritated. Whatever we do we need to keep that annoyance in mind.
That is why we provide re-validate as an option. Most of the cases, the re-validation will pass without any issues. If re-validate has issues, view owner has to recreate their view anyway, no matter whether we implement this feature or not, and no matter how to implement it. For issues listed by Daniel which might pass validation while generate different result, it is at customers' hand if they choose this option. It looks like not a big deal as most of the existing systems have such kind behavior.
--On Tue, Sep 6, 2016 at 1:25 PM, Daniel Gustafsson <dgust...@pivotal.io> wrote:> On 05 Sep 2016, at 19:00, Dave Cramer <dcr...@pivotal.io> wrote:
>
> At one point upstream had a TODO for this feature, I'm not sure they wouldn't
> accept the feature. Now the implementation might be a different story
The item is still in the TODO on the wiki and there has been significant amount
of discussion around it over the years, all of which boils down to: very few
cases can be solved automagically such that no subtle breakage can be expected
in the consumers of the view, developer/dba interaction will be required anyway.
If we can figure out a good API for how to deal with this in a safe way I think
that could be of interest to upstream as well though.
cheers ./daniel
Best Regards,Yandong
Best Regards,
Yandong
Daniel Gustafsson
Sep 8, 2016, 5:42:46 AM9/8/16
to Yandong Yao, Robert Eckhardt, Dave Cramer, Ivan Novick, Greenplum Developers
> On 08 Sep 2016, at 01:03, Yandong Yao <yy...@pivotal.io> wrote:
>
> On Thu, Sep 8, 2016 at 1:39 AM, Robert Eckhardt <reck...@pivotal.io <mailto:reck...@pivotal.io>> wrote:
successful result but also the expected outcome. Then there are the other
cases where it doesn’t..
Let’s say we have a RECOMPILE keyword in SQL and the textual representation of
the view stored somewhere. Invoking RECOMPILE on a view name runs the textual
representation and if it creates a valid parsetree the compiled version of the
view is replaced with the new tree.
create table t (a integer, b varchar);
create view v as select * from t;
begin;
alter table t add column c text;
alter table t alter column b type text;
-- View v is now invalid due to change of b
recompile view v;
commit;
The view v is now returning three columns instead of two which breaks the SQL
standard which states that a SELECT * should return the set of columns present
at the time of view creation (I don’t have the paragraph number handy). Even
if that was an acceptable trade-off, there are more pitfalls under the next
rock turned over. RECOMPILE would need access to not only the textual
representation of the view but also the environment at the time of creation.
Consider the below example:
create function foo.f(integer) returns integer as
$$ select $1+1; $$ language sql;
create function bar.f(integer) returns text as
$$ select $1::TEXT ||'a'; $$ language sql;
set search_path=foo;
create view public.v as select f(5);
-- select * from public.v; now returns the value 6
set search_path=bar;
recompile view public.v;
-- select * from public.v; now returns the value ‘5a'
While not saying we should punt the issue over to the user to “figure it out”,
I’m playing devils advocate to illustrate the foot shooting capabilities so we
know what we’re up against.
cheers ./daniel
>
> On Thu, Sep 8, 2016 at 1:39 AM, Robert Eckhardt <reck...@pivotal.io <mailto:reck...@pivotal.io>> wrote:
> On Tue, Sep 6, 2016 at 11:25 AM, Yandong Yao <yy...@pivotal.io <mailto:yy...@pivotal.io>> wrote:
> Could we take back a bit and just mark view is invalid if underlying table changes impact the view, without provide any feature to revalidate it automatically (maybe make auto revalidation as an option if customer really want such behavior)?
>
> I think the question here is what we want to provide to the view owner and to the person trying to leverage the view. In my opinion we ought to provide the maintainer the original SQL view declaration (currently not stored) and what was changed in the underlying table.
>
> One real customer case is that DBA own the physical tables (maybe a few view also), and many application developers create their views (thousands of views) according to their application requirements. When DBA want to change schema of physical table, DBA has to drop all views and recreate them. If DBA has the option to change physical table and invalid all views impacted by the changes, then view owner could change their view by themselves later at their convenient time.
>
> If I am to be completely honest this is a feature that would just piss me off as a user. If I had something relying on a view and it stopped working I'd be irritated. Whatever we do we need to keep that annoyance in mind.
>
> That is why we provide re-validate as an option. Most of the cases, the re-validation will pass without any issues. If re-validate has issues, view owner has to recreate their view anyway, no matter whether we implement this feature or not, and no matter how to implement it. For issues listed by Daniel which might pass validation while generate different result, it is at customers' hand if they choose this option. It looks like not a big deal as most of the existing systems have such kind behavior.
There are no doubt cases when re-evaluating the view text will yield not only a
> Could we take back a bit and just mark view is invalid if underlying table changes impact the view, without provide any feature to revalidate it automatically (maybe make auto revalidation as an option if customer really want such behavior)?
>
> I think the question here is what we want to provide to the view owner and to the person trying to leverage the view. In my opinion we ought to provide the maintainer the original SQL view declaration (currently not stored) and what was changed in the underlying table.
>
> One real customer case is that DBA own the physical tables (maybe a few view also), and many application developers create their views (thousands of views) according to their application requirements. When DBA want to change schema of physical table, DBA has to drop all views and recreate them. If DBA has the option to change physical table and invalid all views impacted by the changes, then view owner could change their view by themselves later at their convenient time.
>
> If I am to be completely honest this is a feature that would just piss me off as a user. If I had something relying on a view and it stopped working I'd be irritated. Whatever we do we need to keep that annoyance in mind.
>
> That is why we provide re-validate as an option. Most of the cases, the re-validation will pass without any issues. If re-validate has issues, view owner has to recreate their view anyway, no matter whether we implement this feature or not, and no matter how to implement it. For issues listed by Daniel which might pass validation while generate different result, it is at customers' hand if they choose this option. It looks like not a big deal as most of the existing systems have such kind behavior.
successful result but also the expected outcome. Then there are the other
cases where it doesn’t..
Let’s say we have a RECOMPILE keyword in SQL and the textual representation of
the view stored somewhere. Invoking RECOMPILE on a view name runs the textual
representation and if it creates a valid parsetree the compiled version of the
view is replaced with the new tree.
create table t (a integer, b varchar);
create view v as select * from t;
begin;
alter table t add column c text;
alter table t alter column b type text;
-- View v is now invalid due to change of b
recompile view v;
commit;
The view v is now returning three columns instead of two which breaks the SQL
standard which states that a SELECT * should return the set of columns present
at the time of view creation (I don’t have the paragraph number handy). Even
if that was an acceptable trade-off, there are more pitfalls under the next
rock turned over. RECOMPILE would need access to not only the textual
representation of the view but also the environment at the time of creation.
Consider the below example:
create function foo.f(integer) returns integer as
$$ select $1+1; $$ language sql;
create function bar.f(integer) returns text as
$$ select $1::TEXT ||'a'; $$ language sql;
set search_path=foo;
create view public.v as select f(5);
-- select * from public.v; now returns the value 6
set search_path=bar;
recompile view public.v;
-- select * from public.v; now returns the value ‘5a'
While not saying we should punt the issue over to the user to “figure it out”,
I’m playing devils advocate to illustrate the foot shooting capabilities so we
know what we’re up against.
cheers ./daniel
Daniel Gustafsson
Sep 8, 2016, 6:07:08 AM9/8/16
to Ivan Novick, Robert Eckhardt, Yandong Yao, Dave Cramer, Greenplum Developers
> On 07 Sep 2016, at 22:10, Ivan Novick <ino...@pivotal.io> wrote:
>
> It sounds like there are a few fundamental PostgreSQL things we are pushing to get added as infrastructure that could enable the features we want:
>
> 1) Ability to store original view text in the database
In upstream this has little chance of happening, it has been up for discussion
>
> It sounds like there are a few fundamental PostgreSQL things we are pushing to get added as infrastructure that could enable the features we want:
>
> 1) Ability to store original view text in the database
many times and always been rejected (not that I bear any weight on that call
but a patch implementing it would have to solve all the pre-existing issues
raised). That being said, nothing stops us from saving the query text in the
database in a normal table or in a COMMENT on the actual view as per my earlier
email in this thread.
> 2) Ability to invalidate a view instead of giving a dependency error (Would need a place in the catalog to mark views as valid/invalid)
view is a compiled representation of a query you still need to compile the
query to make it valid again much like if it’s dropped. A transaction
involving an invalid view will still ROLLBACK albeit I guess with a different
error message.
The one different can be that an invalid view retains the dependency chain but
that opens up another can of worms, what to do when database changes are
hamstrung/blocked by dependencies in abaondoned invalid views?
> 3) Ability to re-compile the view and attempt to make it valid again.
below for an alternative approach which is less brute-force.
https://groups.google.com/a/greenplum.org/d/msg/gpdb-dev/_TpeAiFAcsw/gXLBQzl0AwAJ
> Above is basically what the customers/users of other database systems like Oracle/TD/IBM are asking for in PG based databases like GPDB.
things though? In a post to -hackers an ex Greenplum employee gives a good
summary of the current state at the time of writing (which given that this is
base level plumbing I assume hasn’t changed drastically):
https://www.postgresql.org/message-id/C75261D0.3FE4%25cwelton%40greenplum.com
The conclusion being that every product is slightly broken in disjoint ways =)
Now, as stated earlier in this thread, not doing anything is clearly not an
improvement. Personally I think we can do better than all of the above. We
need to supply good tooling but I am personally not convinced that this tooling
belongs in the database (apart from an API assisting said tooling). Below is a
proposal based off previous discussions here and a prior suggestion from Heikki
in another thread.
First let’s identify which problems it is we are optimizing for:
* The DBA own the relations, application developers own the views
* A relation can have many views
* Figuring out which views that need to be changed on ALTER TABLE is hard
* Everyone wants automagic but human intervention is needed in some cases
* Fixing the views is generally punted to after-the-fact
Did I miss any?
Based on the above set, my take is that we thus need tooling that can assist
the relation owner in figuring out a) what is impacted by a change; b) what can
safely be changed automatically; c) what needs human intervention to be fixed;
d) what will be broken if the objects in c are dropped/invalidated. Further it
should ideally pre-empt problems rather than return a list of now defunct
database objects (or invalidating them silently).
Assumptions/Requirements:
* The query text is available *somewhere*, it can be in the database but it
can also be in a Git repository, doesn’t matter (pluggable part of the tool
to adapt to customer needs?) The reason for wanting the original query text
is that formatting and comments etc which can greatly assist in fixing a
query aren’t propagated to the parsetree.
* We can extend the view text *in some way* with unittest data and queries.
It can be a separate file in a Git repo or in a database column should we
want to store it there.
* The tool should not rely on 100% coverage for the previous assumptions,
if no test exists then it shouldn’t fail, if no query test exist it
shouldn’t fail. Since the tool will be retrofitted on top of production
setups, make it possible to back-fill all the details.
With this, the tool should then for a change of datatype of column c in table t
perform:
1. Identify recursively every database object that needs to be changed
when altering the type of column c.
2. The set of views identified as dependent are V, for each view v in V
emit and run a script testing the change. If the unittest query isn’t
available then mark that by running select no_test(); which includes that
information in the report. If the view text isn’t available, take the
parsetree generated definition from the database.
begin;
create table tt (like t);
insert into table tt values (<testcase data 1>), .. (<testcase data n>);
alter table tt alter column c type foo;
create view v as <query text with t replaced with tt>;
select ok(<testcase query 1>);
..
select ok(<testcase query n>);
rollback;
3. Generate a report for the processing in step 2, which views can be
safely recompiled and which will break as well as the dependency chain
outlining why they break (a view not even impacted at all by the change of
c might be broken due to it selecting from a view that ended up being
dropped).
4. Iff the operator requests to continue, generate DDL to alter the type
by recreating the views that passed test and drop the views that failed the
test (if any).
5. The DDL can be inspected and version controlled before executed in the
database. If a staging environment exists it can also be tested there
before going into production. This way the underlying view queries can be
fixed and the process repeated until satisfaction that business logic and
processes won’t suddenly fail.
The functionality we need from the database is thus to identify all dependant
objects and test functionality much like what pgTAP offers (albeit a subset of
it).
Basically it’s a twist on recompiling from the textual representation that adds
a level of certainty on the success, and also properly identifies what needs to
be addressed in case of failures. It does add work since the views need tests
but that shouldn’t be much of a showstopper, having tests is a Good Thing.
Also, if executed without any tests etc it would still work, just be less user
friendly and give less value.
Without having given it too much thought I think something like the above also
could be used in Chef (and possibly Puppet) for automating node convergence on
database changes for anyone that have a CI pipeline like that. That would need
further validation though.
Is this proposal a way forward in giving our users a better experience? Am I
missing any parts of the equation?
cheers ./daniel
Yandong Yao
Sep 8, 2016, 7:16:30 PM9/8/16
to Daniel Gustafsson, Simon Gao, Ivan Novick, Robert Eckhardt, Dave Cramer, Greenplum Developers
Thanks Daniel for the great detailed info.
As we are talking more and more user experience, I would suggest PMs to do user interviews and validations with a dozen real customers who are suffering from this issue.
--
Best Regards,
Yandong
Daniel Gustafsson
Sep 9, 2016, 2:28:42 AM9/9/16
to Yandong Yao, Simon Gao, Ivan Novick, Robert Eckhardt, Dave Cramer, Greenplum Developers
> On 09 Sep 2016, at 01:16, Yandong Yao <yy...@pivotal.io> wrote:
>
> Thanks Daniel for the great detailed info.
>
> As we are talking more and more user experience, I would suggest PMs to do user interviews and validations with a dozen real customers who are suffering from this issue.
That would be extremely useful.
>
> Thanks Daniel for the great detailed info.
>
> As we are talking more and more user experience, I would suggest PMs to do user interviews and validations with a dozen real customers who are suffering from this issue.
> On Thu, Sep 8, 2016 at 6:07 PM, Daniel Gustafsson <dgust...@pivotal.io <mailto:dgust...@pivotal.io>> wrote:
> > On 07 Sep 2016, at 22:10, Ivan Novick <ino...@pivotal.io <mailto:ino...@pivotal.io>> wrote:
> >
> > It sounds like there are a few fundamental PostgreSQL things we are pushing to get added as infrastructure that could enable the features we want:
> >
> > 1) Ability to store original view text in the database
>
> In upstream this has little chance of happening, it has been up for discussion
> many times and always been rejected (not that I bear any weight on that call
> but a patch implementing it would have to solve all the pre-existing issues
> raised). That being said, nothing stops us from saving the query text in the
> database in a normal table or in a COMMENT on the actual view as per my earlier
> email in this thread.
>
> > 2) Ability to invalidate a view instead of giving a dependency error (Would need a place in the catalog to mark views as valid/invalid)
>
> What is the difference between an invalidated view and a dropped view? Since a
> view is a compiled representation of a query you still need to compile the
> query to make it valid again much like if it’s dropped. A transaction
> involving an invalid view will still ROLLBACK albeit I guess with a different
> error message.
>
> The one different can be that an invalid view retains the dependency chain but
> that opens up another can of worms, what to do when database changes are
> hamstrung/blocked by dependencies in abaondoned invalid views?
>
> > 3) Ability to re-compile the view and attempt to make it valid again.
>
> See my reply to Yandong Yao earlier today for a comment on this as well as
> below for an alternative approach which is less brute-force.
>
> https://groups.google.com/a/greenplum.org/d/msg/gpdb-dev/_TpeAiFAcsw/gXLBQzl0AwAJ <https://groups.google.com/a/greenplum.org/d/msg/gpdb-dev/_TpeAiFAcsw/gXLBQzl0AwAJ>
> >
> > It sounds like there are a few fundamental PostgreSQL things we are pushing to get added as infrastructure that could enable the features we want:
> >
> > 1) Ability to store original view text in the database
>
> In upstream this has little chance of happening, it has been up for discussion
> many times and always been rejected (not that I bear any weight on that call
> but a patch implementing it would have to solve all the pre-existing issues
> raised). That being said, nothing stops us from saving the query text in the
> database in a normal table or in a COMMENT on the actual view as per my earlier
> email in this thread.
>
> > 2) Ability to invalidate a view instead of giving a dependency error (Would need a place in the catalog to mark views as valid/invalid)
>
> What is the difference between an invalidated view and a dropped view? Since a
> view is a compiled representation of a query you still need to compile the
> query to make it valid again much like if it’s dropped. A transaction
> involving an invalid view will still ROLLBACK albeit I guess with a different
> error message.
>
> The one different can be that an invalid view retains the dependency chain but
> that opens up another can of worms, what to do when database changes are
> hamstrung/blocked by dependencies in abaondoned invalid views?
>
> > 3) Ability to re-compile the view and attempt to make it valid again.
>
> See my reply to Yandong Yao earlier today for a comment on this as well as
> below for an alternative approach which is less brute-force.
>
>
> > Above is basically what the customers/users of other database systems like Oracle/TD/IBM are asking for in PG based databases like GPDB.
>
> Depending on what they use today they are likely to want radically different
> things though? In a post to -hackers an ex Greenplum employee gives a good
> summary of the current state at the time of writing (which given that this is
> base level plumbing I assume hasn’t changed drastically):
>
> https://www.postgresql.org/message-id/C75261D0.3FE4%25cwelton%40greenplum.com <https://www.postgresql.org/message-id/C75261D0.3FE4%25cwelton%40greenplum.com>
> > Above is basically what the customers/users of other database systems like Oracle/TD/IBM are asking for in PG based databases like GPDB.
>
> Depending on what they use today they are likely to want radically different
> things though? In a post to -hackers an ex Greenplum employee gives a good
> summary of the current state at the time of writing (which given that this is
> base level plumbing I assume hasn’t changed drastically):
>
Marshall
Sep 9, 2016, 9:03:07 AM9/9/16
to Greenplum Developers
Not that we should always ask "How does Oracle do it?" but I wondered and found an article by Tom Kyte, which may be of interest:
If my read is correct, sometimes it's logically necessary to recompile the view, sometimes not. Assuming we had the original definition of the view, could we figure out if it required recoding or could be done mechanically?
MEP
Daniel Gustafsson
Sep 26, 2016, 7:48:08 AM9/26/16
to Yandong Yao, Ivan Novick, Robert Eckhardt, Greenplum Developers
> On 09 Sep 2016, at 08:28, Daniel Gustafsson <dgust...@pivotal.io> wrote:
>
>> On 09 Sep 2016, at 01:16, Yandong Yao <yy...@pivotal.io> wrote:
>>
>> Thanks Daniel for the great detailed info.
>>
>> As we are talking more and more user experience, I would suggest PMs to do user interviews and validations with a dozen real customers who are suffering from this issue.
>
> That would be extremely useful.
Has there been any progress on this?
>
>> On 09 Sep 2016, at 01:16, Yandong Yao <yy...@pivotal.io> wrote:
>>
>> Thanks Daniel for the great detailed info.
>>
>> As we are talking more and more user experience, I would suggest PMs to do user interviews and validations with a dozen real customers who are suffering from this issue.
>
> That would be extremely useful.
cheers ./daniel
Ivan Novick
Sep 26, 2016, 1:54:13 PM9/26/16
to Daniel Gustafsson, Yandong Yao, Robert Eckhardt, Greenplum Developers
Pivotal has 3 major customers of GPDB that have raised this issue and are eager to provide feedback on this and they are complaining about the lack of ability to in-validate views (like available in Oracle). Direct quote "most painful thing about managing the installation".
They are also willing to consider alternative approaches if we can make the process of table definition changes more smooth in the case when they have 10,000 to 30,000 views on top of their schema which makes it difficult to update tables without conflict with the dependent views.
Here is a reference to the feature in Oracle: https://docs.oracle.com/cd/B28359_01/server.111/b28310/general007.htm#BABJAIFA
Cheers,
Ivan
Reply all
Reply to author
Forward
0 new messages