Re: DROP/CREATE

-----Original Message-----
From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr]
Sent: 30 October 2001 07:57
To: dpage@vale-housing.co.uk
Cc: pgadmin-hackers@postgresql.org; pgsql-hackers@postgresql.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.

-----Original Message-----
From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr]
Sent: 30 October 2001 09:21
To: Dave Page
Cc: pgadmin-hackers@postgresql.org
Subject: RE: DROP/CREATE

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.

A third solution would be to work with PL/pgSQL and
development tables (i.e
code repository).
The notion of Code repository is interesting because it is
not linked to
PostgreSQL internals.
A code repository can be located anywhere on the planet. Cool
feature for
development teams.

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).

With PL/pgSQL we can ***easily*** track and rebuild objects.
Before that,
we need a PL/pgSQL wizard in pgAdmin.
PostgreSQL might incorporate PL/pgSQL as a standard feature
when protection
for infinite loops is added.

I think that's unlikely from the responses you got from pgsql-hackers
recently.

Code repositories would be a nice solution as completely
independent from
PgAdmin. This means PhpPgAdmin would also benefit from it.
Ultimately, when
Postgresql gets PL/pgSQL infinite loop protection,
repositories could get
included in Postgresql. So why not go for it?

I've no problem with working with the phpPgAdmin people, that can only be a
good thing.

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.

OK, I will not upload pgSchema to CVS if modified. On my
side, I have to
consider migration from pgAdmin I to pgAdmin II to comply
with PostgreSQL
7.2. Without rebuilding, I cannot work and maintain 100
tables, 50 views,
30 triggers and 200 functions.

No, I can see your problem. Remember though that the code in pgAdmin I is
far from foolproof, as you've said before, we need absolute confidence that
*every* dependency is found and dealt with, something the pgAdmin I code
makes a good stab at but could be fooled.

I really believe that the only truly reliable way to do this is for
PostgreSQL to provide either a pg_dependencies table or a function that
tells us the dependencies for a given object. If this email actually makes
it to the pgsql-hackers list perhaps someone can comment on whether this is
likely to happen?

What are your plans? If you don't mind, I would prefer to go
for a PL/pgSQL
repository feature. This would be more advanced that in
pgAdmin I, testing
the new features on my side only. Please advise me for
pgShema compilation
guidelines.

I'm happy for you to look at code repositories, though I think they should
allow use of PL/Perl and PL/TCL as well. This shouldn't be a problem of
course because the PL code isn't 'compiled' by PostgreSQL like SQL functions
or Views are.

As far as pgSchema goes, compile it as I said, but pay attention to the
existing design and try to match the style/layout of the classes. For an
example of 'bolted on' functionality (as opposed to the core object
hierarchy), look at the History/Graveyard stuff.

Cheers, Dave.

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.

Could you give me your feedback again for view modification:
- 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.

This would allow migration from pgAdmin I to pgAdmin II.

/Later,
Jean-Michel