Re: PKs without indexes

--- On Tue, 4/19/11, Jerry Sievers <gsievers19(at)comcast(dot)net> wrote:

> From: Jerry Sievers <gsievers19(at)comcast(dot)net>
> Subject: Re: [ADMIN] PKs without indexes
> To: jweatherman91(at)alumni(dot)wfu(dot)edu
> Cc: pgsql-admin(at)postgresql(dot)org
> Date: Tuesday, April 19, 2011, 11:19 AM
> John P Weatherman <jweatherman91(at)alumni(dot)wfu(dot)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

Slony will use any unique index on a table for replication purposes, so the list of tables should come from:

select relname
from pg_class c
join pg_namespace n on c.relnamespace = n.oid
where nspname = current_schema()
and relkind = 'r'
and c.oid not in (
select indrelid
from pg_index
where indisprimary or indisunique
)
order by 1;

Bob Lunney