Thread: Feature requests after crash
Dear Dave, When deleting pgadmin_* tables and functions, I dropped by error plpgsql_call_handler which standed right after pgadmin in alphabetical order... A GUI like pgAdmin2 is like a machine gun. Bang Bang. I am shot down. IMHO, dropping plpgsql_call_handler function is a total nonsense as : - it breaks the language definition, - it breaks all existing PLpgSQL functions, - it cannot be recreated (or with different OID which is nonsense). A language should only be dropped in the language tab. Waiting for your approval to move it to system. In the same field, we should prevent users from dropping functions used in: - other functions, - views, - triggers. Could we define read-only clauses in pgSchema objects to automatically query ->provides objects... ->requires objects... Examples : a) trigger foo -> requires -> function bar b) function son -> requires -> father, mother (two functions) c) function father -> provides -> son = it cannot be dropped d) table foo -> provides -> view bar = table cannot be dropped e) Language plpgsql -> provides -> functions foo, bar, etc -> it cannot be dropped requires and provides are read-only functions returning a string. When object->provides is null, the object can be dropped, otherwise it cannot. It should be an option like row count. It is not like a dependency chain but rather a security mechanism. Shall it be added to the to-do-list? It does not seem too difficult to add. What do you think my friend? Cheers, Jean-Michel
If you wish to add code to prevent dropping of functions that are *currently* in use as a language handler, then that's
finewith me.
As for the dependencies, you will need to write parsers for at least the following languages to do what you propose:
SQL
pl/SQL
pl/pgSQL
pl/tcl
pl/perl
pl/sh
pl/python
pl/java (when it's written)
Without these parsers, we will not be able to track dependencies within functions or views.
Triggers should be easy enough though, but without views/functions (and tables/indexes/rules which can also depend on
functions)there is little point.
I think the only practical answer is to wait for a pg_dependencies table in 7.3.
We could prevent dropping of languages that are in use though, as well as handlers.
Please finish the ALTER VIEW code before looking at this though - it's pretty much there now.
Good night,
Dave.
_____________Original message ____________
Subject: [pgadmin-hackers] Feature requests after crash
Sender: Jean-Michel POURE <jm.poure@freesurf.fr>
Date: Thu, 21 Feb 2002 22:22:56 +0000
Dear Dave,
When deleting pgadmin_* tables and functions, I dropped by error
plpgsql_call_handler which standed right after pgadmin in alphabetical
order... A GUI like pgAdmin2 is like a machine gun.
Bang Bang. I am shot down.
IMHO, dropping plpgsql_call_handler function is a total nonsense as :
- it breaks the language definition,
- it breaks all existing PLpgSQL functions,
- it cannot be recreated (or with different OID which is nonsense).
A language should only be dropped in the language tab. Waiting for your
approval to move it to system.
In the same field, we should prevent users from dropping functions used in:
- other functions,
- views,
- triggers.
Could we define read-only clauses in pgSchema objects to automatically query
->provides objects...
->requires objects...
Examples :
a) trigger foo -> requires -> function bar
b) function son -> requires -> father, mother (two functions)
c) function father -> provides -> son = it cannot be dropped
d) table foo -> provides -> view bar = table cannot be dropped
e) Language plpgsql -> provides -> functions foo, bar, etc -> it cannot be
dropped
requires and provides are read-only functions returning a string. When
object->provides is null, the object can be dropped, otherwise it cannot. It
should be an option like row count.
It is not like a dependency chain but rather a security mechanism. Shall it
be added to the to-do-list? It does not seem too difficult to add. What do
you think my friend?
Cheers,
Jean-Michel
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Dear Dave, pseudo ALTER VIEW is commited to CVS. I changed a little bit pgView->SQL which can be used for any SQL generation. This will make it easier to maintain SQL when PostgreSQL evolves. What do you think? Cheers, Jean-Michel
Le Jeudi 21 Février 2002 23:39, Dave Page a écrit : > Without these parsers, we will not be able to track dependencies within > functions or views. Why do we need parsers? pgAdmin I had very simple queries to track dependencies (ILIKE '%function_foo%' clause). This suffice to build a provides clause is each object. For example, if "function_foo" is used in "view_bar" and "function_bar": function_foo->provides="function:function_bar;view:view_bar" view_bar->requires="function:function_foo"; function_bar->requires="function:function_foo"; If you prefer, provides = child, requires = father. I agree this is not as powerfull as a recursive dependency table, but this suffice to offer 99% security to users. The purpose is not to track n-levels of dependecies but just visualize 1 level for security reasons. So why not implement it in pgSchema? Cheers, Jean-Michel
ILIKE will not work correctly in *many* situations. Consider the following functions: my_sum(int4, int4) RETURNS int4 my_sum(int8, int8) RETURNS int8 In a function, a line of code reads: $var = my_sum($1, $2); How do we know which function this is without parsing the code to figure out what $1 & $2 are? This is complicated more when a pl may contain code that matches the ILIKE, for example if there were a builtin python functioncalled my_sum. The pl/python handler could deal with that, but a simple ILIKE would give a false positive. ILIKE will also fail with: my_sum(int4, int4) "My_Sum"(int4, int4) My_Sum(int4, int4) all of which are valid, but not necessarily equivalent. If you can come up with a solution that overcomes these and any similar problems, great, but I do not want to release codethat cannot be relied upon without having lot's of caveats about how you can use it. Regards, Dave. _____________Original message ____________ Subject: Re: [pgadmin-hackers] Feature requests after crash Sender: Jean-Michel POURE <jm.poure@freesurf.fr> Date: Fri, 22 Feb 2002 13:11:47 +0000 Le Jeudi 21 Février 2002 23:39, Dave Page a écrit : > Without these parsers, we will not be able to track dependencies within > functions or views. Why do we need parsers? pgAdmin I had very simple queries to track dependencies (ILIKE '%function_foo%' clause). This suffice to build a provides clause is each object. For example, if "function_foo" is used in "view_bar" and "function_bar": function_foo->provides="function:function_bar;view:view_bar" view_bar->requires="function:function_foo"; function_bar->requires="function:function_foo"; If you prefer, provides = child, requires = father. I agree this is not as powerfull as a recursive dependency table, but this suffice to offer 99% security to users. The purpose is not to track n-levels of dependecies but just visualize 1 level for security reasons. So why not implement it in pgSchema? Cheers, Jean-Michel
> my_sum(int4, int4) > "My_Sum"(int4, int4) > My_Sum(int4, int4) Yes, but such a redundancy would be detected, which is ***better*** than nothing. We can then issue a warning instead of "blocking everything". Furthermore, many names are not case sensitive because of SQL (tables, views), which makes ILIKE the perfect solution to track dependencies : tables<->views, views<->functions, tables<->functions, etc... + we are only looking for one-level depencies. This is quite easy to implement using pgSchema... I am testing ALTER TRIGGER wich will be available tonight. Cheers, Jean-Michel
Comments prefixed with DP _____________Original message ____________ Subject: Re: [pgadmin-hackers] Feature requests after crash Sender: Jean-Michel POURE <jm.poure@freesurf.fr> Date: Fri, 22 Feb 2002 19:24:14 +0000 > my_sum(int4, int4) > "My_Sum"(int4, int4) > My_Sum(int4, int4) Yes, but such a redundancy would be detected, which is ***better*** than nothing. We can then issue a warning instead of "blocking everything". DP: We can't issue warnings only when the code thinks we should unless we're confident it'll be right all the time. Everytime it gives a false negative there'll be a user swearing at the program because it didn't warn him of the dependencyon the object he just dropped. At least now the user knows it's *always* his/her responsibility. DP: That aside, the overhead just doing such a rudimentry check would huge. Before dropping any function you would have tocheck: Every other function Every view Every trigger Every rule Every index Every type Every operator Every aggregate That sort of overhead is just ridiculous. Furthermore, many names are not case sensitive because of SQL (tables, views), which makes ILIKE the perfect solution to track dependencies : tables<->views, views<->functions, tables<->functions, etc... DP: Huh? PostgreSQL identifiers *are* case sensitive if quoted (which they are in pgAdmin) - I beleive this is true for *all*object types. + we are only looking for one-level depencies. DP: Yes, but I don't think that makes any difference. I think we should add a warning before dropping language handlers and languages that have any defined functions and leaveit at that. After all, Explorer won't stop you deleting kernel32.dll and the Gnome file manager won't stop you deleting/vmlinuz - we have to draw the line somewhere and leave things up to the user. It's not like we don't give them awarning first at all. Regards, Dave