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

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) type "blob" does not exist #90

Closed
ericdill opened this issue Sep 7, 2020 · 3 comments
Closed

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) type "blob" does not exist #90

ericdill opened this issue Sep 7, 2020 · 3 comments

Comments

@ericdill
Copy link
Contributor

ericdill commented Sep 7, 2020

With the following config, I tried to create a new quetz instance:

config (internal info masked with {var})

[github]
# TODO: Figure out if there are any other auth schemes available?
# Register the app here: https://github.com/settings/applications/new
client_id = "{id}"
client_secret = "{secret}"

[sqlalchemy]
# TODO: See if we can use an aurora postgres backend here
database_url = "postgres+psycopg2://{username}:{password}@{host}:5432/quetz"
#database_url = "sqlite:///./quetz.sqlite"

[session]
# openssl rand -hex 32
secret = "{secret}"
https_only = false

[s3]
bucket_prefix="s3://{bucket}"

And got the following stack trace:

$ quetz create quetz_run --copy-conf ./dev_config.toml
Copying config file from ./dev_config.toml to quetz_run/config.toml
Traceback (most recent call last):
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedObject: type "blob" does not exist
LINE 3:  id BLOB NOT NULL,
            ^


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

Traceback (most recent call last):
  File "/opt/userenvs/quetz/quetz/bin/quetz", line 33, in <module>
    sys.exit(load_entry_point('quetz', 'console_scripts', 'quetz')())
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/typer/main.py", line 214, in __call__
    return get_command(self)(*args, **kwargs)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/typer/main.py", line 497, in wrapper
    return callback(**use_params)  # type: ignore
  File "/opt/quetz/src/quetz/cli.py", line 286, in create
    db = get_session(config.sqlalchemy_database_url)
  File "/opt/quetz/src/quetz/database.py", line 26, in get_session
    Base.metadata.create_all(engine)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 4555, in create_all
    bind._run_visitor(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2097, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1656, in _run_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 145, in traverse_single
    return meth(obj, **kw)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 783, in visit_metadata
    self.traverse_single(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 145, in traverse_single
    return meth(obj, **kw)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 827, in visit_table
    self.connection.execute(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1068, in _execute_ddl
    ret = self._execute_context(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) type "blob" does not exist
LINE 3:  id BLOB NOT NULL,
            ^

[SQL:
CREATE TABLE users (
	id BLOB NOT NULL,
	username VARCHAR,
	PRIMARY KEY (id)
)

]
(Background on this error at: http://sqlalche.me/e/13/f405)

Looks like this might need to be replaced with the LargeBinary type according to this somewhat random github issue and the sqlalchemy docs. I'll give this LargeBinary thing a shot and report back
@ericdill
Copy link
Contributor Author

ericdill commented Sep 8, 2020 •
edited
Loading

Ok, s/BLOB/LargeBinary/ seems to get me a bit further, bit now I get a different error:

$ quetz create quetz_run --copy-conf ./dev_config.toml
Copying config file from ./dev_config.toml to quetz_run/config.toml
Traceback (most recent call last):
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "packages"


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

Traceback (most recent call last):
  File "/opt/userenvs/quetz/quetz/bin/quetz", line 33, in <module>
    sys.exit(load_entry_point('quetz', 'console_scripts', 'quetz')())
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/typer/main.py", line 214, in __call__
    return get_command(self)(*args, **kwargs)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/typer/main.py", line 497, in wrapper
    return callback(**use_params)  # type: ignore
  File "/opt/quetz/src/quetz/cli.py", line 286, in create
    db = get_session(config.sqlalchemy_database_url)
  File "/opt/quetz/src/quetz/database.py", line 26, in get_session
    Base.metadata.create_all(engine)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 4555, in create_all
    bind._run_visitor(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2097, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1656, in _run_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 145, in traverse_single
    return meth(obj, **kw)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 783, in visit_metadata
    self.traverse_single(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 145, in traverse_single
    return meth(obj, **kw)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 827, in visit_table
    self.connection.execute(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1068, in _execute_ddl
    ret = self._execute_context(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/opt/userenvs/quetz/quetz/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "packages"

[SQL:
CREATE TABLE package_members (
	channel_name VARCHAR NOT NULL,
	package_name VARCHAR NOT NULL,
	user_id BYTEA NOT NULL,
	role VARCHAR,
	PRIMARY KEY (channel_name, package_name, user_id),
	FOREIGN KEY(channel_name) REFERENCES channels (name),
	FOREIGN KEY(package_name) REFERENCES packages (name),
	FOREIGN KEY(user_id) REFERENCES users (id)
)

]
(Background on this error at: http://sqlalche.me/e/13/f405)

diff --git a/quetz/db_models.py b/quetz/db_models.py
index b4392f7..78fd100 100644
--- a/quetz/db_models.py
+++ b/quetz/db_models.py
@@ -4,7 +4,7 @@
 import enum

 from sqlalchemy import (
-    BLOB,
+    LargeBinary,
     Boolean,
     Column,
     DateTime,
@@ -21,7 +21,7 @@ from sqlalchemy.orm import relationship

 from .database import Base

-UUID = BLOB(length=16)
+UUID = LargeBinary(length=16)


 class User(Base):

Going to try tearing down the whole stack and rebuilding to see if the database is in some weird state. Will report back tomorrow some time. Error persists after stack rebuild.

I'm using Aurora serverless postgres (an AWS RDS) as the postgres backend which requires postgres 10.7. Are there version constraints on the postgres compatibility of quetz?

Any ideas on what could be causing this?

@wolfv
Copy link
Member

wolfv commented Nov 16, 2020

Hey, I think we have much better postgres compatibility now. @btel do you think we can close this?

@btel
Copy link
Collaborator

btel commented Nov 16, 2020

yes, this should work now

@wolfv wolfv closed this as completed Nov 16, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@btel @wolfv @ericdill