Haystack: Duplicate document bulk index error with Faiss document store

[x0rzkov]

Hi,

Hope you are all well !

I think that a parameter is missing for the faiss document store while writing documents like you made for elasticesarch.

:param update_existing_documents: Whether to update any existing documents with the same ID when adding
documents. When set as True, any document with an existing ID gets updated.
If set to False, an error is raised if the document ID of the document being
added already exists.

Because, I tried to load some documents and I have the following error preventing me to load the full dataset into the faiss store.

Error: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: (MySQLdb._exceptions.IntegrityError) (1062, "Duplicate entry '52713153-stackoverflow.com' for key 'PRIMARY'")
[SQL: INSERT INTO document (id, text, `index`, vector_id) VALUES (%s, %s, %s, %s)]
[parameters: (('52713148-stackoverflow.com', b'Question: \nBehaviorSubject next method not updating changes for another component \n <p>Hello I have developed user service, working of that user s ... (63 characters truncated) ... lationship with them. if i am changing data from peoplecompoent it is not detected by inboxcomponent. Here i am posnting code for \n\n Answer: \nBody', 'document', None), ('52713149-stackoverflow.com', b"Question: \nposition of floating button changes with number of elements in flatlist in react native \n <p>I have a flatlist below which I need to pl ... (80 characters truncated) ... bottom edge of the screen; instead, I want the flatlist window to be a fixed size and scrollable to accommodate more rows. Below t\n\n Answer: \nBody", 'document', None), ('52713150-stackoverflow.com', b"Question: \nRails 6, Devise and Active Storage \n <p>I'm using Devise and Active Storage in my rails 6 application. Currently using <code>before_act ... (27 characters truncated) ... de> disables all images for non users, which is great, except I want to allow images with the <code>record_type</code> 'News' in m\n\n Answer: \nBody", 'document', None), ('52713152-stackoverflow.com', b'Question: \nfish shell shows error in piping - new error \n "<p><a href=""https://i.stack.imgur.com/wyBue.jpg"" rel=""nofollow noreferrer""><img src ... (37 characters truncated) ... g"" alt=""enter image description here"" /></a>I am getting multiple errors from shell init scripts which more or less seem the sa\n\n Answer: \nBody', 'document', None), ('52713153-stackoverflow.com', b'Question: \nApplying CSS to Datatables that has NEXT Page \n "<p>I have this Script that works and does what I need, but it will only apply to the f ... (289 characters truncated) ... our $.each loop each time the page changes or (I think better) use <a href=""https://datatables.net/reference/option/createdRow"" rel=""nofollow nore', 'document', None), ('52713154-stackoverflow.com', b'Question: \nNumber of rows in a datetime index sampled by day \n "<p>I am trying to count the number of rows of the index in Data frame X and then g ... (42 characters truncated) ... as Data frame Y showing 11 rows on 7/10/2020 and 12 rows in 7/11/2020</p> <p><img src=""https://i.stack.imgur.com/rLX5o.jpg"" alt=\n\n Answer: \nBody', 'document', None), ('52713157-stackoverflow.com', b'Question: \nIs it possible to turn a gameObject (using DontDestroyOnLoad()) into a child of a gameObject from another scene? \n <p>I am making a 2d  ... (127 characters truncated) ... ite Renderer (in the game scene) is enabled and all other sprites are disabled... so I made an empty object containing the skins (\n\n Answer: \nBody', 'document', None), ('52713159-stackoverflow.com', b'Question: \nRobotframework for loop continue with next test \n <p>I have below code:</p> <pre><code>*** Settings *** Library     OperatingSystem Lib ... (291 characters truncated) ... ramework.org/robotframework/latest/RobotFrameworkUserGuide.html#templates-with-for-loops"" rel=""nofollow noreferrer"">Templates with for loops</a>, ', 'document', None)  ... displaying 10 of 500 total bound parameter sets ...  ('52714181-stackoverflow.com', b'Question: \nLogin & registration redirects - don\'t redirect login when on checkout page in WooCommerce \n "<p>My code works as expected but it shou ... (84 characters truncated) ... code>is_checkout()</code> to try and detect this but it is not working as expected.</p> <pre class=""lang-php prettyprint-override\n\n Answer: \nBody', 'document', None), ('52714183-stackoverflow.com', b'Question: \nHow to activate autocomplete option of tomtom in my html code? \n "<p>I have the following simple code for using tomtom which works:</p> ... (61 characters truncated) ... \'use-all-space\'&gt;     &lt;head&gt;     &lt;meta http-equiv=\'X-UA-Compatible\' content=\'IE=Edge\' /&gt;     &lt;meta charset=\n\n Answer: \nBody', 'document', None))]
(Background on this error at: http://sqlalche.me/e/13/gkpj) (Background on this error at: http://sqlalche.me/e/13/7s2a)
document_store.write_documents=1557
Bulk not indexed

Cheers,
X

[x0rzkov]

@tholor Any ideas how to fix it ?

[tholor]

Hey @x0rzkov,

Sorry, it's been crazily busy days on our side. It's correct that FAISSDocumentStore does not support update_existing_documents yet. What is your intended workflow here? Do you really want to update a subset of your existing documents?

If you are just trying to load documents and face this error, it probably means that you still have documents in SQL from a previous run while the FAISS index does not know about them. You can call document_store.delete_all_documents() or manually drop the tables in SQL.

[x0rzkov]

Hi @tholor ,

Hope you are all well !

I am converting the xml stackoverflow dump, extracting pairs of question with their validated answers. Each time, I insert those pairs in the db and update the delta of pairs that have changed.

Would be also convenient to have an ignore flag if duplicate meanwhile you implement the update_existing_documents feature ^^

Is there a quick workaround ?

Cheers,
X

[lalitpagaria]

@x0rzkov Two dirty hack you can do -
1) If you want to update docs on duplicate then, change session.add( to session.merge( at this place https://github.com/deepset-ai/haystack/blob/master/haystack/document_store/sql.py#L135
2) If you want to skip on duplicate then, wrap this line under try/catch. https://github.com/deepset-ai/haystack/blob/master/haystack/document_store/sql.py#L135

For more information please refer https://stackoverflow.com/questions/6611563/sqlalchemy-on-duplicate-key-update#:~:text=ON%20DUPLICATE%20KEY%20UPDATE%20functionality,question%20is%20a%20primary%20key.

[lalitpagaria]

I forgot to mention that in either hack you have to clear faiss_index before calling update_embeddings function. You can do as follows -

faiss_document_store.faiss_index.reset()

[x0rzkov]

@lalitpagaria

Hope you are all well !

Thanks, I ll have a try this morning ^^

[lalitpagaria]

I have raised PR #584 to add this support

[x0rzkov]

Hi @lalitpagaria

Thanks for this !

You have an error in your PyTest:
https://github.com/deepset-ai/haystack/pull/584/checks?check_run_id=1396123070

[lalitpagaria]

Yes, I am trying to fix this. I can't locally test it as my docker engine is not working after macOS upgrade :(

[lalitpagaria]

@x0rzkov It is fixed now. Can you please give it try - https://github.com/lalitpagaria/haystack/tree/add_update_existing_documents

[x0rzkov]

Hi @tholor and @lalitpagaria

I rebuilt my docker container with your fork and it works fine.

Also, I noticed that if the database encoding is utf8bm4, instead of utf-8, the varchar length should be 768 and not 1000.
https://github.com/lalitpagaria/haystack/blob/add_update_existing_documents/haystack/document_store/sql.py#L44

And btw, it is incredibly slow to insert 10M entries, anything we can do about that ?

I do not know, like using LOAD DATA INTO FILE command, for MySQL instances

[x0rzkov]

Hi guys,

Just a quick question, is it normal that the vector_id column is NULL ?

Thanks for any inputs or insights about that.

Cheers,
X

[tholor]

Yes this is normal. If you just call write_documents() and pass the plain documents (without embeddings) there, only their text and meta data will be added to SQL. The vectors can then later be initialized by calling doc_store.update_embeddings(retriever). We decoupled those two operations as update embeddings really depends on the retriever model you choose and is a very heavy operation (usually run on a GPU).

However, if your workflow requires it, you could als compute the embeddings yourself, add them to the dicts and pass everything to write_documents.

Hope this helps!

[x0rzkov]

@tholor

Thanks for your reply :-)

I ll tell you later how it goes for embeddings update in 28:00 hours.

Btw, here is the dataset if you wanna play with it for demo project:
https://www.kaggle.com/lucmichalski/stackoverflow-stackexchange-dataset-10m-qa

[tanaysoni]

Hi @x0rzkov, this should now be resolved with #584. Please update here if you face any issues.