Re: PKs without indexes

--- On Tue, 4/19/11, Jerry Sievers <gsievers19@comcast.net> wrote:

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

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