Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

creating invalid index every run. #49

Open
Zergvl opened this issue Feb 10, 2023 · 0 comments
Open

creating invalid index every run. #49

Zergvl opened this issue Feb 10, 2023 · 0 comments

Comments

@Zergvl
Copy link

Zergvl commented Feb 10, 2023

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant