tbicr / django-pg-zero-downtime-migrations Goto Github PK

I wanted to give this library a try on our project, but it immediately broke our tests as we use PostGIS. I haven't tried anything yet, but we could probably solve this through inheritance in our project.

Would it be a welcome contribution here or would you rather keep this separate? Maybe we could split the main functionality through a Mixin, which would help reuse.

I'm not super familiar with DB backends internal, so please let me know if this doesn't sound feasible or if you see a better solution. Otherwise, I can try putting something together...

Under the "Django Migration Hacks" section the README on Row 8, it considers ALTER TABLE ADD COLUMN SET DEFAULT to be unsafe.

However, since Postgres 11, it could be considered safe (so long as the default value is not volatile) as suggested here: https://www.postgresql.org/docs/current/ddl-alter.html (under the "Tip" section).

Can this be investigated and updated, if needed? Alternatively, if it is still considered unsafe, could clarification be added?

Thank you in advance!

Describe the bug
When dropping a table with a foreign key (pointing to another table) Postgres will lock the foreign table as well as the table being dropped. This causes all queries to be blocked by the operation.

Here is an article describing the problem:
http://db-oriented.com/2018/04/18/excessive-locking-when-dropping-a-table-in-11g/

3 Options for a fix here:

1. Throw an error when a table contains foreign keys (forcing foreign keys to be dropped in a different operation)
2. Rewrite the SQL operation in DeleteModel to drop foreign keys first
3. Add lock_timeout to DROP TABLE operation

To Reproduce

1. What model did you have?

class ModelA(models.Model):
  pass

class ModelB(models.Model):
  related_a = models.ForeignKey(ModelA)

2. How did you change the model?
   Removed ModelB
3. What migration were generated?

class Migration(migrations.Migration):	
    dependencies = [...]	

    operations = [	
        migrations.DeleteModel(name="ModelB"),
    ]

4. What SQL was executed?

DROP TABLE model_b CASCADE;

5. What issue did you get?
   Locked model_b table (expected) and locked model_a for 8 minutes

Expected behavior
Not lock model_a or throw a lock timeout after 2 seconds (ZERO_DOWNTIME_MIGRATIONS_LOCK_TIMEOUT)

Versions:

• Postgres: 10.6
• Python: 3.6
• Django: 2.2.13
• django-pg-zero-downtime-migrations library: 0.8

First thanks for this project!

It'd be great to see if the warnings/errors scheme on unsafe operation is something that could be integrated to Django's core.

I just wanted to give you a small heads up that the DatabaseSchemaEditor schema editor will change a bit in Django 2.2 to allow for check and unique constraint additions through the Meta.constraints API. Most of the details are in django/django#10406

Also PostgreSQL 11 allows non locking NOT NULL column addition so it'd be great to adjust the documentation in consequence and use pg_version detection to avoid raising an exception/warning when not necessary.

Describe the bug

/.venv/lib/python3.10/site-packages/django_zero_downtime_migrations/backends/postgres/schema.py", line 153, in DatabaseSchemaEditorMixin
    sql_create_sequence = PGAccessExclusive(PostgresDatabaseSchemaEditor.sql_create_sequence, use_timeouts=False)
AttributeError: type object 'DatabaseSchemaEditor' has no attribute 'sql_create_sequence'

django_zero_downtime_migrations/backends/postgres/schema.py#L153

Removed in django 4.1 in django/django@2eea361

To Reproduce

1. What model did you have?

https://github.com/django/django/blob/4.1.2/django/contrib/auth/base_user.py#L49

class AbstractBaseUser(models.Model):
    password = models.CharField(_("password"), max_length=128)
    last_login = models.DateTimeField(_("last login"), blank=True, null=True)

    is_active = True

    REQUIRED_FIELDS = []

2. How did you change the model? Did not
3. What migration were generated? None (from what I see)
4. What SQL was executed? N/A
5. What issue did you get?

File "/home/runner/work/project/.venv/lib/python3.10/site-packages/django/contrib/auth/models.py", line 3, in <module>
    from django.contrib.auth.base_user import AbstractBaseUser, BaseUserManager
  File "/home/runner/work/project/.venv/lib/python3.10/site-packages/django/contrib/auth/base_user.py", line 49, in <module>
    class AbstractBaseUser(models.Model):
  File "/home/runner/work/project/.venv/lib/python3.10/site-packages/django/db/models/base.py", line 141, in __new__
    new_class.add_to_class("_meta", Options(meta, app_label))
  File "/home/runner/work/project/.venv/lib/python3.10/site-packages/django/db/models/base.py", line 369, in add_to_class
    value.contribute_to_class(cls, name)
  File "/home/runner/work/project/.venv/lib/python3.10/site-packages/django/db/models/options.py", line 231, in contribute_to_class
    self.db_table, connection.ops.max_name_length()
  File "/home/runner/work/project/.venv/lib/python3.10/site-packages/django/utils/connection.py", line 15, in __getattr__
    return getattr(self._connections[self._alias], item)
  File "/home/runner/work/project/.venv/lib/python3.10/site-packages/django/utils/connection.py", line 62, in __getitem__
    conn = self.create_connection(alias)
  File "/home/runner/work/project/.venv/lib/python3.10/site-packages/django/db/utils.py", line 193, in create_connection
    backend = load_backend(db["ENGINE"])
  File "/home/runner/work/project/.venv/lib/python3.10/site-packages/django/db/utils.py", line 113, in load_backend
    return import_module("%s.base" % backend_name)
  File "/opt/hostedtoolcache/Python/3.10.1/x64/lib/python3.10/importlib/__init__.py", line 126, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "/home/runner/work/project/.venv/lib/python3.10/site-packages/django_zero_downtime_migrations/backends/postgres/base.py", line 5, in <module>
    from .schema import DatabaseSchemaEditor
  File "/home/runner/work/project/.venv/lib/python3.10/site-packages/django_zero_downtime_migrations/backends/postgres/schema.py", line 144, in <module>
    class DatabaseSchemaEditorMixin:
  File "/home/runner/work/project/.venv/lib/python3.10/site-packages/django_zero_downtime_migrations/backends/postgres/schema.py", line 153, in DatabaseSchemaEditorMixin
    sql_create_sequence = PGAccessExclusive(PostgresDatabaseSchemaEditor.sql_create_sequence, use_timeouts=False)
