[bug]: duplicate key value violates unique constraint "uid_chunk_id_idx"

[ga-it]

I am not sure if this is a bug or merely the way you are now avoiding duplicate embeddings.

It occurs on nearly every embedding run.

Message: 'Error adding documents to vectordb:'
Arguments: (IntegrityError('(psycopg.errors.UniqueViolation) duplicate key value violates unique constraint "uid_chunk_id_idx"

As can be seen from the following, the insert repeats multiple times for the same users.

I'm guessing - could this be due to multiple groups a user is member of having access to the same file?

Could a solution be to deduplicate UIDs for each file before the insert?

Arguments: (PendingRollbackError('This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (psycopg.errors.UniqueViolation) duplicate key value violates unique constraint "uid_chunk_id_idx"\n
DETAIL: Key (uid, source_id)=(4E3AE065-E8A4-4F30-ADCF-F8A4A67B6F3C, files__default: 7999141) already exists.\n[SQL: INSERT INTO access_list (uid, source_id) SELECT p0::VARCHAR, p1::VARCHAR FROM (VALUES (%(uid__0)s::VARCHAR, %(source_id__0)s::VARCHAR, 0), (%(uid__1)s::VARCHAR, %(source_id__1)s::VARCHAR, 1), (%(uid__2)s::VARCHAR, %(source_id__2)s::VARCHAR, 2), (%(ui ... 760 characters truncated ... imp_sen(p0, p1, sen_counter) ORDER BY sen_counter RETURNING access_list.id, access_list.id AS id__1]\n[parameters: {
'source_id__0': 'files__default: 7999141', 'uid__0': '0AE6C4F1-0254-406D-A94B-2BBAD5045A40',
'source_id__1': 'files__default: 7999141', 'uid__1': '473035F0-3947-434F-8F0E-A993E8712759',
'source_id__2': 'files__default: 7999141', 'uid__2': '4E3AE065-E8A4-4F30-ADCF-F8A4A67B6F3C',
'source_id__3': 'files__default: 7999141', 'uid__3': '4E3AE065-E8A4-4F30-ADCF-F8A4A67B6F3C',
'source_id__4': 'files__default: 7999141', 'uid__4': 'BFF7E4AF-2815-4F2D-9ADE-D39F2CB4CE4B',
'source_id__5': 'files__default: 7999141', 'uid__5': '94F1C9EA-8596-49E3-9E46-EF5C9D43E5B0',
'source_id__6': 'files__default: 7999141', 'uid__6': 'F69B0766-C729-421C-891F-F54448270B88',
'source_id__7': 'files__default: 7999141', 'uid__7': 'F69B0766-C729-421C-891F-F54448270B88',
'source_id__8': 'files__default: 7999141', 'uid__8': 'F69B0766-C729-421C-891F-F54448270B88',
'source_id__9': 'files__default: 7999141', 'uid__9': 'BFF7E4AF-2815-4F2D-9ADE-D39F2CB4CE4B',
'source_id__10': 'files__default: 7999141', 'uid__10': 'BFF7E4AF-2815-4F2D-9ADE-D39F2CB4CE4B',
'source_id__11': 'files__default: 7999141', 'uid__11': '8AECD92E-4670-4169-82C0-FB5BAB7C7D95',
'source_id__12': 'files__default: 7999141', 'uid__12': '5FFE2E1D-FA35-417F-9940-C200C1EEFA14',
'source_id__13': 'files__default: 7999141', 'uid__13': '306361B8-4B97-48A2-B703-53484C6CB60F',
'source_id__14': 'files__default: 7999141', 'uid__14': 'A7E93E83-91F6-45F8-9EB3-1F39D3AC5C3B',
'source_id__15': 'files__default: 7999141', 'uid__15': 'A53CAE5E-FC4B-45B4-85CD-9CC44CD4AE26',
'source_id__16': 'files__default: 7999141', 'uid__16': 'BEC630D0-ED32-475B-8AF7-80707404F643'}]
\n(Background on this error at: https://sqlalche.me/e/20/gkpj)'),)

[Nextcloud Hub 9] (30.0.1)
Nextcloud Assistant Context Chat 4.0.1
context_chat_backend 4.0.1
Remote AppAPI server

[kyteinsky]

Could a solution be to deduplicate UIDs for each file before the insert?

the access update function takes care to ignore duplicate entries. I'm not entirely sure under what circumstances this error occurs.
Would you mind posting the complete logs for this request? (strip the personal info as you see fit)

[ga-it]

Here is a single document query with the error.

NC server is on 192.168.0.73
Context_chat_backend is app_api manual install on 192.168.0.86

Same setup as old context_chat_backend 3.x

TRACE: 192.168.0.73:36906 - HTTP connection made
TRACE: 192.168.0.73:36906 - ASGI [6] Started scope={'type': 'http', 'asgi': {'version': '3.0', 'spec_version': '2.3'}, 'http_version': '1.1', 'server': ('172.18.0.2', 10034), 'client': ('192.168.0.73', 36906), 'scheme': 'http', 'method': 'PUT', 'root_path': '', 'path': '/loadSources', 'raw_path': b'/loadSources', 'query_string': b'', 'headers': '<...>', 'state': {}}
TRACE: 192.168.0.73:36906 - ASGI [6] Receive {'type': 'http.request', 'body': '<50680 bytes>', 'more_body': True}
TRACE: 192.168.0.73:36906 - ASGI [6] Receive {'type': 'http.request', 'body': '<125976 bytes>', 'more_body': True}
TRACE: 192.168.0.73:36906 - ASGI [6] Receive {'type': 'http.request', 'body': '<2896 bytes>', 'more_body': True}
TRACE: 192.168.0.73:36906 - ASGI [6] Receive {'type': 'http.request', 'body': '<12819 bytes>', 'more_body': False}
Embedding sources:
files__default: 7997026 (240616_Infobase_File.pdf)
Try 0 failed in exception
check_sources took 6.39ms
Filtered sources: ['files__default: 7997026']
processing source: files__default: 7997026
decoded non empty source: files__default: 7997026
Converted sources to documents
--- Logging error ---
Traceback (most recent call last):
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/engine/base.py", line 2118, in _exec_insertmany_context
dialect.do_execute(
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/engine/default.py", line 941, in do_execute
cursor.execute(statement, parameters)
File "/usr/local/lib/python3.11/dist-packages/psycopg/cursor.py", line 97, in execute
raise ex.with_traceback(None)
psycopg.errors.UniqueViolation: duplicate key value violates unique constraint "uid_chunk_id_idx"
DETAIL: Key (uid, source_id)=(USERID, files__default: 7997026) already exists.

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

Traceback (most recent call last):
File "/app/context_chat_backend/vectordb/pgvector.py", line 142, in add_indocuments
session.commit()
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/orm/session.py", line 2028, in commit
trans.commit(_to_root=True)
File "", line 2, in commit
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
ret_value = fn(self, *arg, **kw)
^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/orm/session.py", line 1313, in commit
self._prepare_impl()
File "", line 2, in _prepare_impl
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
ret_value = fn(self, *arg, **kw)
^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/orm/session.py", line 1288, in _prepare_impl
self.session.flush()
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/orm/session.py", line 4352, in flush
self._flush(objects)
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/orm/session.py", line 4487, in _flush
with util.safe_reraise():
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/util/langhelpers.py", line 146, in exit
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/orm/session.py", line 4448, in _flush
flush_context.execute()
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/orm/unitofwork.py", line 466, in execute
rec.execute(self)
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/orm/unitofwork.py", line 642, in execute
util.preloaded.orm_persistence.save_obj(
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
_emit_insert_statements(
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/orm/persistence.py", line 1143, in _emit_insert_statements
result = connection.execute(
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/engine/base.py", line 1418, in execute
return meth(
^^^^^
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/engine/base.py", line 1844, in _execute_context
return self._exec_insertmany_context(dialect, context)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/engine/base.py", line 2126, in _exec_insertmany_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/engine/base.py", line 2355, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/engine/base.py", line 2118, in _exec_insertmany_context
dialect.do_execute(
File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/engine/default.py", line 941, in do_execute
cursor.execute(statement, parameters)
File "/usr/local/lib/python3.11/dist-packages/psycopg/cursor.py", line 97, in execute
raise ex.with_traceback(None)
sqlalchemy.exc.IntegrityError: (psycopg.errors.UniqueViolation) duplicate key value violates unique constraint "uid_chunk_id_idx"
DETAIL: Key (uid, source_id)=(USERIDX, files__default: 7997026) already exists.
[SQL: INSERT INTO access_list (uid, source_id) SELECT p0::VARCHAR, p1::VARCHAR FROM (VALUES (%(uid__0)s::VARCHAR, %(source_id__0)s::VARCHAR, 0), (%(uid__1)s::VARCHAR, %(source_id__1)s::VARCHAR, 1), (%(uid__2)s::VARCHAR, %(source_id__2)s::VARCHAR, 2), (%(ui ... 760 characters truncated ... imp_sen(p0, p1, sen_counter) ORDER BY sen_counter RETURNING access_list.id, access_list.id AS id__1]
[parameters: {'uid__0': '0AE6C4F1-0254-406D-A94B-2BBAD5045A40', 'source_id__0': 'files__default: 7997026', 'uid__1': 'USERID1', 'source_id__1': 'files__default: 7997026', 'uid__2': 'USERIDX', 'source_id__2': 'files__default: 7997026', 'uid__3': 'USERIDX', 'source_id__3': 'files__default: 7997026', 'uid__4': 'USERID4', 'source_id__4': 'files__default: 7997026', 'uid__5': 'USERID5', 'source_id__5': 'files__default: 7997026', 'uid__6': 'USERID6', 'source_id__6': 'files__default: 7997026', 'uid__7': 'USERID7', 'source_id__7': 'files__default: 7997026', 'uid__8': 'USERID8', 'source_id__8': 'files__default: 7997026', 'uid__9': 'USERID9', 'source_id__9': 'files__default: 7997026', 'uid__10': 'USERID10', 'source_id__10': 'files__default: 7997026', 'uid__11': 'USERID11', 'source_id__11': 'files__default: 7997026', 'uid__12': 'USERID12', 'source_id__12': 'files__default: 7997026', 'uid__13': 'USERID13', 'source_id__13': 'files__default: 7997026', 'uid__14': 'USERID14', 'source_id__14': 'files__default: 7997026', 'uid__15': 'USERID15', 'source_id__15': 'files__default: 7997026', 'uid__16': 'USERID16', 'source_id__16': 'files__default: 7997026'}]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/usr/lib/python3.11/logging/init.py", line 1110, in emit
msg = self.format(record)
^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3.11/logging/init.py", line 953, in format
return fmt.format(record)
^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3.11/logging/init.py", line 687, in format
record.message = record.getMessage()
^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3.11/logging/init.py", line 377, in getMessage
msg = msg % self.args
~~~~^~~~~~~~~~~
TypeError: not all arguments converted during string formatting
Call stack:
File "/usr/lib/python3.11/threading.py", line 1002, in _bootstrap
self._bootstrap_inner()
File "/usr/lib/python3.11/threading.py", line 1045, in _bootstrap_inner
self.run()
File "/usr/local/lib/python3.11/dist-packages/anyio/_backends/_asyncio.py", line 1005, in run
result = context.run(func, *args)
File "/app/context_chat_backend/controller.py", line 163, in wrapper
return func(*args, **kwargs)
File "/app/context_chat_backend/controller.py", line 307, in _
added_sources = exec_in_proc(target=embed_sources, args=(vectordb_loader, app.extra['CONFIG'], sources))
File "/app/context_chat_backend/utils.py", line 111, in exec_in_proc
p.start()
File "/usr/lib/python3.11/multiprocessing/process.py", line 121, in start
self._popen = self._Popen(self)
File "/usr/lib/python3.11/multiprocessing/context.py", line 224, in _Popen
return _default_context.get_context().Process._Popen(process_obj)
File "/usr/lib/python3.11/multiprocessing/context.py", line 281, in _Popen
return Popen(process_obj)
File "/usr/lib/python3.11/multiprocessing/popen_fork.py", line 19, in init
self._launch(process_obj)
File "/usr/lib/python3.11/multiprocessing/popen_fork.py", line 71, in _launch
code = process_obj._bootstrap(parent_sentinel=child_r)
File "/usr/lib/python3.11/multiprocessing/process.py", line 314, in _bootstrap
self.run()
File "/usr/lib/python3.11/multiprocessing/process.py", line 108, in run
self._target(*self._args, **self._kwargs)
File "/app/context_chat_backend/utils.py", line 94, in exception_wrap
resconn.send({ 'value': fun(*args, **kwargs), 'error': None })
File "/app/context_chat_backend/chain/ingest/injest.py", line 163, in embed_sources
Added documents to vectordb
return _process_sources(vectordb, config, sources_filtered)
File "/app/context_chat_backend/chain/ingest/injest.py", line 139, in _process_sources
added_sources = vectordb.add_indocuments(indocuments)
File "/app/context_chat_backend/vectordb/pgvector.py", line 146, in add_indocuments
log_error('Error adding documents to vectordb:', e)
Message: 'Error adding documents to vectordb:'
Arguments: (IntegrityError('(psycopg.errors.UniqueViolation) duplicate key value violates unique constraint "uid_chunk_id_idx"\nDETAIL: Key (uid, source_id)=(USERID, files__default: 7997026) already exists.'),)
Count of newly loaded sources: 0 / 1
Sources: []
TRACE: 192.168.0.73:36906 - ASGI [6] Send {'type': 'http.response.start', 'status': 200, 'headers': '<...>'}
INFO: 192.168.0.73:36906 - "PUT /loadSources HTTP/1.1" 200 OK
TRACE: 192.168.0.73:36906 - ASGI [6] Send {'type': 'http.response.body', 'body': '<21 bytes>'}
TRACE: 192.168.0.73:36906 - ASGI [6] Completed
TRACE: 192.168.0.73:36906 - ASGI [7] Started scope={'type': 'http', 'asgi': {'version': '3.0', 'spec_version': '2.3'}, 'http_version': '1.1', 'server': ('172.18.0.2', 10034), 'client': ('192.168.0.73', 36906), 'scheme': 'http', 'method': 'POST', 'root_path': '', 'path': '/query', 'raw_path': b'/query', 'query_string': b'', 'headers': '<...>', 'state': {}}
TRACE: 192.168.0.73:36906 - ASGI [7] Receive {'type': 'http.request', 'body': '<208 bytes>', 'more_body': False}
query: userId='USERID' query='QUery?' useContext=True scopeType=<ScopeType.SOURCE: 'source'> scopeList=['files__default: 7997026'] ctxLimit=20
doc_search took 470.65ms
original traceback: Traceback (most recent call last):
File "/app/context_chat_backend/utils.py", line 94, in exception_wrap
resconn.send({ 'value': fun(*args, **kwargs), 'error': None })
^^^^^^^^^^^^^^^^^^^^
File "/app/context_chat_backend/chain/one_shot.py", line 65, in process_context_query
raise ContextException('No documents retrieved, please index a few documents first')
context_chat_backend.chain.types.ContextException: No documents retrieved, please index a few documents first

--- Logging error ---
Traceback (most recent call last):
File "/usr/local/lib/python3.11/dist-packages/starlette/_exception_handler.py", line 42, in wrapped_app
await app(scope, receive, sender)
File "/usr/local/lib/python3.11/dist-packages/starlette/routing.py", line 73, in app
response = await f(request)
^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/dist-packages/fastapi/routing.py", line 301, in app
raw_response = await run_endpoint_function(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/dist-packages/fastapi/routing.py", line 214, in run_endpoint_function
return await run_in_threadpool(dependant.call, **values)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/dist-packages/starlette/concurrency.py", line 39, in run_in_threadpool
return await anyio.to_thread.run_sync(func, *args)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/dist-packages/anyio/to_thread.py", line 56, in run_sync
return await get_async_backend().run_sync_in_worker_thread(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/dist-packages/anyio/_backends/_asyncio.py", line 2505, in run_sync_in_worker_thread
return await future
^^^^^^^^^^^^
File "/usr/local/lib/python3.11/dist-packages/anyio/_backends/_asyncio.py", line 1005, in run
result = context.run(func, *args)
^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/context_chat_backend/controller.py", line 163, in wrapper
return func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "/app/context_chat_backend/controller.py", line 390, in _
return execute_query(query)
^^^^^^^^^^^^^^^^^^^^
File "/app/context_chat_backend/controller.py", line 379, in execute_query
return exec_in_proc(target=target, args=args)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/context_chat_backend/utils.py", line 117, in exec_in_proc
raise result['error']
context_chat_backend.chain.types.ContextException: No documents retrieved, please index a few documents first

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/usr/lib/python3.11/logging/init.py", line 1110, in emit
msg = self.format(record)
^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3.11/logging/init.py", line 953, in format
return fmt.format(record)
^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3.11/logging/init.py", line 687, in format
record.message = record.getMessage()
^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3.11/logging/init.py", line 377, in getMessage
msg = msg % self.args
~~~~^~~~~~~~~~~
TypeError: not all arguments converted during string formatting
Call stack:
File "/app/./main.py", line 19, in
uvicorn.run(
File "/usr/local/lib/python3.11/dist-packages/uvicorn/main.py", line 579, in run
server.run()
File "/usr/local/lib/python3.11/dist-packages/uvicorn/server.py", line 66, in run
return asyncio.run(self.serve(sockets=sockets))
File "/usr/lib/python3.11/asyncio/runners.py", line 190, in run
return runner.run(main)
File "/usr/lib/python3.11/asyncio/runners.py", line 118, in run
return self._loop.run_until_complete(task)
File "/usr/local/lib/python3.11/dist-packages/uvicorn/protocols/http/h11_impl.py", line 403, in run_asgi
result = await app( # type: ignore[func-returns-value]
File "/usr/local/lib/python3.11/dist-packages/uvicorn/middleware/proxy_headers.py", line 60, in call
return await self.app(scope, receive, send)
File "/usr/local/lib/python3.11/dist-packages/uvicorn/middleware/message_logger.py", line 80, in call
await self.app(scope, inner_receive, inner_send)
File "/usr/local/lib/python3.11/dist-packages/fastapi/applications.py", line 1054, in call
await super().call(scope, receive, send)
File "/usr/local/lib/python3.11/dist-packages/starlette/applications.py", line 113, in call
await self.middleware_stack(scope, receive, send)
File "/usr/local/lib/python3.11/dist-packages/starlette/middleware/errors.py", line 165, in call
await self.app(scope, receive, _send)
File "/app/context_chat_backend/ocs_utils.py", line 79, in call
await self.app(scope, receive, send)
File "/usr/local/lib/python3.11/dist-packages/starlette/middleware/exceptions.py", line 62, in call
await wrap_app_handling_exceptions(self.app, conn)(scope, receive, send)
File "/usr/local/lib/python3.11/dist-packages/starlette/_exception_handler.py", line 42, in wrapped_app
await app(scope, receive, sender)
File "/usr/local/lib/python3.11/dist-packages/starlette/routing.py", line 715, in call
await self.middleware_stack(scope, receive, send)
File "/usr/local/lib/python3.11/dist-packages/starlette/routing.py", line 735, in app
await route.handle(scope, receive, send)
File "/usr/local/lib/python3.11/dist-packages/starlette/routing.py", line 288, in handle
await self.app(scope, receive, send)
File "/usr/local/lib/python3.11/dist-packages/starlette/routing.py", line 76, in app
await wrap_app_handling_exceptions(app, request)(scope, receive, send)
File "/usr/local/lib/python3.11/dist-packages/starlette/_exception_handler.py", line 59, in wrapped_app
response = await handler(conn, exc)
File "/app/context_chat_backend/controller.py", line 124, in _
log_error(f'Context Retrieval Error: {request.url.path}:', exc)
Message: 'Context Retrieval Error: /query:'
Arguments: (ContextException('No documents retrieved, please index a few documents first'),)
TRACE: 192.168.0.73:36906 - ASGI [7] Send {'type': 'http.response.start', 'status': 400, 'headers': '<...>'}
INFO: 192.168.0.73:36906 - "POST /query HTTP/1.1" 400 Bad Request
TRACE: 192.168.0.73:36906 - ASGI [7] Send {'type': 'http.response.body', 'body': '<70 bytes>'}
TRACE: 192.168.0.73:36906 - ASGI [7] Completed
TRACE: 192.168.0.73:36906 - HTTP connection lost