Migration changes column to NOT NULL without updating NULL data first

[tm-drtina]

Apache Airflow version

2.3.2 (latest released)

What happened

During upgrade from Airflow 1.x, I've encountered migration failure in migration https://github.com/apache/airflow/blob/05c542dfa8eee9b4cdca4e9370f459ce807354b2/airflow/migrations/versions/0080_2_0_2_change_default_pool_slots_to_1.py

In PR #20962 on these lines https://github.com/apache/airflow/pull/20962/files#diff-9e46226bab06a05ef0040d1f8cc08c81ba94455ca9a170a0417352466242f2c1L61-L63 the update was removed, which breaks if the original table contains nulls in that column (at least in postgres DB).

What you think should happen instead

No response

How to reproduce

• Have pre 2.0.2 version deployed, where the column was nullable.
• Have task instance with pool_slots = NULL
• Try to migrate to latest version (or any version after Support generating SQL script for upgrades #20962 was merged)

Operating System

Custom NixOS

Versions of Apache Airflow Providers

No response

Deployment

Other

Deployment details

We have NixOS with Airflow installed using setup.py with postgres as a DB.

Anything else

INFO  [alembic.runtime.migration] Running upgrade 449b4072c2da -> 8646922c8a04, Change default ``pool_slots`` to ``1``
Traceback (most recent call last):
  File "/nix/store/[redacted-hash1]-python3.9-SQLAlchemy-1.4.9/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
    self.dialect.do_execute(
  File "/nix/store/[redacted-hash1]-python3.9-SQLAlchemy-1.4.9/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.NotNullViolation: column "pool_slots" contains null values
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "/nix/store/[redacted-hash2]-python3.9-apache-airflow-2.3.2/bin/.airflow-wrapped", line 9, in <module>
    sys.exit(main())
  File "/nix/store/[redacted-hash2]-python3.9-apache-airflow-2.3.2/lib/python3.9/site-packages/airflow/__main__.py", line 38, in main
    args.func(args)
  File "/nix/store/[redacted-hash2]-python3.9-apache-airflow-2.3.2/lib/python3.9/site-packages/airflow/cli/cli_parser.py", line 51, in command
    return func(*args, **kwargs)
  File "/nix/store/[redacted-hash2]-python3.9-apache-airflow-2.3.2/lib/python3.9/site-packages/airflow/cli/commands/db_command.py", line 35, in initdb
    db.initdb()
  File "/nix/store/[redacted-hash2]-python3.9-apache-airflow-2.3.2/lib/python3.9/site-packages/airflow/utils/session.py", line 71, in wrapper
    return func(*args, session=session, **kwargs)
  File "/nix/store/[redacted-hash2]-python3.9-apache-airflow-2.3.2/lib/python3.9/site-packages/airflow/utils/db.py", line 648, in initdb
    upgradedb(session=session)
  File "/nix/store/[redacted-hash2]-python3.9-apache-airflow-2.3.2/lib/python3.9/site-packages/airflow/utils/session.py", line 68, in wrapper
    return func(*args, **kwargs)
  File "/nix/store/[redacted-hash2]-python3.9-apache-airflow-2.3.2/lib/python3.9/site-packages/airflow/utils/db.py", line 1449, in upgradedb
    command.upgrade(config, revision=to_revision or 'heads')
  File "/nix/store/[redacted-hash3]-python3.9-alembic-1.7.7/lib/python3.9/site-packages/alembic/command.py", line 320, in upgrade
    script.run_env()
  File "/nix/store/[redacted-hash3]-python3.9-alembic-1.7.7/lib/python3.9/site-packages/alembic/script/base.py", line 563, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/nix/store/[redacted-hash3]-python3.9-alembic-1.7.7/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 92, in load_python_file
    module = load_module_py(module_id, path)
  File "/nix/store/[redacted-hash3]-python3.9-alembic-1.7.7/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 108, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 850, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "/nix/store/[redacted-hash2]-python3.9-apache-airflow-2.3.2/lib/python3.9/site-packages/airflow/migrations/env.py", line 107, in <module>
    run_migrations_online()
  File "/nix/store/[redacted-hash2]-python3.9-apache-airflow-2.3.2/lib/python3.9/site-packages/airflow/migrations/env.py", line 101, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/nix/store/[redacted-hash3]-python3.9-alembic-1.7.7/lib/python3.9/site-packages/alembic/runtime/environment.py", line 851, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/nix/store/[redacted-hash3]-python3.9-alembic-1.7.7/lib/python3.9/site-packages/alembic/runtime/migration.py", line 620, in run_migrations
    step.migration_fn(**kw)
  File "/nix/store/[redacted-hash2]-python3.9-apache-airflow-2.3.2/lib/python3.9/site-packages/airflow/migrations/versions/0080_2_0_2_change_default_pool_slots_to_1.py", line 41, in upgrade
    batch_op.alter_column("pool_slots", existing_type=sa.Integer, nullable=False, server_default='1')
  File "/nix/store/lb7982cwd56am6nzx1ix0aljz416w6mw-python3-3.9.6/lib/python3.9/contextlib.py", line 124, in __exit__
    next(self.gen)
  File "/nix/store/[redacted-hash3]-python3.9-alembic-1.7.7/lib/python3.9/site-packages/alembic/operations/base.py", line 374, in batch_alter_table
    impl.flush()
  File "/nix/store/[redacted-hash3]-python3.9-alembic-1.7.7/lib/python3.9/site-packages/alembic/operations/batch.py", line 108, in flush
    fn(*arg, **kw)
  File "/nix/store/[redacted-hash3]-python3.9-alembic-1.7.7/lib/python3.9/site-packages/alembic/ddl/postgresql.py", line 170, in alter_column
    super(PostgresqlImpl, self).alter_column(
  File "/nix/store/[redacted-hash3]-python3.9-alembic-1.7.7/lib/python3.9/site-packages/alembic/ddl/impl.py", line 227, in alter_column
    self._exec(
  File "/nix/store/[redacted-hash3]-python3.9-alembic-1.7.7/lib/python3.9/site-packages/alembic/ddl/impl.py", line 193, in _exec
    return conn.execute(construct, multiparams)
  File "/nix/store/[redacted-hash1]-python3.9-SQLAlchemy-1.4.9/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1200, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/nix/store/[redacted-hash1]-python3.9-SQLAlchemy-1.4.9/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 77, in _execute_on_connection
    return connection._execute_ddl(
  File "/nix/store/[redacted-hash1]-python3.9-SQLAlchemy-1.4.9/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1290, in _execute_ddl
    ret = self._execute_context(
  File "/nix/store/[redacted-hash1]-python3.9-SQLAlchemy-1.4.9/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1748, in _execute_context
    self._handle_dbapi_exception(
  File "/nix/store/[redacted-hash1]-python3.9-SQLAlchemy-1.4.9/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
    util.raise_(
  File "/nix/store/[redacted-hash1]-python3.9-SQLAlchemy-1.4.9/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/nix/store/[redacted-hash1]-python3.9-SQLAlchemy-1.4.9/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
    self.dialect.do_execute(
  File "/nix/store/[redacted-hash1]-python3.9-SQLAlchemy-1.4.9/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) column "pool_slots" contains null values
[SQL: ALTER TABLE task_instance ALTER COLUMN pool_slots SET NOT NULL]
(Background on this error at: http://sqlalche.me/e/14/gkpj)

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[boring-cyborg]

Thanks for opening your first issue here! Be sure to follow the issue template!

[potiuk]

Assigned you!