uptimejp / pg_part Goto Github PK

Hi guys,

I figured out a bug when I ran pgpart.add_partition and it is creating the new partition indexes, specifically on pgpart._get_index_def.

We have an index with a more complex order: "CREATE INDEX contacts_updated_at_created_at_ix ON contacts USING btree (updated_at DESC, created_at DESC)". In this case, the function returned the name for the new index as "updated_at DESC_created_at DESC" and this throw and exception trying to create this new index.

So, first I decided to change the regular expression to deal with these cases. The original code on (pg_part--0.1.0.sql:123) is

replace(regexp_replace(regexp_replace(indexdef, '.*\(', ''), '\).*', ''), ', ', '_');

I corrected this problem changing it to

LOWER(replace(replace(regexp_replace(regexp_replace(indexdef, '.*\(', ''), '\).*', ''), ', ', '_'),' ','_'))

But then I discovered another scenario that wasn't covered by my second approach. We have two indexes with the same field, but indexing different information:
"contacts_domain_ix" btree (split_part(email::text, '@'::text, 2))
"contacts_lower_email" btree (lower(email::text))
Both were trying to create an index called contacts_0_email_idx.

As my temporary solution, I decided to use a simpler approach (with an ugly code), just reusing the name of the original index:

replace(replace(regexp_replace(regexp_replace(indexdef, '.*INDEX ',''), ' ON.*',''),'_ix',''),_relname||'_','')

I am available to make a pull request with the solution to this bug, but I want to know if you agree with my last approach or if you have any better idea to solve the problem.

All the best,
Pedro