Rebuilding Pkey and Indexes without locking table in PG 8.4.

On production System, some times DBAs have to rebuild the indices, since, Rebuilding indices lock the table therefore DBA can use Option CONCURRENTLY.

For normal Indices CONCURRENTLY is a best option, however Primary Key Indices Rebuild require Lock on table. To overcome this issue, I have made following function:
SWAP Index with Pkey:

CREATE OR REPLACE FUNCTION swap_for_pkey(text,text,text) returns integer
AS
$$
   DECLARE
     cmd text;
     oid1 integer;
     oid2 integer;
     filenode1 integer;
     filenode2 integer;
     relation text;
   BEGIN
      select oid::integer into oid1 from pg_class where relname=$2 and relnamespace = (select oid from pg_namespace where nspname=$1);
     RAISE NOTICE 'PKEY OID: %',oid1;
      select relfilenode::integer into filenode1 from pg_class where oid=oid1;
      select oid::integer into oid2 from pg_class where relname=$3 and relnamespace = (select oid from pg_namespace where nspname=$1);
     RAISE NOTICE 'PKEY OID: %',oid2;
      select relfilenode::integer into filenode2 from pg_class where oid=oid2;
      select (indrelid::regclass)::text into relation from pg_index where indexrelid=oid1;
    RAISE NOTICE 'RELATION NAME: %',relation;
      cmd:='LOCK '||relation||';';
      RAISE NOTICE 'Executing :- %',cmd;
      Execute cmd;      
      cmd:='UPDATE pg_class SET relfilenode='||filenode2|| ' WHERE oid='||oid1||';';
      RAISE NOTICE 'Executing :- %',cmd;
      Execute cmd;      
      cmd:='UPDATE pg_class SET relfilenode='||filenode1|| ' WHERE oid='||oid2||';';
      RAISE NOTICE 'Executing :- %',cmd;
      Execute cmd;
      cmd:='DROP INDEX '||$1||'.'||$3||';';
      RAISE NOTICE 'Executing :- %',cmd;
      Execute cmd;
      return 0;
   END;
$$language plpgsql;

Concept is simple, Create a UNIQUE INDEX on primary key columns and swap the relfilenode

CREATE OR REPLACE Function rebuild_pkey_index(text,text) returns setof text
AS
$$
  DECLARE
     reloid integer;
     cmd text;
     rec record;
     oid1 integer;
     oid2 integer;
     filenode1 integer;
     filenode2 integer;
     relname1 text;
  BEGIN
    select oid into reloid from pg_class where relname=$2 and relnamespace=(select oid from pg_namespace where nspname=$1);
    for rec in SELECT c2.relname as indexname, substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),0, position( 'INDEX'in pg_catalog.pg_get_indexdef(i.indexrelid, 0, true))+6) ||'CONCURRENTLY '|| c2.relname||'_new '|| substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),position( 'ON 'in pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)))||';' as command FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.oid=reloid  AND c.oid = i.indrelid AND i.indexrelid = c2.oid and i. indisprimary=true ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
    LOOP
       return next rec.command;
       cmd:='SELECT swap_for_pkey('||''''||$1||''''||','||''''||rec.indexname||''''||','||''''||rec.indexname||'_new'||''''||');';
       return next cmd;
   END LOOP;
   END;
$$language plpgsql;

Following is an Output:

postgres=# select rebuild_pkey_index('public','test');                            
rebuild_pkey_index                             
--------------------------------------------------------------------------- 
CREATE UNIQUE INDEX CONCURRENTLY test_pkey1_new ON test USING btree (id); 
SELECT swap_for_pkey('public','test_pkey1','test_pkey1_new');
(2 rows)

Executing the command provided by the above function would give following result:

CREATE INDEX
NOTICE:  PKEY OID: 260669
NOTICE:  PKEY OID: 260679
NOTICE:  RELATION NAME: test
NOTICE:  Executing :- LOCK test;
NOTICE:  Executing :- UPDATE pg_class SET relfilenode=260679 WHERE oid=260669;
NOTICE:  Executing :- UPDATE pg_class SET relfilenode=260678 WHERE oid=260679;
NOTICE:  Executing :- DROP INDEX public.test_pkey1_new;
 swap_for_pkey 
---------------
             0
(1 row)

Similarly for Non Pkey Indices, I have written following function, which can provide the all the command for rebuilding the non-pkey index:

CREATE OR REPLACE Function rebuild_nonpkey_index(text,text) returns setof text
AS
$$
  DECLARE
     reloid integer;
     cmd text;
     rec record;
  BEGIN
 
    select oid into reloid from pg_class where relname=$2 and relnamespace=(select oid from pg_namespace where nspname=$1);
    for rec in SELECT c2.relname as indexname, substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),0, position( 'INDEX'in pg_catalog.pg_get_indexdef(i.indexrelid, 0, true))+6) ||'CONCURRENTLY '|| c2.relname||'_new '|| substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),position( 'ON 'in pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)))||';' as command FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.oid=reloid  AND c.oid = i.indrelid AND i.indexrelid = c2.oid and i. indisprimary=false ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
    LOOP
       return next rec.command;
       cmd:= 'DROP INDEX '||rec.indexname||';';
       return next cmd;
       cmd:='ALTER INDEX '||rec.indexname||'_new'||' RENAME TO '||rec.indexname||';';
       return next cmd;
     END LOOP;
   END;
$$language plpgsql;

Output:

postgres=# select rebuild_nonpkey_index('public','test');                                                                                           
rebuild_nonpkey_index                        
-------------------------------------------------------------------- 
CREATE INDEX CONCURRENTLY test_idx_new ON test USING btree (col1); 
DROP INDEX test_idx; 
ALTER INDEX test_idx_new RENAME test_idx;
(3 rows)

Above functions can also be use in PG9.0. However, for PG9.1 Developers are working on ALTER TABLE command which can be use for Swapping the Unique index for Primary key.