Re: pg_advisor schema proof of concept - Mailing list pgsql-hackers

Hello hackers,

> please find attached a quick proof of concept for a 'pg_advisor' schema.

I'm still pushing my agenda, despite lack of reaction on the list;-)
I had time this week-end to improve my current 'pg_advisor'
prototype schema.

This new version is now less a proof of concept and more a preliminary
implementation for discussion.


If you want to test on an existing database, the scripts only
creates an additionnal schema which may be removed quite simply.

(1) if necessary: sh> createlang -d mybase plpgsql

(2) sh> psql mybase < pg_catalog.sql

(3) use: psql mybase> SELECT * FROM xpg_catalog.??????;

(4) clean: psql mybase> DROP SCHEMA xpg_catalog CASCADE;   also if (1) sh> droplang -d mybase plpgsql


Some thoughts and questions about a "pg_advisor" schema design:

(1) should it use pg_catalog.* or information_schema.*? - is portability desirable? - my initial version is based on
pg_catalog.- information_schema could make it more portable?   well, I'm not sure it would do the job. I need to know
whatare the   system schemas, and it is likely that this would differ? what about   support functions? - should it be
compatiblewith old versions of postgreSQL?   if yes, what about support functions?
 

(2) advices should be associated: - a kind (design/model, performance... what else?) - a severity (info, notice,
warning,error... others? different?) - a title - an abstract - a description with examples - what about a "subject",
suchas "referencial integrity" or "index"...   if so, what could be the sujects? or maybe it is not needed? - should we
usethe COMMENT infrastructure for that?   I don't think so, but it could be done.
 

(3) needed support function - should be added to pg_catalog? implemented in C? - can we use plpgsql? SQL? others?   I
wouldtry to avoid anything other that pg_catalog and sql functions,   but I needed to add several functions that were
missing.

(4) advices implementations. - I implemented 11 basic "design" advices at the time.   I tested them with existing
databases,and I'm pretty happy   with the result: I had very few comments on "good" design/model,   and a lot of
warningsor notice on badly designed tables. - what other "design" advices would be useful?   how to grade them (from
infoto error)?   . "cross schema contraints/tables"? - what about "performance" advices?   what support functions are
usefulfor those? - others?
 

(5) documentation - should include design notes for new advices? - how to make things more modular? - let us use
commentsabout every view and columns... - how to 'localise' pg_advisor?   a more general issue is how to 'localise'
COMMENTS.

(6) possible inclusion in postgresql? - among other contributions? what about contrib/advisor? - added to template1 on
defaultinstallation?   maybe not for a first release? or yes? it is easier to communicate   about
 

Have a nice day,

-- 
Fabien Coelho - coelho@cri.ensmp.fr