Description of problem: It is possible to get into the state when showing list of activation keys produces ISE. This issue is about schema flaw on PostgreSQL DB backend. Note the issue was discovered by pstudeni and investigated by jdobes. Version-Release number of selected component (if applicable): satellite-schema-5.7.0.27-1.el6sat.noarch (but others are affected as well) How reproducible: We were not able to reproduce it besides one case Steps to Reproduce: 1. Unreliable reproducer: a. Delete lots (hundreds) of activation keys via spacecmd: # spacecmd -s ... -u ... -p ... activationkey_list \ | grep "^1-" \ | xargs spacecmd -s ... -u ... -p ... activationkey_delete -y b. During it is deleting, refresh Systems -> Activation keys multiple times 2. Reliable, but not real-world reproducer: a. To see the issue, lets break directly the DB: rhnschema=# INSERT INTO rhnset (user_id, label, element) VALUES (1, 'activation_keys', 123); rhnschema=# INSERT INTO rhnset (user_id, label, element) VALUES (1, 'activation_keys', 123); rhnschema=# INSERT INTO rhnset (user_id, label, element) VALUES (1, 'activation_keys', 123); b. Show duplicate rows: rhnschema=# select user_id, label, element, count(*) from rhnset group by user_id, label, element having count(*) > 1; user_id | label | element | count ---------+-----------------+---------+------- 1 | activation_keys | 123 | 3 (1 row) c. Check ISE on Systems -> Activation Keys d. Fix it: rhnschema=# DELETE FROM rhnset WHERE user_id = 1 AND label = 'activation_keys' AND element = 123; DELETE 3 Actual results: Problem is that rhnset have unique constraint from multiple columns and some of them ("element_two" and "element_three") does not have "not null" configured: rhnschema=# \d rhnset Table "public.rhnset" Column | Type | Modifiers ---------------+-----------------------+----------- user_id | numeric | not null label | character varying(32) | not null element | numeric | not null element_two | numeric | element_three | numeric | Indexes: "rhn_set_user_label_elem_unq" UNIQUE CONSTRAINT, btree (user_id, label, element, element_two, element_three) Check constraints: "vn_rhnset_label" CHECK (label::text <> ''::text) Foreign-key constraints: "rhn_set_user_fk" FOREIGN KEY (user_id) REFERENCES web_contact(id) ON DELETE CASCADE Expected results: It should not be possible to insert duplicate rows into the table.
WebUI ISE reported as bug 1434339
This can be fixed by setting multiple unique indexes on PostgreSQL, each index will be limited by where clauses on nullable columns. Another and bigger problem is to upgrade and clean existing rhnset table which can already contain duplicate records.
first piece of the fix: f7aa18b9078f1816372e8026412498eae0bce059
upgrade on postgresql: e6dd4b91befcaba99d70ec9b450142066b49d007
fixing upgrade on PostgreSQL 8.4, spacewalk master: 444685cea65c39574812765805e1e386885fb4cf
Verified with spacewalk-schema-2.5.1-48 and satellite-schema-5.8.0.31-1 with ManagedDB. It is no more possible to have duplicate rows in rhnschema: spacesatschema=# INSERT INTO rhnset (user_id, label, element) VALUES (1, 'activation_keys', 123); INSERT 0 1 spacesatschema=# INSERT INTO rhnset (user_id, label, element) VALUES (1, 'activation_keys', 123); ERROR: duplicate key value violates unique constraint "rhn_set_user_label_elem_unq" DETAIL: Key (user_id, label, element)=(1, activation_keys, 123) already exists.