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
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
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
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
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
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 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])
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
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 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) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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
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
