Skip site navigation (1) Skip section navigation (2)

Patch to reindex primary keys

From: Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>
To: PGSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Patch to reindex primary keys
Date: 2010-09-30 00:42:48
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
    Attached is a patch that implements replacing a primary key with another
index. This would help overcome the limitation that primary keys cannot be
without taking exclusive locks.

    The use case is to create an identical index, concurrenlty, with the
structure as the primary key, and then use this feature to atomically
the primary key's underlying index.

    Before I dive into the internals, here's what this patch enables
Postgres to do:

postgres=# create table mytable( a int primary key );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"mytable_pkey" for table "mytable"
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 );
postgres=# \d mytable
    Table "public.mytable"
 Column |  Type   | Modifiers
 a      | integer | not null
    "mytable_pkey" PRIMARY KEY, btree (a)
    "mysecond_key" UNIQUE, btree (a)

postgres=# begin;
postgres=# alter table mytable drop constraint mytable_pkey;
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"
postgres=# commit
postgres-# ;
postgres=# \d mytable
    Table "public.mytable"
 Column |  Type   | Modifiers
 a      | integer | not null
    "mysecond_key" PRIMARY KEY, btree (a)


    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
                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
            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
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
    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.
@ EnterpriseDB - The Enterprise Postgres Company

singh(dot)gurjeet(at){ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device

Attachment: replace_pkey_index.patch
Description: application/octet-stream (17.3 KB)


pgsql-hackers by date

Next:From: Robert HaasDate: 2010-09-30 00:44:56
Subject: Re: english parser in text search: support for multiple words in the same position
Previous:From: Robert HaasDate: 2010-09-30 00:38:59
Subject: Re: Review: Patch for Synchronous Replication

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group