Thread: Better name/syntax for "online" index creation
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'm also wondering about where in the command the keyword should go.
As submitted it's
( { <replaceable class="parameter">column</replaceable> | ( <replaceable
class="parameter">expression</replaceable>) } [ <replaceable class="parameter">opclass</replaceable> ] [, ...] ) [
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> ]
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.
I'm tempted to put the new keyword at the very front:
SHARED CREATE INDEX ....
which would probably mean that we'd have to document it as if it were a
completely separate command from CREATE INDEX, but then again that might
not be a bad thing considering how differently the two cases behave.
If not that, we probably still need to put it somewhere near the front
for psql's sake.
Comments?
regards, tom lane
> SHARED CREATE INDEX .... > Comments? CREATE [UNIQUE] INDEX <foo> [WITH NOLOCK] ON ... --
Sorry, hit send too quickly. NOLOCK is kinda like NOWAIT, except implies that the command will not take a strong lock instead of stating that it will not wait for one. On Mon, 2006-07-24 at 11:20 -0400, Rod Taylor wrote: > > SHARED CREATE INDEX .... > > > Comments? > > CREATE [UNIQUE] INDEX <foo> [WITH NOLOCK] ON ... > > --
Am Montag, 24. Juli 2006 17:13 schrieb Tom Lane: > 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? CONCURRENTLY > I'm tempted to put the new keyword at the very front: > > SHARED CREATE INDEX .... > > which would probably mean that we'd have to document it as if it were a > completely separate command from CREATE INDEX, but then again that might > not be a bad thing considering how differently the two cases behave. What is so different about them that would justify this? -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes:
> Am Montag, 24. Juli 2006 17:13 schrieb Tom Lane:
>> I'm tempted to put the new keyword at the very front:
>>
>> SHARED CREATE INDEX ....
>>
>> which would probably mean that we'd have to document it as if it were a
>> completely separate command from CREATE INDEX, but then again that might
>> not be a bad thing considering how differently the two cases behave.
> What is so different about them that would justify this?
Well, mainly it's exactly the reason that psql wants to know the
difference: one can be executed inside a transaction block, and the
other one can't. To my mind that's a sufficiently big difference
that it deserves a different command name. We messed this up with
CLUSTER but that's not a precedent I want to follow.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.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
Greg Stark asked: > 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 have to live with MSSQL at work, and I didn't remember anything like this, so I looked up the syntax for CREATE INDEX (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlre f/ts_tsqlcon_6lyk.asp), and I can't find anything that gives the user control over this. The closest is this note in the remarks: Backup and CREATE INDEX operations do not block each other. If a backup is in progress, index is created in a fully logged mode, which may require extra log space. It sounds to me like they fall back to 'online' index creation if a backup is in progress, but give the user no control over it. I also looked in the settings and didn't see anything relevant. Regards, Paul Bort
Greg Stark wrote: > Tom Lane <tgl@sss.pgh.pa.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.
Greg Stark wrote: > 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. Huh? Only if you invent your own stored-procedure theory or have a hitherto unknown interpretation of the SQL standard. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > Greg Stark wrote: > > 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. > > Huh? Only if you invent your own stored-procedure theory or have a > hitherto unknown interpretation of the SQL standard. We've discussed a couple of times that stored procedures should have enough abilities to start and commit transactions on their own, to be useful to administration tasks like vacuuming. It's not something that has been set in stone but it's something to consider. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Rod Taylor wrote: > Sorry, hit send too quickly. > > NOLOCK is kinda like NOWAIT, except implies that the command will not > take a strong lock instead of stating that it will not wait for one. > > On Mon, 2006-07-24 at 11:20 -0400, Rod Taylor wrote: > > > SHARED CREATE INDEX .... I like NOLOCK myself. ONLINE makes it sound like non-ONLINE index creation has to happen when the server is down. I don't like SHARED as the first word because we don't do this in any other cases --- it makes it look like the command is SHARED, and I am sure people will try putting SHARED in front of other commands like UPDATE. "Hey, where is the SHARED manual page?" Anyway, if you want psql to easily identify it, just return NOLOCK as part of the command string returned: test=> create index i on test(x);CREATE INDEX NOLOCK -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Rod Taylor wrote: > > Sorry, hit send too quickly. > > > > NOLOCK is kinda like NOWAIT, except implies that the command will not > > take a strong lock instead of stating that it will not wait for one. > > > > On Mon, 2006-07-24 at 11:20 -0400, Rod Taylor wrote: > > > > SHARED CREATE INDEX .... > > I like NOLOCK myself. ONLINE makes it sound like non-ONLINE index > creation has to happen when the server is down. > > I don't like SHARED as the first word because we don't do this in any > other cases --- it makes it look like the command is SHARED, and I am > sure people will try putting SHARED in front of other commands like > UPDATE. "Hey, where is the SHARED manual page?" > > Anyway, if you want psql to easily identify it, just return NOLOCK as > part of the command string returned: > > test=> create index i on test(x); > CREATE INDEX NOLOCK Oh, psql needs to know before the command is sent? How do we handle it now with CLUSTER? Whatever psql is trying to prevent doesn't seem to warrant mucking up the logical order of the CREATE INDEX command. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > > Anyway, if you want psql to easily identify it, just return NOLOCK as > > part of the command string returned: > > > > test=> create index i on test(x); > > CREATE INDEX NOLOCK > > Oh, psql needs to know before the command is sent? How do we handle it > now with CLUSTER? We don't, which is exactly the problem. If I'm not mistaken, currently psql in autocommit off mode, CLUSTER doesn't start a transaction block, which is arguably wrong because some forms of CLUSTER (single-table) are able to work within a transaction. But since not all of them are, then we must act like they all were, because otherwise we would send spurious error messages to the user. > Whatever psql is trying to prevent doesn't seem to > warrant mucking up the logical order of the CREATE INDEX command. Personally I'm not sure if this is too serious an issue. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Bruce Momjian wrote:
>> Oh, psql needs to know before the command is sent? How do we handle it
>> now with CLUSTER?
> We don't, which is exactly the problem. If I'm not mistaken, currently
> psql in autocommit off mode, CLUSTER doesn't start a transaction block,
> which is arguably wrong because some forms of CLUSTER (single-table) are
> able to work within a transaction.
psql could actually tell these apart if it worked just a bit harder.
CLUSTER with no arguments is the one case, CLUSTER with anything after
it is the other. Not sure why we couldn't be bothered to get that
right in psql the first time :-(.
But to get back to the point at hand, I think that there should be some
equally obvious syntactic clue about what CREATE INDEX does --- and
burying an ONLINE keyword near the end of the command doesn't qualify.
regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > Bruce Momjian wrote: > >> Oh, psql needs to know before the command is sent? How do we handle it > >> now with CLUSTER? > > > We don't, which is exactly the problem. If I'm not mistaken, currently > > psql in autocommit off mode, CLUSTER doesn't start a transaction block, > > which is arguably wrong because some forms of CLUSTER (single-table) are > > able to work within a transaction. > > psql could actually tell these apart if it worked just a bit harder. > CLUSTER with no arguments is the one case, CLUSTER with anything after > it is the other. Not sure why we couldn't be bothered to get that > right in psql the first time :-(. > > But to get back to the point at hand, I think that there should be some > equally obvious syntactic clue about what CREATE INDEX does --- and > burying an ONLINE keyword near the end of the command doesn't qualify. OK, how about CREATE INDEX x NOLOCK ON tab ... Please don't use it as the first word. I am afraid we would regret that. Or maybe CREATE INDEX x ON tab NOLOCK.... Having the NOLOCK associated with the table name makes sense. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> OK, how about CREATE INDEX x NOLOCK ON tab ... Please don't use it as
> the first word. I am afraid we would regret that.
> Or maybe CREATE INDEX x ON tab NOLOCK.... Having the NOLOCK
> associated with the table name makes sense.
psql would be a lot happier if the NOLOCK keyword were in front of
either of the identifiers. Otherwise it's got to deal with quoting
and possible schema qualification.
I have no objection to either of these syntaxes as long as someone else
writes the psql patch, however ;-)
regards, tom lane
Am Dienstag, 25. Juli 2006 06:55 schrieb Bruce Momjian: > Or maybe CREATE INDEX x ON tab NOLOCK.... Having the NOLOCK > associated with the table name makes sense. Semantically, NOLOCK (or whatever word) seems most closely associated with CREATE INDEX, so having it as third word would make sense, and it would be quite easy to parse for psql. Strictly speaking, however, it would have to be NOLOCKLY in that case. :-) -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > Am Dienstag, 25. Juli 2006 06:55 schrieb Bruce Momjian: > > Or maybe CREATE INDEX x ON tab NOLOCK.... Having the NOLOCK > > associated with the table name makes sense. > > Semantically, NOLOCK (or whatever word) seems most closely associated with > CREATE INDEX, so having it as third word would make sense, and it would be > quite easy to parse for psql. > > Strictly speaking, however, it would have to be NOLOCKLY in that case. :-) We can use the same code we use in psql for tab completion to find a keyword in the line. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
> Semantically, NOLOCK (or whatever word) seems most closely associated with > CREATE INDEX, so having it as third word would make sense, and it would be > quite easy to parse for psql. > > Strictly speaking, however, it would have to be NOLOCKLY in that case. :-) In this case CREATE INDEX CONCURRENTLY ... sounds better to me, although the whole feature sounds nice any way you will finally call it ;-) Cheers, Csaba.
Csaba Nagy <nagy@ecircle-ag.com> writes:
>> Strictly speaking, however, it would have to be NOLOCKLY in that case. :-)
> In this case CREATE INDEX CONCURRENTLY ... sounds better to me, although
> the whole feature sounds nice any way you will finally call it ;-)
That reads well to me too. We'd need to check whether it can be parsed
without making CONCURRENTLY a fully-reserved word, but offhand I think
it would work because ON is already a fully-reserved word ...
regards, tom lane
Ühel kenal päeval, T, 2006-07-25 kell 11:26, kirjutas Tom Lane:
> Csaba Nagy <nagy@ecircle-ag.com> writes:
> >> Strictly speaking, however, it would have to be NOLOCKLY in that case. :-)
>
> > In this case CREATE INDEX CONCURRENTLY ... sounds better to me, although
> > the whole feature sounds nice any way you will finally call it ;-)
>
> That reads well to me too. We'd need to check whether it can be parsed
> without making CONCURRENTLY a fully-reserved word, but offhand I think
> it would work because ON is already a fully-reserved word ...
At some point we may add some other ops we start doing CONCURRENTLY,
like perhaps CLUSTER CONCURRENTLY or even ALTER TABLE CONCURRENTLY ADD
COLUMN x DEFAULT nextval('s'); and other table rewriting ops.
--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia
Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com
> Tom Lane wrote: > > psql could actually tell these apart if it worked just a bit harder. > CLUSTER with no arguments is the one case, CLUSTER with > anything after it is the other. Not sure why we couldn't be > bothered to get that right in psql the first time :-(. > Should this go on the to-do list? Regards, Paul Bort
On 7/25/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Csaba Nagy <nagy@ecircle-ag.com> writes: > >> Strictly speaking, however, it would have to be NOLOCKLY in that case. :-) > > > In this case CREATE INDEX CONCURRENTLY ... sounds better to me, although > > the whole feature sounds nice any way you will finally call it ;-) > > That reads well to me too. We'd need to check whether it can be parsed > without making CONCURRENTLY a fully-reserved word, but offhand I think > it would work because ON is already a fully-reserved word ... Is there a chance that the locking variant will be replaced by non-locking variant, or do we definitely want the locking variant to stay? Basically, this means whether the naming is temporary or permanent. -- marko
"Marko Kreen" <markokr@gmail.com> writes:
> Is there a chance that the locking variant will be replaced by
> non-locking variant,
No, I don't think so. Given that the concurrent form is much slower
(extra table scan) and can't be wrapped into a transaction block,
it'll always be a special option.
regards, tom lane
Quoting Greg Stark <gsstark@MIT.EDU>: > Tom Lane <tgl@sss.pgh.pa.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? Fwiw a few data points: MSSQL uses (WITH ONLINE=ON) much like we and Oracle use ONLINE tacked on to the end of the create index command. The DB2 handbook says "Tables can now be reorganized online with almost full acess to the table allowed" but their syntax does not use the word "online". They actually support three modes: ALLOW NO ACCESS ALLOW READ ACCESS ALLOW WRITE ACCESS I'm not sure where that leaves us. The word "online" seems deeply ingrained in the database vocabulary for this feature but there's a certain amount of SQLishness in the DB2 syntax too. DB2 also supports some other nice options like interrupting an index build and then resuming it later. If there's demand that seems like something we would be able to support too. The same infrastructure might be useful for suspending and resuming vacuums on large tables too. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
I understand the negative implications with calling it "ONLINE" with regards to the index rebuild but I believe that would follow what the industry and professionals understand. Oracle denotes this concept as ONLINE and microsoft with it's new SQL Server 2005 markets the ability to do "ONLINE" reindexing. (rebuilding an index in SQL Server 2000 will cause blocking) If I didn't know anything about PostgreSQL and read a manual about it's indexing capabilities and read that it had ONLINE reindexing, the first thing that I would think about was the ability to rebuild my indexes without causing any blocking or writers or readers. There might be a better token word to use in this situation but I don't think that ONLINE would be out of bounds... Just my 2 cents... Paul Silveira -- View this message in context: http://www.nabble.com/Better-name-syntax-for-%22online%22-index-creation-tf1992993.html#a5501425 Sent from the PostgreSQL - hackers forum at Nabble.com.
Ühel kenal päeval, K, 2006-07-26 kell 06:40, kirjutas Gregory S Stark: > The DB2 handbook says "Tables can now be reorganized online with almost full > acess to the table allowed" but their syntax does not use the word "online". Does this reorganizing refer to what we do with CLUSTER ? I'd really love to be able to CLUSTER a table with ALLOW WRITE ACCESS. currently this requires quite a complicated dance with views,triggers, logfiles and changing access funtions. > They actually support three modes: > > ALLOW NO ACCESS > ALLOW READ ACCESS > ALLOW WRITE ACCESS > > I'm not sure where that leaves us. The word "online" seems deeply ingrained in > the database vocabulary for this feature but there's a certain amount of > SQLishness in the DB2 syntax too. > > DB2 also supports some other nice options like interrupting an index build and > then resuming it later. If there's demand that seems like something we > would be > able to support too. The same infrastructure might be useful for > suspending and > resuming vacuums on large tables too. > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
Peter Eisentraut <peter_e@gmx.net> writes: > Greg Stark wrote: > > 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. > > Huh? Only if you invent your own stored-procedure theory or have a > hitherto unknown interpretation of the SQL standard. Well I haven't read that section of the standard. But from discussions on the mailing list the key feature distinguishing "stored procedures" from "functions" will be whether they live inside or outside transactions. Functions are called within WHERE clauses and elsewhere and cannot start or end transactions since that would be nonsensical. Stored procedures are basically shell scripts running in the database engine and can do pretty much anything the user can do from a psql prompt including things like starting and end transactions and mutating state variables. I suppose there's nothing stopping them from being called with a transaction already opened, closing it and starting a new one but it seems like that would be very confusing for users. Essentially that's why CLUSTER and CREATE INDEX ... ONLINE make sure there isn't a user started transaction already. Not because it wouldn't work but because the interface would just be surprising and confusing for users. I guess that never stopped the SQL standard committee before, why should it start now? p -- greg
Paul Silveira <plabrh1@gmail.com> writes:
> If I didn't know anything about PostgreSQL and read a manual about it's
> indexing capabilities and read that it had ONLINE reindexing, the first
> thing that I would think about was the ability to rebuild my indexes without
> causing any blocking or writers or readers.
Only because you're already familiar with Oracle --- it will not convey
such a meaning to people who don't know that product.
regards, tom lane
Gregory Start wrote: > > Fwiw a few data points: > > MSSQL uses (WITH ONLINE=ON) much like we and Oracle use ONLINE tacked > on to the > end of the create index command. > Where did you find this? I thought my MSDN-foo was pretty good, and I didn't find this when searched a couple days ago. Regards, Paul Bort
Hannu Krosing <hannu@skype.net> writes: > Ãhel kenal päeval, K, 2006-07-26 kell 06:40, kirjutas Gregory S Stark: > > The DB2 handbook says "Tables can now be reorganized online with almost full > > acess to the table allowed" but their syntax does not use the word "online". > > Does this reorganizing refer to what we do with CLUSTER ? > > I'd really love to be able to CLUSTER a table with ALLOW WRITE ACCESS. It's hard for me to tell from this handbook but it sort of seems like what you're looking for is "IN PLACE" for which the default is ALLOW WRITE ACCESS. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Yeah, your probably right. :)
On 7/26/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Paul Silveira <plabrh1@gmail.com> writes:
> If I didn't know anything about PostgreSQL and read a manual about it's
> indexing capabilities and read that it had ONLINE reindexing, the first
> thing that I would think about was the ability to rebuild my indexes without
> causing any blocking or writers or readers.
Only because you're already familiar with Oracle --- it will not convey
such a meaning to people who don't know that product.
regards, tom lane
Ühel kenal päeval, K, 2006-07-26 kell 13:23, kirjutas Gregory Stark: > Hannu Krosing <hannu@skype.net> writes: > > > Ãœhel kenal päeval, K, 2006-07-26 kell 06:40, kirjutas Gregory S Stark: > > > The DB2 handbook says "Tables can now be reorganized online with almost full > > > acess to the table allowed" but their syntax does not use the word "online". > > > > Does this reorganizing refer to what we do with CLUSTER ? > > > > I'd really love to be able to CLUSTER a table with ALLOW WRITE ACCESS. > > It's hard for me to tell from this handbook but it sort of seems like what > you're looking for is "IN PLACE" for which the default is ALLOW WRITE ACCESS. IN PLACE CLUSTER would be sweet. Do you know of anyone who would be willing to do it for postgresql ? Preferrably for 8.2 :) -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
I really like the CREATE INDEX CONCURRENTLY suggestion that I've seem in this thread. That seems like a good alternative to ONLINE and is very easy to understand. Regards, Paul -- View this message in context: http://www.nabble.com/Better-name-syntax-for-%22online%22-index-creation-tf1992993.html#a5538009 Sent from the PostgreSQL - hackers forum at Nabble.com.