search

pgpartman / pg_partman

Partition management extension for PostgreSQL
Other
2.08k stars 281 forks source link

Partition creation fails when partial indexes are present with tablespace #657

Open mingravity opened 6 months ago

mingravity commented 6 months ago

In this function: inherit_template_properties

If there is a partial index and also tablespace then the query formed adds TABLESPACE clause to the end like below: e.g. CREATE INDEX ON table1(col1) WHERE col1 > 10 TABLESPACE my_tbl_spc;

This is invalid SQL. Instead should be CREATE INDEX ON table1(col1) TABLESPACE my_tbl_spc WHERE col1 > 10;

Possible solution:

IF (ARRAY_length(regexp_matches(v_sql, ' WHERE ', 'i'), 1) > 0) THEN v_sql := regexp_replace (v_sql, ' WHERE ', format(' TABLESPACE %I WHERE ', v_index_list.tablespace_name), 'i' ); ELSE v_sql := v_sql || format(' TABLESPACE %I', v_index_list.tablespace_name); -- THIS IS THE CURRENT STATEMENT

keithf4 commented 5 months ago

Thank you for finding this issue. Will try and see if I can figure out a fix for this.

keithf4 commented 2 weeks ago

Apologies for the delay looking into this.

You're correct in that partial indexes would cause an issue here. However, this code block is only handling unique indexes, so it would be a bit odd for that to occur. I'll still include the fix to handle the odd edge case. Thank you!