Collect_pull_requests : IntegrityError('(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "pull-request-insert-unique"

[collectoss-issue-migrator]

Note

Migrated from augurlabs/augur#3192
Originally opened by @cdolfi on 2025-06-16

---

We are seeing about 50 of these post duplicate script. I will provide examples but if it is not already, pull_request natural key to be based off of pr_src_id might solve this problem, similar to augurlabs/augur#3046

Errors:

IntegrityError('(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "pull-request-insert-unique"\nDETAIL: Key (pr_url)=(https://api.github.com/repos/quarkusio/quarkus-release/pulls/4) already exists.\n')

IntegrityError('(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "pull-request-insert-unique"\nDETAIL: Key (pr_url)=(https://api.github.com/repos/apache/incubator-kie-drools/pulls/1789) already exists.\n')

IntegrityError('(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "pull-request-insert-unique"\nDETAIL: Key (pr_url)=(https://api.github.com/repos/apache/incubator-kie-kogito-runtimes/pulls/2787) already exists.\n')

IntegrityError('(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "pull-request-insert-unique"\nDETAIL: Key (pr_url)=(https://api.github.com/repos/sigstore/sigstore-go/pulls/76) already exists.\n')

IntegrityError('(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "pull-request-insert-unique"\nDETAIL: Key (pr_url)=(https://api.github.com/repos/osbuild/osbuild.github.io/pulls/23) already exists.\n')

IntegrityError('(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "pull-request-insert-unique"\nDETAIL: Key (pr_url)=(https://api.github.com/repos/sigstore/ruby-sigstore/pulls/34) already exists.\n')

IntegrityError('(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "pull-request-insert-unique"\nDETAIL: Key (pr_url)=(https://api.github.com/repos/huggingface/lm-evaluation-harness/pulls/11) already exists.\n')

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @sgoggins on 2025-06-16

---

This issue is certainly a result of ongoing duplicates, and a byproduct of the conservative, "minimize destruction" nature of the the "old augur instance clean up script" for PRs, etc. @cdolfi : Do you think the solution is to take a more aggressive stance with duplicates that remain after the first script? This would involve a simpler approach of simply deleting remaining repositories that do not have repo_src_id populated.

The first script already goes in and tries to populate any NULLs, erroring, of course, if that repo_src_id is already on another repo.

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @cdolfi on 2025-06-16

---

@sgoggins This issue has nothing to do with repo_src_ids from what I can see. From investigation these examples do not have duplicates in our database currently, its that its trying to insert a duplicate because it does not realize it is in there. These all have pr_src_ids in there already, thats why my hypothesis is that if the natural key for prs is changed like it was in augurlabs/augur#3046 it would solve this problem

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @sgoggins on 2025-06-16

---

@cdolfi : I have not observed a duplicate on the PR table in the absence of a duplicate repo (pre-repo_src_id versions of Augur that started collecting before we addressed the common movement of repos) ... Does every repo in your your instance have a repo_src_id?

Try:

select * from augur_data.pull_requests where pr_url = 'https://api.github.com/repos/sigstore/sigstore-go/pulls/76'

I have not seen a case, yet, where checking for duplicates in this way did not in fact show that:

1. That URL is already in the pull_requests table and
2. Not for the repo_id that's throwing the error

If you could check those things, then we can be sure about whether or not this is different from previously observed issues.

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @cdolfi on 2025-06-16

---

@sgoggins yes that is the query I used to test each error to see if there was already more than one PR. For each one including the example you have provided, there is only 1 pull_request result, the problem is that it is trying to insert it again. I just double checked to make sure. It is very reminiscent of the error we hit with pull_request_events a few months ago when all the last_collect_on dates were set to null.

We do have some repo_src_ids that are null, all of them I have individually checked are ones where the repo no longer exist. For these errors all of them have repo_src_ids

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @cdolfi on 2025-06-17

---

https://github.com/chaoss/augur/blob/7f8f04928b00dc69c5b000e6ac288c063f75b3b9/augur/tasks/github/pull_requests/tasks.py#L122 Changing this to be just based off of pr_src_id might fix the issue

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @MoralCode on 2025-10-29

---

this was reported by a contributor today as well:

File "/Users/admin/augur_venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
  cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "pull-request-insert-unique"
DETAIL: Key (pr_url)=(https://api.github.com/repos/z-galaxy/busd/pulls/271) already exists.

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @MoralCode on 2025-10-29

---

Seems like this issue may be related to adding repos via the CLI and/or CSV files

per @sgoggins analysis:

augur/tasks/frontend.py has the method called first. (add_new_github_repos)
augur/application/cli/db.py seems to handle the insertion of repos at the command line (add_repos)

A brief examination of the code, [...] suggests to me that you possibly added repositories using the command line tools? I say that because it "looks like" we actually already have the GH ID in the table, and we are checking for duplicates in that interface.

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @MoralCode on 2025-10-31

---

pull-request-insert-unique as a constraint is defined at https://github.com/chaoss/augur/blob/4221cba76e6ce93e8d4e2f2676f1525bfeacc8ca/augur/application/db/models/augur_data.py#L1696

It looks like the underlying issue is likely to be a duplicate URL issue

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @cdolfi on 2025-10-31

---

augur/augur/tasks/github/pull_requests/tasks.py

Line 122 in 7f8f049

pr_natural_keys = ["repo_id", "pr_src_id"]
Changing this to be just based off of pr_src_id might fix the issue

@MoralCode good catch, I think changing what you pointed out and this to be based on pr_src_id will solve the issue

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @MoralCode on 2025-10-31

---

the two lines above it are already constraints that ensure uniqueness beased on repo ID and PR source ID (why they are duplicated i dont know, i also suspect we have unnecessary indexes but those are subjects for different issues):
https://github.com/chaoss/augur/blob/4221cba76e6ce93e8d4e2f2676f1525bfeacc8ca/augur/application/db/models/augur_data.py#L1693-L1697