search

jazzband / django-redshift-backend

Redshift database backend for Django
Apache License 2.0
83 stars 48 forks source link

unsupported jsonb error caused on migration with django-sql-explorer>=5.1 #154

Closed shimizukawa closed 2 months ago

shimizukawa commented 2 months ago

The error occurs because the django-sql-explorer is trying to use the unsupported jsonb data type in django-redshift-backend during migration.

 - django-sql-explorer==5.0.2
 + django-sql-explorer==5.1.1
(venv) @shimizukawa ➜ /workspaces/django-redshift-backend/examples/dj-sql-explorer (152-dj51) $ python manage.py migrate
(0.196) 
            SELECT c.relname,
            CASE WHEN c.relkind IN ('m', 'v') THEN 'v' ELSE 't' END
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('f', 'm', 'p', 'r', 'v')
                AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                AND pg_catalog.pg_table_is_visible(c.oid)
        ; args=None; alias=default
(0.162) SELECT "django_migrations"."id", "django_migrations"."app", "django_migrations"."name", "django_migrations"."applied" FROM "django_migrations"; args=(); alias=default
(0.168) 
            SELECT c.relname,
            CASE WHEN c.relkind IN ('m', 'v') THEN 'v' ELSE 't' END
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('f', 'm', 'p', 'r', 'v')
                AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                AND pg_catalog.pg_table_is_visible(c.oid)
        ; args=None; alias=default
(0.161) SELECT "django_migrations"."id", "django_migrations"."app", "django_migrations"."name", "django_migrations"."applied" FROM "django_migrations"; args=(); alias=default
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, explorer, sessions, testapp
Running migrations:
(0.168) 
            SELECT c.relname,
            CASE WHEN c.relkind IN ('m', 'v') THEN 'v' ELSE 't' END
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('f', 'm', 'p', 'r', 'v')
                AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                AND pg_catalog.pg_table_is_visible(c.oid)
        ; args=None; alias=default
  Applying explorer.0020_databaseconnection_extras_and_more...ALTER TABLE "explorer_databaseconnection" ADD COLUMN "extras" jsonb NULL; (params [])
(0.162) ALTER TABLE "explorer_databaseconnection" ADD COLUMN "extras" jsonb NULL; args=[]; alias=default
Traceback (most recent call last):
  File "/workspaces/django-redshift-backend/django_redshift_backend/_vendor/django40/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedObject: type "jsonb" does not exist

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

Traceback (most recent call last):
  File "/workspaces/django-redshift-backend/examples/dj-sql-explorer/manage.py", line 22, in <module>
    main()
  File "/workspaces/django-redshift-backend/examples/dj-sql-explorer/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/workspaces/django-redshift-backend/examples/dj-sql-explorer/venv/lib/python3.10/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/workspaces/django-redshift-backend/examples/dj-sql-explorer/venv/lib/python3.10/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/workspaces/django-redshift-backend/examples/dj-sql-explorer/venv/lib/python3.10/site-packages/django/core/management/base.py", line 413, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/workspaces/django-redshift-backend/examples/dj-sql-explorer/venv/lib/python3.10/site-packages/django/core/management/base.py", line 459, in execute
    output = self.handle(*args, **options)
  File "/workspaces/django-redshift-backend/examples/dj-sql-explorer/venv/lib/python3.10/site-packages/django/core/management/base.py", line 107, in wrapper
    res = handle_func(*args, **kwargs)
  File "/workspaces/django-redshift-backend/examples/dj-sql-explorer/venv/lib/python3.10/site-packages/django/core/management/commands/migrate.py", line 357, in handle
    post_migrate_state = executor.migrate(
  File "/workspaces/django-redshift-backend/examples/dj-sql-explorer/venv/lib/python3.10/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
  File "/workspaces/django-redshift-backend/examples/dj-sql-explorer/venv/lib/python3.10/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
  File "/workspaces/django-redshift-backend/examples/dj-sql-explorer/venv/lib/python3.10/site-packages/django/db/migrations/executor.py", line 255, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/workspaces/django-redshift-backend/examples/dj-sql-explorer/venv/lib/python3.10/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/workspaces/django-redshift-backend/examples/dj-sql-explorer/venv/lib/python3.10/site-packages/django/db/migrations/operations/fields.py", line 110, in database_forwards
    schema_editor.add_field(
  File "/workspaces/django-redshift-backend/django_redshift_backend/base.py", line 392, in add_field
    self.execute(sql, params)
  File "/workspaces/django-redshift-backend/django_redshift_backend/_vendor/django40/db/backends/base/schema.py", line 192, in execute
    cursor.execute(sql, params)
  File "/workspaces/django-redshift-backend/django_redshift_backend/_vendor/django40/db/backends/utils.py", line 102, in execute
    return super().execute(sql, params)
  File "/workspaces/django-redshift-backend/django_redshift_backend/_vendor/django40/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/workspaces/django-redshift-backend/django_redshift_backend/_vendor/django40/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/workspaces/django-redshift-backend/django_redshift_backend/_vendor/django40/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/workspaces/django-redshift-backend/examples/dj-sql-explorer/venv/lib/python3.10/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/workspaces/django-redshift-backend/django_redshift_backend/_vendor/django40/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: type "jsonb" does not exist

This output indicates an error that occurred during the attempt to update the Django SQL Explorer and perform database migrations. Below are the key points explained:

1. Version Update:

 - django-sql-explorer==5.0.2
 + django-sql-explorer==5.1.1

The Django SQL Explorer has been updated from version 5.0.2 to 5.1.1.

2. Migration Execution:

The command python manage.py migrate is executed to attempt to update the database.

3. Specific Migration:

Applying explorer.0020_databaseconnection_extras_and_more...

This migration is trying to add a new column named extras to the explorer_databaseconnection table.

4. Error Occurrence:

psycopg2.errors.UndefinedObject: type "jsonb" does not exist

This error indicates that the database does not recognize the jsonb data type.

5. Specific SQL Command:

ALTER TABLE "explorer_databaseconnection" ADD COLUMN "extras" jsonb NULL;

This SQL command is failing.

6. Detailed Error:

django.db.utils.ProgrammingError: type "jsonb" does not exist

This indicates that the database you are using (likely Redshift) does not support the jsonb data type.

The main cause of this error is that the new version of Django SQL Explorer is attempting to use the jsonb type, but the database you are using (Redshift) does not support this type. Redshift differs from standard PostgreSQL in that it does not support some advanced data types (in this case, jsonb).

Possible Solutions:

  1. Continue using the older version of Django SQL Explorer.
  2. Customize this migration to use a data type compatible with Redshift (e.g., text).
  3. Report the compatibility issue to the developers of Django SQL Explorer and suggest alternatives.

The choice of which solution to pursue will depend on the requirements and constraints of your project.

shimizukawa commented 2 months ago

It was commented in issue #12 that Redshift does not support the json type.

Official documents also mention JSON type in:

For this reason, I will make a fix specifying has_native_json_field = False.