FOREIGN KEY constraint failed errors

[tarsius]

Several issues about such errors have been opened in the past. I am now closing most that are still open and open this one to start over. Different issues can lead to this error, both known and unknown.

Known causes

• FOREIGN KEY constraint failed on Github [workaround available] #294 Should be fixable but I haven't gotten around to that yet. If you experience this error on Github, then please first activate the workaround proposed in that issue.

Potential causes

Pagination

If, for example, another user creates a new issue, while we fetch issues, then it could happen that we fetch the same issue twice: We fetch the newest 100 issues, someone adds a new issue, and we fetch issue 201-300, but what was once the 100th most recent edited issue is not the 101th most recently edited issue so we fetch it again.

Currently Forge assumes that doesn't happen. And I believe that with Github's GraphQL API it does not happen. That uses "pointers" that are suppose to prevent this very issue. However Gitlab's REST API most likely does not prevent this.

Forge could check all fetched issues for duplicates before storing them in the database. That would likely be expensive. Or it could use "insert or replace" instead of "insert" (hoping that there are no other causes, where we wouldn't want to do that).

But before implementing any countermeasures, I would first like to be more confident that this really is the cause of most or all such errors (aside from the known one above).

Please use the following debugging code to investigate. The expectation is that some issues would appear more than once in the output. The duplicates might not always be direct neighbors.

diff --git a/closql.el b/closql.el
index 587ae37..d013515 100644
--- a/closql.el
+++ b/closql.el
@@ -327,6 +327,17 @@ (cl-defmethod closql-insert ((db closql-database) obj &optional replace)
           (push (cons slot (closql-oref obj slot)) alist)
           (closql--oset obj slot eieio--unbound))))
     (emacsql-with-transaction db
+      (let* ((key (oref-default obj closql-primary-key))
+             (val (closql--oref obj key)))
+        (message "-- inserting")
+        (message "   %s: %S" key val)
+        (message "   %s/decoded: %S" key
+                 (ignore-errors (base64-decode-string val)))
+        (message "   class: %s" (eieio-object-class obj))
+        (dolist (slot '(number pullreq issue title))
+          (when (and (slot-exists-p obj slot)
+                     (slot-boundp obj slot))
+            (message "   %s: %s" slot (eieio-oref obj slot)))))
       (emacsql db
                (if replace
                    [:insert-or-replace-into $i1 :values $v2]

Past reports emphasized the size of affected repositories. If I am right about the cause, then it isn't so much the size that matters but how much activity is taking place while we are fetching.

[tarsius]

On second thought, I would expect PRIMARY KEY constraint failed in that case.