Duplicate key value violates unique constraint when syncing multiple Ubuntu distributions

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

• https://github.com/pulp/pulp_deb/issues/410
• https://github.com/pulp/pulp_deb/issues/421

Start by reproducing the failure by synchronizing focal, focal-updates, focal-backports, and focal-security in one Pulp repository. Trace the synchronization path that creates published artifacts and compare the behavior with pulp_deb issues 410 and 421; done means the duplicate package is created once and included in both metadata sets without a unique-constraint error.