Opened 5 years ago

Closed 22 months ago

Last modified 22 months ago

#13245 closed Cleanup/optimization (fixed)

Document Oracle behavior regarding quoting of database table names

Reported by: skoom Owned by: nobody
Component: Documentation Version: master
Severity: Normal Keywords: oracle
Cc: timo, shai 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

I ran into an error when using Oracle and creating automatic models (for history tracking) from user created models.

My automatic model I call History<ModelName> but I want the tables to be h_<db_table>.

I access Model._meta.db_table and append a h_ to it. Because this db_table is passed in the Meta of the automatic model, it isn't being truncated when the models is created.

File: django.db.models.options

Line: 105

Action: remove one indentation level

Change History (19)

comment:1 Changed 5 years ago by skoom

  • Has patch set
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

Since the patch is just removing one indentation level I haven't included an attachment.

comment:2 Changed 5 years ago by russellm

  • Component changed from Core framework to Database layer (models, ORM)
  • Needs tests set
  • Patch needs improvement set
  • Triage Stage changed from Unreviewed to Accepted

I disagree that truncation is the right thing to do. db_table is a directive to tell the database backend the literal table name to use. The truncate_name operation will munge the name of the table under certain conditions. To my mind, this would be surprising behavior. If I manually specify a name, I would expect it to be used literally.

However, I would expect to see a validation error if you ever tried to synchronize a model with a db_table specification that exceeded the limits of the backend. Accepting on the basis that there is a missing validation condition.

comment:3 Changed 4 years ago by lukeplant

  • Type set to Bug

comment:4 Changed 4 years ago by lukeplant

  • Severity set to Normal

comment:5 Changed 3 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:6 Changed 3 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:7 Changed 2 years ago by akaariai

  • Summary changed from self.db_table should truncate_name when db_table is passed in Meta to manually set db_table should not validate if it needs to be truncated

comment:8 Changed 22 months ago by timo

  • Cc timo added
  • Easy pickings set
  • Has patch unset
  • Needs tests unset
  • Patch needs improvement unset

The validation should be added in django.core.management.validation.get_validation_errors() with a test in tests/invalid_models/invalid_models/models.py. The validation and the test will have to be conditional on connection.ops.max_name_length() -- there's already an example in invalid_models/models.py for connection.features.interprets_empty_strings_as_nulls.

comment:9 Changed 22 months ago by shai

  • Cc shaib added
  • Keywords oracle added

The current behavior of the Oracle backend is to truncate, unless the given name is quoted.

That is, if Meta has

    db_table='supercallifragilisticexpialidocious'

it will be truncated; if it has

    db_table='"supercallifragilisticexpialidocious"'

we assume the programmer knows what they're doing. I don't like it much as an API (it is also related to case issues), but I don't think anything needs to be changed for this bug.

Is there is any related issue on other backends? Has anyone played with over-63-chars-long table names on PostgreSQL? Otherwise, I think this can be closed.

comment:10 Changed 22 months ago by timo

  • Easy pickings unset

Hi Shai, I looked into this on PostgreSQL. Specifying db_name="string longer ... than 63 characters" results in the table name being silently truncated at 63 characters (does not use django.db.backends.utils.truncate_name). Arguably this should be fixed, perhaps there's already a ticket, although it seems quite uncommon and low priority.

The action item I was proposing for this ticket was what Russ proposed in comment 2: "I would expect to see a validation error if you ever tried to synchronize a model with a db_table specification that exceeded the limits of the backend."

Do you disagree with that? Of course, this ticket seems a bit trickier than I expected given the Oracle specific logic in it's definition of quote_name you mentioned regarding a quoted db_name.

comment:11 Changed 22 months ago by shai

Hi Tim,

Yes, I disagree with the proposed solution -- actually, with the bug itself as it stands -- because it means some perfectly-working apps will suddenly stop working on Oracle; apps developed on other backends are particularly susceptible to this.

We probably need a documentation fix here. Other than that, we need to be consistent, but the other backends' limitations are so seldom met, that it is indeed very low priority.

comment:12 Changed 22 months ago by shai

  • Cc shai added; shaib removed

comment:13 Changed 22 months ago by shai

Suggested documentation fixes in https://github.com/django/django/pull/1787

comment:14 Changed 22 months ago by timo

  • Component changed from Database layer (models, ORM) to Documentation
  • Has patch set
  • Summary changed from manually set db_table should not validate if it needs to be truncated to Document Oracle behavior regarding quoting of database table names
  • Triage Stage changed from Accepted to Ready for checkin
  • Type changed from Bug to Cleanup/optimization

comment:15 Changed 22 months ago by Shai Berger <shai@…>

  • Resolution set to fixed
  • Status changed from new to closed

In 6de3726423b8df4fb8ab05caa109a8c6308e6060:

Fixed #13245: Explained Oracle's behavior w.r.t db_table
and how to prevent table-name truncation

Thanks russellm & timo for discussion, and timo for review.

Backported from master 317040a73b77be8f8210801793b2ce6d1a69301e

comment:16 Changed 22 months ago by Shai Berger <shai@…>

In cc0dcfc64405419d95e87c8dcc612bd441a8d6de:

Fixed #13245: Explained Oracle's behavior w.r.t db_table
and how to prevent table-name truncation

Thanks russellm & timo for discussion, and timo for review.

Backported from master 317040a73b77be8f8210801793b2ce6d1a69301e

comment:17 Changed 22 months ago by Shai Berger <shai@…>

In 27f3f341369c8e18ea4a360a1f7c01531836b68b:

Fixed #13245: Explained Oracle's behavior w.r.t db_table
and how to prevent table-name truncation

Thanks russellm & timo for discussion, and timo for review.

Backported from master 317040a73b77be8f8210801793b2ce6d1a69301e

comment:18 Changed 22 months ago by Shai Berger <shai@…>

In 317040a73b77be8f8210801793b2ce6d1a69301e:

Fixed #13245: Explained Oracle's behavior w.r.t db_table
and how to prevent table-name truncation

Thanks russellm & timo for discussion, and timo for review.

comment:19 Changed 22 months ago by shai

Hmm, cherry-picking this was too easy... I forgot to add branch prefixes to the header line.

6de3726423b8df4fb8ab05caa109a8c6308e6060 is for 1.4.x
cc0dcfc64405419d95e87c8dcc612bd441a8d6de is for 1.5.x
27f3f341369c8e18ea4a360a1f7c01531836b68b is for 1.6.x
317040a73b77be8f8210801793b2ce6d1a69301e is for master

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