Re: pg_advisor schema proof of concept

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 what are the
system schemas, and it is likely that this would differ? what about
support functions?
- should it be compatible with 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", such as "referencial integrity" or "index"...
if so, what could be the sujects? or maybe it is not needed?
- should we use the 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 would try 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 warnings or notice on badly designed tables.
- what other "design" advices would be useful?
how to grade them (from info to error)?
. "cross schema contraints/tables"?
- what about "performance" advices?
what support functions are useful for those?
- others?

(5) documentation
- should include design notes for new advices?
- how to make things more modular?
- let us use comments about 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 default installation?
maybe not for a first release? or yes? it is easier to communicate
about

Have a nice day,

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr