pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPKs without indexes
5+ messages / 4 participants
[nested] [flat]
* PKs without indexes
@ 2011-04-19 10:45 John P Weatherman <jweatherman91@alumni.wfu.edu>
0 siblings, 2 replies; 5+ messages in thread
From: John P Weatherman @ 2011-04-19 10:45 UTC (permalink / raw)
To: pgsql-admin
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.
Thanks,
John
John P Weatherman
Sr Database Administrator
Centerstone
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PKs without indexes
@ 2011-04-19 15:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: John P Weatherman <jweatherman91@alumni.wfu.edu>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2011-04-19 15:05 UTC (permalink / raw)
To: jweatherman91@alumni.wfu.edu; +Cc: pgsql-admin
John P Weatherman <jweatherman91@alumni.wfu.edu> writes:
> 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.
At least in Postgres, there is no such animal as a PK (or unique)
constraint without an underlying index. Maybe you'd better be more
clear about what problem you're having.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PKs without indexes
@ 2011-04-19 15:19 Jerry Sievers <gsievers19@comcast.net>
parent: John P Weatherman <jweatherman91@alumni.wfu.edu>
1 sibling, 1 reply; 5+ messages in thread
From: Jerry Sievers @ 2011-04-19 15:19 UTC (permalink / raw)
To: jweatherman91@alumni.wfu.edu; +Cc: pgsql-admin
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
>
> Thanks,
>
> John
>
>
> John P Weatherman
> Sr Database Administrator
> Centerstone
--
Jerry Sievers
Postgres DBA/Development Consulting
e: gsievers19@comcast.net
p: 305.321.1144
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PKs without indexes
@ 2011-04-19 19:51 Bob Lunney <bob_lunney@yahoo.com>
parent: Jerry Sievers <gsievers19@comcast.net>
0 siblings, 1 reply; 5+ messages in thread
From: Bob Lunney @ 2011-04-19 19:51 UTC (permalink / raw)
To: jweatherman91@alumni.wfu.edu, Jerry Sievers <gsievers19@comcast.net>; +Cc: pgsql-admin
--- 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PKs without indexes
@ 2011-04-20 01:59 John P Weatherman <jweatherman91@alumni.wfu.edu>
parent: Bob Lunney <bob_lunney@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: John P Weatherman @ 2011-04-20 01:59 UTC (permalink / raw)
To: pgsql-admin; +Cc: john.weatherman@centerstone.org
Awesome! Thanks Bob.
On Tue, 2011-04-19 at 12:51 -0700, Bob Lunney wrote:
>
> 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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-04-20 01:59 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-19 10:45 PKs without indexes John P Weatherman <jweatherman91@alumni.wfu.edu>
2011-04-19 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-19 15:19 ` Jerry Sievers <gsievers19@comcast.net>
2011-04-19 19:51 ` Bob Lunney <bob_lunney@yahoo.com>
2011-04-20 01:59 ` John P Weatherman <jweatherman91@alumni.wfu.edu>
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