Обсуждение: ...

On 2020-05-26 11:49, Andrey Klychkov wrote:
> Centos 8.1, kernel 4.18.0-147.8.1.el8_1.x86_64
> PostgreSQL 12.3
> 
> I was trying to test execution time of reindexdb command.
> I ran the following command several times from the script:
> # time reindexdb -d ep2_test --concurrently
> The command broke indexes.

Did you by any chance upgrade the operating system on this server at 
some point?

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

=?UTF-8?B?QW5kcmV5IEtseWNoa292?= <aaklychkov@mail.ru> writes:
>> Did you by any chance upgrade the operating system on this server at
>> some point?

> It was installed from Centos 8.1 official iso, then was updated right after installation.

Well, there are two issues here:

1. How did you manage to get duplicate entries into the table?  This
suggests that the existing index is corrupt, else it should have detected
the duplicate.  Peter's question was leading towards one known way that
indexes on text columns can become corrupt.

2. Is reindexdb handling the failure sanely?  While I'd agree that
this behavior isn't especially desirable, it's the price of using
REINDEX CONCURRENTLY.  On failure, you're expected to clean up
manually by removing the leftover invalid index.  Perhaps the
documentation isn't clear enough about that, but I don't see a
bug there.

            regards, tom lane

On Tue, May 26, 2020 at 10:50:19AM -0400, Tom Lane wrote:
> 2. Is reindexdb handling the failure sanely?  While I'd agree that
> this behavior isn't especially desirable, it's the price of using
> REINDEX CONCURRENTLY.  On failure, you're expected to clean up
> manually by removing the leftover invalid index.  Perhaps the
> documentation isn't clear enough about that, but I don't see a
> bug there.

There is a paragraph about the handling of invalid indexes on the
reindex page:
https://www.postgresql.org/docs/devel/sql-reindex.html#SQL-REINDEX-CONCURRENTLY
"The recommended recovery method in such cases is to drop the invalid
index and try again to perform REINDEX CONCURRENTLY. The concurrent
index created during the processing has a name ending in the suffix
ccnew, or ccold if it is an old index definition which we failed to
drop. Invalid indexes can be dropped using DROP INDEX, including
invalid toast indexes."
--
Michael

1. if other indexes are possible to rebuild, reindexdb rebuilds them
2. if not (why?), reindexdb reports why

Среда, 27 мая 2020, 10:49 +03:00 от Michael Paquier <michael@paquier.xyz>:
 

On Tue, May 26, 2020 at 10:50:19AM -0400, Tom Lane wrote:

> 2. Is reindexdb handling the failure sanely? While I'd agree that
> this behavior isn't especially desirable, it's the price of using
> REINDEX CONCURRENTLY. On failure, you're expected to clean up
> manually by removing the leftover invalid index. Perhaps the
> documentation isn't clear enough about that, but I don't see a
> bug there.

There is a paragraph about the handling of invalid indexes on the
reindex page:
https://www.postgresql.org/docs/devel/sql-reindex.html#SQL-REINDEX-CONCURRENTLY
"The recommended recovery method in such cases is to drop the invalid
index and try again to perform REINDEX CONCURRENTLY. The concurrent
index created during the processing has a name ending in the suffix
ccnew, or ccold if it is an old index definition which we failed to
drop. Invalid indexes can be dropped using DROP INDEX, including
invalid toast indexes."
--
Michael
 

Michael Paquier <michael@paquier.xyz> writes:
> On Tue, May 26, 2020 at 10:50:19AM -0400, Tom Lane wrote:
>> ...  Perhaps the
>> documentation isn't clear enough about that, but I don't see a
>> bug there.

> There is a paragraph about the handling of invalid indexes on the
> reindex page:

Yeah, but the OP is invoking this via reindexdb.  Do we need to have
anything about it on the reindexdb page?

            regards, tom lane

On Wed, May 27, 2020 at 03:10:18PM +0300, Andrey Klychkov wrote:
> I got the error related to the one UNIQUE index, why all the new indexes are invalid?
> It would be good:
> *  if other indexes are possible to rebuild, reindexdb rebuilds them
> *  if not (why?), reindexdb reports why

When you use reindexdb for a database, the tool launches actually a
REINDEX DATABASE that does a reindex for all tables, one-by-one,
launching one transaction for each table reindexed.  REINDEX
CONCURRENTLY is a multi-step process consisting in the following:
- Create an index definition which is a copy of the index to rebuild.
- Build the index.
- Validate the index.
- Switch dependencies of the old and new indexes.
- Drop the old index.

And the main point is that when doing a table-level REINDEX, all the
steps above are applied to all the indexes of a relation
one-at-a-time.  Hence, if you have N indexes on a table, REINDEX
CONCURRENTLY first creates N new cloned indexes, then it builds N
indexes, it validates N indexes, etc.  The reason why you see this
many invalid indexes is that you have a failure at build phase,
meaning that all the other indexes did not reach yet the stage where
they are valid to be used (indisvalid is changed when dependencies are
swapped to be precise), so a failure causes the creation of a set of
invalid indexes, associated with the table previously reindexed.
--
Michael

