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 details2. stats info3. query stats
(correlatedwith 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 C2. 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