Re: [HACKERS] REINDEX CONCURRENTLY 2.0

Hi,

Here is a third take on this feature, heavily based on Michael Paquier's 
2.0 patch. This time the patch does not attempt to preserve the index 
oids, but instead creates new indexes and moves all dependencies from 
the old indexes to the new before dropping the old ones. The only 
downside I can see to this approach is that we no logner will able to 
reindex catalog tables concurrently, but in return it should be easier 
to confirm that this approach can be made work.

This patch relies on that we can change the indisvalid flag of indexes 
transactionally, and as far as I can tell this is the case now that we 
have MVCC for the catalog updates.

The code does some extra intermediate commits when building the indexes 
to avoid long running transactions.

How REINDEX CONCURRENTLY operates:

For each table:

1. Create new indexes without populating them, and lock the tables and 
indexes for the session.

2. After waiting for all running transactions populate each index in a 
separate transaction and set them to ready.

3. After waiting again for all running transactions validate each index 
in a separate transaction (but not setting them to valid just yet).

4. Swap all dependencies over from each old index to the new index and 
rename the old and the new indexes (from the <name> to <name>_ccold and 
<name>_new to <name>), and set isprimary and isexclusion flags. Here we 
also mark the new indexes as valid and the old indexes as invalid.

5. After waiting for all running transactions we change each index from 
invalid to dead.

6. After waiting for all running transactions we drop each index.

7. Drop all session locks.

Andreas

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers