Opened 7 years ago

Closed 3 years ago

Last modified 3 years 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: Tim Graham, Shai Berger 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 7 years ago by skoom

Has patch: set

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

comment:2 Changed 7 years ago by Russell Keith-Magee

Component: Core frameworkDatabase layer (models, ORM)
Needs tests: set
Patch needs improvement: set
Triage Stage: UnreviewedAccepted

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 6 years ago by Luke Plant

Type: Bug

comment:4 Changed 6 years ago by Luke Plant

Severity: Normal

comment:5 Changed 5 years ago by Aymeric Augustin

UI/UX: unset

Change UI/UX from NULL to False.

comment:6 Changed 5 years ago by Aymeric Augustin

Easy pickings: unset

Change Easy pickings from NULL to False.

comment:7 Changed 3 years ago by Anssi Kääriäinen

Summary: self.db_table should truncate_name when db_table is passed in Metamanually set db_table should not validate if it needs to be truncated

comment:8 Changed 3 years ago by Tim Graham

Cc: Tim Graham 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 3 years ago by Shai Berger

Cc: Shai Berger 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 3 years ago by Tim Graham

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 3 years ago by Shai Berger

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 3 years ago by Shai Berger

Cc: Shai Berger added; Shai Berger removed

comment:13 Changed 3 years ago by Shai Berger

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

comment:14 Changed 3 years ago by Tim Graham

Component: Database layer (models, ORM)Documentation
Has patch: set
Summary: manually set db_table should not validate if it needs to be truncatedDocument Oracle behavior regarding quoting of database table names
Triage Stage: AcceptedReady for checkin
Type: BugCleanup/optimization

comment:15 Changed 3 years ago by Shai Berger <shai@…>

Resolution: fixed
Status: newclosed

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 3 years 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 3 years 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 3 years 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 3 years ago by Shai Berger

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