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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-hackers pgsql-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

Browse pgadmin-hackers by date

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

Browse pgsql-hackers by date

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