Opened 3 months ago

Last modified 3 months ago

#29015 assigned Cleanup/optimization

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 (Chainz) 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 (4)

comment:1 Changed 3 months ago by Tim Graham

Component: UncategorizedDatabase layer (models, ORM)
Summary: Make Django aware of PostgreSQL database name length limitsAdd an error when the PostgreSQL database name length limit is exceeded
Triage Stage: UnreviewedAccepted
Type: BugCleanup/optimization

comment:2 Changed 3 months ago by Priyansh Saxena

Owner: changed from nobody to Priyansh Saxena
Status: newassigned

comment:3 Changed 3 months ago by Adam (Chainz) Johnson

Cc: Adam (Chainz) Johnson added

comment:4 Changed 3 months ago by Priyansh Saxena

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