Thread

1. Recreating a primary key

   Ericson Smith <eric@did-it.com> — 2003-02-28T16:47:54Z

   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>
   
   
   

2. Re: Recreating a primary key

   Tom Lane <tgl@sss.pgh.pa.us> — 2003-02-28T20:08:26Z

   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
   
   

3. Re: Recreating a primary key

   scott.marlowe <scott.marlowe@ihs.com> — 2003-02-28T20:33:53Z

   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.
   
   
   

4. Re: Recreating a primary key

   Ericson Smith <eric@did-it.com> — 2003-02-28T21:03:43Z

   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>
   
   
   

5. Re: Recreating a primary key

   Dmitry Tkach <dmitry@openratings.com> — 2003-02-28T22:30:02Z

   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...