Re: [PATCH] Improve documentation of REINDEX options
On Tue, Dec 17, 2019 at 10:23:51AM +0100, Josef Šimánek wrote: > For me this is the default way how to reindex whole table manually in psql > since you get some "progress". Anyway I can remove it if you don't see any > benefit in extending this example. I have thought more about this one through the night, and I am still not sure that this brings much. So, committed a simpler version handling only the option part. -- Michael signature.asc Description: PGP signature
Re: [PATCH] Improve documentation of REINDEX options
út 17. 12. 2019 v 6:36 odesílatel Michael Paquier napsal: > On Fri, Dec 13, 2019 at 10:28:33AM +0100, Josef Šimánek wrote: > > I have prepared patch to improve documentation for REINDEX. It > > should be more inline with another documentation pages. > > > > You can see the change applied in attached file. Patch can be found at > > https://github.com/simi/postgres/pull/3 (diff - > > https://github.com/simi/postgres/pull/3.diff, patch - > > https://github.com/simi/postgres/pull/3.patch). > > Please, always attach your patches to emails sent on this mailing > list. If for a reason or another, the data located to with external > link is lost (imagine for example that your github account is gone or > that github is reduced to ashes), then such patches would be lost, and > anybody looking at this email 10 years from now would not know what > you have been writing about here. I am attaching it here for the > archive's sake. > > +where option can > be: > + > +VERBOSE > Why not... We did that in the docs of ANALYZE for v11 when > introducing the parenthesized grammar flavor for the options > available. > > - Rebuild all the indexes on the table my_table: > + Rebuild all the indexes on the table my_table > with progress report per index: > > > -REINDEX TABLE my_table; > +REINDEX (VERBOSE) TABLE my_table; > Not sure if this part brings much to the reader though. It is not > like the command description of REINDEX is complicated with dozens > of option choices. > For me this is the default way how to reindex whole table manually in psql since you get some "progress". Anyway I can remove it if you don't see any benefit in extending this example. > -- > Michael >
Re: [PATCH] Improve documentation of REINDEX options
út 17. 12. 2019 v 6:36 odesílatel Michael Paquier
napsal:
> On Fri, Dec 13, 2019 at 10:28:33AM +0100, Josef Šimánek wrote:
> > I have prepared patch to improve documentation for REINDEX. It
> > should be more inline with another documentation pages.
> >
> > You can see the change applied in attached file. Patch can be found at
> > https://github.com/simi/postgres/pull/3 (diff -
> > https://github.com/simi/postgres/pull/3.diff, patch -
> > https://github.com/simi/postgres/pull/3.patch).
>
> Please, always attach your patches to emails sent on this mailing
> list. If for a reason or another, the data located to with external
> link is lost (imagine for example that your github account is gone or
> that github is reduced to ashes), then such patches would be lost, and
> anybody looking at this email 10 years from now would not know what
> you have been writing about here. I am attaching it here for the
> archive's sake.
>
Sorry, I'm attaching the same patch now for future reference.
>
> +where option can
> be:
> +
> +VERBOSE
> Why not... We did that in the docs of ANALYZE for v11 when
> introducing the parenthesized grammar flavor for the options
> available.
>
> - Rebuild all the indexes on the table my_table:
> + Rebuild all the indexes on the table my_table
> with progress report per index:
>
>
> -REINDEX TABLE my_table;
> +REINDEX (VERBOSE) TABLE my_table;
> Not sure if this part brings much to the reader though. It is not
> like the command description of REINDEX is complicated with dozens
> of option choices.
> --
> Michael
>
From 992fbb946daf0e5483319665c74a1a75dfea503b Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Josef=20=C5=A0im=C3=A1nek?=
Date: Fri, 13 Dec 2019 01:59:07 +0100
Subject: [PATCH] Unify REINDEX options documentation and extend examples.
---
doc/src/sgml/ref/reindex.sgml | 10 +++---
1 file changed, 7 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 10881ab03a85..007cceb320ee 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -21,7 +21,11 @@ PostgreSQL documentation
-REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
+REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
+
+where option can be:
+
+VERBOSE
@@ -422,10 +426,10 @@ REINDEX INDEX my_index;
- Rebuild all the indexes on the table my_table:
+ Rebuild all the indexes on the table my_table with progress report per index:
-REINDEX TABLE my_table;
+REINDEX (VERBOSE) TABLE my_table;
Re: [PATCH] Improve documentation of REINDEX options
On Fri, Dec 13, 2019 at 10:28:33AM +0100, Josef Šimánek wrote:
> I have prepared patch to improve documentation for REINDEX. It
> should be more inline with another documentation pages.
>
> You can see the change applied in attached file. Patch can be found at
> https://github.com/simi/postgres/pull/3 (diff -
> https://github.com/simi/postgres/pull/3.diff, patch -
> https://github.com/simi/postgres/pull/3.patch).
Please, always attach your patches to emails sent on this mailing
list. If for a reason or another, the data located to with external
link is lost (imagine for example that your github account is gone or
that github is reduced to ashes), then such patches would be lost, and
anybody looking at this email 10 years from now would not know what
you have been writing about here. I am attaching it here for the
archive's sake.
+where option can
be:
+
+VERBOSE
Why not... We did that in the docs of ANALYZE for v11 when
introducing the parenthesized grammar flavor for the options
available.
- Rebuild all the indexes on the table my_table:
+ Rebuild all the indexes on the table my_table
with progress report per index:
-REINDEX TABLE my_table;
+REINDEX (VERBOSE) TABLE my_table;
Not sure if this part brings much to the reader though. It is not
like the command description of REINDEX is complicated with dozens
of option choices.
--
Michael
From 992fbb946daf0e5483319665c74a1a75dfea503b Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Josef=20=C5=A0im=C3=A1nek?=
Date: Fri, 13 Dec 2019 01:59:07 +0100
Subject: [PATCH] Unify REINDEX options documentation and extend examples.
---
doc/src/sgml/ref/reindex.sgml | 10 +++---
1 file changed, 7 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 10881ab03a85..007cceb320ee 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -21,7 +21,11 @@ PostgreSQL documentation
-REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
+REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
+
+where option can be:
+
+VERBOSE
@@ -422,10 +426,10 @@ REINDEX INDEX my_index;
- Rebuild all the indexes on the table my_table:
+ Rebuild all the indexes on the table my_table with progress report per index:
-REINDEX TABLE my_table;
+REINDEX (VERBOSE) TABLE my_table;
signature.asc
Description: PGP signature
[PATCH] Improve documentation of REINDEX options
Hello!
According to discussion at pgsql-general (
https://www.postgresql.org/message-id/flat/CAFp7QwqFYcHiARfT91rOQj%3DmFT0MWBE%2BkxEmjfQh3QmRN1UBiw%40mail.gmail.com#05b75be4fd11c0e6216f0b329c808f72)
I
have prepared patch to improve documentation for REINDEX. It should be more
inline with another documentation pages.
You can see the change applied in attached file. Patch can be found at
https://github.com/simi/postgres/pull/3 (diff -
https://github.com/simi/postgres/pull/3.diff, patch -
https://github.com/simi/postgres/pull/3.patch).
This change is based on idea of Pave Stěhule, thanks a lot for that!
Similar approach was used recently in
https://www.postgresql.org/docs/devel/sql-dropdatabase.html.
Title: REINDEX
REINDEXPrev UpSQL CommandsHome NextREINDEXREINDEX — rebuild indexesSynopsisREINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
where option can be:
VERBOSE
Description
REINDEX rebuilds an index using the data
stored in the index's table, replacing the old copy of the index. There are
several scenarios in which to use REINDEX:
An index has become corrupted, and no longer contains valid
data. Although in theory this should never happen, in
practice indexes can become corrupted due to software bugs or
hardware failures. REINDEX provides a
recovery method.
An index has become “bloated”, that is it contains many
empty or nearly-empty pages. This can occur with B-tree indexes in
PostgreSQL under certain uncommon access
patterns. REINDEX provides a way to reduce
the space consumption of the index by writing a new version of
the index without the dead pages. See Section 24.2 for more information.
You have altered a storage parameter (such as fillfactor)
for an index, and wish to ensure that the change has taken full effect.
If an index build fails with the CONCURRENTLY option,
this index is left as “invalid”. Such indexes are useless
but it can be convenient to use REINDEX to rebuild
them. Note that only REINDEX INDEX is able
to perform a concurrent build on an invalid index.
ParametersINDEX
Recreate the specified index.
TABLE
Recreate all indexes of the specified table. If the table has a
secondary “TOAST” table, that is reindexed as well.
SCHEMA
Recreate all indexes of the specified schema. If a table of this
schema has a secondary “TOAST” table, that is reindexed as
well. Indexes on shared system catalogs are also processed.
This form of REINDEX cannot be executed inside a
transaction block.
DATABASE
Recreate all indexes within the current database.
Indexes on shared system catalogs are also processed.
This form of REINDEX cannot be executed inside a
transaction block.
SYSTEM
Recreate all indexes on system catalogs within the current database.
Indexes on shared system catalogs are included.
Indexes on user tables are not processed.
This form of REINDEX cannot be executed inside a
transaction block.
name
The name of the specific index, table, or database to be
reindexed. Index and table names can be schema-qualified.
Presently, REINDEX DATABASE and REINDEX SYSTEM
can only reindex the current database, so their parameter must match
the current database's name.
CONCURRENTLY
When this option is used, PostgreSQL will rebuild the
index without taking any locks that prevent concurrent inserts,
updates, or deletes on the table; whereas a standard index rebuild
locks out writes (but not reads) on the table until it's done.
There are several caveats to be aware of when using this option
— see Rebuilding Indexes Concurrently.
VERBOSE
Prints a progress report as each index is reindexed.
Notes
If you suspect corruption of an index on a user table, you can
simply rebuild that index, or all indexes on the table, using
REINDEX INDEX or REINDEX TABLE.
Things are more difficult if you need to recover from corruption of
an index on a system table. In this case it's important for the
system to not have used any of the suspect indexes itself.
(Indeed, in this sort of scenario you might find that server
processes are crashing immediately at start-up, due to reliance on
the corrupted indexes.) To recover safely, the server must be started
with the -P option, which prevents it from using
indexes for system catalog lookups.
One way to do this is to shut down the server and start a single-user
PostgreSQL server
with the -P option included on its command line.
Then, REINDEX DATABASE, REINDEX SYSTEM,
REINDEX TABLE, or REINDEX INDEX can be
issued, depending on how much you want to reconstruct. If in
doubt, use REINDEX SYSTEM to select
reconstruction o
