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