Thread: DROP/CREATE
Hello Dave and all, Views are based on OIDs and there is not simple way to implement DROP/CREATE VIEW in pgAdmin II. Seems more easy for CREATE OR REPLACE TRIGGER. I posted today a mail on pg-hackers to ask if someone could implement CREATE OR REPLACE directly in PostgreSQL. Cheers, Jean-Michel
> -----Original Message----- > From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr] > Sent: 21 October 2001 15:27 > To: pgadmin-hackers@postgresql.org > Cc: dpage@vale-housing.co.uk > Subject: DROP/CREATE > > > Hello Dave and all, > > Views are based on OIDs and there is not simple way to implement > DROP/CREATE VIEW in pgAdmin II. Seems more easy for CREATE OR REPLACE > TRIGGER. I posted today a mail on pg-hackers to ask if someone could > implement CREATE OR REPLACE directly in PostgreSQL. Yes, I saw your email. Actually, editting a view in pgSchema is probably not that hard as pgSchema doesn't give two hoots about the change in OID - what is more difficult is renaming an object or changing anything that would affect the .Identifier property. In reality, the biggest issue with this in pgSchema is probably that no dependency checking will be done. 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!). To edit a view in pgSchema, a Public Property Definition(vData As String) is required in pgView.cls. This will: 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. Job's done! Actually, probably the hardest bit is resetting the ACL. In theory, the same could be achieved for Triggers, although you wouldn't be able to change the Table. In other news: - pgadmin.org is now registered. It's (well, www.pgadmin.org) setup currently with frames based forwarding to pgadmin.postgresql.org. - pgAdmin II has now been run and (not extensively) tested on Windows XP Professional (release version). All seems OK... Cheers, Dave.
[resent...] > -----Original Message----- > From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr] > Sent: 21 October 2001 15:27 > To: pgadmin-hackers@postgresql.org > Cc: dpage@vale-housing.co.uk > Subject: DROP/CREATE > > > Hello Dave and all, > > Views are based on OIDs and there is not simple way to implement > DROP/CREATE VIEW in pgAdmin II. Seems more easy for CREATE OR REPLACE > TRIGGER. I posted today a mail on pg-hackers to ask if someone could > implement CREATE OR REPLACE directly in PostgreSQL. Yes, I saw your email. Actually, editting a view in pgSchema is probably not that hard as pgSchema doesn't give two hoots about the change in OID - what is more difficult is renaming an object or changing anything that would affect the .Identifier property. In reality, the biggest issue with this in pgSchema is probably that no dependency checking will be done. 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!). To edit a view in pgSchema, a Public Property Definition(vData As String) is required in pgView.cls. This will: 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. Job's done! Actually, probably the hardest bit is resetting the ACL. In theory, the same could be achieved for Triggers, although you wouldn't be able to change the Table. In other news: - pgadmin.org is now registered. It's (well, www.pgadmin.org) setup currently with frames based forwarding to pgadmin.postgresql.org. - pgAdmin II has now been run and (not extensively) tested on Windows XP Professional (release version). All seems OK... Cheers, Dave. -- Dave Page (dpage@postgresql.org) http://pgadmin.postgresql.org/
> 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
> -----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.
>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? Yes you can. This is a great feature and the only way for updating 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. 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. 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. 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 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. 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. Cheers, Jean-Michel
> -----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
> -----Original Message----- > From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr] > Sent: 30 October 2001 10:39 > To: pgadmin-hackers@postgresql.org > Cc: dave Page; pgsql-hackers@postgresql.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.
>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). Yes, it is possible, but functions treat views and table as SQL. Remember, I learnt it from you !!! No dependency problem. Ex: Create table1. Create function1 that outputs a value from table1. Drop table1. Create table1. Run function1. It should work. Needs some testing to verify. > > 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. I did not think of it. You are right. >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?). Triggers for 100% sure. We could automate the creation of triggers for view updating/deleting. Highly wanted cool feature. Cheers, Jean-Michel
> -----Original Message----- > From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr] > Sent: 30 October 2001 11:08 > To: Dave Page > Cc: pgadmin-hackers@postgresql.org > Subject: RE: DROP/CREATE > > > > >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). > > Yes, it is possible, but functions treat views and table as > SQL. Remember, I learnt it from you !!! No dependency problem. > > Ex: Create table1. Create function1 that outputs a value from > table1. Drop table1. Create table1. Run function1. It should work. > > Needs some testing to verify. You misunderstand me I think. In PostgreSQL a table row is a type itself, therefore you can pass an entire tuple to or from a function (example from pgAdmin I basSQL.bas). Note the function return value pgadmin_table_cache which is a table: CREATE FUNCTION pgadmin_get_rows(oid) RETURNS pgadmin_table_cache AS 'SELECT DISTINCT ON(table_oid) * FROM pgadmin_table_cache WHERE table_oid = $1 ORDER BY table_oid, table_timestamp DESC' LANGUAGE 'sql'" This could easily be pl/pgsql rather than SQL, and whilst the function body is OK as we discovered, what would happen in the above example if I added a column to pgadmin_table_cache? And if pgadmin_table_cache was (if possible) a view, then there is our problem. Unfortunately I can't test this right now but iff you can I'd be interested to hear what you find. > >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?). > > Triggers for 100% sure. We could automate the creation of > triggers for view > updating/deleting. > Highly wanted cool feature. Agreed, very cool. Added to the list.