-
Notifications
You must be signed in to change notification settings - Fork 53
Open
Description
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?
Metadata
Metadata
Assignees
Labels
No labels