Re: pg_advisor schema proof of concept

On Monday 22 March 2004 09:38, Fabien COELHO wrote:
> 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.

Had a look, and it seems good to me - pretty much what I was thinking of.

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

> 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?

Not sure portability is important, but using information_schema will 
presumably make it less likely that things will change between versions.

>     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?

Not sure it's worth the trouble to support 7.3, and anything below that is 
going to be a lot of work.

> (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?

Might be useful to be able to run all relevant tests against a single table, 
especially if we end up with lots of tests.

>   - should we use the COMMENT infrastructure for that?
>     I don't think so, but it could be done.

No - I think the separate table (advice_classification) is right.

> (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.

If plpgsql works OK, I say stick with it.

> (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.

Actually picked up a genuine mistake on one of my databases (mismatched 
pkey<=>fkey sizes). It's been worth the money already :-)

>   - what other "design" advices would be useful?
>     how to grade them (from info to error)?

Probably a matter of opinion. It'll give people something to argue about, 
anyway.

>     . "cross schema contraints/tables"?
>   - what about "performance" advices?

Well, I can see how you could examine the stats tables, but you'd probably 
need to be able to see the queries too.

>     what support functions are useful for those?
>   - others?
>
> (5) documentation
>   - should include design notes for new advices?

I think so.

>   - how to make things more modular?

We probably need a good list of tests before deciding what to make into 
"libraries"

>   - let us use comments about every view and columns...
>   - how to 'localise' pg_advisor?
>     a more general issue is how to 'localise' COMMENTS.

Not sure we want any of the text in the comments. Put all the messages/titles 
in a description table like you already have and people can translate the 
text in that file.

> (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

I think we're going to want a gborg project for developing/coordinating tests 
anyway. Having the schema included in contrib/ might help adoption, but so 
would pgadmin/phpgadmin. Any client-builders reading this? What do you think?

--  Richard Huxton Archonet Ltd