Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

error while comparing json fields in schema against PostgreSQL DB with compare_server_default=True option #1119

Closed
casualuser opened this issue Nov 15, 2022 · 4 comments
Closed

error while comparing json fields in schema against PostgreSQL DB with compare_server_default=True option #1119

casualuser opened this issue Nov 15, 2022 · 4 comments
Labels
autogenerate - defaults autogenerate - detection awaiting info waiting for the submitter to give more information

Comments

@casualuser
Copy link

casualuser commented Nov 15, 2022 •
edited
Loading

Describe the bug
Recently I've found that it's required to set implicitly compare_type=True and compare_server_default=True in alembic config to get expected comparing and changes autogeneration.

With compare_server_default=True I started to receive this error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) operator does not exist: json = unknown
LINE 1: SELECT '[]'::json = '[]'
                          ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

[SQL: SELECT '[]'::json = '[]']
(Background on this error at: https://sqlalche.me/e/14/f405)

Expected behavior
I was expecting finally to verify my schema against databases for all stages and be sure all manual tweaks for all fields matches the schema.

To Reproduce
ALTER TABLE public.config ADD epochs json NOT NULL DEFAULT '[]'::json;

from sqlalchemy_json import NestedMutableJson

class Config(Base):
    __tablename__ = 'config'

    id = Column(postgresql.INTEGER, primary_key=True)
    epochs = Column(NestedMutableJson, nullable=False, server_default='[]', default=list())

Error

Traceback (most recent call last):
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/config.py", line 559, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/config.py", line 553, in main
    self.run_cmd(cfg, options)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/config.py", line 530, in run_cmd
    fn(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/command.py", line 212, in revision
    script_directory.run_env()
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/script/base.py", line 490, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 97, in load_python_file
    module = load_module_py(module_id, path)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 783, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "alembic_staging/env.py", line 97, in <module>
    run_migrations_online()
  File "alembic_staging/env.py", line 91, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/runtime/environment.py", line 813, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/runtime/migration.py", line 549, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/command.py", line 188, in retrieve_migrations
    revision_context.run_autogenerate(rev, context)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/api.py", line 462, in run_autogenerate
    self._run_environment(rev, migration_context, True)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/api.py", line 501, in _run_environment
    compare._populate_migration_script(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/compare.py", line 25, in _populate_migration_script
    _produce_net_changes(autogen_context, upgrade_ops)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/compare.py", line 54, in _produce_net_changes
    comparators.dispatch("schema", autogen_context.dialect.name)(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/util/langhelpers.py", line 307, in go
    fn(*arg, **kw)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/compare.py", line 89, in _autogen_for_tables
    _compare_tables(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/compare.py", line 220, in _compare_tables
    with _compare_columns(
  File "/Users/proto/.pyenv/versions/3.8.6/lib/python3.8/contextlib.py", line 113, in __enter__
    return next(self.gen)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/compare.py", line 333, in _compare_columns
    comparators.dispatch("column")(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/util/langhelpers.py", line 307, in go
    fn(*arg, **kw)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/compare.py", line 1118, in _compare_server_default
    is_diff = autogen_context.migration_context._compare_server_default(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/runtime/migration.py", line 676, in _compare_server_default
    return self.impl.compare_server_default(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/ddl/postgresql.py", line 96, in compare_server_default
    return not self.connection.scalar(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1162, in scalar
    return self.execute(object_, *multiparams, **params).scalar()
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1263, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1452, in _execute_clauseelement
    ret = self._execute_context(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1814, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1995, in _handle_dbapi_exception
    util.raise_(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
    self.dialect.do_execute(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) operator does not exist: json = unknown
LINE 1: SELECT '[]'::json = '[]'
                          ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

[SQL: SELECT '[]'::json = '[]']
(Background on this error at: https://sqlalche.me/e/14/f405)

Versions.

  • OS: macOS Ventura 13.0.1
  • Python: 3.8.6
  • Alembic: 1.6.5
  • SQLAlchemy: 1.4.22
  • Database: Aurora PostgreSQL 13.6
  • DBAPI:

Additional context

Have a nice day!
@casualuser casualuser added the requires triage New issue that requires categorization label Nov 15, 2022
@zzzeek
Copy link
Member

zzzeek commented Nov 15, 2022

hi -

can this be expressed using the SQLAlchemy JSON datatype? I don't know what NestedMutableJson is, not sure if that's part of the problem (seems unlikely).

@zzzeek
Copy link
Member

zzzeek commented Nov 15, 2022

also, does this fix?

server_default='[]::json'

would seem simple to achieve

@zzzeek zzzeek added autogenerate - defaults autogenerate - detection awaiting info waiting for the submitter to give more information and removed requires triage New issue that requires categorization labels Nov 15, 2022
@casualuser
Copy link
Author

in some cases with SQLAlchemy JSON datatype session.commit() doesn't update json fields if it was changed
if json field was set initially and written on top of blank value then it was working but not updates for even 1 level json objects
NestedMutableJson allows to trace down changes in json structure

I was expecting that this will be supported by SQLAlchemy by default
it will be nice to have this functionality inside SQLAlchemy itself

with server_default='[]::json' it's a little bit different but still doesn't pass
and server_default='[]'::json doesn't work with message regarding not allowed syntax

please look more into this

Traceback (most recent call last):
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/config.py", line 559, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/config.py", line 553, in main
    self.run_cmd(cfg, options)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/config.py", line 530, in run_cmd
    fn(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/command.py", line 212, in revision
    script_directory.run_env()
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/script/base.py", line 490, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 97, in load_python_file
    module = load_module_py(module_id, path)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 783, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "alembic_staging/env.py", line 97, in <module>
    run_migrations_online()
  File "alembic_staging/env.py", line 91, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/runtime/environment.py", line 813, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/runtime/migration.py", line 549, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/command.py", line 188, in retrieve_migrations
    revision_context.run_autogenerate(rev, context)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/api.py", line 462, in run_autogenerate
    self._run_environment(rev, migration_context, True)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/api.py", line 501, in _run_environment
    compare._populate_migration_script(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/compare.py", line 25, in _populate_migration_script
    _produce_net_changes(autogen_context, upgrade_ops)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/compare.py", line 54, in _produce_net_changes
    comparators.dispatch("schema", autogen_context.dialect.name)(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/util/langhelpers.py", line 307, in go
    fn(*arg, **kw)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/compare.py", line 89, in _autogen_for_tables
    _compare_tables(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/compare.py", line 220, in _compare_tables
    with _compare_columns(
  File "/Users/proto/.pyenv/versions/3.8.6/lib/python3.8/contextlib.py", line 113, in __enter__
    return next(self.gen)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/compare.py", line 333, in _compare_columns
    comparators.dispatch("column")(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/util/langhelpers.py", line 307, in go
    fn(*arg, **kw)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/autogenerate/compare.py", line 1118, in _compare_server_default
    is_diff = autogen_context.migration_context._compare_server_default(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/runtime/migration.py", line 676, in _compare_server_default
    return self.impl.compare_server_default(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/alembic/ddl/postgresql.py", line 96, in compare_server_default
    return not self.connection.scalar(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1162, in scalar
    return self.execute(object_, *multiparams, **params).scalar()
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1263, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1452, in _execute_clauseelement
    ret = self._execute_context(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1814, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1995, in _handle_dbapi_exception
    util.raise_(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
    self.dialect.do_execute(
  File "/Users/proto/.pyenv/versions/3.8.6/envs/tf_x86/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) operator does not exist: json = unknown
LINE 1: SELECT '[]'::json = '[]::json'
                          ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

[SQL: SELECT '[]'::json = '[]::json']
(Background on this error at: https://sqlalche.me/e/14/f405)

@casualuser
Copy link
Author

casualuser commented Nov 16, 2022 •
edited
Loading

@zzzeek
actually I've just tried to play with it a little bit more and have got server_default=text("'{}'::json") and server_default=text("'[]'::json") works!
thx for a tip, will try to apply this on DB now

@casualuser casualuser mentioned this issue Nov 18, 2022
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
autogenerate - defaults autogenerate - detection awaiting info waiting for the submitter to give more information
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@zzzeek @casualuser