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(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +