Opened 5 years ago

Closed 5 years ago

#14203 closed (duplicate)

Manual schema specification for tables is not compatible with index creation statements

Reported by: terpsquared Owned by: nobody
Component: Database layer (models, ORM) Version: 1.2
Severity: Keywords: database postgres schema
Cc: Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description (last modified by russellm)

A method for specifying postgres schemas to be used within Django is specified here.

From this, the syntax for specifying a schema-located table when declaring a Django model is:

    class Meta:
        db_table = '"myschema"."mytable"'

This is compatible with the creation of tables; however, when generating indices, improper names are generated.

The SQL it generates is in the following format:

CREATE INDEX ""myschema"."mytable"_my_column_id" ON "myschema"."mytable" ("my_column_id");

The quoting mechanism is not currently fully compatible with this syntax; while it could be considered non-standard Django, it would seem that correcting this problem would not be overly difficult, and should not interfere with typical functionality.

The fix in django/db/backends/postgresql/ is as follows:

            def get_index_sql(index_name, opclass=''):
                return (style.SQL_KEYWORD('CREATE INDEX') + ' ' +
                        style.SQL_TABLE(qn(index_name)) + ' ' +
                        style.SQL_KEYWORD('ON') + ' ' +
                        style.SQL_TABLE(qn(db_table)) + ' ' +
                        "(%s%s)" % (style.SQL_FIELD(qn(f.column)), opclass) +
                        "%s;" % tablespace_sql)


            def get_index_sql(index_name, opclass=''):
                return (style.SQL_KEYWORD('CREATE INDEX') + ' ' +
                        style.SQL_TABLE(qn(index_name.replace('"', '').replace('.', '_'))) + ' ' +
                        style.SQL_KEYWORD('ON') + ' ' +
                        style.SQL_TABLE(qn(db_table)) + ' ' +
                        "(%s%s)" % (style.SQL_FIELD(qn(f.column)), opclass) +
                        "%s;" % tablespace_sql)

I have attached a patch for this change.

Attachments (1) (977 bytes) - added by terpsquared 5 years ago.

Download all attachments as: .zip

Change History (2)

Changed 5 years ago by terpsquared


comment:1 Changed 5 years ago by russellm

  • Description modified (diff)
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to duplicate
  • Status changed from new to closed

I'm going to close this as a dupe of #6148. Django doesn't currently have *any* support for schemas, manually specified or otherwise. If we're going to add schema support, we're not just going to do it for manually specified table names. The fact that the technique from the mailing list works at all is accident, not intention.

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