Re: Better name/syntax for "online" index creation

Quoting Greg Stark <gsstark(at)MIT(dot)EDU>:

> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
>> I'm fairly dissatisfied with the naming of Greg Stark's proposed new
>> feature for creating indexes without blocking writers of the table.
>> To my mind, "ONLINE" just doesn't convey any useful information ---
>> the existing CREATE INDEX functionality could already be said to be
>> "online", in the sense that you don't have to take down the database
>> to do it. I thought about "SHARED" but someone could probably raise
>> the same objection to it. Anyone have a better idea?
>
> I know Oracle calls this "online" index builds. In fact it works similarly
> with a single keyword "online" tacked on near the end of the create index
> statement.
>
> Anyone know what MSSQL or DB2 call it?

Fwiw a few data points:

MSSQL uses (WITH ONLINE=ON) much like we and Oracle use ONLINE tacked
on to the
end of the create index command.

The DB2 handbook says "Tables can now be reorganized online with almost full
acess to the table allowed" but their syntax does not use the word "online".
They actually support three modes:

ALLOW NO ACCESS
ALLOW READ ACCESS
ALLOW WRITE ACCESS

I'm not sure where that leaves us. The word "online" seems deeply ingrained in
the database vocabulary for this feature but there's a certain amount of
SQLishness in the DB2 syntax too.

DB2 also supports some other nice options like interrupting an index build and
then resuming it later. If there's demand that seems like something we
would be
able to support too. The same infrastructure might be useful for
suspending and
resuming vacuums on large tables too.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com