DEFAULT_INDEX_TABLESPACE setting is ignored for Indexes defined with multiple fields.

[Bruce Cutler]

Tested with Postgresql backend, Django versions 3.2 & 4.0

Defining an Index in the Meta of a model ignores the DEFAULT_INDEX_TABLESPACE setting if multiple fields are used for the index.
The setting is honoured if only a single field defines the index.

Example case, with a postgresql DB defined in settings, as well as:

DEFAULT_TABLESPACE = 'data_ts'
DEFAULT_INDEX_TABLESPACE = 'index_ts'

The below model definition:

class MyModel(models.Model):
    foo = models.CharField(max_length=10)
    bar = models.CharField(max_length=10)

    class Meta:
        indexes = [
            models.Index(fields=['foo']),
            models.Index(fields=['foo', 'bar']),
        ]

generates this output from management command sqlmigrate run on the produced migration:

BEGIN;
--
-- Create model MyModel
--
CREATE TABLE "cbcap_mymodel" ("id" bigserial NOT NULL PRIMARY KEY USING INDEX TABLESPACE "index_ts", "foo" varchar(10) NOT NULL, "bar" varchar(10) NOT NULL) TABLESPACE "data_ts";
--
-- Create index cbcap_mymod_foo_f01529_idx on field(s) foo of model mymodel
--
CREATE INDEX "cbcap_mymod_foo_f01529_idx" ON "cbcap_mymodel" ("foo") TABLESPACE "index_ts";
--
-- Create index cbcap_mymod_foo_44bcd6_idx on field(s) foo, bar of model mymodel
--
CREATE INDEX "cbcap_mymod_foo_44bcd6_idx" ON "cbcap_mymodel" ("foo", "bar") TABLESPACE "data_ts";
COMMIT;

Note the 'data_ts' tablespace is used for the second index

[Simon Charette]

Thank you for your report.

There's effectively a discrepancy between ​what's documented and how ​it's implemented for indexes including multiple fields or solely expressions.

Would you be interested in submitting a patch that makes _get_index_tablespace_sql consider DEFAULT_INDEX_TABLESPACE before model._meta.db_tablespace if the former is defined?

[Mariusz Felisiak]

​PR

[Bruce Cutler]

​PR on github raised for this patch.

No tests were created for the patch, as 3 existing tests already fail prior to the changes (and pass since) when DEFAULT_INDEX_TABLESPACE is specified in settings:

======================================================================
FAIL: test_db_tablespace (model_indexes.tests.IndexesTests) [<object object at 0x7f8bcd80fa00>] (fields=['shortcut', 'isbn'])
...
AssertionError: '"idx_def_tbsp"' not found in 'create index "" on "model_indexes_book" ("shortcut", "isbn")'

======================================================================
FAIL: test_db_tablespace (model_indexes.tests.IndexesTests) [<object object at 0x7f8bcd80fa00>] (fields=['title', 'author'])
...
AssertionError: '"idx_def_tbsp"' not found in 'create index "" on "model_indexes_book" ("title", "author")'

======================================================================
FAIL: test_func_with_tablespace (model_indexes.tests.IndexesTests)
...
AssertionError: '"idx_def_tbsp"' not found in 'CREATE INDEX "functional_no_tbls" ON "model_indexes_book" ((LOWER("shortcut")) DESC)'

[Mariusz Felisiak]

Replying to Bruce Cutler:

​PR on github raised for this patch.

No tests were created for the patch, as 3 existing tests already fail prior to the changes (and pass since) when DEFAULT_INDEX_TABLESPACE is specified in settings:

======================================================================
FAIL: test_db_tablespace (model_indexes.tests.IndexesTests) [<object object at 0x7f8bcd80fa00>] (fields=['shortcut', 'isbn'])
...
AssertionError: '"idx_def_tbsp"' not found in 'create index "" on "model_indexes_book" ("shortcut", "isbn")'

======================================================================
FAIL: test_db_tablespace (model_indexes.tests.IndexesTests) [<object object at 0x7f8bcd80fa00>] (fields=['title', 'author'])
...
AssertionError: '"idx_def_tbsp"' not found in 'create index "" on "model_indexes_book" ("title", "author")'

======================================================================
FAIL: test_func_with_tablespace (model_indexes.tests.IndexesTests)
...
AssertionError: '"idx_def_tbsp"' not found in 'CREATE INDEX "functional_no_tbls" ON "model_indexes_book" ((LOWER("shortcut")) DESC)'

Unfortunately it's not covered by CI because we set the same tablespace to the DEFAULT_TABLESPACE and DEFAULT_INDEX_TABLESPACE. Overriding DEFAULT_TABLESPACE to None should make it covered.

[Mariusz Felisiak <felisiak.mariusz@…>]

In de1c8320:

Fixed #33773 -- Made Index with multiple fields respect DEFAULT_INDEX_TABLESPACE.

Thanks to Simon Charette for locating where issue lay.

[Mariusz Felisiak <felisiak.mariusz@…>]

In a7f398ae:

[4.1.x] Fixed #33773 -- Made Index with multiple fields respect DEFAULT_INDEX_TABLESPACE.

Thanks to Simon Charette for locating where issue lay.

Backport of de1c8320cedee5d4f91b153a7fe82eb63876470f from main