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


Tue, 27 Aug 2002 15:33:37 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
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

Tue, 27 Aug 2002 10:17:06 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: REINDEX ALL and CLUSTER ALL
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".


Tue, 27 Aug 2002 09:51:10 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: REINDEX ALL and CLUSTER ALL

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

Tue, 27 Aug 2002 12:08:25 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: REINDEX ALL and CLUSTER ALL
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)


Tue, 27 Aug 2002 12:13:02 -0400 (CLT)
Alvaro Herrera <alvherre@atentus.com>
Re: REINDEX ALL and CLUSTER ALL
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

Tue, 27 Aug 2002 12:18:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: REINDEX ALL and CLUSTER ALL
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".
> > 
> > 
> 
> 


Tue, 27 Aug 2002 11:14:58 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: REINDEX ALL and CLUSTER ALL

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

Tue, 27 Aug 2002 13:45:20 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: REINDEX ALL and CLUSTER ALL
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)
> > 
> 
> 


Tue, 27 Aug 2002 13:39:49 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Is REINDEX ALL safe?
"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

Tue, 27 Aug 2002 16:08:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: REINDEX ALL and CLUSTER ALL 
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

Tue, 27 Aug 2002 16:15:35 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: REINDEX ALL and CLUSTER ALL