Code

Opened 4 years ago

Closed 6 months ago

Last modified 6 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

Attachments (0)

Change History (19)

comment:1 Changed 4 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 4 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 3 years ago by lukeplant

  • Type set to Bug

comment:4 Changed 3 years ago by lukeplant

  • Severity set to Normal

comment:5 Changed 2 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:6 Changed 2 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:7 Changed 8 months 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 6 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 6 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 6 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 6 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 6 months ago by shai

  • Cc shai added; shaib removed

comment:13 Changed 6 months ago by shai

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

comment:14 Changed 6 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 6 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 6 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 6 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 6 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 6 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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.