dataegret / pgcompacttable

BSD 3-Clause "New" or "Revised" License
314 stars 48 forks source link

creating invalid index every run. #49

Open Zergvl opened 1 year ago

Zergvl commented 1 year ago

If table contains duplicate data with an existing constraint (invalid index), then pgcompacttable will create a new invalid index every time it is run.

                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 num    | integer |           | not null |
Indexes:
    "t2_con" PRIMARY KEY, btree (id, num)

select * from t2;
 id | num
----+-----
  1 |   2
  1 |   3
(2 rows)

then disabling index, insert some duplicate data and reenable index

select * from t2;
 id | num
----+-----
  1 |   2
  1 |   3
  1 |   2
(3 rows)

insert into t2 values ( '1', '2' );
ERROR:  duplicate key value violates unique constraint "t2_con"
DETAIL:  Key (id, num)=(1, 2) already exists.

reindex table t2;
ERROR:  could not create unique index "t2_con"
DETAIL:  Key (id, num)=(1, 2) is duplicated.

then start pgcompacttable and got error in log, but pgcompacttable didnt fail or exited, it continued working:

[Fri Feb 10 10:41:20 2023] (bloat:public.t2) SQL Error: ERROR:  could not create unique index "t2_con_ccnew"

next run:

[Fri Feb 10 10:41:23 2023] (bloat:public.t2) SQL Error: ERROR:  could not create unique index "t2_con_ccnew1"

and so on...

final view of table after 4 runs:

 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 num    | integer |           | not null |
Indexes:
    "t2_con" PRIMARY KEY, btree (id, num)
    "t2_con_ccnew" UNIQUE, btree (id, num) INVALID
    "t2_con_ccnew1" UNIQUE, btree (id, num) INVALID
    "t2_con_ccnew2" UNIQUE, btree (id, num) INVALID
    "t2_con_ccnew3" UNIQUE, btree (id, num) INVALID

So, why pgcompacttable does not remove invalid indexes after itself?