Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#29015 closed Cleanup/optimization (fixed)

Add an error when the PostgreSQL database name length limit is exceeded

Reported by: Tadej Janež Owned by: Priyansh Saxena
Component: Database layer (models, ORM) Version: 2.0
Severity: Normal Keywords:
Cc: Adam Johnson Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

PostgreSQL has a maximum identifier lenght limit that is set to 63 bytes by default (see ​https://www.postgresql.org/docs/current/static/runtime-config-preset.html for more details).

If a user chooses a database name longer than this, PostgreSQL will truncate the identifier to 63 bytes and operate normally.
Django apparently doesn't notice this and behaves as if the database name is not truncated.

However, in some cases, Django needs to be aware of this. For example, when running tests in parallel. Django will fail due to a pre-existing database with the same name and not finding the template database to clone.

Here is an example using ​https://github.com/mdamien/django-tutorial.

First setup up a virtual environment and clone the project:

mkvirtualenv --python=python3 django-db-name-too-long-bug
pip install Django psycopg2
git clone https://github.com/mdamien/django-tutorial
cd django-tutorial

Edit mysite/settings.py and change database configuration to:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'foo',
     }
}

Confirm that the tests run without an error:

$ ./manage.py test -v 2
Creating test database for alias 'default' ('test_foo')...
Operations to perform:
  Synchronize unmigrated apps: messages, staticfiles
  Apply all migrations: admin, auth, contenttypes, polls, sessions
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying polls.0001_initial... OK
  Applying sessions.0001_initial... OK
System check identified no issues (0 silenced).
test_detail_view_with_a_future_question (polls.tests.QuestionIndexDetailTests) ... ok
test_detail_view_with_a_past_question (polls.tests.QuestionIndexDetailTests) ... ok
test_was_published_recently_with_future_question (polls.tests.QuestionMethodTests) ... ok
test_was_published_recently_with_old_question (polls.tests.QuestionMethodTests) ... ok
test_was_published_recently_with_recent_question (polls.tests.QuestionMethodTests) ... ok
test_index_view_with_a_future_question (polls.tests.QuestionViewTests) ... ok
test_index_view_with_a_past_question (polls.tests.QuestionViewTests) ... ok
test_index_view_with_future_question_and_past_question (polls.tests.QuestionViewTests) ... ok
test_index_view_with_no_questions (polls.tests.QuestionViewTests) ... ok
test_index_view_with_two_past_questions (polls.tests.QuestionViewTests) ... ok

----------------------------------------------------------------------
Ran 10 tests in 0.054s

OK
Destroying test database for alias 'default' ('test_foo')...

Confirm that the tests when run in parallel run without an error:

$ ./manage.py test -v 2 --parallel
Creating test database for alias 'default' ('test_foo')...
Operations to perform:
  Synchronize unmigrated apps: messages, staticfiles
  Apply all migrations: admin, auth, contenttypes, polls, sessions
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying polls.0001_initial... OK
  Applying sessions.0001_initial... OK
Cloning test database for alias 'default' ('test_foo')...
Cloning test database for alias 'default' ('test_foo')...
Cloning test database for alias 'default' ('test_foo')...
System check identified no issues (0 silenced).
test_was_published_recently_with_future_question (polls.tests.QuestionMethodTests) ... ok
test_was_published_recently_with_old_question (polls.tests.QuestionMethodTests) ... ok
test_was_published_recently_with_recent_question (polls.tests.QuestionMethodTests) ... ok
test_detail_view_with_a_future_question (polls.tests.QuestionIndexDetailTests) ... ok
test_detail_view_with_a_past_question (polls.tests.QuestionIndexDetailTests) ... ok
test_index_view_with_a_future_question (polls.tests.QuestionViewTests) ... ok
test_index_view_with_a_past_question (polls.tests.QuestionViewTests) ... ok
test_index_view_with_future_question_and_past_question (polls.tests.QuestionViewTests) ... ok
test_index_view_with_no_questions (polls.tests.QuestionViewTests) ... ok
test_index_view_with_two_past_questions (polls.tests.QuestionViewTests) ... ok

----------------------------------------------------------------------
Ran 10 tests in 0.119s

OK
Destroying test database for alias 'default' ('test_foo_1')...
Destroying test database for alias 'default' ('test_foo_2')...
Destroying test database for alias 'default' ('test_foo_3')...
Destroying test database for alias 'default' ('test_foo')...

Now edit mysite/settings.py again and change database name to something very long. For example, set the following:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'something-very-very-veeeeeeeeeeeeeeeerrrrrrrrrrrrrryyyyyyyyyy'
                '-long-that-will-get-truncated',
    }
}

Running the tests without --parallel works fine, even though Django mistakenly thinks it created a test database with such a long name:

$ ./manage.py test -v 2
Creating test database for alias 'default' ('test_something-very-very-veeeeeeeeeeeeeeeerrrrrrrrrrrrrryyyyyyyyyy-long-that-will-get-truncated')...
Operations to perform:
  Synchronize unmigrated apps: messages, staticfiles
  Apply all migrations: admin, auth, contenttypes, polls, sessions
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying polls.0001_initial... OK
  Applying sessions.0001_initial... OK
