starred/modoboa-modoboa

Fork 0

mirror of https://github.com/modoboa/modoboa.git synced 2026-04-26 17:36:01 +03:00

[GH-ISSUE #2508] Upgrade from 1.17.0 to 2.0.0 fails - psycopg2.errors.FeatureNotSupported: cannot alter type of a column used by a view or rule #1615

New issue

Closed

opened 2026-02-27 11:18:07 +03:00 by kerem · 5 comments

kerem commented

2026-02-27 11:18:07 +03:00

Owner

Originally created by @nexusgoblin on GitHub (May 7, 2022).
Original GitHub issue: https://github.com/modoboa/modoboa/issues/2508

Impacted versions 1.17.0 / 2.0.0

OS Type: Ubuntu
OS Version: 20.04 LTS
Database Type: PostgreSQL
Database version: 12.10
Modoboa: 1.17.0 /2.0.0
installer used: Yes
Webserver: Nginx

Steps to reproduce

Following upgrade instructions:

sudo -u modoboa -i
bash
source instance/bin/activate
(env) pip install modoboa==2.0.0
(env) cd instance
(env) python manage.py migrate

Current behavior

(env) $ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, authtoken, contenttypes, core, dnstools, lib, limits, maillog, modoboa_amavis, modoboa_contacts, modoboa_dmarc, modoboa_postfix_autoreply, modoboa_radicale, otp_static, otp_totp, relaydomains, reversion, sessions, sites, transport
Running migrations:
  Applying admin.0020_auto_20210130_2226...Traceback (most recent call last):
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.FeatureNotSupported: cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view dkim depends on column "name"


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

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 "/srv/modoboa/env/lib/python3.8/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/core/management/base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/core/management/base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 232, in handle
    post_migrate_state = executor.migrate(
  File "/srv/modoboa/env/lib/python3.8/site-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 "/srv/modoboa/env/lib/python3.8/site-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 "/srv/modoboa/env/lib/python3.8/site-packages/django/db/migrations/executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/migrations/operations/fields.py", line 249, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 534, in alter_field
    self._alter_field(model, old_field, new_field, old_type, new_type,
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/postgresql/schema.py", line 122, in _alter_field
    super()._alter_field(
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 680, in _alter_field
    self.execute(
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 137, in execute
    cursor.execute(sql, params)
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.NotSupportedError: cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view dkim depends on column "name"

Expected behavior

Migrate completes successfully.

Video/Screenshot link (optional)

N/A

Originally created by @nexusgoblin on GitHub (May 7, 2022). Original GitHub issue: https://github.com/modoboa/modoboa/issues/2508 # Impacted versions 1.17.0 / 2.0.0 * OS Type: Ubuntu * OS Version: 20.04 LTS * Database Type: PostgreSQL * Database version: 12.10 * Modoboa: 1.17.0 /2.0.0 * installer used: Yes * Webserver: Nginx # Steps to reproduce Following upgrade instructions: ``` sudo -u modoboa -i bash source instance/bin/activate (env) pip install modoboa==2.0.0 (env) cd instance (env) python manage.py migrate ``` # Current behavior ``` (env) $ python manage.py migrate Operations to perform: Apply all migrations: admin, auth, authtoken, contenttypes, core, dnstools, lib, limits, maillog, modoboa_amavis, modoboa_contacts, modoboa_dmarc, modoboa_postfix_autoreply, modoboa_radicale, otp_static, otp_totp, relaydomains, reversion, sessions, sites, transport Running migrations: Applying admin.0020_auto_20210130_2226...Traceback (most recent call last): File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute return self.cursor.execute(sql, params) psycopg2.errors.FeatureNotSupported: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view dkim depends on column "name" The above exception was the direct cause of the following exception: 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 "/srv/modoboa/env/lib/python3.8/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line utility.execute() File "/srv/modoboa/env/lib/python3.8/site-packages/django/core/management/__init__.py", line 375, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File "/srv/modoboa/env/lib/python3.8/site-packages/django/core/management/base.py", line 323, in run_from_argv self.execute(*args, **cmd_options) File "/srv/modoboa/env/lib/python3.8/site-packages/django/core/management/base.py", line 364, in execute output = self.handle(*args, **options) File "/srv/modoboa/env/lib/python3.8/site-packages/django/core/management/base.py", line 83, in wrapped res = handle_func(*args, **kwargs) File "/srv/modoboa/env/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 232, in handle post_migrate_state = executor.migrate( File "/srv/modoboa/env/lib/python3.8/site-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 "/srv/modoboa/env/lib/python3.8/site-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 "/srv/modoboa/env/lib/python3.8/site-packages/django/db/migrations/executor.py", line 245, in apply_migration state = migration.apply(state, schema_editor) File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/migrations/migration.py", line 124, in apply operation.database_forwards(self.app_label, schema_editor, old_state, project_state) File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/migrations/operations/fields.py", line 249, in database_forwards schema_editor.alter_field(from_model, from_field, to_field) File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 534, in alter_field self._alter_field(model, old_field, new_field, old_type, new_type, File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/postgresql/schema.py", line 122, in _alter_field super()._alter_field( File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 680, in _alter_field self.execute( File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 137, in execute cursor.execute(sql, params) File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 67, in execute return self._execute_with_wrappers(sql, params, many=False, executor=self._execute) File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers return executor(sql, params, many, context) File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute return self.cursor.execute(sql, params) File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/utils.py", line 89, in __exit__ raise dj_exc_value.with_traceback(traceback) from exc_value File "/srv/modoboa/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute return self.cursor.execute(sql, params) django.db.utils.NotSupportedError: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view dkim depends on column "name" ``` # Expected behavior Migrate completes successfully. # Video/Screenshot link (optional) N/A

kerem

2026-02-27 11:18:07 +03:00

closed this issue
added the
documentation
label

kerem commented

2026-02-27 11:18:08 +03:00

Author

Owner

@nexusgoblin commented on GitHub (May 7, 2022):

This would seem to be a postgres change issue. Manually dropping the view (dkim) allows python manage.py migrate to complete successfully. Should part of the migrate process be dropping the dkim view and recreating as a final step?

What I did to work around this issue was to:

$ sudo su - postgres
$ psql
psql (12.10 (Ubuntu 12.10-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# \c modoboa
You are now connected to database "modoboa" as user "postgres".
modoboa-# \d+ dkim
                                         View "public.dkim"
      Column      |          Type          | Collation | Nullable | Default | Storage  | Description 
------------------+------------------------+-----------+----------+---------+----------+-------------
 id               | integer                |           |          |         | plain    | 
 domain_name      | character varying(100) |           |          |         | extended | 
 private_key_path | character varying(254) |           |          |         | extended | 
 selector         | character varying(30)  |           |          |         | extended | 
View definition:
 SELECT admin_domain.id,
    admin_domain.name AS domain_name,
    admin_domain.dkim_private_key_path AS private_key_path,
    admin_domain.dkim_key_selector AS selector
   FROM admin_domain
  WHERE admin_domain.enable_dkim;

modoboa=# drop view dkim;
DROP VIEW

At this point, I went back and (re)ran python mnanage.py migrate, which was successful. Then I recreated the view:

modoboa=# CREATE OR REPLACE VIEW dkim AS
modoboa-#  SELECT admin_domain.id,
modoboa-#     admin_domain.name AS domain_name,
modoboa-#     admin_domain.dkim_private_key_path AS private_key_path,
modoboa-#     admin_domain.dkim_key_selector AS selector
modoboa-#    FROM admin_domain
modoboa-#   WHERE admin_domain.enable_dkim;
CREATE VIEW
modoboa=# grant select on dkim to opendkim;
GRANT
modoboa=# \d+ dkim
                                         View "public.dkim"
      Column      |          Type          | Collation | Nullable | Default | Storage  | Description 
------------------+------------------------+-----------+----------+---------+----------+-------------
 id               | integer                |           |          |         | plain    | 
 domain_name      | character varying(253) |           |          |         | extended | 
 private_key_path | character varying(254) |           |          |         | extended | 
 selector         | character varying(30)  |           |          |         | extended | 
View definition:
 SELECT admin_domain.id,
    admin_domain.name AS domain_name,
    admin_domain.dkim_private_key_path AS private_key_path,
    admin_domain.dkim_key_selector AS selector
   FROM admin_domain
  WHERE admin_domain.enable_dkim;

I'm continuing wi the rest of the upgrade now.

Edit: Update to include missing grant to dkim.

@nexusgoblin commented on GitHub (May 7, 2022): This would seem to be a postgres change issue. Manually dropping the view (`dkim`) allows `python manage.py migrate` to complete successfully. Should part of the migrate process be dropping the `dkim` view and recreating as a final step? What I did to work around this issue was to: ``` $ sudo su - postgres $ psql psql (12.10 (Ubuntu 12.10-0ubuntu0.20.04.1)) Type "help" for help. postgres=# \c modoboa You are now connected to database "modoboa" as user "postgres". modoboa-# \d+ dkim View "public.dkim" Column | Type | Collation | Nullable | Default | Storage | Description ------------------+------------------------+-----------+----------+---------+----------+------------- id | integer | | | | plain | domain_name | character varying(100) | | | | extended | private_key_path | character varying(254) | | | | extended | selector | character varying(30) | | | | extended | View definition: SELECT admin_domain.id, admin_domain.name AS domain_name, admin_domain.dkim_private_key_path AS private_key_path, admin_domain.dkim_key_selector AS selector FROM admin_domain WHERE admin_domain.enable_dkim; modoboa=# drop view dkim; DROP VIEW ``` At this point, I went back and (re)ran `python mnanage.py migrate`, which was successful. Then I recreated the view: ``` modoboa=# CREATE OR REPLACE VIEW dkim AS modoboa-# SELECT admin_domain.id, modoboa-# admin_domain.name AS domain_name, modoboa-# admin_domain.dkim_private_key_path AS private_key_path, modoboa-# admin_domain.dkim_key_selector AS selector modoboa-# FROM admin_domain modoboa-# WHERE admin_domain.enable_dkim; CREATE VIEW modoboa=# grant select on dkim to opendkim; GRANT modoboa=# \d+ dkim View "public.dkim" Column | Type | Collation | Nullable | Default | Storage | Description ------------------+------------------------+-----------+----------+---------+----------+------------- id | integer | | | | plain | domain_name | character varying(253) | | | | extended | private_key_path | character varying(254) | | | | extended | selector | character varying(30) | | | | extended | View definition: SELECT admin_domain.id, admin_domain.name AS domain_name, admin_domain.dkim_private_key_path AS private_key_path, admin_domain.dkim_key_selector AS selector FROM admin_domain WHERE admin_domain.enable_dkim; ``` I'm continuing wi the rest of the upgrade now. _Edit:_ Update to include missing grant to `dkim`.

kerem commented

2026-02-27 11:18:08 +03:00

Author

Owner

@nexusgoblin commented on GitHub (May 7, 2022):

Oops, missed a step in the above when recreating the view. the opendkim user needs to be able to query the view so GRANT SELECT ON dkim TO opendkim; needs to be run after recreating the dkim view.

I'll edit the comment above for completeness.

@nexusgoblin commented on GitHub (May 7, 2022): Oops, missed a step in the above when recreating the view. the `opendkim` user needs to be able to query the view so `GRANT SELECT ON dkim TO opendkim;` needs to be run after recreating the `dkim` view. I'll edit the comment above for completeness.

kerem commented

2026-02-27 11:18:08 +03:00

Author

Owner

@tonioo commented on GitHub (May 9, 2022):

@nexusgoblin Looks like we need to update the documentation

@tonioo commented on GitHub (May 9, 2022): @nexusgoblin Looks like we need to update the documentation

kerem commented

2026-02-27 11:18:08 +03:00

Author

Owner

@Yanonix commented on GitHub (Oct 5, 2022):

Is it possible to fix directly the migration ? Currently, it breaks current backup/restore/migration script

@Yanonix commented on GitHub (Oct 5, 2022): Is it possible to fix directly the migration ? Currently, it breaks current backup/restore/migration script

kerem commented

2026-02-27 11:18:08 +03:00

Author

Owner

@tonioo commented on GitHub (Oct 5, 2022):

@Yanonix I pushed a fix in the installer repo

@tonioo commented on GitHub (Oct 5, 2022): @Yanonix I pushed a fix in the installer repo