... - Mailing list pgsql-bugs

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.

Each time the number of invalid indexes increases.

Output:

1.
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.

real    6m18.564s
user    0m0.002s
sys     0m0.003s

2.
WARNING:  cannot reindex system catalogs concurrently, skipping all
WARNING:  cannot reindex invalid index "public.act_re_procdef_pkey_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_uniq_procdef_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_deployment_id_idx_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_1939707_index_ccnew" concurrently, skipping
reindexdb: error: reindexing of database "ep2_test" failed: ERROR:  could not create unique index "act_uniq_procdef_ccnew1"
DETAIL:  Key (key_, version_, tenant_id_)=(com.edisoft.doc.fns.upd.service.act, 3, ) is duplicated.

real    6m11.914s
user    0m0.003s
sys     0m0.003s

3.
WARNING:  cannot reindex system catalogs concurrently, skipping all
WARNING:  cannot reindex invalid index "public.act_re_procdef_pkey_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_uniq_procdef_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_deployment_id_idx_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_pkey_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_uniq_procdef_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_deployment_id_idx_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_1939707_index_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_1939707_index_ccnew1" concurrently, skipping
reindexdb: error: reindexing of database "ep2_test" failed: ERROR:  could not create unique index "act_uniq_procdef_ccnew2"
DETAIL:  Key (key_, version_, tenant_id_)=(com.edisoft.doc.fns.upd.service.act, 3, ) is duplicated.

real    6m28.386s
user    0m0.003s
sys     0m0.002s

4.
WARNING:  cannot reindex system catalogs concurrently, skipping all
WARNING:  cannot reindex invalid index "public.act_re_procdef_pkey_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_uniq_procdef_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_deployment_id_idx_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_pkey_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_uniq_procdef_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_deployment_id_idx_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_pkey_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_uniq_procdef_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_deployment_id_idx_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_1939707_index_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_1939707_index_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_1939707_index_ccnew2" concurrently, skipping
reindexdb: error: reindexing of database "ep2_test" failed: ERROR:  could not create unique index "act_uniq_procdef_ccnew3"
DETAIL:  Key (key_, version_, tenant_id_)=(com.edisoft.doc.fns.upd.service.act, 3, ) is duplicated.

real    6m17.088s
user    0m0.004s
sys     0m0.003s

5.
WARNING:  cannot reindex system catalogs concurrently, skipping all
WARNING:  cannot reindex invalid index "public.act_re_procdef_pkey_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_uniq_procdef_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_deployment_id_idx_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_pkey_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_uniq_procdef_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_deployment_id_idx_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_pkey_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_uniq_procdef_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_deployment_id_idx_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_pkey_ccnew3" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_uniq_procdef_ccnew3" concurrently, skipping
WARNING:  cannot reindex invalid index "public.act_re_procdef_deployment_id_idx_ccnew3" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_1939707_index_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_1939707_index_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_1939707_index_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_1939707_index_ccnew3" concurrently, skipping
reindexdb: error: reindexing of database "ep2_test" failed: ERROR:  could not create unique index "act_uniq_procdef_ccnew4"
DETAIL:  Key (key_, version_, tenant_id_)=(com.edisoft.doc.fns.upd.service.act, 3, ) is duplicated.

real    6m19.540s
user    0m0.004s
sys     0m0.002s

 

end so on.

 

There were 0 invalid indexes before, after 6 iterations 34 invalid indexes.