Re: Recreating a primary key
От | Ericson Smith |
---|---|
Тема | Re: Recreating a primary key |
Дата | |
Msg-id | 1046466223.19273.59.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | Recreating a primary key (Ericson Smith <eric@did-it.com>) |
Список | pgsql-general |
I guess what I was trying to avoid, was the exclusive table locking with REINDEX. What we ended up doing was writing a script to iterate through pg_index (and others) and drop/recreate all our indices. This works very well, without locking the tables. There is a little slowdown as there are some sequential scans when indices cannot be used, but on the whole, we saved about 4Gigs through rebuilding our indices, while having concurrent access to the database by users and other scripts. We plan to run this script maybe once per week or so. My original question was answered by Tom Lane: > In 7.3, there's ALTER TABLE ADD PRIMARY KEY. In earlier releases, > you'd have to fake it by manually poking pg_index.indisprimary > after you create a unique index. > > regards, tom lane His suggestion will work for our primary keys, which we are skipping in that script now. - Ericson Smith eric@did-it.com On Fri, 2003-02-28 at 15:33, scott.marlowe wrote: > On 28 Feb 2003, Ericson Smith wrote: > > > Hi, > > > > Is there a way to re-create a primary key? > > > > So I create tables with CREATE TABLE... PRIMARY KEY(col...) syntax. > > > > I later want to drop the primary key and re-create it (performance > > considerations, and to not worry about exclusive table locking with a > > rebuild index). > > > > The documentation says that primary keys are basically UNIQUE and NOT > > NULL keys, but checking with pg_index, I see the "indisprimary" column > > is set to true. > > > > Any quick and dirty way to rebuild these indices? > > Have you tried reindex? It does basically just that. And I've just > tested to be sure, it does work on pkey indexes just fine. -- Ericson Smith <eric@did-it.com>
В списке pgsql-general по дате отправления: