Opened 2 years ago

Closed 2 years ago

Last modified 2 years ago

#33773 closed Bug (fixed)

DEFAULT_INDEX_TABLESPACE setting is ignored for Indexes defined with multiple fields.

Reported by: Bruce Cutler Owned by: Bruce Cutler
Component: Migrations Version: 4.0
Severity: Normal Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Change History (9)

comment:1 by Simon Charette, 2 years ago

Component: Database layer (models, ORM)Migrations
Triage Stage: UnreviewedAccepted

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?

comment:2 by Bruce Cutler, 2 years ago

Owner: changed from nobody to Bruce Cutler
Status: newassigned

comment:3 by Mariusz Felisiak, 2 years ago

Has patch: set
Needs tests: set

comment:4 by Bruce Cutler, 2 years ago

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)'

comment:5 by Bruce Cutler, 2 years ago

Needs tests: unset

in reply to:  4 comment:6 by Mariusz Felisiak, 2 years ago

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.

comment:7 by Mariusz Felisiak, 2 years ago

Triage Stage: AcceptedReady for checkin

comment:8 by Mariusz Felisiak <felisiak.mariusz@…>, 2 years ago

Resolution: fixed
Status: assignedclosed

In de1c8320:

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

Thanks to Simon Charette for locating where issue lay.

comment:9 by Mariusz Felisiak <felisiak.mariusz@…>, 2 years ago

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

Note: See TracTickets for help on using tickets.
Back to Top