Re: Reducing relation locking overhead - Mailing list pgsql-hackers

From Alvaro Herrera
Subject Re: Reducing relation locking overhead
Date
Msg-id 20051205150855.GA14256@surnet.cl
Whole thread Raw
In response to Re: Reducing relation locking overhead  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
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 lockacquire 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
theindex as appropiate  mark the index as completerelease 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.


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Reducing relation locking overhead
Next
From: Andrew Dunstan
Date:
Subject: Re: [PATCHES] snprintf() argument reordering not working