Greg Stark wrote: > Tom Lane <[EMAIL PROTECTED]> 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. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
