In my previous blog post about Parallel Reindexing, Robert Treat asked about locking issues, and as my follow-up comment stated, the script wasn't engineered with locking in mind. It was built for offline reindexing. Part of the issue with Reindexing is while you can create an index concurrent with normal database operation in PostgreSQL 8.2, you can not reindex concurrently. This feature is currently on the PostgreSQL Todo List.
As I thought about the problem, it occurred to me that on the surface this was a fairly trivial problem to solve. Get the schema for the indexes of a table, create new indexes currently with a temporary name, drop the old index and rename the new index. There might be some locking issues with ALTER INDEX ... RENAME TO, but overall it seemed like the easiest and best strategy to go with. That is, until I started prototyping. Then it occurred to me - Primary Keys. Primary keys differ from normal indexes in that they are also constraints on the table. This is also true of unique indexes. By adding constraints to the mix, and having to remove and re-add primary keys, the equation just became more complex.
Take the given pg_constraint table from the pg_catalog schema:
Table "pg_catalog.pg_constraint"
Column | Type | Modifiers
---------------+------------+-----------
conname | name | not null
connamespace | oid | not null
contype | "char" | not null
condeferrable | boolean | not null
condeferred | boolean | not null
conrelid | oid | not null
contypid | oid | not null
confrelid | oid | not null
confupdtype | "char" | not null
confdeltype | "char" | not null
confmatchtype | "char" | not null
conkey | smallint[] |
confkey | smallint[] |
conbin | text |
consrc | text |
Indexes:
"pg_constraint_oid_index" UNIQUE, btree (oid)
"pg_constraint_conname_nsp_index" btree (conname, connamespace)
"pg_constraint_conrelid_index" btree (conrelid)
"pg_constraint_contypid_index" btree (contypid)
This would appear to be the primary source of internal data organization for table related constraints, and while it most likely is, merely removing a row from this table and inserting a new one for the new index doesn't give us the ability to remove a primary key and make a newly constructed index the new primary key for a table.
This is where I've explored to with regard to the index related constraints. As I explore further in the pg_catalog schema and how an index is stored as a primary key, I will detail it further here.
