pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedRe[4]:
2+ messages / 1 participants
[nested] [flat]
* Re[4]:
@ 2020-05-27 12:10 ` Andrey Klychkov <aaklychkov@mail.ru>
1 sibling, 0 replies; 2+ messages in thread
From: Andrey Klychkov @ 2020-05-27 12:10 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
=======
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:
* if other indexes are possible to rebuild, reindexdb rebuilds them
* 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re[4]:
@ 2020-06-01 14:39 ` Andrey Klychkov <aaklychkov@mail.ru>
1 sibling, 0 replies; 2+ messages in thread
From: Andrey Klychkov @ 2020-06-01 14:39 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Continuing working with the feature.
If we run the following, we won't see any broken indexes:
zabbix=# \d sysmap_shape
Table "public.sysmap_shape"
Column | Type | Collation | Nullable | Default
------------------+----------------------+-----------+----------+-----------------------------
….
….
Indexes:
"sysmap_shape_pkey" PRIMARY KEY, btree (sysmap_shapeid)
"sysmap_shape_1" btree (sysmapid)
But if we run the following query we'll see a broken index related to the same table but including toast.
SELECT pg_class.relname
FROM pg_class, pg_index
WHERE pg_index.indisvalid = false
AND pg_index.indexrelid = pg_class.oid;
we get:
pg_toast_68086_index_ccnew
It's logical but not obvious.
So, would be also really helpful to have in the documentation:
1. note about the query above, i.e. how to find invalid indexes including TOAST to https://www.postgresql.org/docs/current/sql-reindex.html and to https://www.postgresql.org/docs/current/sql-dropindex.html
2. Referrence how to drop toast indexes to https://www.postgresql.org/docs/current/sql-dropindex.html
I’ve seen on the internet people ask each other about this (when i was searching the same not seeing that in the documentation).
Thanks for the previous patch!
>Воскресенье, 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
>
--
Regards,
Andrew K.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-06-01 14:39 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-05-27 12:10 ` Andrey Klychkov <aaklychkov@mail.ru>
2020-06-01 14:39 ` Andrey Klychkov <aaklychkov@mail.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox