1

Issue Summary

Hi,

I’m trying to deploy Redash with pg8000 instead of psycopg2 (due to licensing issues).

SQLAlchemy supports this: when I define the PostgreSQL host address, I need to specify SQLALCHEMY_DATABASE_URI as postgresql+pg8000://postgres:password@localhost.

When I run manage.py database create_tables for the first time, I get this error:

  File "/PATH/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 846, in visit_table
    _is_metadata_operation=_is_metadata_operation,
  File "/PATH/python2.7/site-packages/sqlalchemy/event/attr.py", line 297, in __call__
    fn(*args, **kw)
  File "/PATH/python2.7/site-packages/sqlalchemy_searchable/__init__.py", line 284, in after_create
    connection.execute(str(clause), **clause.params)
  File "/PATH/python2.7/site-packages/sqlalchemy/engine/base.py", line 974, in execute
    return self._execute_text(object_, multiparams, params)
  File "/PATH/python2.7/site-packages/sqlalchemy/engine/base.py", line 1147, in _execute_text
    parameters,
  File "/PATH/python2.7/site-packages/sqlalchemy/engine/base.py", line 1240, in _execute_context
    e, statement, parameters, cursor, context
  File "/PATH/python2.7/site-packages/sqlalchemy/engine/base.py", line 1458, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/PATH/python2.7/site-packages/sqlalchemy/util/compat.py", line 296, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/PATH/python2.7/site-packages/sqlalchemy/engine/base.py", line 1236, in _execute_context
    cursor, statement, parameters, context
  File "/PATH/python2.7/site-packages/sqlalchemy/engine/default.py", line 536, in do_execute
    cursor.execute(statement, parameters)
  File "/PATH/python2.7/site-packages/pg8000/core.py", line 899, in execute
    self._c.execute(self, operation, args)
  File "/PATH/python2.7/site-packages/pg8000/core.py", line 1890, in execute
    self.handle_messages(cursor)
  File "/PATH/python2.7/site-packages/pg8000/core.py", line 2029, in handle_messages
    raise self.error
sqlalchemy.exc.ProgrammingError: (pg8000.core.ProgrammingError) {u'C': u'42P18', u'F': u'postgres.c', u'M': u'could not determine data type of parameter $1', u'L': u'1400', u'S': u'ERROR', u'R': u'exec_parse_message', u'V': u'ERROR'} [SQL: "CREATE FUNCTION\n                queries_search_vector_update() RETURNS TRIGGER AS $$\n            BEGIN\n                NEW.search_vector = ((setweight(to_tsvector(%s, regexp_replace(coalesce(CAST(NEW.id AS TEXT), ''), '[-@.]', ' ', 'g')), %s) || setweight(to_tsvector(%s, regexp_replace(coalesce(NEW.name, ''), '[-@.]', ' ', 'g')), %s)) || setweight(to_tsvector(%s, regexp_replace(coalesce(NEW.description, ''), '[-@.]', ' ', 'g')), %s)) || setweight(to_tsvector(%s, regexp_replace(coalesce(NEW.query, ''), '[-@.]', ' ', 'g')), %s);\n                RETURN NEW;\n            END\n            $$ LANGUAGE 'plpgsql';\n            "] [parameters: {u'setweight_4': 'D', u'setweight_2': 'A', u'setweight_3': 'C', u'setweight_1': 'B', u'to_tsvector_1': 'pg_catalog.simple', u'to_tsvector_2': 'pg_catalog.simple', u'to_tsvector_3': 'pg_catalog.simple', u'to_tsvector_4': 'pg_catalog.simple'}] (Background on this error at: http://sqlalche.me/e/f405)

I’ve found this https://stackoverflow.com/questions/49240287/pg8000-core-programmingerror-could-not-determine-data-type-of-parameter-2 that explains this error, but the solution is too low-level for my Redash deployment.

I suspect that the definition of the function passed to SQLAlchemy needs to be updated to match pg8000, but I couldn’t find where it is defined.
Could you please assist me with this error?

Does anyone else tried to deploy Redash with pg8000, and succeed in solving this error?

Technical details:

  • Redash Version: 7.0
  • Browser/OS: Linux
  • How did you install Redash: Manually, using docker
2

Running create_tables the second time succeeded.

I’ve created another new PostgreSQL instance, and run a new redash 7.0 with psycopg2 with the create_tables command.

I’ve compared the db schema between the two postgres (pg8000 failed run and after that a successful run(A), and psycopg2 run(B)).

These are the difference in the schema between the different runs:

Exist in B, missing from A

--
-- Name: queries_search_vector_update(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.queries_search_vector_update() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
            BEGIN
                NEW.search_vector = ((setweight(to_tsvector('pg_catalog.simple', regexp_replace(coalesce(CAST(NEW.id AS TEXT), ''), '[-@.]', ' ', 'g')), 'B') || setweight(to_tsvector('pg_catalog.simple', regexp_replace(coalesce(NEW.name, ''), '[-@.]', ' ', 'g')), 'A')) || setweight(to_tsvector('pg_catalog.simple', regexp_replace(coalesce(NEW.description, ''), '[-@.]', ' ', 'g')), 'C')) || setweight(to_tsvector('pg_catalog.simple', regexp_replace(coalesce(NEW.query, ''), '[-@.]', ' ', 'g')), 'D');
                RETURN NEW;
            END
            $$;


ALTER FUNCTION public.queries_search_vector_update() OWNER TO postgres;


--
-- Name: alembic_version alembic_version_pkc; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.alembic_version
    ADD CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num);



--
-- Name: queries queries_search_vector_trigger; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER queries_search_vector_trigger BEFORE INSERT OR UPDATE ON public.queries FOR EACH ROW EXECUTE PROCEDURE public.queries_search_vector_update();

Can I use the pg8000 schema(B)?

Do I need to manualy create the missing items?

What will happen of these will be missing?