PostgreSQL error when trying to delete a conversation

Hi, I’m getting a PostgreSQL error when I try to delete some conversations. I’m using Rasa 2.8.0 and Rasa X 0.42.0 installed with docker-compose.

rasa_error

I thought it might be because these conversations were using an old version of the chatbot with intents that no longer exist, but I recently encountered this problem with a tester’s conversation on the current version and I can’t figure out what’s causing the problem, or what the “message_log_to_tag_mapping” table is for.

I would also like to know if there is a way to access this db from outside the server, I did some tests with psycopg2 in a python script but I get a “could not translate host name to address: Name or service not known” error using the server domain as host with port 5432 port and the credentials found in the .env and docker-compose.yml files.

Here are the logs obtained when trying to delete one of these conversations:

rasa-x_1           | Traceback (most recent call last):
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
rasa-x_1           |     self.dialect.do_execute(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 717, in do_execute
rasa-x_1           |     cursor.execute(statement, parameters)
rasa-x_1           | psycopg2.errors.ForeignKeyViolation: update or delete on table "message_log" violates foreign key constraint "message_log_to_tag_mapping_message_log_id_fkey" on table "message_log_to_tag_mapping"
rasa-x_1           | DETAIL:  Key (id)=(391) is still referenced from table "message_log_to_tag_mapping".
rasa-x_1           |
rasa-x_1           |
rasa-x_1           | The above exception was the direct cause of the following exception:
rasa-x_1           |
rasa-x_1           | Traceback (most recent call last):
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sanic/app.py", line 973, in handle_request
rasa-x_1           |     response = await response
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/rasax/community/api/decorators.py", line 216, in decorated_function
rasa-x_1           |     return await await_and_return_response(args, kwargs, request)
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/rasax/community/api/decorators.py", line 146, in await_and_return_response
rasa-x_1           |     response = await response
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/rasax/community/api/blueprints/stack.py", line 260, in delete_conversation_by_id
rasa-x_1           |     EventService.from_request(request).delete_conversation_by_id(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/rasax/community/services/event_service.py", line 1642, in delete_conversation_by_id
rasa-x_1           |     self.commit()
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/rasax/community/database/service.py", line 24, in commit
rasa-x_1           |     self.session.commit()
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 1428, in commit
rasa-x_1           |     self._transaction.commit(_to_root=self.future)
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 829, in commit
rasa-x_1           |     self._prepare_impl()
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
rasa-x_1           |     self.session.flush()
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 3298, in flush
rasa-x_1           |     self._flush(objects)
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 3438, in _flush
rasa-x_1           |     transaction.rollback(_capture_exception=True)
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
rasa-x_1           |     compat.raise_(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 207, in raise_
rasa-x_1           |     raise exception
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 3398, in _flush
rasa-x_1           |     flush_context.execute()
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
rasa-x_1           |     rec.execute(self)
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/unitofwork.py", line 667, in execute
rasa-x_1           |     util.preloaded.orm_persistence.delete_obj(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/persistence.py", line 340, in delete_obj
rasa-x_1           |     _emit_delete_statements(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/persistence.py", line 1440, in _emit_delete_statements
rasa-x_1           |     c = connection._execute_20(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1583, in _execute_20
rasa-x_1           |     return meth(self, args_10style, kwargs_10style, execution_options)
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
rasa-x_1           |     return connection._execute_clauseelement(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1452, in _execute_clauseelement
rasa-x_1           |     ret = self._execute_context(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1814, in _execute_context
rasa-x_1           |     self._handle_dbapi_exception(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1995, in _handle_dbapi_exception
rasa-x_1           |     util.raise_(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 207, in raise_
rasa-x_1           |     raise exception
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
rasa-x_1           |     self.dialect.do_execute(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 717, in do_execute
rasa-x_1           |     cursor.execute(statement, parameters)
rasa-x_1           | sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) update or delete on table "message_log" violates foreign key constraint "message_log_to_tag_mapping_message_log_id_fkey" on table "message_log_to_tag_mapping"
rasa-x_1           | DETAIL:  Key (id)=(391) is still referenced from table "message_log_to_tag_mapping".
rasa-x_1           |
rasa-x_1           | [SQL: DELETE FROM conversation WHERE conversation.sender_id = %(sender_id)s]
rasa-x_1           | [parameters: {'sender_id': 'fe52208348d94350903b9a5ad758a896'}]
rasa-x_1           | (Background on this error at: https://sqlalche.me/e/14/gkpj)
rasa-x_1           | (psycopg2.errors.ForeignKeyViolation) update or delete on table "message_log" violates foreign key constraint "message_log_to_tag_mapping_message_log_id_fkey" on table "message_log_to_tag_mapping"
rasa-x_1           | DETAIL:  Key (id)=(391) is still referenced from table "message_log_to_tag_mapping".
rasa-x_1           |
rasa-x_1           | [SQL: DELETE FROM conversation WHERE conversation.sender_id = %(sender_id)s]
rasa-x_1           | [parameters: {'sender_id': 'fe52208348d94350903b9a5ad758a896'}]
rasa-x_1           | (Background on this error at: https://sqlalche.me/e/14/gkpj)

Any help is appreciated :smiley:

Thank you!

@Flo are you able to see the data in database?

No I can’t access the database at all and I don’t know how to try directly on the server (I’m new to docker and I tried from outside the server first), I only manipulate the data using the Rasa X UI or API.

And when trying with psycopg2 :

conn = psycopg2.connect(
    host="http://mydomain.com",
    port=5432,
    database="rasa",
    user="admin",
    password="XXXXX" 
)

I get

could not translate host name "http://mydomain.com" to address: Name or service not known

And looking at the conversations affected by the deletion error, I don’t see what they have in common or how they differ from the others that I can delete…

@Flo can you mention the server IP in host?

@nik202 So yes, using the IP the server seems to be found (it had to be host='mydomain.com' and not host='http://mydomain.com') but now I have a new error message:

Connection refused
Is the server running on host "mydomain.com" (<ServerIP>) and accepting TCP/IP connections on port 5432?

And the $ sudo lsof -i -P -n | grep LISTEN command shows that there is nothing listening on port 5432, but the DB_PORT variable found in docker-compose.yml is 5432 as shown here: Customize Your Deployment.

I’m also wondering if the DB_HOST variable (default value is “db”) should be used somewhere or if if I should link the container port 5432 to a server port (which I don’t know how to do either …)

By adding the following entry to docker-compose.override.yml:

rasa-x:
  ports:
    - "5432:5432"

The $ sudo lsof -i -P -n | grep LISTEN command shows that my server is now listening on port 5432:

docker-pr 3833863            root    4u  IPv4 102922123      0t0  TCP *:5432 (LISTEN)
docker-pr 3833880            root    4u  IPv6 102923063      0t0  TCP *:5432 (LISTEN)

But I still can’t connect to the db, I get the same error as in my previous post, which prevents me from fixing the initial problem of deleting some conversations.