#1789 Unable to add host to channel due to database constraint
Closed: Fixed 2 years ago by tkopecek. Opened 2 years ago by trondham.

When fiddling around with hosts and channels, I removed a host from a channel and later wanted to add that host to the channel again. This failed with the following error message:

[ERROR] koji: Fault: <Fault 1: '<class \'psycopg2.IntegrityError\'>: duplicate key value violates unique constraint "host_channels_host_id_key"\nDETAIL:  Key (host_id, channel_id)=(9, 2) already exists.\n'>

Looking in the database, there was a unique host_id, channel_id pair:

koji=> select * from host_channels where host_id = 9 and channel_id = 2;
host_id | channel_id | create_event | revoke_event | creator_id | revoker_id | active 
---------+------------+--------------+--------------+------------+------------+--------
       9 |          2 |        86704 |        94767 |          1 |          1 | 
(1 row)

After a discussion on IRC (#koji on freenode) it seemed probable that this issue was a remnant from the upgrade a while ago from koji <= 1.15. It was suggested to me that I needed to remove the index "host_channels_host_id_key":

koji=> ALTER TABLE host_channels DROP CONSTRAINT host_channels_host_id_key;
ALTER TABLE

After removing the constraint everything works as expected.

This was on koji 1.18, database is postgresql 11.0.

-trond


@trondham Thanks for reporting this issue.
According to the error msg, index host_channels_host_id_key - (host_id, channel_id) causes the check failed.
It may be created in a very old release, and upgrade sql for 1.15 ->1.16 only drops host_channels_host_id_channel_id_key.
So

ALTER TABLE host_channels DROP CONSTRAINT host_channels_host_id_key;

will resolve this problem.

Metadata Update from @julian8628:
- Custom field Size adjusted to None

2 years ago

Both of these potentially can exist (and should be dropped):

ALTER TABLE host_channels DROP CONSTRAINT IF EXISTS host_channels_host_id_channel_id_key;
ALTER TABLE host_channels DROP CONSTRAINT IF EXISTS host_channels_host_id_key;

We can add latter to next migration to fix potential old installations.

Metadata Update from @tkopecek:
- Custom field Size adjusted to small (was: None)
- Issue priority set to: Low (was: Normal)
- Issue set to the milestone: 1.20
- Issue tagged with: bug, easyfix

2 years ago

Metadata Update from @tkopecek:
- Issue tagged with: no_qe

2 years ago

Login to comment on this ticket.

Metadata
Related Pull Requests
  • #1790 Merged 2 years ago