search

OpenEnergyPlatform / oeplatform

Repository for the code of the Open Energy Platform (OEP) website. The OEP provides an interface to the Open Energy Family
http://openenergyplatform.org/
GNU Affero General Public License v3.0
61 stars 19 forks source link

Schemas in the whitelist must exist for manage.py mirror operations to succeed #1070

Open pajot opened 1 year ago

pajot commented 1 year ago

When mirroring schemas that have been imported into the database using postgresql tools (e.g. psql), the schema must be entered into the whitelist. (This operation is necessary to make imported data visible on the front end.)

If the whitelist contains schemas that do not exist in the database, python manage.py mirror will throw cascading exceptions:

Traceback (most recent call last):
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/models/query.py", line 581, in get_or_create
    return self.get(**kwargs), False
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/models/query.py", line 435, in get
    raise self.model.DoesNotExist(
dataedit.models.DoesNotExist: Schema matching query does not exist.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "dataedit_schema_pkey"
DETAIL:  Key (id)=(2) already exists.

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

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/home/sfbosch/oep-website/dataedit/management/commands/mirror.py", line 30, in handle
    s, _ = Schema.objects.get_or_create(name=schema)
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/models/query.py", line 588, in get_or_create
    return self.create(**params), True
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/models/query.py", line 453, in create
    obj.save(force_insert=True, using=self.db)
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/models/base.py", line 739, in save
    self.save_base(using=using, force_insert=force_insert,
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/models/base.py", line 776, in save_base
    updated = self._save_table(
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/models/base.py", line 881, in _save_table
    results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/models/base.py", line 919, in _do_insert
    return manager._insert(
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/models/query.py", line 1270, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1416, in execute_sql
    cursor.execute(sql, params)
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/sfbosch/oep-website/venv-mondb/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.IntegrityError: duplicate key value violates unique constraint "dataedit_schema_pkey"
DETAIL:  Key (id)=(2) already exists.

Instead, the operation should fail gracefully. For example, non-existent schemas could be ignored, or the operation could fail with a message indicating which schemas need to be checked.

wingechr commented 1 year ago

I can not reproduce this. On my test system, I added a new name into schema_whitelist without creating it in postgres. mirror command runs fine.

I think I need a better example under what conditions the problem occurs.