Re: Pseudo modification of views and triggers ( again)
Le Mercredi 20 Février 2002 09:17, Dave Page a écrit : > BTW, have you been following the discussion on pgsql-hackers about the > ongoing pl/pgSQL drop column code? Yes, sounds interesting but the code will probably be very close to ours. But, didn't we say no PL/pgSQL in our previous discussions? pgAdmin2 is intended for a large audience, so better if no PL/pgSQL is required. What I see in pgSchema is a powerfull pseudo "CREATE TABLE foo_new AS" clause to copy schema information with selected linked objects (indexes, columns, primary key, triggers, rules, etc...). We only have to move data between tables, drop the old table and rename the new one. This is more powerfull than hidding deleted columns AND it can be used to reorder columns. By the way, there seems to be anoyther way to modify a primary key : create a unique index foo_new, drop the primary key foo, and rename foo_new to foo. What do you think? Cheers, Jean-Michel
Re: ALTER VIEW
Le Mercredi 20 Février 2002 10:27, Dave Page a écrit :
> I just noticed in your update you forgot to alter the pgView.SQL function
> entry iLogEvent call to log the new parameters; It should look like:
>
> Public Property Get SQL(Optional PseudoAlter As Boolean) As String
> On Error GoTo Err_Handler
> objServer.iLogEvent "Entering " & App.Title & ":pgView.Property Get SQL(" &
> PseudoAlter & ")", etFullDebug
Hi Dave,
This will take some time before I fully understand the code. I just committed
VIEW RENAMING to CVS. Now, it is impossible to change view definition. I
looked for the error and did not find it.
I miss something. Could you please help me and check the code? I will not
commit anything related to views so that you can apply changes directly.
Cheers,
Jean-Michel
Re: To-Do List
Hi Dave, Altering view definition is still broken. Can you help me understand why? This is not a question of .dll as I recompiled everything a restarted the computer. > 1) Rollback in case of failure is not yet enabled (or tested?) The code is commented. I will uncomment after 2) is fixes. > 2) The new OID is not yet retrieved. I really don't know how to do it. Can you do it for me please? > I also noticed that following a 'Refresh Below' on the Views icon, the > comment for a view on which the definition has been changed vanishes. We > should probably invalidate the comment cache after the drop/recreate. OK, this can be easily done. /Jean-Michel
Re: plpgsql_call_handler
Le Mercredi 20 Février 2002 11:51, Dave Page a écrit : > No, it's user defined because the user/sysadmin adds it himself. plpgsql_call_handler is a sys object added by user input. It does not necassary mean it is a user object.. Users may think it is allowed to drop this object ***because*** it is in user area. There is a potential breakage problem of databases using PLpgSQL. On the converse, this object should never be removed in pgAdmin2 but withing SQL queries. This sounds safer to me. What do you think? Jean-Michel
plpgsql_call_handler
Hi Dave, I see plpgsql_call_handler is part of user objects in function list. Shouldn't it be part of system objects? Cheers, Jean-Michel
Trigger display bug
Dear Dave, CREATE TRIGGER "tg_term_source_iu" BEFORE DELETEINSERT OR UPDATE ON "translationforge_term_source" FOR EACH ROW EXECUTE PROCEDURE tg_term_source_iu(); The properties of the trigger appear to be OK. Only the SQL definition does not display well. I tried to understand the bug but did not succeed. Is this an hbx display bug? Cheers, Jean-Michel
Re: To-Do List
Le Mercredi 20 Février 2002 10:23, Dave Page a écrit : > If there's anything I've missed, please let me > know. IMHO, PostgreSQL installer under Cygwin is ***essential***. This is the next thing after table alter column. By the way, when do you think you will be ablt to fix view definition, just for info? Cheers, Jean-Michel
Re: PgAdmin II
> A fine upgrade to an already good product. It still has exactly the right features for DB admin, and no extraneous fluff. Thanks. Dave, Chris likes your pgAdmin2 very much. > Is there an option that would simulate the old Security Window from PgAdmin (1) ? I found the security wizard, but it doesn't quite match the ease of use of the old security window... What would you propose? Please send your feature proposal to [EMAIL PROTECTED] Regards, Jean-Michel POURE
Re: ALTER VIEW
> -Original Message-
> From: Jean-Michel POURE [mailto:[EMAIL PROTECTED]]
> Sent: 20 February 2002 09:37
> To: Dave Page
> Cc: [EMAIL PROTECTED]
> Subject: Re: ALTER VIEW
>
>
> Le Mercredi 20 Février 2002 10:27, Dave Page a écrit :
> > I just noticed in your update you forgot to alter the pgView.SQL
> > function entry iLogEvent call to log the new parameters; It should
> > look like:
> >
> > Public Property Get SQL(Optional PseudoAlter As Boolean) As
> String On
> > Error GoTo Err_Handler objServer.iLogEvent "Entering " &
> App.Title &
> > ":pgView.Property Get SQL(" & PseudoAlter & ")", etFullDebug
>
> Hi Dave,
>
> This will take some time before I fully understand the code.
> I just committed
> VIEW RENAMING to CVS. Now, it is impossible to change view
> definition. I
> looked for the error and did not find it.
>
> I miss something. Could you please help me and check the
> code? I will not
> commit anything related to views so that you can apply
> changes directly.
View renaming *looks* OK. I'll check it now. BTW, I already added view
updating to the changelog (thought you forgot - sorry) - I just tend to add
items at the bottom.
Regards, Dave.
Re: plpgsql_call_handler
> -Original Message- > From: Jean-Michel POURE [mailto:[EMAIL PROTECTED]] > Sent: 20 February 2002 10:57 > To: Dave Page; [EMAIL PROTECTED] > Subject: Re: [pgadmin-hackers] plpgsql_call_handler > > > Le Mercredi 20 Février 2002 11:51, Dave Page a écrit : > > No, it's user defined because the user/sysadmin adds it himself. > > plpgsql_call_handler is a sys object added by user input. > > It does not necassary mean it is a user object.. Users may > think it is > allowed to drop this object ***because*** it is in user area. > There is a > potential breakage problem of databases using PLpgSQL. The only way we could handle plpgsql_call_handler is by specifically checking for it's name. What happens if a sysadmin renames it? What about the perl, tcl or python handlers, or untrusted versions of these? More importantly, what about handlers that people add themselves for languages we don't (yet) know about? All that aside, a system object (as pgSchema understands it) is an object that is part of the database (i.e. in template0), or an object that is implicitly created as a result of another action (i.e. a primary key index). Sequences created by serial columns are *not* system object as we have no way of telling how they were created. Therefore plpgsql_call_handler is not a system object. > > On the converse, this object should never be removed in > pgAdmin2 but withing > SQL queries. Why? Only the owner or a superuser could drop it anyway so there's no security risk. Regards, Dave.
Re: To-Do List
> -Original Message- > From: Jean-Michel POURE [mailto:[EMAIL PROTECTED]] > Sent: 20 February 2002 13:23 > To: Dave Page > Cc: [EMAIL PROTECTED] > Subject: Re: [pgadmin-hackers] To-Do List > > > Hi Dave, > > Altering view definition is still broken. Can you help me > understand why? I just updated from CVS, checked for uncomitted changes (there weren't any) and recompiled using BuildAll.bat and everything works perfectly - rename and alter definition. > This is not a question of .dll as I recompiled everything a > restarted the > computer. Try a CVS Update. > > 1) Rollback in case of failure is not yet enabled (or tested?) > The code is commented. I will uncomment after 2) is fixes. OK. > > 2) The new OID is not yet retrieved. > I really don't know how to do it. Can you do it for me please? Not right now (sorry, today is rent increase letter day when we print 5000 rent increase notices for our tenants - it's a tad hectic right now), but it's pretty easy. You know how you re-get the view definition after updating it, to pick up the syntax changes that PostgreSQL makes to it? All you need to do is modify the query to get the OID from pg_class as well, and update lOID. > > I also noticed that following a 'Refresh Below' on the > Views icon, the > > comment for a view on which the definition has been changed > vanishes. > > We should probably invalidate the comment cache after the > > drop/recreate. > OK, this can be easily done. Ta. Regards, Dave.
Re: Changes committed
> -Original Message- > From: Jean-Michel POURE [mailto:[EMAIL PROTECTED]] > Sent: 20 February 2002 15:55 > To: Dave Page > Subject: Changes committed > > > Dear Dave, > > Changes are commited to CVS : bugfix, view OID retrieval, > view rollback. > Definitely looks like it's heading the right way :-). I've made some changes - please check & confirm you agree they're OK: 1) OID comes from pg_class.oid (relfilenode is the filename on disk which *may* be different). 2) Kill recordsets after use to be safe. 3) Only commit when we actually change something, not when just retrieving updated properties. I'm not overly convinced that the rollback is right though - it's already fallen over once for me. How about, creating the view with a random name, and then if that succeeds, drop the old one and rename the new? I've checked in my changes anyway. Regards, Dave.
Re: To-Do List
> -Original Message- > From: Jean-Michel POURE [mailto:[EMAIL PROTECTED]] > Sent: 20 February 2002 10:05 > To: Dave Page; [EMAIL PROTECTED] > Subject: Re: [pgadmin-hackers] To-Do List > > > Le Mercredi 20 Février 2002 10:23, Dave Page a écrit : > > If there's anything I've missed, please let me > > know. > > IMHO, PostgreSQL installer under Cygwin is ***essential***. It is essential to PostgreSQL, but not necessarily to pgAdmin. Certainly it will be very useful. > This is the next > thing after table alter column. By the way, when do you think > you will be > ablt to fix view definition, just for info? Fixed that (you forgot to reset the tag on txtProperties(0) at the end of frmView.Initialise. I'm just working on rename (which is slightly broken now :-( ) at the moment... /D
Re: Trigger display bug
> -Original Message- > From: Jean-Michel POURE [mailto:[EMAIL PROTECTED]] > Sent: 20 February 2002 11:44 > To: [EMAIL PROTECTED] > Cc: Dave Page > Subject: [pgadmin-hackers] Trigger display bug > > > Dear Dave, > > CREATE TRIGGER "tg_term_source_iu" BEFORE DELETEINSERT OR UPDATE ON > "translationforge_term_source" FOR EACH ROW EXECUTE PROCEDURE > tg_term_source_iu(); > > The properties of the trigger appear to be OK. Only the SQL > definition does > not display well. I tried to understand the bug but did not > succeed. Is this > an hbx display bug? No, pgSchema (not clearing a variable in a loop). Fixed in CVS now, thanks for the report. Regards, Dave.
Re: plpgsql_call_handler
> -Original Message- > From: Jean-Michel POURE [mailto:[EMAIL PROTECTED]] > Sent: 20 February 2002 10:36 > To: Dave Page; [EMAIL PROTECTED] > Subject: [pgadmin-hackers] plpgsql_call_handler > > > Hi Dave, > > I see plpgsql_call_handler is part of user objects in function list. > Shouldn't it be part of system objects? No, it's user defined because the user/sysadmin adds it himself. It is a slightly grey area though I'll admit. /D
To-Do List
I've just updated and re-organised the pgAdmin To-Do list. It's checked into CVS & updated on the website. If there's anything I've missed, please let me know. Regards, Dave.
Re: To-Do List
> -Original Message- > From: Jean-Michel POURE [mailto:[EMAIL PROTECTED]] > Sent: 20 February 2002 10:20 > To: Dave Page > Subject: Re: [pgadmin-hackers] To-Do List > > > Le Mercredi 20 Février 2002 11:13, Dave Page a écrit : > > Fixed that (you forgot to reset the tag on txtProperties(0) > at the end > > of frmView.Initialise. > > > > I'm just working on rename (which is slightly broken now > :-( ) at the > > moment... > > OK, thanks. Seems like I am a little kid breaking > everything... There's over 30,000 lines of code in there now. I'm not at all surprised that you need a while to get familiar with it. Anyhoo, rename wasn't broken - pgAdmin had started an instance of the old compiled pgSchema, rather than the version I thought I was running in the debugger. It works now anyway (and is checked in). As I said above, you had forgotten to reset the tags (which we use a 'dirty' flags) at the end of the Initialise sub. I can see 2 outstanding issues at the moment: 1) Rollback in case of failure is not yet enabled (or tested?) 2) The new OID is not yet retrieved. I also noticed that following a 'Refresh Below' on the Views icon, the comment for a view on which the definition has been changed vanishes. We should probably invalidate the comment cache after the drop/recreate. Regards, Dave.
Re: Pseudo modification of views and triggers (
> -Original Message- > From: Jean-Michel POURE [mailto:[EMAIL PROTECTED]] > Sent: 20 February 2002 09:21 > To: Dave Page > Cc: [EMAIL PROTECTED] > Subject: Re: [pgadmin-hackers] Pseudo modification of views > and triggers ( again) > > > Le Mercredi 20 Février 2002 09:17, Dave Page a écrit : > > BTW, have you been following the discussion on > pgsql-hackers about the > > ongoing pl/pgSQL drop column code? > > Yes, sounds interesting but the code will probably be very > close to ours. > But, didn't we say no PL/pgSQL in our previous discussions? > pgAdmin2 is > intended for a large audience, so better if no PL/pgSQL is required. Yes, you're right - it would just be useful to look at in case Zoltan/Christopher find any 'gotchas'. > What I see in pgSchema is a powerfull pseudo "CREATE TABLE > foo_new AS" > clause to copy schema information with selected linked > objects (indexes, > columns, primary key, triggers, rules, etc...). We only have > to move data > between tables, drop the old table and rename the new one. > > This is more powerfull than hidding deleted columns AND it > can be used to > reorder columns. Yes. Not to mention that the reverse engineered SQL you disliked so much will be invaluable in achieving this :-) > By the way, there seems to be anoyther way to modify a > primary key : create a > unique index foo_new, drop the primary key foo, and rename > foo_new to foo. That won't work, because pg_index.indisprimary won't be set on the new index definition. *I think* I remember seeing a posting on pgsql-hackers that mentioned there was an undocumented way to add a pkey. BTW, do your passwords work now? Regards, Dave.
Re: Pseudo modification of views and triggers (
> -Original Message- > From: Jean-Michel POURE [mailto:[EMAIL PROTECTED]] > Sent: 19 February 2002 21:19 > To: Dave Page > Cc: [EMAIL PROTECTED] > Subject: Re: [pgadmin-hackers] Pseudo modification of views > and triggers (again) > > > > I've just found out that views can be renamed using ALTER TABLE > > RENAME... iirc, VIEWs are referred to by OID from PL/SQL > > functions & other views, and by name from pl/pgsql functions. > OK. Since we can modify PL/pgSQL functions, it is no problem. > It is a pitty we don't have CREATE OR REPLACE VIEW anyway. Yes, it is. The beauty of the code we've written though, is that it will take minutes to convert to CREATE OR REPLACE when it is available. > > Sequences can also be renamed in this way. > Great. It also works for indexes. So interesting. Does it? I wasn't aware of that. > > Triggers could be implemented exactly as Views. Each property will > > need to be handled seperately, though in the future we > might look into > > merging multiple updates into one. > Can we rename a trigger with ALTER TABLE RENAME ? Doubtful. I believe ALTER TABLE works for sequences & views because they live in pg_class. I guess ALTER TABLE RENAME just doesn't check or care about relkind. > > The Name property must be handled differently (as it would be for > > Views & > > Sequences) in the Collection class. This has already been > done for tables, > > so there is some example code to steal. > You already did 99% of the job and I wron't complain about it. > > The next big thing now is table pseudo-modification. We > should be able to > provide a very nice solution. Hopefully, but let's get Views & Triggers nailed first. BTW, have you been following the discussion on pgsql-hackers about the ongoing pl/pgSQL drop column code? Regards, Dave.
