Duplicate key value violates unique constraint when syncing multiple Ubuntu distributions

[quba42]

Version

foreman-3.3.0-1.el8
rubygem-pulp_deb_client-2.18.0-1.el8.noarch

Describe the bug
Originally reported here: https://community.theforeman.org/t/duplicate-key-value-violates-unique-constrain-when-synchronizing-product-published-deb-file/29872

Error message:

duplicate key value violates unique constraint “core_publishedartifact_publication_id_relative__97f785f4_uniq”
DETAIL: Key (publication_id, relative_path)=(6790107c-dc2f-4375-90d0-0656462a497d, pool/all/f/flashrom/libflashrom-dev_1.2-5_amd64.deb) already exists.

It looks like the package pool/all/f/flashrom/libflashrom-dev_1.2-5_amd64.deb exists in multiple distributions and there is a race condition to create the one DB entry during sync.

To Reproduce
Synchronize some combination of focal forcal-updates focal-backports focal-security in a single sync.

Expected behavior
The duplicate package is created once, and is included in both sets of metadata without error.

Workaround
It is possible to work around this issue, by syncing the affected APT distributions into a single Pulp repository each. (Rather than multiple APT distributions into a single Pulp repository).

Possibly related

• duplicate key value violates unique constraint "core_artifact_sha256_key" #410
• Duplicate key violates Unique-Constraint core_artifact_sha256_key when syncing debian bullseye-backports #421

[mdellweg]

This seems to generate a conflict on PublishedArtifact. Can you confirm this is a sync error? I think it might be a publication time error.

[quba42]

I have not yet tried to reproduce this, since I expect I will need to deploy a "large disk size test environment" to do so.

But yes, looking again, this error is probably on the publish. I had not considered this possibility since I sometimes stupidly forget that Katello sync != Pulp sync.

[mdellweg]

With autopublish a sync in pulp might also get there. I was just trying to narrow it down.

[adam-kosseck]

I have the same kind of error after I created a single repository for Bionic, Focal and Jammy (as having 3 separate repos for each distro was bugging me).

duplicate key value violates unique constraint "core_publishedartifact_publication_id_relative__97f785f4_uniq"
DETAIL:   Key (publication_id, relative_path)=(376e8f4a-6d77-4c17-9ab4-710bf3363582, pool/all/a/adium-theme-ubuntu_0.3.4-0ubuntu4_all.deb) already exists.

When I tried to create a single Jammy repository (jammy, jammy-updates, jammy-security: main restricted universe multiverse), it seemed to work ok.

Looking at it in Dynflow, the run tab shows everything as green except "Actions::Pulp3::Repository::CreatePublication" and "Actions::Pulp3::Repository::SavePublication".

This is with Foreman 3.3.0-1, Katello-4.5.0-1, python39-pulp-deb-2.18.0-3.

EDIT:
For those that don't want to try syncing a whole OS repo, I have also replicated this for Puppet.
Repo: http://apt.puppet.com
Release/Distro: focal
Components: puppet7 puppet-tools
Architectures: amd64

The above is quite a small repo (compared to an OS), and if you try and have puppet7 and puppet-tools in the same Repo in foreman it errors with:

duplicate key value violates unique constraint "core_publishedartifact_publication_id_relative__97f785f4_uniq"
DETAIL:  Key (publication_id, relative_path)=(a1975917-9779-4169-ad55-a16d4c403cb7, pool/all/p/puppet-bolt/puppet-bolt_3.4.0-1focal_amd64.deb) already exists.