3.2 upgrade fails: cannot ALTER TABLE "maasserver_partition" because it has pending trigger events

[dann frazier]

Attempted upgrade from 3.1/stable to 3.2/beta:

ubuntu@maas:~$ sudo snap refresh maas --channel=3.2/beta
error: cannot perform the following tasks:
- Run post-refresh hook of "maas" snap if present (run hook "post-refresh":
-----
Operations to perform:
  Apply all migrations: auth, contenttypes, maasserver, metadataserver, piston3, sessions, sites
Running migrations:
  Applying maasserver.0252_drop_fannetwork... OK
  Applying maasserver.0253_nodeconfig... OK
  Applying maasserver.0254_default_nodeconfig_devices... OK
  Applying maasserver.0255_node_current_config... OK
  Applying maasserver.0256_blockdevice_nodeconfig_only... OK
  Applying maasserver.0257_filesystem_populate_node_config_id... OK
  Applying maasserver.0258_filesystem_nodeconfig_only... OK
  Applying maasserver.0259_add_hardware_sync_flag... OK
  Applying maasserver.0260_drop_maas_support_views... OK
  Applying maasserver.0261_interface_nodeconfig_only... OK
  Applying maasserver.0262_nodeconfig_link_replace_node... OK
  Applying maasserver.0263_vlan_racks_on_delete... OK
  Applying maasserver.0264_nodedevice_nodeconfig_link... OK
  Applying maasserver.0265_nodedevice_nodeconfig_migrate... OK
  Applying maasserver.0266_nodedevice_unlink_node... OK
  Applying maasserver.0267_add_machine_specific_sync_interval_fields... OK
  Applying maasserver.0268_partition_index...Traceback (most recent call last):
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.ObjectInUse: cannot ALTER TABLE "maasserver_partition" because it has pending trigger events

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/snap/maas/19262/bin/maas-region", line 8, in <module>
    sys.exit(run())
  File "/snap/maas/19262/lib/python3.8/site-packages/maasserver/region_script.py", line 77, in run
    run_django(is_snap, is_devenv)
  File "/snap/maas/19262/lib/python3.8/site-packages/maasserver/region_script.py", line 66, in run_django
    management.execute_from_command_line()
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/core/management/base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/core/management/base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "/snap/maas/19262/lib/python3.8/site-packages/maasserver/management/commands/dbupgrade.py", line 116, in handle
    call_command("migrate", interactive=False)
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/core/management/__init__.py", line 148, in call_command
    return command.execute(*args, **defaults)
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/core/management/base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/core/management/commands/migrate.py", line 232, in handle
    post_migrate_state = executor.migrate(
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/db/migrations/executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/db/migrations/operations/models.py", line 527, in database_forwards
    alter_together(
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/db/backends/base/schema.py", line 367, in alter_unique_together
    self.execute(self._create_unique_sql(model, columns))
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/db/backends/base/schema.py", line 137, in execute
    cursor.execute(sql, params)
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/snap/maas/19262/usr/lib/python3/dist-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.OperationalError: cannot ALTER TABLE "maasserver_partition" because it has pending trigger events

Traceback (most recent call last):
  File "/snap/maas/19262/bin/maas", line 8, in <module>
    sys.exit(main())
  File "/snap/maas/19262/lib/python3.8/site-packages/maascli/__init__.py", line 39, in main
    options.execute(options)
  File "/snap/maas/19262/lib/python3.8/site-packages/maascli/snap.py", line 448, in __call__
    raise exc
  File "/snap/maas/19262/lib/python3.8/site-packages/maascli/snap.py", line 445, in __call__
    self.handle(options)
  File "/snap/maas/19262/lib/python3.8/site-packages/maascli/snap.py", line 939, in handle
    sys.exit(migrate_db())
  File "/snap/maas/19262/lib/python3.8/site-packages/maascli/snap.py", line 353, in migrate_db
    subprocess.check_call(
  File "/usr/lib/python3.8/subprocess.py", line 364, in check_call
    raise CalledProcessError(retcode, cmd)
subprocess.CalledProcessError: Command '['/snap/maas/19262/bin/maas-region', 'dbupgrade']' returned non-zero exit status 1.
-----)

[Alexsander de Souza (alexsander-souza)]

The host was running postgresql v10, which is unsupported. After upgrading the DB to v12 the migrations worked as expected.

[dann frazier (dannf)]

@Alexsander thanks so much for your help root causing this. Is it possible for MAAS to detect an unsupported postgresql version and abort before (failing to) upgrade? Reopening this bug for that request, but let me know if a new bug is preferred.

[Alexsander de Souza (alexsander-souza)]

Yes it's possible. (in fact I'm already implementing it)

[dann frazier (dannf)]

\o/

[Edvinas Sulzickis (madness31)]

Experiencing the same upgrading maas from 3.1 to 3.3:

~$ sudo apt upgrade
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Calculating upgrade... Done
Try Ubuntu Pro beta with a free personal subscription on up to 5 machines.
Learn more at https://ubuntu.com/pro
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.
2 not fully installed or removed.
After this operation, 0 B of additional disk space will be used.
Do you want to continue? [Y/n] y
Setting up maas-region-controller (1:3.3.0~beta2-12870-g.34e582bb0-0ubuntu1~22.04.1) ...
Operations to perform:
  Apply all migrations: auth, contenttypes, maasserver, metadataserver, piston3, sessions, sites
Running migrations:
  Applying maasserver.0268_partition_index...Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.ObjectInUse: cannot ALTER TABLE "maasserver_partition" because it has pending trigger events

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/sbin/maas-region", line 33, in <module>
    sys.exit(load_entry_point('maas==3.3.0b2', 'console_scripts', 'maas-region')())
  File "/usr/lib/python3/dist-packages/maasserver/region_script.py", line 77, in run
    run_django(is_snap, is_devenv)
  File "/usr/lib/python3/dist-packages/maasserver/region_script.py", line 66, in run_django
    management.execute_from_command_line()
  File "/usr/lib/python3/dist-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/usr/lib/python3/dist-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/lib/python3/dist-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/lib/python3/dist-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/usr/lib/python3/dist-packages/maasserver/management/commands/dbupgrade.py", line 125, in handle
    call_command(
  File "/usr/lib/python3/dist-packages/django/core/management/__init__.py", line 181, in call_command
    return command.execute(*args, **defaults)
  File "/usr/lib/python3/dist-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/usr/lib/python3/dist-packages/django/core/management/base.py", line 89, in wrapped
    res = handle_func(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/django/core/management/commands/migrate.py", line 244, in handle
    post_migrate_state = executor.migrate(
  File "/usr/lib/python3/dist-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/lib/python3/dist-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_init...

[Edvinas Sulzickis (madness31)]

As my logs are saying I am using psql 14, so maybe there is another reason for failing maas upgrade?

[Edvinas Sulzickis (madness31)]

Although psql -V command shows 14 version, but if I check psql version from within database I am getting another result:

1) sudo -u postgres psql

psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1), server 10.22 (Ubuntu 10.22-0ubuntu0.18.04.1))
Type "help" for help.

2) SELECT version();
PostgreSQL 10.22 (Ubuntu 10.22-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit

Can the author of this post tell us how did he upgraded psql version?

[Alexsander de Souza (alexsander-souza)]

Depending on how you upgraded Postgresql you might have versions 10 and 14 running at the same time. Please have a look at https://discourse.maas.io/t/upgrading-postgresql-to-version-12/5913

[Edvinas Sulzickis (madness31)]

Actually I did not do anything for postgresql. What I only did was upgraded the Ubuntu OS from 20.04 to 22.04. So I assume during update from Ubuntu 20.04 to Ubuntu 22.04 new version of postgresql was installed. This is what I was following https://maas.io/docs/whats-new-in-maas#heading--How-to-upgrade-from-3-2-or-lower-to-MAAS-3-3

[Edvinas Sulzickis (madness31)]

I see that my current version is 10.22:

$ sudo -u postgres psql -c "SELECT version();"
[sudo] password for edvinas:
Sorry, try again.
[sudo] password for edvinas:
                                                               version
--------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.22 (Ubuntu 10.22-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
(1 row)

So probably I should go and execute all the steps in https://discourse.maas.io/t/upgrading-postgresql-to-version-12/5913 and then verify that this command "sudo -u postgres psql -c "SELECT version();"" shows correct version?

[Alexsander de Souza (alexsander-souza)]

yes, upgrading Ubuntu won't migrate your data to a newer version of Postgresql (it's too risky to do this automatically). Running that procedure should fix this

[Edvinas Sulzickis (madness31)]

Thanks. I have just finished to proceed with those steps and the issue was resolved. Just a little thing is that we now starting to get some warning "The PostgreSQL version in use is older than 14.". maas 3.3 will do support postgresql 12, but support will be dropped on maas 3.4 version