Alter view with dependence without drop view!
Hello!
I want make change in one view that have dependence view's, so when i try
to make change i'm block because of this, what is the best solution??
thank you!!
best regard
Elson Vaz
One way I have approached this problem is:
1) Use PgAdmin attempt the change.
2) Examine the error report PgAdmin displays that identifies which dependent views are preventing your progress.
3) Wrap your original DDL from step 1 within the DROP and CREATE DDL associated with the closest dependent view.
4) Return to step 1 and repeat until step 1 succeeds.
With multiple iterations of this procedure, you will incrementally grow a DDL script that drops dependent views in the correct order, eliminating dependencies, and then recreate them in the proper order, respecting dependencies.
When this procedure got old, I started using a script created using pg_dump and pg_restore, as initially outlined here:
/messages/by-id/55C3F0B4.5010600@computer.org
and with a correction noted here:
/messages/by-id/0456dfda-4623-1331-7dca-e3cff914357b@computer.org
-- B
----- Original Message -----
From: "Elson Vaz" <elsonlei@gmail.com>
To: pgsql-general@lists.postgresql.org
Sent: Tuesday, January 30, 2018 8:40:45 AM
Subject: Alter view with dependence without drop view!
Hello!
I want make change in one view that have dependence view's, so when i try to make change i'm block because of this, what is the best solution??
thank you!!
best regard
Elson Vaz
On Tue, Jan 30, 2018 at 8:34 AM, btober@computer.org <btober@broadstripe.net
wrote:
When this procedure got old, I started using a script created using
pg_dump and pg_restore, as initially outlined here:
Yeah, the short answer is PostgreSQL doesn't make it possible to edit
"middle" views without having the code on hand for all dependent views so
you can recreate them. You either maintain those views and order manually
or you rely on pg_dump to figure it out for you (the former, with version
control, is highly recommended).
I could see it being possible to program the server to be more helpful here
- by say allowing it to drop but remember view definitions and the
re-create them from the remembered versions by name - but no one has seen
the motivation to do so; I suspect partially in light of the fact that
"version control" is a recommended practice.
David J.
On Tue, Jan 30, 2018 at 12:48 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
On Tue, Jan 30, 2018 at 8:34 AM, btober@computer.org <
btober@broadstripe.net> wrote:When this procedure got old, I started using a script created using
pg_dump and pg_restore, as initially outlined here:Yeah, the short answer is PostgreSQL doesn't make it possible to edit
"middle" views without having the code on hand for all dependent views so
you can recreate them. You either maintain those views and order manually
or you rely on pg_dump to figure it out for you (the former, with version
control, is highly recommended).I could see it being possible to program the server to be more helpful
here - by say allowing it to drop but remember view definitions and the
re-create them from the remembered versions by name - but no one has seen
the motivation to do so; I suspect partially in light of the fact that
"version control" is a recommended practice.David J.
*Just a side note, it is not a good practice to create views based on other
views. *
*Multiple reasons are stated here:*
*https://dba.stackexchange.com/questions/5487/is-nested-view-a-good-database-design
<https://dba.stackexchange.com/questions/5487/is-nested-view-a-good-database-design>*
*To summarize, you suffer performance degradation, columns names and the
tables referenced become obscured and you incur the problems you are now
experiencing.*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I want make change in one view that have dependence view's, so when i try
to make change i'm block because of this, what is the best solution??
Just to be clear, there are some changes you can make, some you can't.
Basically you can't change the definition of existing columns in the view.
But if you want to change the logic that generates those columns, or add
new columns at the end, you can do that with CREATE OR REPLACE VIEW ...
On Tue, Jan 30, 2018 at 10:12 AM, Melvin Davidson <melvin6925@gmail.com>
wrote:
On Tue, Jan 30, 2018 at 12:48 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:On Tue, Jan 30, 2018 at 8:34 AM, btober@computer.org <
btober@broadstripe.net> wrote:When this procedure got old, I started using a script created using
pg_dump and pg_restore, as initially outlined here:Yeah, the short answer is PostgreSQL doesn't make it possible to edit
"middle" views without having the code on hand for all dependent views so
you can recreate them. You either maintain those views and order manually
or you rely on pg_dump to figure it out for you (the former, with version
control, is highly recommended).
I agree it's best to keep the definitions of the view yourself--among other
things it lets you keep comments. But you can also generate a
perfectly-functional definition of a view with \d+.
*Just a side note, it is not a good practice to create views based on
other views. **Multiple reasons are stated here:*
*https://dba.stackexchange.com/questions/5487/is-nested-view-a-good-database-design
<https://dba.stackexchange.com/questions/5487/is-nested-view-a-good-database-design>**To summarize, you suffer performance degradation, columns names and the
tables referenced become obscured and you incur the problems you are now
experiencing.*
That link you pointed to includes arguments both for and against. It seems
to me that performance weighs against nested views, clarity can cut both
ways, and nested views can help centralize and encapsulate business logic,
avoiding needs for reuse and maintaining consistency. Whether they are a
good idea or not depends on your goals, priorities and specific situation.
Cheers,
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
Elson Vaz schrieb am 30.01.2018 um 14:40:
Hello!
I want make change in one view that have dependence view's, so when i try to make change i'm block because of this, what is the best solution??
This can easily be dealt with when using a schema management tool.
We use Liquibase for this and the main migration script simply drops all views before running any migration (so that table columns can be removed or renamed without having to think about view dependencies).
Once the table migrations are done, all views are re-created automatically.
The Liquibase migration script and the SQL source for the views are stored together in Git.
Thomas