Thread: Feature requests after crash

Feature requests after crash

From
Jean-Michel POURE
Date:
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

Re: Feature requests after crash

From
Dave Page
Date:
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)



Re: Feature requests after crash

From
Jean-Michel POURE
Date:
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

Re: Feature requests after crash

From
Jean-Michel POURE
Date:
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

Re: Feature requests after crash

From
Dave Page
Date:
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



Re: Feature requests after crash

From
Jean-Michel POURE
Date:
> 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

Re: Feature requests after crash

From
Dave Page
Date:
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