Re: Reducing relation locking overhead

I wonder if it would work to release the AccessShareLock before
acquiring the ExclusiveLock. Of course, this would let any ALTER TABLE
or DROP TABLE to do anything they wanted, but we could check that the
table is still the same after reacquiring the exclusive lock. REINDEX
would have to abort if anything unexpected happened to the table while
the REINDEX transaction was waiting after releasing the shared lock.

What's not at all clear to me is at what time is the lock "upgraded"?
Just after scanning the heap for the first time? In this case, how do
we detect all the tuples that need to be inserted after we acquire the
exclusive lock? Are they listed somewhere?

I imagine each table could have a global flag telling "there is an
online reindex running for this relation". If this flag is set, each
insert/delete to the index needs to save aside somewhere, the CTIDs of
tuples it is inserting/deleting. So the protocol for reindex could be:

acquire vacuum lock
acquire read lock
set REINDEX flag
build the bulk of the index
-- this takes a lot of time ...
-- meanwhile other transactions save CTIDs in a "spill area"
release read lock

acquire exclusive lock
recheck the table, abort if something weird happened
read the spill area, insert/delete from the index as appropiate
mark the index as complete
release exclusive lock
release vacuum lock

The "vacuum lock" is designed to leave any VACUUM out of the equation,
but let run any select/insert/update/delete run. Maybe this lock could
leave ALTER TABLE and other stuff out too. Not sure if we have
something like this in our lock table -- if not, can we create it?

Note that by this proposal, any DDL gets more expensive -- but if it's
the normal case (no REINDEX running), it's only a flag check.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.