> please find attached a quick proof of concept for a 'pg_advisor' schema.
Here is a "larger" but nevertheless still quick proof of concept, alas
with some buggy PL/pgSQL that I wrote with my little finger.
It implements some foreign key type checks for which I submitted be
patches some time ago.
The more I think about it, the more I find it should be the way to go,
rather than having a new external tool.
--
Fabien Coelho - [EMAIL PROTECTED]
--
-- $Id: pg_advisor.sql,v 1.13 2004/03/19 14:55:39 coelho Exp $
--
-- pg_advisor maybe future system schema?
--
-- at the time it is xpg_advisor as pg_ is reserved.
--
DROP SCHEMA xpg_advisor CASCADE;
CREATE SCHEMA xpg_advisor;
COMMENT ON SCHEMA xpg_advisor
IS 'various advices about database design or performance' ;
SET search_path TO xpg_advisor,pg_catalog;
--
-- DESIGN ADVICES (da_*)
--
--
-- tables without primary keys
--
CREATE VIEW da_tables_without_primary_key AS
SELECT n.nspname AS Schema, c.relname AS Name
FROM pg_class AS c
JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
WHERE
-- no comments about system catalogs.
n.nspname NOT IN ('pg_catalog','pg_toast','information_schema','xpg_advisor')
-- only tables
AND c.relkind = 'r'
-- no primary key
AND NOT c.relhaspkey
ORDER BY Schema ASC, Name ASC;
COMMENT ON VIEW da_tables_without_primary_key
IS 'it is better to have a primary key on your tables';
-- SELECT * FROM da_tables_without_primary_key;
--
-- tables with composite primary keys?
--
CREATE VIEW da_tables_with_composite_primary_key AS
SELECT n.nspname AS Schema, c.relname AS Name
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.
n.nspname NOT IN ('pg_catalog','pg_toast','information_schema','xpg_advisor')
-- 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
ORDER BY Schema ASC, Name ASC;
COMMENT ON VIEW da_tables_with_composite_primary_key
IS 'it may be considered a better design to have simple primary keys';
-- SELECT * FROM da_tables_with_composite_primary_key;
--
-- missing array_index function
--
CREATE OR REPLACE FUNCTION array_index(anyarray, anyelement)
RETURNS INTEGER AS '
DECLARE
tab ALIAS FOR $1;
item ALIAS FOR $2;
i INTEGER;
BEGIN
i := 1;
LOOP
IF i > array_upper(tab, 1) THEN
RETURN NULL;
END IF;
IF tab[i] = item THEN
RETURN i;
END IF;
i := i+1;
END LOOP;
END;'
LANGUAGE plpgsql;
COMMENT ON FUNCTION array_index(anyarray, anyelement)
IS 'missing array_index function... should be already there!';
--
-- 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 constraints
AND array_index(c.confkey, fka.attnum)=array_index(c.conkey, cca.attnum)
;
--
-- foreign keys which do not exactly match their target key types?
--
CREATE VIEW da_foreign_key_type_dont_match AS
SELECT
nc.nspname AS Schema,
cc.relname AS Name,
fkc.ccol AS AttName,
format_type(fkc.ctyp, fkc.cmod) AS ColumnType,
nf.nspname AS FSchema,
cf.relname AS FName,
fkc.fcol AS FAttName,
format_type(fkc.ftyp, fkc.fmod) AS ForeignType,
fkc.component AS Component
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_attribute AS ac ON (fkc.ccol=ac.oid)
JOIN pg_class AS cf ON (fkc.frel=cf.oid)
JOIN pg_namespace AS nf ON (cf.relnamespace=nf.oid)
--JOIN pg_attribute AS af ON (fkc.fcol=af.oid)
WHERE
-- no comments about system catalogs.
nc.nspname NOT IN('pg_catalog','pg_toast','information_schema','xpg_advisor')
AND
nf.nspname NOT IN('pg_catalog','pg_toast','information_schema','xpg_advisor')
-- only tables (redundant?)
AND cc.relkind='r' AND cf.relkind='r'
-- non matching type
AND fkc.ctyp!=fkc.ftyp
ORDER BY Schema ASC, Name ASC, FSchema ASC, FName ASC, Component ASC;
COMMENT ON VIEW da_foreign_key_type_dont_match
IS 'non matching foreing key component, maybe a bad design';
-- SELECT * FROM da_foreign_key_type_dont_match;
--
-- others?
--
-- foreign keys which do match their target types but with different sizes?
-- tables with large primary keys (such as TEXT, VARCHAR(64)...)?
--
-- 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 OR REPLACE FUNCTION count_tuples(OID)
RETURNS INTEGER AS '
DECLARE
oid ALIAS FOR $1;
tablename TEXT;
schemaname TEXT;
res RECORD;
BEGIN
-- get
SELECT INTO schemaname, tablename n.nspname, c.relname
FROM pg_class AS c JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
WHERE c.oid=oid;
IF NOT FOUND THEN
RETURN NULL;
END IF;
FOR res IN
EXECUTE \'SELECT COUNT(*) AS n FROM \' || schemaname || \'.\' || tablename
LOOP
RETURN res.n;
END LOOP;
RETURN NULL;
END;'
LANGUAGE 'plpgsql';
COMMENT ON FUNCTION count_tuples(OID)
IS 'count number of rows in table or view, given its oid';
-- count summary of design advices?
CREATE VIEW design_advices_summary AS
SELECT
count_tuples(c.oid) AS Count,
c.relname AS Name,
d.description AS Description
FROM pg_class AS c
JOIN pg_description AS d ON (d.objoid=c.oid)
JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
WHERE
-- xpg_advisor views
n.nspname = 'xpg_advisor'
-- only design advices
AND c.relname LIKE 'da_%'
ORDER BY Name ASC;
-- SELECT * FROM design_advices_summary;
--
-- PERFORMANCE ADVICES (pa_*)
--
-- no usable primary key index for foreign key referencial integrity checks?
-- no usable index for foreigh key on deletes?
--
-- count summary of performance advices?
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
