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.
This new version is now less a proof of concept and more a preliminary
implementation for discussion.
If you want to test on an existing database, the scripts only
creates an additionnal schema which may be removed quite simply.
(1) if necessary: sh> createlang -d mybase plpgsql
(2) sh> psql mybase < pg_catalog.sql
(3) use: psql mybase> SELECT * FROM xpg_catalog.??????;
(4) clean: psql mybase> DROP SCHEMA xpg_catalog CASCADE;
also if (1) sh> droplang -d mybase plpgsql
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?
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?
(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?
- should we use the COMMENT infrastructure for that?
I don't think so, but it could be done.
(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.
(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.
- what other "design" advices would be useful?
how to grade them (from info to error)?
. "cross schema contraints/tables"?
- what about "performance" advices?
what support functions are useful for those?
- others?
(5) documentation
- should include design notes for new advices?
- how to make things more modular?
- let us use comments about every view and columns...
- how to 'localise' pg_advisor?
a more general issue is how to 'localise' COMMENTS.
(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
Have a nice day,
--
Fabien Coelho - [EMAIL PROTECTED]--
-- $Id: pg_advisor.sql,v 1.69 2004/03/21 17:38:52 coelho Exp $
--
-- pg_advisor maybe future system schema?
--
-- at the time it is 'xpg_advisor',
-- as the 'pg_' prefix is reserved for system schemas.
--
-- how to localise comments? descriptions?
-- should this use 'information_schema' instead of 'pg_catalog'?
-- more explicit comments about views? with examples?
-- add comments to all columns?
-- give a grade/mark? ;-)
--
DROP SCHEMA xpg_advisor CASCADE;
CREATE SCHEMA xpg_advisor;
COMMENT ON SCHEMA xpg_advisor IS
'Various advices about database design or performance.
This views check various common \'maybe\' common faults using
available information from the table descriptions. It deals
primarily with constraints (foreign key, primary key, unique)
and attempts to detect inconsistancies.'
;
SET search_path TO xpg_advisor,pg_catalog;
-------------------------------------------------------------------------------
--
-- UTILS
--
CREATE TABLE advice_kind(
kid INTEGER PRIMARY KEY,
description TEXT UNIQUE NOT NULL
);
COPY advice_kind(kid,description) FROM STDIN;
1 misc
2 design
3 performance
\.
COMMENT ON TABLE advice_kind IS
'kind of advice such as \'performance\' or \'design\''
;
CREATE TABLE advice_severity(
sid INTEGER PRIMARY KEY,
description TEXT UNIQUE NOT NULL
);
COPY advice_severity(sid,description) FROM STDIN;
1 info
2 notice
3 warning
4 error
\.
COMMENT ON TABLE advice_severity IS
'severity description of an advice, from \'info\' to \'serious\''
;
CREATE TABLE advice_classification(
advice OID PRIMARY KEY, -- REFERENCES pg_class(oid),
-- the following denormalisation is NECESSARY at the time.
name TEXT UNIQUE NOT NULL, -- copy of pg_class.relname of above
kid INTEGER NOT NULL REFERENCES advice_kind,
sid INTEGER NOT NULL REFERENCES advice_severity,
title TEXT UNIQUE NOT NULL,
abstract TEXT UNIQUE NOT NULL,
description TEXT
);
COMMENT ON TABLE advice_classification IS
'advice classification such as kind, severity...'
;
--
-- may be somewhere?
--
CREATE OR REPLACE FUNCTION is_system_schema(TEXT) RETURNS BOOLEAN AS '
SELECT $1 LIKE \'pg_%\' OR $1 LIKE \'xpg_%\' OR $1 = \'information_schema\';
' LANGUAGE 'SQL';
COMMENT ON FUNCTION is_system_schema(TEXT) IS '
tells whether a schema name is system or user.
includes extensions as xpg_* or information_schema.
';
-- test:
-- SELECT is_system_schema('pg_catalog');
-- SELECT is_system_schema('public');
--
-- missing array_index function. quick PL/pgSQL fix.
--
CREATE OR REPLACE FUNCTION array_index(anyarray, anyelement)
RETURNS INTEGER AS '
DECLARE
tab ALIAS FOR $1;
item ALIAS FOR $2;
i INTEGER;
BEGIN
FOR i IN 1..array_upper(tab, 1) LOOP
IF tab[i] = item THEN
RETURN i;
END IF;
END LOOP;
-- item not found in tab
RETURN NULL;
END;'
LANGUAGE plpgsql;
COMMENT ON FUNCTION array_index(anyarray, anyelement) IS
'missing array_index function... should be already there!'
;
-- test whether two array contents are set-equal.
-- [1,1,2] and [2,1,2] are consided equal.
CREATE OR REPLACE FUNCTION array_ceq(anyarray, anyarray)
RETURNS BOOLEAN AS '
DECLARE
tab1 ALIAS FOR $1;
tab2 ALIAS FOR $2;
i INTEGER;
BEGIN
IF array_upper(tab1, 1) != array_upper(tab2, 1) THEN
RETURN FALSE;
END IF;
FOR i IN 1..array_upper(tab1, 1) LOOP
IF NOT tab1[i] = ANY (tab2) THEN
RETURN FALSE;
END IF;
IF NOT tab2[i] = ANY (tab1) THEN
RETURN FALSE;
END IF;
END LOOP;
RETURN TRUE;
END;
' LANGUAGE 'plpgsql';
COMMENT ON FUNCTION array_ceq(anyarray, anyarray) IS
'equality of content of two arrays in any order'
;
--
-- how to SELECT COUNT(*) FROM [the view of which I have the oid]?
-- here is the missing function, that may be somewhere I guess?
--
CREATE TABLE counter(call OID);
CREATE OR REPLACE FUNCTION count_tuples(TEXT)
RETURNS INTEGER AS '
DECLARE
tablename ALIAS FOR $1;
res RECORD;
BEGIN
-- should prevent recursion? how?
FOR res IN EXECUTE \'SELECT COUNT(*) AS n FROM xpg_advisor.\' || tablename
LOOP
RETURN res.n;
END LOOP;
END;'
LANGUAGE 'plpgsql';
COMMENT ON FUNCTION count_tuples(OID) IS
'meta function for meta queries...
- count number of rows in table or view, given its oid.
- beware, this function is dangerous (costly, infinite recursion).'
;
-- ??? how to advise postgres optimiser that count_tuples is very expensive?
--
-- add advice classification
--
CREATE OR REPLACE FUNCTION
set_advice_info(TEXT, INTEGER, INTEGER, TEXT, TEXT, TEXT)
RETURNS INTEGER AS '
INSERT INTO
advice_classification(advice, name, kid, sid, title, abstract, description)
SELECT c.oid, $1, $2, $3, $4, $5, $6
FROM pg_namespace AS n JOIN pg_class AS c ON (c.relnamespace=n.oid)
WHERE n.nspname=current_schema() AND c.relname=$1;
SELECT 1;'
LANGUAGE 'SQL';
COMMENT ON FUNCTION
set_advice_info(TEXT, INTEGER, INTEGER, TEXT, TEXT, TEXT) IS
'add an advice information to advice_classification table:
- view name of the advice
- kind of advice number (see advice_kind)
- severity of advice number (see advice_severity)
- advice title (a short sentence)
- advice abstract (a paragraph)
- advice description (a longer explanation)'
;
-------------------------------------------------------------------------------
--
-- DESIGN ADVICES views
--
-- the comment should justify really why these advices are given.
-- I'm not even sure they are good advices;-)
--
-- tables without primary keys
--
CREATE VIEW da_tables_without_primary_key AS
SELECT
n.nspname AS SchemaName,
c.relname AS TableName
FROM pg_class AS c
JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
WHERE
-- no comments about system catalogs.
NOT is_system_schema(n.nspname)
-- only tables
AND c.relkind = 'r'
-- no primary key
AND NOT c.relhaspkey
ORDER BY SchemaName ASC, TableName ASC;
COMMENT ON VIEW da_tables_without_primary_key IS
'advice: all tables without a declared primary key'
;
SELECT set_advice_info('da_tables_without_primary_key', 2, 4,
'no primary key',
'a primary key on every table is better design...
- what is the point of having a table, if you do not have
and identifiable concept to put inside?
- relational algebra is build upon the set theory.
relations are sets. sets only store distinct elements.
- maybe some UNIQUE NOT NULL attribute should be the primary key?',
NULL
);
-- SELECT * FROM da_tables_without_primary_key;
--
-- tables with composite primary keys?
-- or should it be declared only if foreigns??
-- or if partial???
-- or not if parts are foreign keys?
-- or it is ok anyway?
--
CREATE VIEW da_tables_with_composite_primary_key AS
SELECT
n.nspname AS SchemaName,
c.relname AS TableName
FROM pg_class AS c
JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
JOIN pg_constraint AS o ON (o.conrelid=c.oid)
WHERE
-- no comments about system catalogs.
NOT is_system_schema(n.nspname)
-- only tables
AND c.relkind = 'r'
-- with a primary key
AND c.relhaspkey
-- the primary key constraint
AND contype = 'p'
-- more than 1 element in conkey
AND array_upper(o.conkey, 1)>1
-- partial primary key???
AND relnatts>array_upper(o.conkey, 1)
ORDER BY SchemaName ASC, TableName ASC;
COMMENT ON VIEW da_tables_with_composite_primary_key IS
'advice: all tables with composite primary keys';
SELECT set_advice_info('da_tables_with_composite_primary_key', 2, 1,
'composite partial primary key',
'- I prefer a simple primary key, without semantics...
because foreign keys if any will be simpler.',
NULL
);
-- SELECT * FROM da_tables_with_composite_primary_key;
--
-- composite foreign key...
--
CREATE VIEW da_composite_foreign_key AS
SELECT
n.nspname AS SchemaName,
c.relname AS TableName,
o.conname AS ConstraintName
FROM pg_class AS c
JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
JOIN pg_constraint AS o ON (o.conrelid=c.oid)
WHERE
-- no comments about system catalogs.
NOT is_system_schema(n.nspname)
-- only tables
AND c.relkind = 'r'
-- the foreign key constraint
AND contype = 'f'
-- more than 1 element in confkey
AND array_upper(o.confkey, 1)>1
ORDER BY SchemaName ASC, TableName ASC, ConstraintName ASC;
COMMENT ON VIEW da_composite_foreign_key IS
'advice: composite foreign key'
;
SELECT set_advice_info('da_composite_foreign_key', 2, 2,
'composite foreign key',
'simple foreign keys are better design...
- the foreign key replicates the information that some
referenced key components belong together.
- on updates of the referenced keys,
all corresponding foreign keys must be updated.',
NULL
);
-- SELECT * FROM da_composite_foreign_key;
--
-- I like simple INTEGER PRIMARY KEY better
--
CREATE VIEW da_non_integer_primary_key AS
SELECT -- DISTINCT
n.nspname AS SchemaName,
t.relname AS TableName,
a.attname AS AttName,
format_type(a.atttypid, a.atttypmod) AS TypeName
-- c.conname AS ConstraintName
FROM pg_namespace AS n
JOIN pg_class AS t ON (t.relnamespace=n.oid)
JOIN pg_constraint AS c ON (c.conrelid=t.oid)
JOIN pg_attribute AS a ON (a.attnum = ANY (c.conkey) AND a.attrelid=t.oid)
JOIN pg_type AS typ ON (a.atttypid=typ.oid)
JOIN pg_namespace AS ntyp ON (typ.typnamespace=ntyp.oid)
WHERE
-- no comments about system schema
NOT is_system_schema(n.nspname)
-- only tables (redundant?)
AND t.relkind = 'r'
-- primary key contraint
AND c.contype = 'p'
-- the key type is not an integer? or a short type??
AND ntyp.nspname='pg_catalog'
AND NOT typ.typname = ANY (ARRAY['int2', 'int4', 'int8', 'oid'])
ORDER BY SchemaName ASC, TableName ASC;
COMMENT ON VIEW da_non_integer_primary_key IS
'advice: non integer primary key'
;
SELECT set_advice_info('da_non_integer_primary_key', 2, 1,
'primary key is not an integer',
'not really a problem, but:
- I like semantics integer primary keys without application semantics...
- it makes updates easier, as the number will not need to be changed,
hence matching foreign keys won\'t need to be changed either.',
NULL
);
--
-- unique constraint with nullable attribute?
--
CREATE VIEW da_unique_nullable_attribute AS
SELECT
nc.nspname AS SchemaName,
c.relname AS TableName,
a.attname AS AttName,
no.nspname AS ConstraintSchemaName,
o.conname AS ConstraintName
FROM pg_class AS c
JOIN pg_namespace AS nc ON (c.relnamespace=nc.oid)
JOIN pg_constraint AS o ON (o.conrelid=c.oid)
JOIN pg_namespace AS no ON (o.connamespace=no.oid)
JOIN pg_attribute AS a ON (a.attrelid=c.oid)
WHERE
-- no comments about system catalogs.
NOT is_system_schema(nc.nspname) AND NOT is_system_schema(no.nspname)
-- only tables (redundant, constraint are necessary on tables?)
AND c.relkind = 'r'
-- it is a 'unique' constraint
AND contype = 'u'
-- attribute is in the constraint
AND a.attnum = ANY (o.conkey)
-- attribute is nullable
AND NOT a.attnotnull
ORDER BY SchemaName ASC, TableName ASC, AttName ASC,
ConstraintSchemaName ASC, ConstraintName ASC;
COMMENT ON VIEW da_unique_nullable_attribute IS
'advice: unique constraint with nullable attribute'
;
SELECT set_advice_info('da_unique_nullable_attribute', 2, 2,
'unique constraint with nullable attribute',
'maybe a bad design... either
- the NULL value has a particular semantic for an attribute
- or you forgot a NOT NULL declaration',
NULL
);
-- SELECT * FROM da_unique_nullable_attribute;
CREATE VIEW da_nullable_attribute AS
SELECT
n.nspname AS SchemaName,
c.relname AS TableName,
a.attname AS AttName
FROM pg_class AS c
JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
JOIN pg_attribute AS a ON (a.attrelid=c.oid)
WHERE
-- no comments about system catalogs.
NOT is_system_schema(n.nspname)
-- only tables (redundant, constraint are necessary on tables?)
AND c.relkind = 'r'
-- attribute is nullable
AND NOT a.attnotnull
ORDER BY SchemaName ASC, TableName ASC, AttName ASC;
COMMENT ON VIEW da_nullable_attribute IS
'advice: nullable attributes'
;
SELECT set_advice_info('da_nullable_attribute', 2, 1,
'nullable attribute',
'- most of the time attributes should be NOT NULL
- but it is perfectly right to have a NULL value of missing fields',
NULL
);
--
-- internal foreign key constraint...
--
CREATE VIEW in_foreign_key_contraint AS
SELECT
-- constraint
c.oid AS constraint,
-- referencing table
c.conrelid AS crel,
cca.attname AS ccol,
cca.atttypid AS ctyp,
cca.atttypmod AS cmod,
cca.attlen AS clen,
-- referenced table, foreign part
c.confrelid AS frel,
fka.attname AS fcol,
fka.atttypid AS ftyp,
fka.atttypmod AS fmod,
fka.attlen AS flen,
array_index(c.confkey, fka.attnum) AS component
FROM pg_constraint AS c
JOIN pg_attribute AS cca ON (c.conrelid=cca.attrelid)
JOIN pg_attribute AS fka ON (c.confrelid=fka.attrelid)
WHERE
-- foreign key constraint
c.contype='f'
-- column attribute in constraint
AND cca.attnum = ANY (c.conkey)
-- foreign key attribute
AND fka.attnum = ANY (c.confkey)
-- matching key component
AND array_index(c.confkey, fka.attnum)=array_index(c.conkey, cca.attnum)
;
-- verbose view
CREATE VIEW in_verbose_foreign_key_contraint AS
SELECT
-- ready for pretty printing
nc.nspname AS SchemaName,
cc.relname AS TableName,
fkc.ccol AS AttName,
format_type(fkc.ctyp, fkc.cmod) AS ColumnType,
nf.nspname AS FSchemaName,
cf.relname AS FName,
fkc.fcol AS FAttName,
format_type(fkc.ftyp, fkc.fmod) AS ForeignType,
-- row stuff for selection
fkc.*
FROM in_foreign_key_contraint AS fkc
JOIN pg_class AS cc ON (fkc.crel=cc.oid)
JOIN pg_namespace AS nc ON (cc.relnamespace=nc.oid)
JOIN pg_class AS cf ON (fkc.frel=cf.oid)
JOIN pg_namespace AS nf ON (cf.relnamespace=nf.oid)
WHERE
-- no comments about system catalogs.
NOT is_system_schema(nc.nspname) AND NOT is_system_schema(nf.nspname)
-- only tables (redundant? only tables can have ri contraints?)
AND cc.relkind='r' AND cf.relkind='r';
--
-- foreign keys which do not exactly match their target key types?
--
CREATE VIEW da_foreign_key_type_dont_match AS
SELECT SchemaName, TableName, AttName, ColumnType,
FSchemaName, FName, FAttName, ForeignType,
Component
FROM in_verbose_foreign_key_contraint
WHERE ctyp!=ftyp -- non matching type
ORDER BY SchemaName ASC, TableName ASC,
FSchemaName ASC, FName ASC, Component ASC;
COMMENT ON VIEW da_foreign_key_type_dont_match IS
'advice: non matching foreign key types'
;
SELECT set_advice_info('da_foreign_key_type_dont_match', 2, 3,
'non exactly matching foreign key types',
'maybe a bad design...
- do you have any argument why the type shouldn\'t be the same?',
NULL
);
-- SELECT * FROM da_foreign_key_type_dont_match;
--
-- foreign keys which do match their target types but with different sizes?
--
CREATE VIEW da_foreign_key_type_match_but_size AS
SELECT SchemaName, TableName, AttName, ColumnType,
FSchemaName, FName, FAttName, ForeignType,
Component
FROM in_verbose_foreign_key_contraint
WHERE ctyp=ftyp -- matching type
-- but non matching variable length
AND clen=-1 AND flen=-1 AND cmod!=fmod
ORDER BY SchemaName ASC, TableName ASC,
FSchemaName ASC, FName ASC, Component ASC;
COMMENT ON VIEW da_foreign_key_type_match_but_size IS
'advice: non matching size in foreign key types'
;
SELECT set_advice_info('da_foreign_key_type_match_but_size', 2, 3,
'non exactly matching foreign key type sizes',
'maybe a bad design...
- do you have any argument why the type size shouldn\'t be the same?
- as a foreign key must match its referenced key,
all matching values will necessarily be the same.
. if the foreign key is smaller, some referenced key cannot be matched.
. if the foreign key is larger, the added part cannot be used.',
NULL
);
--
-- tables with large primary keys (such as TEXT, VARCHAR(64)...)?
-- if there are foreign keys that use them...
--
CREATE VIEW da_large_primary_key_with_foreigns AS
SELECT SchemaName, TableName, AttName, ColumnType,
FSchemaName, FName, FAttName, ForeignType,
Component
FROM in_verbose_foreign_key_contraint
WHERE ctyp=ftyp -- matching type
-- variable length
AND clen=-1 AND flen=-1
-- matching (otherwise already in previous table)
AND cmod=fmod
-- but large (16 bytes) or unbounded (e.g. TEXT)
AND (cmod>16 OR cmod=-1)
ORDER BY SchemaName ASC, TableName ASC,
FSchemaName ASC, FName ASC, Component ASC;
COMMENT ON VIEW da_large_primary_key_with_foreigns IS
'advice: large primary key with foreign keys'
;
SELECT set_advice_info('da_large_primary_key_with_foreigns', 2, 2,
'large key types for foreign keys',
'maybe a bad design...
- if the type is large, it means that there are some information inside,
and this information is replicated in foreign keys.
- if the key is meaningful and must be updated, all foreign keys will
have to be updated as well.
- maybe you should rather have an simple primary key,
and a UNIQUE NOT NULL field?',
NULL
);
--
-- other design advices?
--
CREATE VIEW da_foreign_key_not_to_primary AS
SELECT
nf.nspname AS FSchemaName,
clf.relname AS FTableName,
np.nspname AS SchemaName,
clp.relname AS TableName
FROM
-- foreign key part
pg_namespace AS nf
JOIN pg_class AS clf ON (clf.relnamespace=nf.oid)
JOIN pg_constraint AS cf ON (cf.conrelid=clf.oid)
JOIN
-- primary key part
pg_namespace AS np
JOIN pg_class AS clp ON (clp.relnamespace=np.oid)
JOIN pg_constraint AS cp ON (cp.conrelid=clp.oid)
ON (cf.confrelid = cp.conrelid)
WHERE
-- no comments about system tables
NOT is_system_schema(nf.nspname)
AND NOT is_system_schema(np.nspname)
-- tables (redundant? constraints only on tables?)
AND clp.relkind='r' AND clf.relkind='r'
-- foreign constraint and primary constraint
AND cf.contype='f' AND cp.contype='p'
-- that no not match
AND NOT array_ceq(cp.conkey, cf.confkey)
ORDER BY FSchemaName ASC, FTableName ASC, SchemaName ASC, TableName ASC
;
COMMENT ON VIEW da_foreign_key_not_to_primary IS
'advice: foreign key does not reference primary key'
;
SELECT set_advice_info('da_foreign_key_not_to_primary', 2, 1,
'foreign key not to a primary key',
'not really a problem
- but why not to the primary key of the target?',
NULL
);
-- SELECT * FROM da_foreign_key_not_to_primary;
CREATE VIEW in_tables AS
SELECT DISTINCT n.nspname, t.relname, t.oid
FROM pg_namespace AS n JOIN pg_class AS t ON (t.relnamespace=n.oid)
WHERE NOT is_system_schema(n.nspname) AND t.relkind='r';
CREATE VIEW in_table_src_ri_constraints AS
SELECT *
FROM in_tables AS t JOIN pg_constraint AS c ON (c.conrelid=t.oid)
WHERE c.contype='f';
CREATE VIEW in_table_dst_ri_constraints AS
SELECT *
FROM in_tables AS t JOIN pg_constraint AS c ON (c.confrelid=t.oid)
WHERE c.contype='f';
--
-- isolated tables, that have no foreign keys, and that are not referenced...
--
CREATE VIEW da_isolated_table AS
SELECT nspname AS SchemaName, relname AS TableName
FROM in_tables
EXCEPT
SELECT nspname AS SchemaName, relname AS TableName
FROM in_table_src_ri_constraints
EXCEPT
SELECT nspname AS SchemaName, relname AS TableName
FROM in_table_dst_ri_constraints;
COMMENT ON VIEW da_isolated_table IS
'advice: tables which are isolated (not referenced and no reference)'
;
SELECT set_advice_info('da_isolated_table', 2, 3,
'isolated tables, not referenced and no reference',
'maybe missing FOREIGN KEY declarations?
- tables without reference on other tables
- and not referenced by other tables',
NULL);
-------------------------------------------------------------------------------
--
-- PERFORMANCE ADVICES (pa_*)
--
-- no usable primary key index for foreign key referencial integrity checks?
-- no usable index for foreign key on deletes?
-- never used indexes given enough statistics? costly?
--
-------------------------------------------------------------------------------
--
-- count SUMMARY of advices
--
CREATE VIEW summary_of_advices AS
SELECT
-- optimizer/structural bug if c.relname instead of ac.name
-- count_tuples get called too often, with infinite recursion in expansion?
count_tuples(ac.name) AS Nb, -- costly!
c.relname AS TableName,
akind.description AS Kind,
akind.kid AS nkind,
asev.description AS Severity,
asev.sid AS nsev,
ac.title AS Title,
ac.abstract AS Abstract,
ac.description AS Description
FROM
-- system information
pg_class AS c
JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
-- advice information
JOIN advice_classification AS ac ON (c.oid=ac.advice)
JOIN advice_kind AS akind ON (ac.kid=akind.kid)
JOIN advice_severity AS asev ON (ac.sid=asev.sid)
WHERE
-- only advisor tables!
n.nspname = 'xpg_advisor'
-- always true:
-- AND ac.name=c.relname
ORDER BY nkind ASC, nsev DESC, Title ASC;
COMMENT ON VIEW summary_of_advices IS
'count summary of all advices'
;
--
-- the short version...
--
CREATE VIEW short_summary_of_advices AS
SELECT Kind, Severity, Nb, Title
FROM summary_of_advices
WHERE Nb>0;
COMMENT ON VIEW short_summary_of_advices IS
'short summary of currently loaded design advices (da_* views)'
;
COMMENT ON COLUMN short_summary_of_advices.Kind IS
'the kind of advice it is, such as \'performance\'or \'design\''
;
COMMENT ON COLUMN short_summary_of_advices.Severity IS
'the severity of the advice, from \'info\' to \'error\''
;
COMMENT ON COLUMN short_summary_of_advices.Nb IS
'the number of offending objects found.'
;
COMMENT ON COLUMN short_summary_of_advices.Title IS
'short description of the advice.'
;
-- VACUUM FULL ANALYZE;
-- show design advice counts...
\echo '-- summary of advices'
SELECT * FROM short_summary_of_advices;
--
-- $Id: pg_advisor_test.sql,v 1.5 2004/03/21 09:31:17 coelho Exp $
--
DROP SCHEMA bad_design CASCADE;
CREATE SCHEMA bad_design;
SET search_path TO bad_design;
-- some tables, ok.
CREATE TABLE t1(id INTEGER PRIMARY KEY, xxx INTEGER UNIQUE NOT NULL);
CREATE TABLE t2(id TEXT PRIMARY KEY, msg TEXT);
CREATE TABLE t3(id VARCHAR(64) PRIMARY KEY, msg TEXT);
-- no primary key
CREATE TABLE t4(id INTEGER UNIQUE NOT NULL, msg TEXT);
CREATE TABLE t5(id TEXT UNIQUE NOT NULL, msg TEXT);
CREATE TABLE t6(id VARCHAR(64) UNIQUE NOT NULL, msg TEXT);
-- nullable unique constraint
CREATE TABLE t7(id INTEGER PRIMARY KEY, msg TEXT UNIQUE NULL);
-- ok
CREATE TABLE f1(id INTEGER PRIMARY KEY, t1id INTEGER REFERENCES t1);
-- large primary keys with foreigns
CREATE TABLE f2(id INTEGER PRIMARY KEY, t2id TEXT REFERENCES t2);
CREATE TABLE f3(id INTEGER PRIMARY KEY, t3id VARCHAR(64) REFERENCES t3);
-- bad types
CREATE TABLE f4(id INTEGER PRIMARY KEY, t1id TEXT REFERENCES t1);
CREATE TABLE f5(id INTEGER PRIMARY KEY, t1id VARCHAR(64) REFERENCES t1);
CREATE TABLE f6(id INTEGER PRIMARY KEY, t2id VARCHAR(64) REFERENCES t2);
-- bad type sizes
CREATE TABLE f7(id INTEGER PRIMARY KEY, t3id VARCHAR(32) REFERENCES t3);
-- composite primary key (ok)
CREATE TABLE f8(id1 INTEGER, id2 INTEGER, PRIMARY KEY (id1, id2));
-- composite partial primary key
CREATE TABLE f9(id1 INTEGER, id2 INTEGER, PRIMARY KEY (id1, id2), msg TEXT);
-- composite foreign key
CREATE TABLE f10(id INTEGER PRIMARY KEY, id1 INTEGER, id2 INTEGER,
FOREIGN KEY (id1, id2) REFERENCES f9);
-- foreign key not to primary key
CREATE TABLE f11(id INTEGER PRIMARY KEY, fxxx INTEGER REFERENCES t1(xxx));
pg_advisor_test.sh
Description: Bourne shell script
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
