Thread: Recreating a primary key
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? -- Ericson Smith <eric@did-it.com>
Ericson Smith <eric@did-it.com> writes:
> Is there a way to re-create a primary key?
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
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? > > Not sure I understand what you mean - whatever "quick and dirty" way you choose to rebuild an index, it has to lock the table, because otherwise the resulting index will be incorrect...
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>
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.