Re: DROP/CREATE

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm(dot)poure(at)freesurf(dot)fr]
> Sent: 30 October 2001 07:57
> 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
>
>
> > 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?

Well, I would point out that pgAdmin I doesn't do all this, but I'll concede
that it does do more than pgAdmin II at the moment.

I don't think rules are an issue are they? Can you create them on Views
(certainly pgAdmin won't let you - should it?) - scrub that, (typing as I
think!) how else would you create an updateable view using rules? Does the
same apply to triggers i.e. can you create them on views?

> 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'm beginning to think this is correct. I see the work you did in pgAdmin I
as a kind of proof of concept. The more we discuss these things, the more I
think of problems like this that would be seriously hard work to do client
side. To get around the problem here for example, you need to have a full
blown parser to figure out the tables involved. What if the view calls some
functions as well? What if that function takes an entire tuple from a
(modified) table as an argument (or returns it) - then things get really
hairy.

I think the only way we can reliably do this is with the addition of either
safe CREATE OR REPLACE sql commands, or addition of a suitable
pg_dependencies table which is maintained by PostgreSQL itself.

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

No, just that if you break compatibility you may need to run buildall.bat(?)
to recompile everything. Please don't commit anything to do with this until
I've taken a look either - I don't want to add any more features now until
after the first full release.

Cheers, Dave.