Re: pg_advisor schema proof of concept

Dear Richard,

> > (1) should it use pg_catalog.* or information_schema.*?
>
> Not sure portability is important, but using information_schema will
> presumably make it less likely that things will change between versions.

Another issue I found is that, although all the contents of
information_schema can be found in pg_catalog (as it derives from it!) not
all of pg_catalog may be found in information_schema...

In particular, for "performance" advices about indexes, operators and
casts, I'm not sure the all information is available in
information_schema, from the quick look I had about it.

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

That could be done quite easily, I've added a feature in my working
version about which schemas should be tested. It is easy to have
both a schema/table names and to be able to filter those of interest
to the user. I'll resend later an updated version for discussion.

> If plpgsql works OK, I say stick with it.

Hmmm. I'm not very happy with plpgsql, as I had an infinite recursion
which is partly due to plpgsql, partly to a very bad plan by the
optimiser, and partly to the fact that I want to do strange things with
tables querying about tables, so it goes bad quickly if the table starts
querying about itself to count it's own lines:-)

> > (4) advices implementations.
> > - I implemented 11 basic "design" advices at the time. [...]
>
> Actually picked up a genuine mistake on one of my databases (mismatched
> pkey<=>fkey sizes). It's been worth the money already :-)

That is a point.

The other question is how many "false positive".

That's why I put a grade, for things that are matters of opinions, as you
say... So that "controversial" advices can be downgraded to notice or info.

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

I was thinking along the kind of missing index Tom was arguing about
for RI checks, that may be helped if an appropriate index is available.

I'm not sure what could be done, even with the query, in the general case.
How to guess what index would help make a better plan? It depends
on the optimiser itself, on what kind of indexes could be built, and so
on. That's more human expect work than tool work.

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

Ok. but the system should be able to store several locales.
I guess it is possible to know about the current locale within
SQL, e.g. by querying lc_message in pg_settings for instance.

> > (6) possible inclusion in postgresql?
>
> I think we're going to want a gborg project for developing/coordinating
> tests anyway.

Why not.
How much work in the infrastructure ?
What would be the added value ? better communication ?

> Having the schema included in contrib/ might help adoption,
> but so would pgadmin/phpgadmin.

Sure.

Thanks a lot for your comments. I'll "submitted" an updated version later.

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