Обсуждение: REINDEX ALL and CLUSTER ALL
Would it be worth adding REINDEX ALL and CLUSTER ALL as actual SQL commands? This would be neat. Plus, it means we don't have to worry about having unix-only script in the distro once we have Win32 support. Actually, we should just leave the 'ALL' off. That will make them behave like VACUUM without arguments... Chris
Christopher Kings-Lynne wrote:
> Would it be worth adding REINDEX ALL and CLUSTER ALL as actual SQL commands?
> This would be neat. Plus, it means we don't have to worry about having
> unix-only script in the distro once we have Win32 support.
>
> Actually, we should just leave the 'ALL' off. That will make them behave
> like VACUUM without arguments...
Wow, now that is a nify idea! Let me add it to TODO and we can get rid
of the shell scripts entirely:
o Allow CLUSTER to cluster all tables, remove clusterdbo Allow REINDEX to rebuild all indexes, remove
/contrib/reindex
If we ever get the index growth fixed, we will not need the reindex
change, I guess, but maybe if they have some index corruption but they
are not sure where it may be helpful.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
On Tue, 27 Aug 2002, Bruce Momjian wrote: > Christopher Kings-Lynne wrote: > > Would it be worth adding REINDEX ALL and CLUSTER ALL as actual SQL commands? > > This would be neat. Plus, it means we don't have to worry about having > > unix-only script in the distro once we have Win32 support. > > > > Actually, we should just leave the 'ALL' off. That will make them behave > > like VACUUM without arguments... > > Wow, now that is a nify idea! Let me add it to TODO and we can get rid > of the shell scripts entirely: > > o Allow CLUSTER to cluster all tables, remove clusterdb > o Allow REINDEX to rebuild all indexes, remove /contrib/reindex > > If we ever get the index growth fixed, we will not need the reindex > change, I guess, but maybe if they have some index corruption but they > are not sure where it may be helpful. Isn't it true that reindex's behavior is to simply, quietly delete the index? that was reported by someone when all this was going around before. I wrote my own reindex script that basically (in a single transaction) grabbed the definition of the index, dropped said index, then recreated it, then committed the transaction, so that if it failed for any reason, the old index was still there. If reindex does "lose" the index on failure then we need to look at changing how it works before we recommend it as a "daily maintenance routine".
REINDEX just rebuilds the index, not just drop it. In fact, 7.3 will have a reindexdb script. --------------------------------------------------------------------------- scott.marlowe wrote: > On Tue, 27 Aug 2002, Bruce Momjian wrote: > > > Christopher Kings-Lynne wrote: > > > Would it be worth adding REINDEX ALL and CLUSTER ALL as actual SQL commands? > > > This would be neat. Plus, it means we don't have to worry about having > > > unix-only script in the distro once we have Win32 support. > > > > > > Actually, we should just leave the 'ALL' off. That will make them behave > > > like VACUUM without arguments... > > > > Wow, now that is a nify idea! Let me add it to TODO and we can get rid > > of the shell scripts entirely: > > > > o Allow CLUSTER to cluster all tables, remove clusterdb > > o Allow REINDEX to rebuild all indexes, remove /contrib/reindex > > > > If we ever get the index growth fixed, we will not need the reindex > > change, I guess, but maybe if they have some index corruption but they > > are not sure where it may be helpful. > > Isn't it true that reindex's behavior is to simply, quietly delete the > index? that was reported by someone when all this was going around > before. I wrote my own reindex script that basically (in a single > transaction) grabbed the definition of the index, dropped said index, then > recreated it, then committed the transaction, so that if it failed for any > reason, the old index was still there. > > If reindex does "lose" the index on failure then we need to look at > changing how it works before we recommend it as a "daily maintenance > routine". > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Tue, 27 Aug 2002, Bruce Momjian wrote:
> Christopher Kings-Lynne wrote:
> > Would it be worth adding REINDEX ALL and CLUSTER ALL as actual SQL commands?
> > This would be neat. Plus, it means we don't have to worry about having
> > unix-only script in the distro once we have Win32 support.
> >
> > Actually, we should just leave the 'ALL' off. That will make them behave
> > like VACUUM without arguments...
>
> Wow, now that is a nify idea! Let me add it to TODO and we can get rid
> of the shell scripts entirely:
>
> o Allow CLUSTER to cluster all tables, remove clusterdb
> o Allow REINDEX to rebuild all indexes, remove /contrib/reindex
Huh... I asked whether to do the CLUSTER ALL thing, and someone said it
was just bloat; no one seemed to think it was useful, so I abandoned the
idea.
--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Porque Kim no hacia nada, pero, eso si,
con extraordinario exito" ("Kim", Kipling)
Alvaro Herrera wrote: > On Tue, 27 Aug 2002, Bruce Momjian wrote: > > > Christopher Kings-Lynne wrote: > > > Would it be worth adding REINDEX ALL and CLUSTER ALL as actual SQL commands? > > > This would be neat. Plus, it means we don't have to worry about having > > > unix-only script in the distro once we have Win32 support. > > > > > > Actually, we should just leave the 'ALL' off. That will make them behave > > > like VACUUM without arguments... > > > > Wow, now that is a nify idea! Let me add it to TODO and we can get rid > > of the shell scripts entirely: > > > > o Allow CLUSTER to cluster all tables, remove clusterdb > > o Allow REINDEX to rebuild all indexes, remove /contrib/reindex > > Huh... I asked whether to do the CLUSTER ALL thing, and someone said it > was just bloat; no one seemed to think it was useful, so I abandoned the > idea. Oh, we did? Yes, I remember that. Well, seeing as we now need clusterdb command, it would be better to get the backend to do it rather than have a separate command floating around. A separate script is certainly more bloat than whatever code we would add in cluster.c. I think this may have been before we got on the idea of marking pg_attribute with cluster info so we could more easily do cluster of all tables. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Sorry, that should have been: Isn't it true that reindex's behavior ON A FAILURE is to simply, quietly delete the index? that was reported ^^^^^^^^^^^^^ On Tue, 27 Aug 2002, Bruce Momjian wrote: > > REINDEX just rebuilds the index, not just drop it. In fact, 7.3 will > have a reindexdb script. > > --------------------------------------------------------------------------- > > scott.marlowe wrote: > > On Tue, 27 Aug 2002, Bruce Momjian wrote: > > > > > Christopher Kings-Lynne wrote: > > > > Would it be worth adding REINDEX ALL and CLUSTER ALL as actual SQL commands? > > > > This would be neat. Plus, it means we don't have to worry about having > > > > unix-only script in the distro once we have Win32 support. > > > > > > > > Actually, we should just leave the 'ALL' off. That will make them behave > > > > like VACUUM without arguments... > > > > > > Wow, now that is a nify idea! Let me add it to TODO and we can get rid > > > of the shell scripts entirely: > > > > > > o Allow CLUSTER to cluster all tables, remove clusterdb > > > o Allow REINDEX to rebuild all indexes, remove /contrib/reindex > > > > > > If we ever get the index growth fixed, we will not need the reindex > > > change, I guess, but maybe if they have some index corruption but they > > > are not sure where it may be helpful. > > > > Isn't it true that reindex's behavior is to simply, quietly delete the > > index? that was reported by someone when all this was going around > > before. I wrote my own reindex script that basically (in a single > > transaction) grabbed the definition of the index, dropped said index, then > > recreated it, then committed the transaction, so that if it failed for any > > reason, the old index was still there. > > > > If reindex does "lose" the index on failure then we need to look at > > changing how it works before we recommend it as a "daily maintenance > > routine". > > > > > >
I am not sure, but it certainly makes sense that it would drop the index on failure. I would never expect it to fail, however. --------------------------------------------------------------------------- scott.marlowe wrote: > Sorry, that should have been: > > Isn't it true that reindex's behavior ON A FAILURE is to simply, quietly > delete the index? that was reported ^^^^^^^^^^^^^ > > > On Tue, 27 Aug 2002, Bruce Momjian wrote: > > > > > REINDEX just rebuilds the index, not just drop it. In fact, 7.3 will > > have a reindexdb script. > > > > --------------------------------------------------------------------------- > > > > scott.marlowe wrote: > > > On Tue, 27 Aug 2002, Bruce Momjian wrote: > > > > > > > Christopher Kings-Lynne wrote: > > > > > Would it be worth adding REINDEX ALL and CLUSTER ALL as actual SQL commands? > > > > > This would be neat. Plus, it means we don't have to worry about having > > > > > unix-only script in the distro once we have Win32 support. > > > > > > > > > > Actually, we should just leave the 'ALL' off. That will make them behave > > > > > like VACUUM without arguments... > > > > > > > > Wow, now that is a nify idea! Let me add it to TODO and we can get rid > > > > of the shell scripts entirely: > > > > > > > > o Allow CLUSTER to cluster all tables, remove clusterdb > > > > o Allow REINDEX to rebuild all indexes, remove /contrib/reindex > > > > > > > > If we ever get the index growth fixed, we will not need the reindex > > > > change, I guess, but maybe if they have some index corruption but they > > > > are not sure where it may be helpful. > > > > > > Isn't it true that reindex's behavior is to simply, quietly delete the > > > index? that was reported by someone when all this was going around > > > before. I wrote my own reindex script that basically (in a single > > > transaction) grabbed the definition of the index, dropped said index, then > > > recreated it, then committed the transaction, so that if it failed for any > > > reason, the old index was still there. > > > > > > If reindex does "lose" the index on failure then we need to look at > > > changing how it works before we recommend it as a "daily maintenance > > > routine". > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
I would guess that if someone mentioned it, then it HAS happened at least once, maybe more. Would doing it in a transaction be a good idea or not? I'm not that familiar with the implications of doing a reindex by hand in a transaction. Since reindex was designed to fix broken indexes, it's use to reclaim space may awaken bugs no man has dared to dream exist before. Or something like that. On Tue, 27 Aug 2002, Bruce Momjian wrote: > > I am not sure, but it certainly makes sense that it would drop the index > on failure. I would never expect it to fail, however. > > --------------------------------------------------------------------------- > > scott.marlowe wrote: > > Sorry, that should have been: > > > > Isn't it true that reindex's behavior ON A FAILURE is to simply, quietly > > delete the index? that was reported ^^^^^^^^^^^^^ > > > > > > On Tue, 27 Aug 2002, Bruce Momjian wrote: > > > > > > > > REINDEX just rebuilds the index, not just drop it. In fact, 7.3 will > > > have a reindexdb script. > > > > > > --------------------------------------------------------------------------- > > > > > > scott.marlowe wrote: > > > > On Tue, 27 Aug 2002, Bruce Momjian wrote: > > > > > > > > > Christopher Kings-Lynne wrote: > > > > > > Would it be worth adding REINDEX ALL and CLUSTER ALL as actual SQL commands? > > > > > > This would be neat. Plus, it means we don't have to worry about having > > > > > > unix-only script in the distro once we have Win32 support. > > > > > > > > > > > > Actually, we should just leave the 'ALL' off. That will make them behave > > > > > > like VACUUM without arguments... > > > > > > > > > > Wow, now that is a nify idea! Let me add it to TODO and we can get rid > > > > > of the shell scripts entirely: > > > > > > > > > > o Allow CLUSTER to cluster all tables, remove clusterdb > > > > > o Allow REINDEX to rebuild all indexes, remove /contrib/reindex > > > > > > > > > > If we ever get the index growth fixed, we will not need the reindex > > > > > change, I guess, but maybe if they have some index corruption but they > > > > > are not sure where it may be helpful. > > > > > > > > Isn't it true that reindex's behavior is to simply, quietly delete the > > > > index? that was reported by someone when all this was going around > > > > before. I wrote my own reindex script that basically (in a single > > > > transaction) grabbed the definition of the index, dropped said index, then > > > > recreated it, then committed the transaction, so that if it failed for any > > > > reason, the old index was still there. > > > > > > > > If reindex does "lose" the index on failure then we need to look at > > > > changing how it works before we recommend it as a "daily maintenance > > > > routine". > > > > > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > >
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> Sorry, that should have been:
> Isn't it true that reindex's behavior ON A FAILURE is to simply, quietly
> delete the index? that was reported ^^^^^^^^^^^^^
No.
If you are doing a standalone system index rebuild (with backend -P
switch) then REINDEX does a "TRUNCATE" of the index relation and
rebuilds it in place. If that fails partway through, you'd be left
with a corrupted index ... which presumably is the same problem you
started with, so I'm not that concerned about it.
The TRUNCATE approach is also used for rebuilding indexes on shared
system relations (pg_database, pg_shadow, pg_group). This seems
necessary since REINDEX has no way to update pg_class.relfilenode in
databases other than the current one.
In all other cases the rebuild is rollback-able, and a failure should
leave you exactly where you were before.
Given these facts I think it would be a bad idea to include the shared
system relations in any automatic "REINDEX ALL" command. One could
make a good argument that any such command should skip *all* system
tables, actually.
regards, tom lane
Tom Lane wrote: > "scott.marlowe" <scott.marlowe@ihs.com> writes: > > Sorry, that should have been: > > Isn't it true that reindex's behavior ON A FAILURE is to simply, quietly > > delete the index? that was reported ^^^^^^^^^^^^^ > > No. > > If you are doing a standalone system index rebuild (with backend -P > switch) then REINDEX does a "TRUNCATE" of the index relation and > rebuilds it in place. If that fails partway through, you'd be left > with a corrupted index ... which presumably is the same problem you > started with, so I'm not that concerned about it. > > The TRUNCATE approach is also used for rebuilding indexes on shared > system relations (pg_database, pg_shadow, pg_group). This seems > necessary since REINDEX has no way to update pg_class.relfilenode in > databases other than the current one. > > In all other cases the rebuild is rollback-able, and a failure should > leave you exactly where you were before. > > > Given these facts I think it would be a bad idea to include the shared > system relations in any automatic "REINDEX ALL" command. One could > make a good argument that any such command should skip *all* system > tables, actually. Yes, absolutely. REINDEX is not like vacuum. It needs to skip all system tables, I think. Those indexes are tied into backend structures. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073