search

brazil-data-cube / bdc-catalog

Brazil Data Cube Image Metadata Catalog
https://bdc-catalog.readthedocs.io/en/latest/
GNU General Public License v3.0
4 stars 10 forks source link

Bug in manual set up database - relation "idx_bdc_items_start_date" already exists #196

Closed raphaelrpl closed 1 year ago

raphaelrpl commented 1 year ago

It seems like SQLAlchemy auto index name resolver is duplicating the index name as following:

export SQLALCHEMY_DATABASE_URI="postgresql://postgres:postgres@localhost:5432/bdc"
bdc-db db init
bdc-db db create-namespaces
bdc-db db create-extension-postgis
bdc-db db create-schema
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/bdc_db/cli.py", line 104, in create_schema
    table.create(bind=_db.engine, checkfirst=True)
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 962, in create
    bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3233, in _run_ddl_visitor
    conn._run_ddl_visitor(visitorcallable, element, **kwargs)
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2216, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 524, in traverse_single
    return meth(obj, **kw)
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 911, in visit_table
    self.traverse_single(index, create_ok=True)
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 524, in traverse_single
    return meth(obj, **kw)
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 942, in visit_index
    self.connection.execute(CreateIndex(index))
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1385, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1477, in _execute_ddl
    ret = self._execute_context(
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1948, in _execute_context
    self._handle_dbapi_exception(
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2129, in _handle_dbapi_exception
    util.raise_(
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1905, in _execute_context
    self.dialect.do_execute(
  File "/tmp/bdc-catalog/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateTable) relation "idx_bdc_items_start_date" already exists

We should force a fixed name for bdc_catalog.models.item.Item

Index(None, start_date.desc(), id),

The alembic set up is working:

export SQLALCHEMY_DATABASE_URI="postgresql://postgres:postgres@localhost:5432/bdc"
bdc-db db init
bdc-db db create-namespaces
bdc-db db create-extension-postgis
bdc-db alembic upgrade

https://github.com/brazil-data-cube/bdc-catalog/blob/07649a6f493567880ffbd841c0daeacb97e98d81/bdc_catalog/alembic/f3112636be24_remove_lccs_db.py#L41