AttributeError: type object 'DatabaseSchemaEditor' has no attribute 'sql_create_sequence'
Traceback (most recent call last):
  File "/home/runner/work/project/scan_migrations.py", line 120, in <module>
    exit_code = check_migrations()
  File "/home/runner/work/project/scan_migrations.py", line 96, in check_migrations
    if should_skip_migrations():
  File "/home/runner/work/project/scan_migrations.py", line 35, in should_skip_migrations
    migrations = subprocess.check_output(
  File "/opt/hostedtoolcache/Python/3.10.1/x64/lib/python3.10/subprocess.py", line 420, in check_output
    return run(*popenargs, stdout=PIPE, timeout=timeout, check=True,
  File "/opt/hostedtoolcache/Python/3.10.1/x64/lib/python3.10/subprocess.py", line 524, in run
    raise CalledProcessError(retcode, process.args,
subprocess.CalledProcessError: Command 'poetry run ./manage.py migrate --plan' returned non-zero exit status 1.

Expected behavior

Shouldn't fail when checking migrations

Versions:

• Postgres: 13
• Python: 3.11
• Django: 3.1.12
• django-pg-zero-downtime-migrations library: 0.11

Have you ever hooked prettier into your editor before for markdown files? It automatically handles wrapping lines and tables.

master...tony:format-with-prettier

One command: npx prettier --no-config --write README.md

Currently when CREATE INDEX operation is detected, it is quietly replaced with CREATE INDEX CONCURRENTLY. It would be super useful to have a flag in settings, which would allow to choose whether the user wants backend to do that or just to consider this operation unsafe and possibly raise an exception (if an appropriate flag is set).

Describe the bug
I'm getting the following error when running a migration with django v3.2:

AttributeError: 'DatabaseSchemaEditor' object has no attribute 'skip_default_on_alter'

Seems like in v0.11 there was this condition. I'm wondering if this should be checking django < 4 instead:

In more recent versions the condition is removed completely but there's still a reference to skip_default_on_alter

To Reproduce
Sorry don't have a reproducible example but I'm hoping the root cause will be clear.

Expected behavior
Migration succeeds

Versions:

• Postgres: 13
• Python: 3.10
• Django: 3.2
• django-pg-zero-downtime-migrations library: 0.11, 0.14

Will we have a release to pypi soon?

It could be a 0.10.1 to incorporate the latest fix #27 and maybe a 0.11 for django 3.2 and 4.0?

Relevant updates from 3.2 and 4.0:

• postgresql/schema.py: starting at 8c7992f, wider search postgresql/
• base/schema.py starting at 8c7992f

Hi,

Thanks for your project!

We think we have same problem. We have a model (MyModel) with 1.000.000 of rows. And we add a field with default value we have a lock during 30-120 seconds. Something like this:

class MyModel(models.Model):
        ...
        # Adding a new field
        x = models.CharField(max_length=250, blank=True, default='XX')
        ...

And its migration:

class Migration(migrations.Migration):

    dependencies = [
        ('api', '0001_initial'),
    ]

    operations = [
        migrations.AddField(
            model_name='mymodel',
            name='x',
            field=models.CharField(blank=True, default='XX', max_length=250),
        ),
    ]

We have done a lot of tests, e.g.: we have created fields without default, but really default is "", and we get the same problem.

Currently, it is our workaround:

1. We create every field with null=True and default=None. So PostgreSQL don't have to write in every row anything.
2. We migrate via a django command every row of "MyModel".
3. We update code, and remove null=False and adding default="XXX"
4. We update the code in production enviroment
5. We migrate via a django command every new row of "MyModel".
6. We generate new migration to add "not null" constrain
7. We run last migration (6 point)

When we find your solution we were very happy, our workaround is very tricky, but your solutions does not work for us. We get a little more time locked (35-125 seconds) instead of get zero downtime.

It is our settings:

DATABASES = {
    'default': {
        'ENGINE': 'django_zero_downtime_migrations_postgres_backend',
        #'ENGINE': 'django.contrib.gis.db.backends.postgis',
        ...
    }
}

ZERO_DOWNTIME_MIGRATIONS_RAISE_FOR_UNSAFE = True

ZERO_DOWNTIME_MIGRATIONS_USE_NOT_NULL = False

Please, could you tell us what is wrong in our case?
Please, could you upload a little project example?

Thanks another time,

Describe the bug

When we are creating and migrating a database from scratch, models that have foreign keys to other models use the deferred SQL statements.
This ensures that the target table was created before running the deferred SQL statements.

To Reproduce

Take basic installed apps:

INSTALLED_APPS = [
    "django.contrib.auth",
    "django.contrib.contenttypes",
...

and run test with them:
It will crash with:

django.db.utils.ProgrammingError: relation "django_content_type" does not exist

So manage.py test creates the test database with a migrate --run-syncdb command.

This usually starts by creating all models, then add the FK constraints (as deferred statements.
But with this package, we create the auth_permission table, and then add the constraints to django_content_type => which fails 😢
Related to this piece of code:

For the example, a fix could be to swap the two Django apps - but the bug also occurs when both models are inside the same app.
I believe this could also happen with a migration adding two models, where one has a FK to another. So perhaps not only when migrating from scratch.

Expected behavior
To not crash :D

One idea, would somehow not to immediately run deferred SQL statements when migrating from scratch the databases
I'm also curious about other possible workaround.

Versions:

• Postgres: 14.0
• Python: 3.10
• Django: 3.2
• django-pg-zero-downtime-migrations library: 0.13

Since 3.6 reached EOL a while ago, it might be time to drop support for it. I can take on this task.

Hi!

Thank you for this library. I think it's cool to have 500 stars, tests, 16k download per month, and clear readme.md description.

I would like to add this library to my project. But I'm a little bit worried, why this note exists?

NOTE: this package is in beta, please check your migrations SQL before applying on production and submit issue for any question.

Is it battle tested? Can you provide some examples on why this can be still beta?

Describe the bug

Renaming a model with a fixed table name (e.g. Book to OldBook) will lead to a migration that raises ALTER_TABLE_RENAME, when no schema changes occur.

To Reproduce

1. What model did you have?

Django model with Meta.db_table = 'table_name' hard coded

from django.db import models
class Book(models.Model):
    # any fields
    name = models.CharField(max_length=128, blank=True)
    class Meta:
        db_table = 'books`

2. How did you change the model?

Renaming the class from Book to OldBook will lead to a migration that raises ALTER_TABLE_RENAME, when in actuality no schema changes occur. (Correct me if I'm mistaken!)

❯ poetry run ./manage.py makemigrations
Did you rename the book.Book model to OldBook? [y/N] y                                                                                                                                                           Migrations for 'book':
  src/project/book/migrations/0004_auto_20211230_2157.py                                                                                                                                                                             - Rename model Book to OldBook

3. What migration were generated?

# Generated by Django 2.2.1 on 2021-12-30 22:18

from django.conf import settings
from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ('book', '0002_auto_20201018_0102'),
    ]

    operations = [
        migrations.RenameModel(
            old_name='Book',
            new_name='OldBook',
        ),
    ]

4. What SQL was executed?

poetry run ./manage.py sqlmigrate book 0004
BEGIN;
--
-- Rename model Book to OldBook
--
COMMIT;

5. What issue did you get?

Traceback (most recent call last):
  File "./manage.py", line 21, in <module>
    main()
  File "./manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "~/project/.venv/lib/python3.7/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "~/project/.venv/lib/python3.7/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "~/project/.venv/lib/python3.7/site-packages/django/core/management/base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "~/project/.venv/lib/python3.7/site-packages/django/core/management/commands/sqlmigrate.py", line 30, in execute
    return super().execute(*args, **options)
  File "~/project/.venv/lib/python3.7/site-packages/django/core/management/base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "~/project/.venv/lib/python3.7/site-packages/django/core/management/commands/sqlmigrate.py", line 64, in handle
    sql_statements = executor.collect_sql(plan)
  File "~/project/.venv/lib/python3.7/site-packages/django/db/migrations/executor.py", line 225, in collect_sql
    state = migration.apply(state, schema_editor, collect_sql=True)
  File "~/project/.venv/lib/python3.7/site-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "~/project/.venv/lib/python3.7/site-packages/django/db/migrations/operations/models.py", line 353, in database_forwards
    new_model._meta.db_table,
  File "~/project/.venv/lib/python3.7/site-packages/django_zero_downtime_migrations/backends/postgres/schema.py", line 394, in alter_db_table
    raise UnsafeOperationException(Unsafe.ALTER_TABLE_RENAME)
django_zero_downtime_migrations.backends.postgres.schema.UnsafeOperationException: ALTER TABLE RENAME is unsafe operation
See details for save alternative https://github.com/tbicr/django-pg-zero-downtime-migrations#changes-for-working-logic

Expected behavior
When database tables are unaltered, don't raise

Versions:

• Postgres: 12
• Python: 3.7
• Django: 2.2
• django-pg-zero-downtime-migrations library: 0.10

We try to applying this default django migration: auth.0002_alter_permission_name_max_length
and catched traseback:
File "/usr/local/lib/python3.10/site-packages/django/core/management/init.py", line 442, in execute_from_command_line
utility.execute()
File "/usr/local/lib/python3.10/site-packages/django/core/management/init.py", line 436, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 412, in run_from_argv
self.execute(*args, **cmd_options)
File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 458, in execute
output = self.handle(*args, **options)
File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 106, in wrapper
res = handle_func(*args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/django/core/management/commands/migrate.py", line 356, in handle
post_migrate_state = executor.migrate(
File "/usr/local/lib/python3.10/site-packages/django/db/migrations/executor.py", line 135, in migrate
state = self._migrate_all_forwards(
File "/usr/local/lib/python3.10/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
state = self.apply_migration(
File "/usr/local/lib/python3.10/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
state = migration.apply(state, schema_editor)
File "/usr/local/lib/python3.10/site-packages/django/db/migrations/migration.py", line 132, in apply
operation.database_forwards(
File "/usr/local/lib/python3.10/site-packages/django/db/migrations/operations/fields.py", line 235, in database_forwards
schema_editor.alter_field(from_model, from_field, to_field)
File "/usr/local/lib/python3.10/site-packages/django_zero_downtime_migrations/backends/postgres/schema.py", line 387, in alter_field
super().alter_field(model, old_field, new_field, strict)
File "/usr/local/lib/python3.10/site-packages/django/db/backends/base/schema.py", line 830, in alter_field
self._alter_field(
File "/usr/local/lib/python3.10/site-packages/django/db/backends/postgresql/schema.py", line 287, in _alter_field
super()._alter_field(
File "/usr/local/lib/python3.10/site-packages/django/db/backends/base/schema.py", line 1010, in _alter_field
fragment, other_actions = self._alter_column_type_sql(
TypeError: DatabaseSchemaEditorMixin._alter_column_type_sql() takes 5 positional arguments but 7 were given

Versions:

• Postgres==13:
• Python==3.10:
• Django==4.2.2:
• django-pg-zero-downtime-migrations==0.12: