REINDEX ALL and CLUSTER ALL

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.

-- 
  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, Pennsylvania 19073

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, Pennsylvania 19073

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, Pennsylvania 19073

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:

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, Pennsylvania 19073

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:

"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, Pennsylvania 19073