hbgasil.blogg.se

1. #POSTGRESQL ALTER TABLE USED BY VIEW HOW TO#
2. #POSTGRESQL ALTER TABLE USED BY VIEW UPDATE#
3. #POSTGRESQL ALTER TABLE USED BY VIEW FULL#
4. #POSTGRESQL ALTER TABLE USED BY VIEW CODE#

Having the master code in the database makes development harder since Upstream PostgreQSL will likely Not do this causing divergence if we put With a table change, fixing all these in the space of a transaction in SQLĪ few other issues I see with storing and thus using the view text in theĭatabase (only storing without using is rather pointless): If there are 100 views that have a conflict

What I would propose is to create a good APIįor working with these dependencies and conflicts instead and rather create a This would imply that SQL should be used as an interface for this, something Iĭoubt will be very user friendly.

#POSTGRESQL ALTER TABLE USED BY VIEW UPDATE#

Allow users to recompile original view code or update it so it works. For example invalidate all views that are modified based on a dependcy. 2) work on a feature to use the original view text. > 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. It’s at least something we can achieve that we a) know works and b) quickly. This is a far cry from the vision statement in the beginning of this email but Technical problem, the solution is mainly a UX issue. To recreate the view when running the alter table statement. Owners of the views will then have to supply an updated query which can be used Of all the views which will need to be changed for the table alteration. Propose we do instead is to supply functionality to extract a detailed report Now, not doing anything is obviously not a very good solution either. Might be causing harm that the user didn’t intend. Table ends up automatically triggering a very expensive matview rewrite we Materialized views even though we currently don’t support them.

#POSTGRESQL ALTER TABLE USED BY VIEW HOW TO#

Is if we we find an operator in the view, how to handle “SELECT foo + 0” whenįoo changes? The amount of corner cases is increasing the more one pokes atĪnother thing to consider is that anything we do should handle the case of Which subtly breaks since it expects a smaller integer range? Another caveat If weĬhange the view from integer to bigint, perhaps there is a stored procedure If we extend this by trying to trace down the dependencies and update the viewĪccordingly to solve the problems, we still can’t guarantee correctness. Summary, this can be proven not to work even when it "works”. Selling point for views, breaking it subtly like this is dangerous. While this example is quite contrived it illustrates the problems which can View however, the results are however not what we’d expect unless we knew about

#POSTGRESQL ALTER TABLE USED BY VIEW FULL#

Success, we can now start playing with full text search. Recreate" solution to this situation it works without even a NOTICE beingĪlter table tt alter column b type tsvector using b::tsvector Now let’s say that a team member want to make use of the new cool text searchįeature merged as part of 8.3 he/she subsequently alters the type of tt.b to Querying our view gives us the following result: Lets start with setting up our table and a view which performs operations onĬreate table tt (a integer, b text) distributed by (a) Ĭreate view ttv as select trim(both ((b || 'a')::text), $$'$$) from tt See below for an example what can happen:

The main problem with it is that when it doesn’t work it doesn’t The allure of this solution is that it works quite often, for a certain value This can then be used in the following sequence to automate the process needed Join pg_class c on (d.objoid = c.oid) where c.relname = 'v' Retrieve it even without involving extra tables:Ĭomment on view v is $view$ select c from t $view$ Making anĮxternal app which saves the text in a table is however trivial, the query textĬan even be saved as a comment on the view object itself to make it easy to On a column which is part of a view works, the object is unchanged). Of the view definition but instead work on an object level (this is why rename PostgreSQL doesn’t use, or store, the textual representation Text making up the view, and then drop/recreate it on either side of the ALTER The views which contain references to an attribute which is being altered suchĪ naive implementation (which is often suggested) is to simply save the query The gist of what we all want is: For the database to automatically re-create Thousands of views makes it hard for a DBA to perform requested table changes. This in combination with an installation having hundreds or even View is prohibited, the view must be dropped before the alter table statement The problem statement is simple: altering an object which is referenced in a Without getting closer to a good solution. PostgreSQL (and Greenplum) arise every now and again unfortunately still The question of how to handle dependent views during table alteration in