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

Greg Stark wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>

> > 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.

If you were going to propose something like the "INCLUDING CONSTRAINTS"
or "EXCLUDING CONSTRAINTS" stuff, which you can specify multiple times
and then the last one takes precedence, then I personally don't like it.
IMHO it's not nice that we accept that kind of things.

> > 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.

CALL presumably?

> 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.

Well, the problem is the AUTOCOMMIT mode. In that case it isn't the
server starting the transaction, but the client app. We already had the
server starting the transaction (back in 7.3 IIRC) and it was such a
problem that it was rolled back, and instead the autocommit logic was
put into the client apps.

In Postgres, just like in Oracle, you're never outside of a transaction
as well. The only difference is when does the transaction end -- in
Postgres, it's either right after the command, or when COMMIT/ROLLBACK
is issued, depending on whether the user (or the client app) issued
BEGIN or not. But you already knew that. So the only problem is that
psql should be figuring out if it should send a BEGIN or not, which
depends on the command being executed. In the normal CREATE INDEX case,
psql should send a BEGIN right before. But in the ONLINE CREATE INDEX
case, psql should figure out that it must *not* send the BEGIN.

The server logic does not change in either case. It only knows to
reject the ONLINE CREATE INDEX when inside a transaction block, because
it cannot possibly roll it back.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.