Re: DROP/CREATE

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm(dot)poure(at)freesurf(dot)fr]
> Sent: 30 October 2001 10:39
> To: pgadmin-hackers(at)postgresql(dot)org
> Cc: dave Page; pgsql-hackers(at)postgresql(dot)org
> Subject: RE: DROP/CREATE
>
>
>
> >Yes (and I agree that it would be a good feature), but that
> will still
> >require full client side parsing of the code to figure out the
> >dependencies
> >- I for one, do not wish to try to recreate (and keep up-to-date) the
> >PostgreSQL parser in VB. Besides which, if we take it that
> far then we might
> >just as well use reverse engineered SQL to scan for
> dependencies. I know you
> >don't like reverse engineered code, but bear in mind that
> the important bits
> >are reported directly from PostgreSQL (e.g. pg_proc.prosrc).
>
> IMHO view modification can be achieved within one
> transaction, without
> development table nor PL/pgSQL.

Yes, I agree. As I said in my first message, there is no problem with
standalone views, but (and this is the killer) if your view is a dependency
of something else like an SQL function or another view then you have a
problem. The problem is even bigger (i.e. harder to detect) if the rowtype
of the view is used as a parameter to or return value from a function (is
this actually possible? I know it is with a table).

> Could you give me your feedback again for view modification:
> - Attempt to create a view with the new definition to ensure
> it's valid.

Yes.

> - Open transaction (in locking mode as we may drop triggers
> in many tables).

Yes.

> - Drop dependent views in OID order. Keep CREATE SQL strings
> for future usage.

Yes.

> - Drop dependent triggers. Keep CREATE SQL strings for future usage.

Can triggers be dependent on views?

> - Drop dependent rules. Keep CREATE SQL strings for future usage.

Yes.

> - Drop the old view and create the new view.

Yes.

> - Create dependent views, triggers and rules.

Yes.

> - Re-apply any comments and ACLs.

Yes.

> - Commit transaction.

I wouldn't be surprised if some of these actions are not 'transactionable'.
Things like create user/database for example definitely aren't.

> - Query pg_class for the updated OID.

Yes.

>
> This would allow migration from pgAdmin I to pgAdmin II.

Incidently, pgAdmin II (and pgSchema) has no concept of objects being
defined on Views at present. I'll add that to my To-Do list - presumable
it's only Rules (and Triggers?).

Dave.