DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=MCmWbeisWGPkXGC/fH9AMtNpRcWj1NAOd49OffhxIUcTkINn+jA389hGQ+q0GmejlPAhKO1WJnwp36/04PNk7Q1ibfGlOsZhQgBQEWRpAhxx9u2JlxAoDlvZBXqVRk4KB+zt2bZNrBG+Hf2QdHPSBITG7EMoVVUegkWR6d1Pz4k=;
Message-ID: <594449.61680.qm@web39702.mail.mud.yahoo.com>
Date: Tue, 19 Apr 2011 12:51:45 -0700 (PDT)
From: Bob Lunney <bob_lunney@yahoo.com>
Subject: Re: PKs without indexes
To: jweatherman91@alumni.wfu.edu, Jerry Sievers <gsievers19@comcast.net>
Cc: pgsql-admin@postgresql.org
In-Reply-To: <8762qap8u2.fsf@comcast.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

=0A--- On Tue, 4/19/11, Jerry Sievers <gsievers19@comcast.net> wrote:=0A=0A=
> From: Jerry Sievers <gsievers19@comcast.net>=0A> Subject: Re: [ADMIN] PKs=
 without indexes=0A> To: jweatherman91@alumni.wfu.edu=0A> Cc: pgsql-admin@p=
ostgresql.org=0A> Date: Tuesday, April 19, 2011, 11:19 AM=0A> John P Weathe=
rman <jweatherman91@alumni.wfu.edu>=0A> writes:=0A> =0A> > Hi all,=0A> >=0A=
> > I am attempting to set up slony-i and have run into a=0A> minor=0A> > g=
litch...apparently whoever designed the database I=0A> have inherited=0A> >=
 didn't consistently build indexes to go along with=0A> Primary Keys, or at=
=0A> > least that's the error message I have been=0A> getting.=A0 I am far =
from=0A> > confident in my own sqlfu in the catalog tables.=A0=0A> Does any=
one have a=0A> > script for identifying tables without indexes that=0A> cor=
respond to their=0A> > PKs?=A0 I'm just trying to avoid re-inventing the=0A=
> wheel if I can help it.=0A> =0A> Here's an example for you...=0A> =0A> be=
gin;=0A> =0A> create schema foo;=0A> set search_path to foo;=0A> =0A> creat=
e table haspk (a int primary key);=0A> create table missingpk (a int);=0A> =
=0A> select relname=0A> from pg_class c=0A> join pg_namespace n on c.relnam=
espace =3D n.oid=0A> where nspname =3D 'foo'=0A> and relkind =3D 'r'=0A> an=
d c.oid not in (=0A> =A0 =A0 select conrelid=0A> =A0 =A0 from pg_constraint=
=0A> =A0 =A0 where contype =3D 'p'=0A> );=0A> =0A> abort;=0A> =0A> HTH=0A=
=0ASlony will use any unique index on a table for replication purposes, so =
the list of tables should come from:=0A=0Aselect relname=0A  from pg_class =
c=0A  join pg_namespace n on c.relnamespace =3D n.oid=0A where nspname =3D =
current_schema()=0A   and relkind =3D 'r'=0A   and c.oid not in (=0A       =
select indrelid=0A         from pg_index=0A        where indisprimary or in=
disunique=0A)=0A order by 1;=0A=0ABob Lunney=0A=0A