Opened 14 years ago

Closed 14 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: no UI/UX: no

Description (last modified by Russell Keith-Magee)

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/creation.py 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)

becomes

            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)

creation.py.patch (977 bytes ) - added by terpsquared 14 years ago.
patch

Download all attachments as: .zip

Change History (2)

by terpsquared, 14 years ago

Attachment: creation.py.patch added

patch

comment:1 by Russell Keith-Magee, 14 years ago

Description: modified (diff)
Resolution: duplicate
Status: newclosed

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