System check identified no issues (0 silenced).
test_detail_view_with_a_future_question (polls.tests.QuestionIndexDetailTests) ... ok
test_detail_view_with_a_past_question (polls.tests.QuestionIndexDetailTests) ... ok
test_was_published_recently_with_future_question (polls.tests.QuestionMethodTests) ... ok
test_was_published_recently_with_old_question (polls.tests.QuestionMethodTests) ... ok
test_was_published_recently_with_recent_question (polls.tests.QuestionMethodTests) ... ok
test_index_view_with_a_future_question (polls.tests.QuestionViewTests) ... ok
test_index_view_with_a_past_question (polls.tests.QuestionViewTests) ... ok
test_index_view_with_future_question_and_past_question (polls.tests.QuestionViewTests) ... ok
test_index_view_with_no_questions (polls.tests.QuestionViewTests) ... ok
test_index_view_with_two_past_questions (polls.tests.QuestionViewTests) ... ok

----------------------------------------------------------------------
Ran 10 tests in 0.051s

OK
Destroying test database for alias 'default' ('test_something-very-very-veeeeeeeeeeeeeeeerrrrrrrrrrrrrryyyyyyyyyy-long-that-will-get-truncated')...

However, running the tests in parallel exposes Django's lack of understanding of identifier truncation in PostgreSQL:

$ ./manage.py test -v 2 --parallel
Creating test database for alias 'default' ('test_something-very-very-veeeeeeeeeeeeeeeerrrrrrrrrrrrrryyyyyyyyyy-long-that-will-get-truncated')...
Operations to perform:
  Synchronize unmigrated apps: messages, staticfiles
  Apply all migrations: admin, auth, contenttypes, polls, sessions
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying polls.0001_initial... OK
  Applying sessions.0001_initial... OK
Cloning test database for alias 'default' ('test_something-very-very-veeeeeeeeeeeeeeeerrrrrrrrrrrrrryyyyyyyyyy-long-that-will-get-truncated')...
Destroying old test database for alias 'default' ('test_something-very-very-veeeeeeeeeeeeeeeerrrrrrrrrrrrrryyyyyyyyyy-long-that-will-get-truncated_1')...
Got an error creating the test database: template database "test_something-very-very-veeeeeeeeeeeeeeeerrrrrrrrrrrrrryyyyyyy" does not exist

When Django created the main test database, "test_something-very-very-veeeeeeeeeeeeeeeerrrrrrrrrrrrrryyyyyyyyyy-long-that-will-get-truncated", in reality, PostgreSQL created a database named "test_something-very-very-veeeeeeeeeeeeeeeerrrrrrrrrrrrrryyyyyyy".

And when Django tried to create a clone of this test database, it executed:

CREATE DATABASE "test_something-very-very-veeeeeeeeeeeeeeeerrrrrrrrrrrrrryyyyyyyyyy-long-that-will-get-truncated_1" WITH TEMPLATE "test_something-very-very-veeeeeeeeeeeeeeeerrrrrrrrrrrrrryyyyyyyyyy-long-that-will-get-truncated"

Of course, this doesn't work since PostgreSQL truncated the identifiers and a database named "test_something-very-very-veeeeeeeeeeeeeeeerrrrrrrrrrrrrryyyyyyy" already exists.

PostgreSQL also returns the second error since the template database "test_something-very-very-veeeeeeeeeeeeeeeerrrrrrrrrrrrrryyyyyyy" does not exist.

I want to note that at first sight it might appear that users won't encounter this in real life at all, but this is not true.
If, for example, a user sets up the CI system to use a unique database name that consists of:

${PROJECT_OWNER}-${PROJECT_NAME}-${BRANCH_NAME}-${BUILD_NUMBER}

this can quickly get larger than 63 bytes (i.e. characters) and the tests will fail with the above uninformative error.

Change History (7)

comment:1 by Tim Graham, 6 years ago

Component: Uncategorized → Database layer (models, ORM)
Summary: Make Django aware of PostgreSQL database name length limits → Add an error when the PostgreSQL database name length limit is exceeded
Triage Stage: Unreviewed → Accepted
Type: Bug → Cleanup/optimization

comment:2 by Priyansh Saxena, 6 years ago

Owner: changed from nobody to Priyansh Saxena
Status: new → assigned

comment:3 by Adam Johnson, 6 years ago

Cc: Adam Johnson added

comment:5 by Tim Graham <timograham@…>, 6 years ago

Resolution: → fixed
Status: assigned → closed

In 6b3d2920:

Fixed #29015 -- Added an exception if the PostgreSQL database name is too long.

comment:6 by Tim Graham <timograham@…>, 6 years ago

In bf17f5e8:

Refs #29015 -- Added database name to PostgreSQL database name too long exception.

comment:7 by Tim Graham <timograham@…>, 6 years ago

In cae84904:

[2.1.x] Refs #29015 -- Added database name to PostgreSQL database name too long exception.

Backport of bf17f5e88466e3f571065345f5b2fea0d8af89fe from master

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