Postgres Pro
Downloads
Home   >   mailing lists

Re[4]: - Mailing list pgsql-bugs

From Andrey Klychkov
Subject Re[4]:
Date
Msg-id 1590581418.481387288@f493.i.mail.ru
Whole thread Raw
In response to Re: Re[2]:  (Michael Paquier <michael@paquier.xyz>)
Responses Re:  (Michael Paquier <michael@paquier.xyz>)
Re:  (Alvaro Herrera <alvherre@2ndquadrant.com>)
List pgsql-bugs
Tree view
=======
BEFORE:
=======
ep2_test=# \d act_re_procdef
                                  Table "public.act_re_procdef"
         Column          |          Type           | Collation | Nullable |        Default
-------------------------+-------------------------+-----------+----------+-----------------------
 id_                     | character varying(64)   |           | not null |
 rev_                    | integer                 |           |          |
 category_               | character varying(255)  |           |          |
 name_                   | character varying(255)  |           |          |
 key_                    | character varying(255)  |           | not null |
 version_                | integer                 |           | not null |
 deployment_id_          | character varying(64)   |           |          |
 resource_name_          | character varying(4000) |           |          |
 dgrm_resource_name_     | character varying(4000) |           |          |
 description_            | character varying(4000) |           |          |
 has_start_form_key_     | boolean                 |           |          |
 suspension_state_       | integer                 |           |          |
 tenant_id_              | character varying(255)  |           |          | ''::character varying
 has_graphical_notation_ | boolean                 |           |          |
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_)
Referenced by:
    TABLE "act_ru_identitylink" CONSTRAINT "act_fk_athrz_procedef" FOREIGN KEY (proc_def_id_) REFERENCES act_re_procdef(id_)
    TABLE "act_ru_execution" CONSTRAINT "act_fk_exe_procdef" FOREIGN KEY (proc_def_id_) REFERENCES act_re_procdef(id_)
    TABLE "act_procdef_info" CONSTRAINT "act_fk_info_procdef" FOREIGN KEY (proc_def_id_) REFERENCES act_re_procdef(id_)
    TABLE "act_ru_task" CONSTRAINT "act_fk_task_procdef" FOREIGN KEY (proc_def_id_) REFERENCES act_re_procdef(id_)
 
I’ve run the following command once
time reindexdb -d ep2_test --concurrently
WARNING:  cannot reindex system catalogs concurrently, skipping all
reindexdb: error: reindexing of database "ep2_test" failed: ERROR:  could not create unique index "act_uniq_procdef_ccnew"
DETAIL:  Key (key_, version_, tenant_id_)=(com.edisoft.doc.fns.upd.service.act, 3, ) is duplicated.
 
=====
AFTER
=====
ep2_test=# \d act_re_procdef
                                  Table "public.act_re_procdef"
         Column          |          Type           | Collation | Nullable |        Default
-------------------------+-------------------------+-----------+----------+-----------------------
 id_                     | character varying(64)   |           | not null |
 rev_                    | integer                 |           |          |
 category_               | character varying(255)  |           |          |
 name_                   | character varying(255)  |           |          |
 key_                    | character varying(255)  |           | not null |
 version_                | integer                 |           | not null |
 deployment_id_          | character varying(64)   |           |          |
 resource_name_          | character varying(4000) |           |          |
 dgrm_resource_name_     | character varying(4000) |           |          |
 description_            | character varying(4000) |           |          |
 has_start_form_key_     | boolean                 |           |          |
 suspension_state_       | integer                 |           |          |
 tenant_id_              | character varying(255)  |           |          | ''::character varying
 has_graphical_notation_ | boolean                 |           |          |
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?
It would be good:
  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
 
 
 
--
Regards,
Andrew K.
 

pgsql-bugs by date:

Previous
From: Vik Fearing
Date:
Subject: Re: BUG #16465: Inconsistent results from comparison of row valueexpressions
Next
From: Tom Lane
Date:
Subject: Re: BUG #16465: Inconsistent results from comparison of row value expressions