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

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?

> I'm also wondering about where in the command the keyword should go.
> As submitted it's
>
> [ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] ) ]
> [ TABLESPACE <replaceable class="parameter">tablespace</replaceable> ]
> + [ ONLINE]
> [ WHERE <replaceable class="parameter">predicate</replaceable> ]

One thing I didn't like about this is that really all of these clauses should
be legal to put in in any order. I'm not sure that's doable with the WHERE
clause but the others ought to be possible to make an arbitrary list that can
be given in any order. But perhaps that's irrelevant if we don't go with
ONLINE at the end at all.

> which seems a bit randomly chosen; what's more it creates a problem for
> psql, which would have to parse nearly the entire command to discover
> whether it's safe to execute inside a transaction block or not.

One thing to think about, what will the command to execute stored procedures
look like? Those will also need to be called from outside a transaction.

I keep coming back to this feeling that the server should be the one starting
the transaction, not psql. But then that could just be my experience with
Oracle. In Oracle you're never "outside a transaction". Transactions
implicitly start the first time you execute almost any statement.

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