On Wed, May 27, 2020 at 08:18:43AM -0400, Tom Lane wrote:
> Yeah, but the OP is invoking this via reindexdb.  Do we need to have
> anything about it on the reindexdb page?

I don't think we should have any duplication between the REINDEX and
reindexdb pages, and we have that in reindexdb:
--concurrently
    Use the CONCURRENTLY option. See REINDEX for further information.

An idea would be to append to the second sentence something like
"where all the caveats of this option are explained in details"?
--
Michael

Michael Paquier <michael@paquier.xyz> writes:
> I don't think we should have any duplication between the REINDEX and
> reindexdb pages, and we have that in reindexdb:
> --concurrently
>     Use the CONCURRENTLY option. See REINDEX for further information.

> An idea would be to append to the second sentence something like
> "where all the caveats of this option are explained in details"?

+1, but I'd change the whole sentence.  Maybe

    Use the CONCURRENTLY option. See REINDEX, where all the caveats of
    this option are explained in detail.

            regards, tom lane

On Thu, May 28, 2020 at 09:14:20AM -0400, Tom Lane wrote:
> +1, but I'd change the whole sentence.  Maybe
>
>     Use the CONCURRENTLY option. See REINDEX, where all the caveats of
>     this option are explained in detail.

Agreed.  Here is a patch for 12~.  Please let me know if that's fine
for you.
--
Michael

Michael Paquier <michael@paquier.xyz> writes:
> Agreed.  Here is a patch for 12~.  Please let me know if that's fine
> for you.

Sure, works for me.

            regards, tom lane

On Fri, May 29, 2020 at 09:17:58AM -0400, Tom Lane wrote:
> Sure, works for me.

Thanks, applied then.
--
Michael

Воскресенье, 31 мая 2020, 4:51 +03:00 от Michael Paquier <michael@paquier.xyz>:
 

On Fri, May 29, 2020 at 09:17:58AM -0400, Tom Lane wrote:

> Sure, works for me.

Thanks, applied then.
--
Michael
 

On Mon, Jun 01, 2020 at 05:39:05PM +0300, Andrey Klychkov wrote:
> I’ve seen on the internet people ask each other about this (when i
> was searching the same not seeing that in the documentation).

Note that indexes and tables can be schema-qualified in a REINDEX
query, so you would need a bit more than the query above, still you
could just wrap that in a plpgsql function that scans pg_class and
pg_index, and issues a set of DROP INDEX commands on each entry.  I
have been wondering as well in the past about adding a set of SQL
queries to be able to do the work in a database as if you run into
this issue repeatedly it is annoying.  So we could add that on the
reindex page close to the paragraph about the drop of invalid
indexes.
--
Michael

On 2020-May-27, Andrey Klychkov wrote:

> =======
> BEFORE:
> =======
>
> Indexes:
>     "act_re_procdef_pkey" PRIMARY KEY, btree (id_)
>     "act_uniq_procdef" UNIQUE CONSTRAINT, btree (key_, version_, tenant_id_)
>     "act_re_procdef_deployment_id_idx" btree (deployment_id_)

> =====
> AFTER
> =====
>
> Indexes:
>     "act_re_procdef_pkey" PRIMARY KEY, btree (id_)
>     "act_re_procdef_pkey_ccnew" UNIQUE, btree (id_) INVALID
>     "act_uniq_procdef" UNIQUE CONSTRAINT, btree (key_, version_, tenant_id_)
>     "act_uniq_procdef_ccnew" UNIQUE, btree (key_, version_, tenant_id_) INVALID
>     "act_re_procdef_deployment_id_idx" btree (deployment_id_)
>     "act_re_procdef_deployment_id_idx_ccnew" btree (deployment_id_) INVALID

> I got the error related to the one UNIQUE index, why all the new indexes are invalid?

> >There is a paragraph about the handling of invalid indexes on the
> >reindex page:
> >https://www.postgresql.org/docs/devel/sql-reindex.html#SQL-REINDEX-CONCURRENTLY
> >"The recommended recovery method in such cases is to drop the invalid
> >index and try again to perform REINDEX CONCURRENTLY.

I think this part remains unanswered: why are there *three* invalid
indexes, and not two?  It makes sense for the UNIQUE indexes to acquire
invalid duplicates, but strangely we also have a
act_re_procdef_deployment_id_idx_ccnew which is *not* unique.  What
happened there?

(I also wonder if it wouldn't make more sense to reindexdb to attempt to
drop indexes that it couldn't complete.)

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Mon, Jun 08, 2020 at 01:37:07PM -0400, Alvaro Herrera wrote:
> I think this part remains unanswered: why are there *three* invalid
> indexes, and not two?  It makes sense for the UNIQUE indexes to acquire
> invalid duplicates, but strangely we also have a
> act_re_procdef_deployment_id_idx_ccnew which is *not* unique.  What
> happened there?

More than one REINDEX query at table-level failed I guess?

> (I also wonder if it wouldn't make more sense to reindexdb to attempt to
> drop indexes that it couldn't complete.)

(That's a good idea.)
--
Michael