Re: Online index builds

Consindering the syntax for this, we currently allow read access during
index creation, just not write access, so I think the new syntax should
be:
CREATE [ UNIQUE ] INDEX name  ON table     [ USING method ] [ [ENABLE] WRITE [ACCESS] ]    ( { column | ( expression )
}[ opclass ] [, ...] )    [ WITH ( storage_parameter = value [, ... ] ) ]    [ TABLESPACE tablespace ]    [ WHERE
predicate]
 

This is clear, and adds no new keywords.

---------------------------------------------------------------------------

Greg Stark wrote:
> 
> I just sent in the patch for online index builds to -patches.
> 
> . The work to combine the two phases into a single non-transactional command
>   is done. I'm not sure how long to wait between lock checks or how verbose to
>   be about why it's taking so long. I do think we have to print something or
>   else the DBA won't know if it's hung waiting for something external.
>   Currently it prints a notice the first time it sleeps. 
> 
> . Also it prints out how many tuples it found which normal index doesn't.
>   Probably that message should go away. On the other hand the index stats
>   probably need to be filled in.
> 
> . I need to check what locks I'm taking. I think I still have some old code
>   with the wrong locks in it.
> 
> . this includes the tid btree opclass sent earlier (with a warning I didn't
>   notice before fixed up). opr_sanity now fails but I think that's due to the
>   gin commits not this opclass.
> 
> . In case of an error during phase2 the invalid index is left in place. It can
>   be dropped with DROP INDEX. The footwork to get it dropped in case of an
>   error would be quite tricky but there's a sketch of how to do it in the source.
> 
> . no documentation yet, there's not much to write though.
> 
> . no regression tests yet. I don't see any way to test this reasonably in the
>   regression tests. I've done some testing myself by building indexes while
>   pgbench is running. Then I have to do index scans to see how many records
>   are returned with index scans. It wouldn't be easy to automate and even if
>   it were done it wouldn't really be all that great a test. The corner cases
>   found during the development are pretty narrow and will be hard to reliably
>   test.
> 
> -- 
> greg
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +