From: Bob Lunney <bob_lunney@yahoo.com>
To: jweatherman91@alumni.wfu.edu, Jerry Sievers <gsievers19@comcast.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: PKs without indexes
Date: Tue, 19 Apr 2011 12:51:45 -0700 (PDT)
Message-ID: <594449.61680.qm@web39702.mail.mud.yahoo.com> (raw)
In-Reply-To: <8762qap8u2.fsf@comcast.net>
--- 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: bob_lunney@yahoo.com, gsievers19@comcast.net
Subject: Re: PKs without indexes
In-Reply-To: <594449.61680.qm@web39702.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox