Re: [HACKERS] REINDEX ALL
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] REINDEX ALL
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] REINDEX ALL
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] REINDEX ALL
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 ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] REINDEX ALL
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] REINDEX ALL
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 [EMAIL PROTECTED]
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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] REINDEX ALL
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 [EMAIL PROTECTED]
Re: [HACKERS] REINDEX ALL and CLUSTER ALL
Tom Lane wrote: > "scott.marlowe" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] REINDEX ALL and CLUSTER ALL
"scott.marlowe" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 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 [EMAIL PROTECTED]) > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] 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". > > > > > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 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 ()
"Porque Kim no hacia nada, pero, eso si,
con extraordinario exito" ("Kim", Kipling)
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 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". ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
