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.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
replace_pkey_index.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
