Delete a row using the http-api leads to server error if table includes not nullable fileds

jh-RLI commented 8 months ago

Description of the issue

I have a table like this one:

table_schema = {
    "columns": [
        # NOTE: first column should be numerical column named `id` .
        # Use `bigserial` if you want the database to create the re
        {"name": "id", "data_type": "bigserial", "primary_key": True},
        {"name": "name", "data_type": "varchar(18)", "is_nullable": False},
        {"name": "is_active", "data_type": "boolean"},
        {"name": "capacity_mw", "data_type": "float"},
        {"name": "installation_datetime_utc", "data_type": "datetime"},
    ]
}

If i try to delete a row by id this error is raised:

api.error.APIError: NotNullViolation('null value in column "name" of relation "_test_data_upload_delete" violates not-null 
constraint\nDETAIL:  Failing row contains (997236, null, test, 2024-01-24 14:05:19.724128, f, f, update, f, 1, null, null, null, null).\n')

looking at my table schema the name column cant be null but the error indicated that the last 4 parameters of the insert statements are null but at least the first one should not be null because it is the name column that is not nullable.

This is related to the versioning system we have in place that keeps track of any changes to the data and stored all delete / edited values in the so called _ schemas on the oedb. In my case the table was created in model_draft schema and the error happend in _model_draft schema.

This is the insert statement that is generated by the oep: INSERT INTO _model_draft._test_data_upload_delete (_message, _user, _type, id) VALUES (%(_message_m0)s, %(_user_m0)s, %(_type_m0)s, %(id_m0)s)

This is the api enpoint im sending the request to: Internal Server Error: /api/v0/schema/model_draft/tables/test_data_upload/rows/1

Steps to Reproduce

Create a table using my table schema
Insert data
Delete a row using the http-api rows/id endpoint with a http delete request

Ideas of solution

Make sure that non-deletable fields are registered when creating the insert statement.

Can you find the reason why all fields in the table are registered as null even though they contain data? With the exception of the id field, which is filled in correctly, but this information is also part of the http request.

Context and Environment

Complete stacktrace:

Internal Server Error: /api/v0/schema/model_draft/tables/test_data_upload/rows/1
Traceback (most recent call last):
  File "/home/jh/github/oeplatform/api/actions.py", line 1384, in _execute_sqla
    cursor.execute(str(compiled), params)
psycopg2.errors.NotNullViolation: null value in column "name" of relation "_test_data_upload_delete" violates not-null constraint
DETAIL:  Failing row contains (997235, null, test, 2024-01-24 13:35:55.809135, f, f, update, f, 1, null, null, null, null).

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/jh/github/oeplatform/env/lib/python3.10/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/home/jh/github/oeplatform/env/lib/python3.10/site-packages/django/core/handlers/base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/home/jh/github/oeplatform/env/lib/python3.10/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
    return view_func(*args, **kwargs)
  File "/home/jh/github/oeplatform/env/lib/python3.10/site-packages/django/views/generic/base.py", line 70, in view
    return self.dispatch(request, *args, **kwargs)
  File "/home/jh/github/oeplatform/env/lib/python3.10/site-packages/rest_framework/views.py", line 509, in dispatch
    response = self.handle_exception(exc)
  File "/home/jh/github/oeplatform/env/lib/python3.10/site-packages/rest_framework/views.py", line 469, in handle_exception
    self.raise_uncaught_exception(exc)
  File "/home/jh/github/oeplatform/env/lib/python3.10/site-packages/rest_framework/views.py", line 480, in raise_uncaught_exception
    raise exc
  File "/home/jh/github/oeplatform/env/lib/python3.10/site-packages/rest_framework/views.py", line 506, in dispatch
    response = handler(request, *args, **kwargs)
  File "/home/jh/github/oeplatform/api/views.py", line 192, in wrapper
    return f(caller, request, *args, **kwargs)
  File "/home/jh/github/oeplatform/api/views.py", line 827, in delete
    result = self.__delete_rows(request, schema, table, row_id)
  File "/home/jh/github/oeplatform/api/views.py", line 89, in wrapper
    result = f(*args, **kwargs)
  File "/home/jh/github/oeplatform/api/views.py", line 857, in __delete_rows
    return actions.data_delete(query, context)
  File "/home/jh/github/oeplatform/api/actions.py", line 1202, in data_delete
    result = __change_rows(request, context, target_table, setter, ["id"])
  File "/home/jh/github/oeplatform/api/actions.py", line 1182, in __change_rows
    _execute_sqla(query, cursor)
  File "/home/jh/github/oeplatform/api/actions.py", line 1386, in _execute_sqla
    raise APIError(repr(e))
api.error.APIError: NotNullViolation('null value in column "name" of relation "_test_data_upload_delete" violates not-null constraint\nDETAIL:  Failing row contains (997235, null, test, 2024-01-24 13:35:55.809135, f, f, update, f, 1, null, null, null, null).\n')
[24/Jan/2024 13:35:55] "DELETE /api/v0/schema/model_draft/tables/test_data_upload/rows/1 HTTP/1.1" 500 153457
['id', '_user', '_message', '_type']
INSERT INTO _model_draft._test_data_upload_delete (_message, _user, _type, id) VALUES (%(_message_m0)s, %(_user_m0)s, %(_type_m0)s, %(id_m0)s)
Internal Server Error: /api/v0/schema/model_draft/tables/test_data_upload/rows/1
Traceback (most recent call last):
  File "/home/jh/github/oeplatform/api/actions.py", line 1384, in _execute_sqla
    cursor.execute(str(compiled), params)
psycopg2.errors.NotNullViolation: null value in column "name" of relation "_test_data_upload_delete" violates not-null constraint
DETAIL:  Failing row contains (997236, null, test, 2024-01-24 14:05:19.724128, f, f, update, f, 1, null, null, null, null).

Workflow checklist

[ ] I am aware of the workflow in CONTRIBUTING.md

wingechr commented 7 months ago

I am currently working on a branch to fix it.

Here is the problem:

all changes (delete, insert, update) are first written to meta tables (__delete, ....) and later applied to the actual table
the meta tables are created with the same table schema as the original table:
```
CREATE TABLE "{meta_schema}"."{edit_table}" ' '(LIKE "{schema}"."{table}"
```
for delete,only the id is inserted, hence the error

wingechr commented 7 months ago

solution 1:

create delete meta table only with id column. we must then also delete all other columns from existing meta tables;

solution 2:

change not null constraint on the delete meta table (also migrate all existing

solution 3:

???

OpenEnergyPlatform / oeplatform