From: Jerry Sievers <gsievers19@comcast.net>
To: jweatherman91@alumni.wfu.edu
Cc: pgsql-admin@postgresql.org
Subject: Re: PKs without indexes
References: <BANLkTimxxeE_6rt=+5X8VynGUX7rvr51VA@mail.gmail.com>
	<1303209946.7538.12.camel@darkflame.weatherman.net>
Date: Tue, 19 Apr 2011 11:19:33 -0400
In-Reply-To: <1303209946.7538.12.camel@darkflame.weatherman.net> (John
	P. Weatherman's message of "Tue, 19 Apr 2011 05:45:46 -0500")
Message-ID: <8762qap8u2.fsf@comcast.net>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

John P Weatherman <jweatherman91@alumni.wfu.edu> writes:

> Hi all,
>
> I am attempting to set up slony-i and have run into a minor
> glitch...apparently whoever designed the database I have inherited
> didn't consistently build indexes to go along with Primary Keys, or at
> least that's the error message I have been getting.  I am far from
> confident in my own sqlfu in the catalog tables.  Does anyone have a
> script for identifying tables without indexes that correspond to their
> PKs?  I'm just trying to avoid re-inventing the wheel if I can help it.

Here's an example for you...

begin;

create schema foo;
set search_path to foo;

create table haspk (a int primary key);
create table missingpk (a int);

select relname
from pg_class c
join pg_namespace n on c.relnamespace = n.oid
where nspname = 'foo'
and relkind = 'r'
and c.oid not in (
    select conrelid
    from pg_constraint
    where contype = 'p'
);

abort;

HTH
>
> Thanks,
>
> John
>
>
> John P Weatherman
> Sr Database Administrator
> Centerstone

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: gsievers19@comcast.net
p: 305.321.1144