Postgres Pro
Downloads
Home   >   mailing lists

Thread: Alter view with dependence without drop view!

Alter view with dependence without drop view!

From
Elson Vaz
Date:
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

Re: Alter view with dependence without drop view!

From
"btober@computer.org"
Date:
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:


and with a correction noted here:





-- B


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

Re: Alter view with dependence without drop view!

From
"David G. Johnston"
Date:
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.

Re: Alter view with dependence without drop view!

From
Melvin Davidson
Date:


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

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.

Re: Alter view with dependence without drop view!

From
Ken Tanzer
Date:
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

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
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Alter view with dependence without drop view!

From
Thomas Kellerer
Date:
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,
whatis 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
tablecolumns 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