Postgres Pro
Downloads
Home   >   mailing lists

Thread: REINDEX ALL

REINDEX ALL

From
"Joshua D. Drake"
Date:
Hello,

We are considering submitting a patch for REINDEX ALL. What syntax would
we like?

REINDEX ALL?
REINDEX DATABASE ALL?


Sincerely,

Joshua D. Drake


-- Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564
Custom programming, 24x7 support, managed services, and hosting
Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG
Reliable replication, Mammoth Replicator - http://www.commandprompt.com/

Re: REINDEX ALL

From
Hans-Jürgen Schönig
Date:
Joshua D. Drake wrote:
> Hello,
> 
> We are considering submitting a patch for REINDEX ALL. What syntax would
> we like?
> 
> REINDEX ALL?
> REINDEX DATABASE ALL?
> 
> 
> Sincerely,
> 
> Joshua D. Drake
> 
> 
> -- Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564
> Custom programming, 24x7 support, managed services, and hosting
> Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG
> Reliable replication, Mammoth Replicator - http://www.commandprompt.com/
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


Joshua,

What is this patch supposed to do?
As far as I can see, there is already a reindex command ...

test=# \h reindex
Command:     REINDEX
Description: rebuild indexes
Syntax:
REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]

test=#
Best regards,
    Hans

-- 
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/664/393 39 74
www.cybertec.at, www.postgresql.at

Re: REINDEX ALL

From
Tom Lane
Date:
Hans-Jürgen Schönig <postgres@cybertec.at> writes:
> What is this patch supposed to do?
> As far as I can see, there is already a reindex command ...

> test=# \h reindex
> Command:     REINDEX
> Description: rebuild indexes
> Syntax:
> REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]

IIRC, "REINDEX DATABASE" only reindexes the system catalogs.  I suppose
Joshua is thinking of reindexing all non-system tables --- whether in
addition to the system catalogs, or just the user tables, isn't clear
... and it makes a difference to what I think the syntax should be ...

Another variant possibly worth considering is REINDEX TABLESPACE to hit
everything within a particular tablespace (defined with respect to the
table's tablespace, or the index's?  I dunno)

Really the question that needs to be answered for any of these is what's
the use-case driving the need for the feature.  Without that you have no
idea what it ought to do.
        regards, tom lane

Re: REINDEX ALL

From
"Joshua D. Drake"
Date:
The question is coming from the TODO:

Allow REINDEX to rebuild all database indexes, remove
contrib/reindexdb

We can do whatever the community wants :) Just tell us what it is.

Sincerely,

Joshua D. Drake



>IIRC, "REINDEX DATABASE" only reindexes the system catalogs.  I suppose
>Joshua is thinking of reindexing all non-system tables --- whether in
>addition to the system catalogs, or just the user tables, isn't clear
>... and it makes a difference to what I think the syntax should be ...
>
>

Re: REINDEX ALL

From
Andreas Pflug
Date:
Joshua D. Drake wrote:
> The question is coming from the TODO:
> 
> Allow REINDEX to rebuild all database indexes, remove
> contrib/reindexdb
> 
> We can do whatever the community wants :) Just tell us what it is.

RECREATE option that performs like  DROP/CREATE INDEX (best without 
changing the indexes OID)

Regards,
Andreas

Re: REINDEX ALL

From
Russell Smith
Date:
On Wed, 6 Apr 2005 08:18 am, Andreas Pflug wrote:
> Joshua D. Drake wrote:
> > The question is coming from the TODO:
> > 
> > Allow REINDEX to rebuild all database indexes, remove
> > contrib/reindexdb
> > 
> > We can do whatever the community wants :) Just tell us what it is.
> 
Does this pose a problem where everything will run inside one transaction,
effectively blocking some db functions until every table has been reindexed?

Regards

Russell Smith

Re: REINDEX ALL

From
"Joshua D. Drake"
Date:
Russell Smith wrote:

>On Wed, 6 Apr 2005 08:18 am, Andreas Pflug wrote:
>  
>
>>Joshua D. Drake wrote:
>>    
>>
>>>The question is coming from the TODO:
>>>
>>>Allow REINDEX to rebuild all database indexes, remove
>>>contrib/reindexdb
>>>
>>>We can do whatever the community wants :) Just tell us what it is.
>>>      
>>>
>Does this pose a problem where everything will run inside one transaction,
>effectively blocking some db functions until every table has been reindexed?
>  
>
No... only for the table that is being indexed.

>Regards
>
>Russell Smith
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>  
>