Re: Further thoughts about warning for costly FK checks

On Thursday 18 March 2004 10:18, Fabien COELHO wrote:
> On Wed, 17 Mar 2004, Tom Lane wrote:

> > though I'd be worried about the portability price paid to have one. Or
> > are you concerned about whether a GUI could invoke it? I don't see why
> > not --- the GUIs don't reimplement pg_dump, do they?

Actually Tom, I think they do (where they have an export facility). How would
you run pg_dump on a remote machine? (well, without building an RPC
mechanism)

> Yes, but pg_dump is more like a blackbox, the interface does not need
> to look at the generated output and interpret it, or in a very simple
> way to check whether it failed.
>
> > > Or separate only mean that it is a "separate" function of the backend
> > > that can be triggered by calling existing functions such as "EXPLAIN"
> > > or "ANALYZE" or new ones such as "CHECK" or "ADVICE" or whatever.
> >
> > That still leaves us in the situation where only people who are capable
> > of doing backend programming can help. I hope that a standalone program
> > would be more understandable and could attract developers who wouldn't
> > touch the backend.

Well - let's look at what info we might need for the ultimate pg_advisor:
1. schema details
2. stats info
3. query stats (correlated with existing stats, so we know what is causing
table-scans)
4. query plans
That's the same information as I need to make decisions, so it must be
sufficient. We already have 1+2, in the system tables/information_schema and
stats schema. The others could be generated if required.

We want two main features:
1. Add new tests without writing C
2. Allow a number of clients (pg_advisor/psql?/pgadmin/phppgadmin/RHdbadmin
etc) to run the tests/process results.

So - have something like:
Core => test domains => tests
Core is responsible for running the right tests (based on user parameters)
Test domains provide one or more views/tablefuncs that individual tests use to
query against.
Tests consist of a query, an assertion, message and keywords to match against
Results are returned as SQL - client formats it how they like.

Anyone can add tests by inserting rows into pg_advisor_tests (or whatever).
Most test-domains can be built using raw SQL/plpgsql (don't want to say all -
haven't thought it through yet).

So - a simple test might be defined as:
INSERT INTO pg_advisor_tests
(pat_id, pat_title,
pat_description,
pat_query,
pat_msg)
VALUES ('NAMES0001','Mixed-case column names',
'You appear to be using mixed-case column-names. See ADVISOR-HINT #32 for why
you need to be careful',
'SELECT schema_name,table_name,column_name FROM colname_test_domain_view
WHERE lower(column_name) <> column_name'
'Mixed-case column: %.%.%'
);

> I think that such tool would generate "WARNING, NOTICE", HINT, CONTEXT
> just as the be does at the time, so I don't think that it is that
> confining. Also, some new fields could be added to improve reports,
> if they are really necessary, but I'm not even that sure that any is
> needed.

Different levels of message sounds sensible to me, though I'm not sure what to
call them.

--
Richard Huxton
Archonet Ltd