Thread

1. Patch to reindex primary keys

   Gurjeet Singh <singh.gurjeet@gmail.com> — 2010-09-30T00:42:48Z

       Attached is a patch that implements replacing a primary key with another
   index. This would help overcome the limitation that primary keys cannot be
   reindexed
   without taking exclusive locks.
   
       The use case is to create an identical index, concurrenlty, with the
   same
   structure as the primary key, and then use this feature to atomically
   replace
   the primary key's underlying index.
   
       Before I dive into the internals, here's what this patch enables
   Postgres to do:
   
   </snip>
   postgres=# create table mytable( a int primary key );
   NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
   "mytable_pkey" for table "mytable"
   CREATE TABLE
   postgres=# insert into mytable select s from generate_series( 1, 100 ) as s;
    INSERT 0 100
   postgres=# create unique index concurrently mysecond_key on mytable( a );
   CREATE INDEX
   postgres=#
   postgres=# \d mytable
       Table "public.mytable"
    Column |  Type   | Modifiers
   --------+---------+-----------
    a      | integer | not null
   Indexes:
       "mytable_pkey" PRIMARY KEY, btree (a)
       "mysecond_key" UNIQUE, btree (a)
   
   postgres=#
   postgres=# begin;
    BEGIN
   postgres=# alter table mytable drop constraint mytable_pkey;
   ALTER TABLE
   postgres=# alter table mytable add primary key (a) with (index =
   'mysecond_key' );
   NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
   "mysecond_key" for table "mytable"
    ALTER TABLE
   postgres=# commit
   postgres-# ;
   COMMIT
   postgres=# \d mytable
       Table "public.mytable"
    Column |  Type   | Modifiers
   --------+---------+-----------
    a      | integer | not null
    Indexes:
       "mysecond_key" PRIMARY KEY, btree (a)
   
   </snip>
   
       Internally, this patch this patch drops current primary key constraintm,
   if any,
   (currently not working, but rest of the feature is still usable), and then
   creates a new constraint with the given index.
   
       Here's the pseudocode I started with:
   
       Check if cxt->pkey->options has a 'WITH INDEX' element
           take an exclusive lock on that index
   
           Does this table have a primary key
               check if index mentioned in cxt->pkey matches that PKEY
   definition,
                   Does column list match
                   Do the opclasses match
                   Does index type match (BTree for now)
                   Do they have the same owner
   
           Append a new command to newcmds to drop the PKey constraint
               use 'rel' variable to get primary key's OID ( modify and reuse
   relationHasPrimaryKey() )
               use relation_open() to get pkey's relation
               use the returned Relation->rd_rel->relname to build DROP
   CONSTRAINT command
               set missingok member of the command so that this would work even
   if there was already a DROP CONSTRAINT for the PKey.
               push this command to newcmds
   
           Chenge the 'WITH INDEX' element, and replace index name in Value* to
   have decimal representation of index's OID.
               This will be used by ATExecAddIndex().
   
   
       The patch is based on REl9_0_STABLE from a few days ago. It is a bit
   hackish,
   and modifies the couple of internal APIs to get the work done. I still have
   a few
   TODO items in there, but wanted to throw this patch out there to get a few
   eyeballs on it while I traveled.
   
   PS: I am (going to bed and then traveling) for the next 20 hours or so, so
   will
       not be able to respond to emails until then.
   
   I dedicate this work to
   
   my dear brother-in-law, Sandeep Singh
   and my dear friend, Mandeep Singh Sethi
   
   Good men... you will be always in our hearts. RIP.
   -- 
   gurjeet.singh
   @ EnterpriseDB - The Enterprise Postgres Company
   http://www.EnterpriseDB.com
   
   singh.gurjeet@{ gmail | yahoo }.com
   Twitter/Skype: singh_gurjeet
   
   Mail sent from my BlackLaptop device
   

2. Re: Patch to reindex primary keys

   Robert Haas <robertmhaas@gmail.com> — 2010-09-30T20:33:09Z

   On Wed, Sep 29, 2010 at 8:42 PM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
   >     Attached is a patch that implements replacing a primary key with another
   > index. This would help overcome the limitation that primary keys cannot be
   > reindexed
   > without taking exclusive locks.
   
   Sounds useful.  You should add this patch here so it gets reviewed
   during the next CommitFest.
   
   https://commitfest.postgresql.org/action/commitfest_view/open
   
   -- 
   Robert Haas
   EnterpriseDB: http://www.enterprisedb.com
   The Enterprise Postgres Company