The documentation for the new REINDEX CONCURRENTLY option says: "When this option is used, PostgreSQL will rebuild the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard reindex build locks out writes (but not reads) on the table until it's done."
This statement appears to be false, not because it's wrong about REINDEX CONCURRENTLY but because it's wrong about regular REINDEX. S1: rhaas=# begin; BEGIN rhaas=# select * from pgbench_branches where filler = 'afafa'; bid | bbalance | filler -----+----------+-------- (0 rows) S2: rhaas=# reindex index pgbench_branches_pkey; -- hangs Typing "COMMIT;" or "ROLLBACK;" in S1 unblocks the reindex and it succeeds, but otherwise it doesn't, contrary to the claim that a regular REINDEX does not block reads. The reason for this seems to be that the REINDEX acquires AccessExclusiveLock on all of the indexes of the table, and a SELECT acquires AccessShareLock on all indexes of the table (even if the particular plan at issue does not use them); e.g. in this case the plan is a Seq Scan. REINDEX acquires only ShareLock on the table itself, but this apparently does nobody wanting to run a query any good. Is it supposed to work this way? Am I confused? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
