Clean up does not remove temporary indexes created
Closed this issue · 4 comments
I am a huge fan of this gem, thanks for taking the time to create it.
I have come across another issue while using this to migrate a primary key from integer to bigint that I'm not sure is a problem or not.
Finalize finalize_column_type_change :projects, :id generates an index as follows.
--
-- Name: index_projects_on_((assigned_to ->> 'id_for_type_change'::text)); Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX "index_projects_on_((assigned_to ->> 'id_for_type_change'::text))" ON public.projects USING btree (((assigned_to ->> 'id_for_type_change'::text)));
Subsequently, when the cleanup_column_type_change :projects, :id helper is called, does not remove the above index. Why is this? the id_for_type_change column was removed, so shouldn't this index also be removed?
Woops, it looks like this index was created from an existing: "index_projects_on_assigned_to_id" btree ((assigned_to ->> 'id'::text)). Which was indexing a key in a jsonb data type.
I think what's missing here in the clean up is also looking at these types of indexes that were generated and automatically removing them.
On second thought.. what I actually should think happen is finalize_column_type_change should be ignoring creating the index in the first place because it's completely irrelevant. The id indexed in the jsonb column is completely unrelated to the id of the row.
Thank you for reporting!
What was happened here, is I think you had some id attribute in the jsonb column and an index on it. When migrating a primary key from int to bigint, we copy all the indexes that potentially include the old primary key in them. For regular indexes (where we reference only columns) this is ok and works well without false positives. But for expression indexes we regexp match if the index expression includes the column name. Which it is in this case and produces a false positive.
This looks like an edge case for me and I don't think I can do anything with it. You have to remove that erroneously created index manually 🤷 .
Sounds good! Thank you!