Encountered postgre error when upgrading from v3 to v4

Issue Summary

We have a V2 redash running on our internal server for several years, recently we deployed a brand new self-hosted v9-beta on AWS, so we need to migrate the existing data (user, dashboards, queries etc) from the DB of V2 to V9-beta.

Considering that the new redash and meta data are seperated on different hosts, so the most reasonable way is to first upgrade the existing database. I started with customized setup and use official release image in order to apply migration script as instructed in document
docker-compose run --rm server manage db upgrade

What I have done so far:

  1. Run the setup script with the REDASH_DATABASE_URL replaced by ours.
  2. Update the image reference with any released V3 version in docker-compose.
  3. Stop the running service and run migration: docker-compose run --rm server manage db upgrade
  4. DB schema changed without failure according to the log, and verified the new columns in V3 have been added.
  5. Update the image reference with any released V4 version in docker-compose.
  6. run migration: docker-compose run --rm server manage db upgrade

The failure happened right after step 6 and it throws an error like this:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) string is too long for tsvector (2432402 bytes, max 1048575 bytes)

Searched for a while but it seems that nobody ever had this issue, get stuck right now…
Any insights would be much appreciated, thanks!

Technical details:

Full logs:

$ docker-compose run --rm server manage db upgrade
Starting redash_redis_1 ... done
[2021-01-08 09:01:41,880][PID:1][INFO][root] Generating grammar tables from /usr/lib/python2.7/lib2to3/Grammar.txt
[2021-01-08 09:01:41,901][PID:1][INFO][root] Generating grammar tables from /usr/lib/python2.7/lib2to3/PatternGrammar.txt
[2021-01-08 09:01:43,392][PID:1][INFO][alembic.runtime.migration] Context impl PostgresqlImpl.
[2021-01-08 09:01:43,393][PID:1][INFO][alembic.runtime.migration] Will assume transactional DDL.
[2021-01-08 09:01:43,400][PID:1][INFO][alembic.runtime.migration] Running upgrade d1eae8b9893e -> 7671dca4e604, empty message
[2021-01-08 09:01:43,405][PID:1][INFO][alembic.runtime.migration] Running upgrade 7671dca4e604 -> 5ec5c84ba61e, Add Query.search_vector field for full text search.
Traceback (most recent call last):
  File "/app/manage.py", line 9, in <module>
    manager()
  File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 716, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/flask/cli.py", line 345, in main
    return AppGroup.main(self, *args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 696, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 1060, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 1060, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 889, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 534, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/flask/cli.py", line 229, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 534, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/flask_migrate/cli.py", line 132, in upgrade
    _upgrade(directory, revision, sql, tag, x_arg)
  File "/usr/local/lib/python2.7/dist-packages/flask_migrate/__init__.py", line 239, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/usr/local/lib/python2.7/dist-packages/alembic/command.py", line 254, in upgrade
    script.run_env()
  File "/usr/local/lib/python2.7/dist-packages/alembic/script/base.py", line 427, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/local/lib/python2.7/dist-packages/alembic/util/pyfiles.py", line 81, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python2.7/dist-packages/alembic/util/compat.py", line 135, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "migrations/env.py", line 87, in <module>
    run_migrations_online()
  File "migrations/env.py", line 80, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python2.7/dist-packages/alembic/runtime/environment.py", line 836, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python2.7/dist-packages/alembic/runtime/migration.py", line 330, in run_migrations
    step.migration_fn(**kw)
  File "/app/migrations/versions/5ec5c84ba61e_.py", line 27, in upgrade
    ['name', 'description', 'query'])
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy_searchable/__init__.py", line 496, in sync_trigger
    conn.execute(update_sql)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 469, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) string is too long for tsvector (2432402 bytes, max 1048575 bytes)
CONTEXT:  PL/pgSQL function queries_search_vector_update() line 3 at assignment
 [SQL: 'UPDATE queries SET name=name']
  • Redash Version: redash/redash:4.0.2.b4720 is throwing the error
  • Browser/OS: Linux Ubuntu
  • How did you install Redash: Official released image installed through customized setup script

I don’t have a solution for you but this information might help you. This was an interesting one to track down. Version 4 added a new search column based on the Postgres TSVector function. Here’s the commit. It looks like some object in your existing metadatabase is simply too large for TSVector to parse. Here’s the migration that’s being attempted. You can read about the tsvector type here.

Do you have an especially large query in your instance? The warning says that it’s trying to set a search vector on a blob that is 2.43mb in size which would be an enormous query (around 1500 pages of SQL or a 2-inch thick book).

1 Like

@k4s1m Thanks man! We did found that there is one query greater than 1M…