Re: CREATE INDEX and HOT (was Question: pg_classattributes and race conditions ?)

On Sat, 2007-03-17 at 11:45 -0400, Tom Lane wrote:
> "Pavan Deolasee" <pavan(dot)deolasee(at)enterprisedb(dot)com> writes:
> > While creating an index, if a HEAP_ONLY tuple is found,
> > CREATE INDEX [CONCURRENTLY] fails with an error and the
> > user needs to SET HOT OFF and then try again. While turning
> > HOT off, the entire table is CHILLed, holding AccessExclusive
> > lock on the table. Once the new index is created, user
> > can turn HOT on again.
>
> It hardly seems acceptable to require exclusive lock to chill a table.
> In production situations, knowing that you'd have to do that to do
> index maintenance on a large table would probably scare you off of
> ever enabling the feature at all. Last year we were getting beaten up
> about how it wasn't acceptable for CREATE INDEX to lock out writes
> for a long time; how is it suddenly acceptable to need to lock out
> both reads and writes for a long time before you can even think
> about creating an index?

I agree with you: It isn't acceptable for us to contemplate an
AccessExclusiveLock before we can build any index.

We *must* make CREATE INDEX CONCURRENTLY work with HOT. The good news is
I think we can without significant difficulty.

The problems are with CREATE INDEX, in some cases. I regret that I did
not see those difficulties until recently, which is why I was concerned
that we spent time on VACUUM FULL rather than this issue. I'm glad now
that you both pressed ahead and solved that though.

As a result of the issues, I think Pavan is playing safe, to make sure
there is *an* option, so that we can build upwards from there. The
proposal is pragmatism only, while we discuss other approaches.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com