Skip site navigation (1) Skip section navigation (2)

Re: DROP/CREATE

From: Jean-Michel POURE <jm(dot)poure(at)freesurf(dot)fr>
To: dpage(at)vale-housing(dot)co(dot)uk
Cc: pgadmin-hackers(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: DROP/CREATE
Date: 2001-10-30 07:56:34
Message-ID: 4.2.0.58.20011030081705.00a76dc0@pop.freesurf.fr (view raw or flat)
Thread:
Lists: pgadmin-hackerspgsql-hackers
 > For a 'standalone' view, this is fine, but if the view is used in 
another view or a function then that will break (I think I'm teaching my 
Grandmother to suck eggs here Jean-Michel!).
 > 1) Attempt to create a view with the new definition to ensure it's valid.
 > 2) Drop the old view.
 > 3) Create the new view.
 > 4) Re-apply any comments and ACLs.
 > 5) Query pg_class for the updated OID.

Dear Friends,

I did not get this email on pgadmin-hackers. We need view dependency 
checking, otherwise there is no chance that I can one day migrate from 
pgAdmin I to pgAdmin II. Hopefully, updating a view is not too difficult:

- Attempt to create a view with the new definition to ensure it's valid.
- Open transaction (in locking mode as we may drop triggers in many tables).
- Drop dependent views in OID order. Keep CREATE SQL strings for future usage.
- Drop dependent triggers. Keep CREATE SQL strings for future usage.
- Drop dependent rules. Keep CREATE SQL strings for future usage.
- Drop the old view and create the new view.
- Create dependent views, triggers and rules.
- Re-apply any comments and ACLs.
- Commit transaction.
- Query pg_class for the updated OID.

Any feedback?

Another issue is that views get very complex when commited. An example 
would be:
CREATE VIEW "view_data_source"
AS SELECT * FROM table 1
LEFT JOIN table 2 ON (xx=ccc)
LEFT JOIN table 3 ON (xx=ccc)

When committed, this view becomes a nightmare because it can hardly be 
read. Another subsequent problem is that views with SELECT * FROM table1 
need updating when fields are added/dropped in tables. In the end we always 
come up with the conclusion that changes should be applied internally to 
PostgreSQL.

I am going to have a look at updating views within a single transaction. 
Are there special guidelines for compiling phSchema?

Best regards,
Jean-Michel

pgsql-hackers by date

Next:From: Dave PageDate: 2001-10-30 08:25:17
Subject: Re: DROP/CREATE
Previous:From: Michael MeskesDate: 2001-10-30 07:31:53
Subject: Re: ecpg - GRANT bug

pgadmin-hackers by date

Next:From: Dave PageDate: 2001-10-30 08:25:17
Subject: Re: DROP/CREATE
Previous:From: Dave PageDate: 2001-10-29 11:08:43
Subject: Website/Colours/Logos

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group