--- 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
