could not create unique index "idx_xs_item_cache_lookup"

[Daniel Drucker]

Upon upgrading to 1.10.0 (from 1.9.2.2), I get dozens of messages like:

xnat-db-1         | 2026-05-07 14:45:42.420 UTC [43] ERROR:  could not create unique index "idx_xs_item_cache_lookup"

xnat-db-1         | 2026-05-07 14:45:42.420 UTC [43] DETAIL:  Key (elementname, ids)=(xdat:user, 178) is duplicated.

xnat-db-1         | 2026-05-07 14:45:42.420 UTC [43] STATEMENT:  CREATE UNIQUE INDEX IF NOT EXISTS idx_xs_item_cache_lookup ON xs_item_cache(elementName, ids)

xnat-db-1         | 2026-05-07 14:45:42.434 UTC [42] ERROR:  could not create unique index "idx_xs_item_cache_lookup"

However, XNAT seems to be running fine. Should I be concerned?

Daniel Drucker

[Rick Herrick]

Hey Daniel,

It looks like the entry for xdat:user with unique ID 178 was cached twice. I'm not sure how that happened because XNAT shouldn't have created a new cache entry if that object was already in the cache. Maybe there was some kind of concurrency issue or something? Anyway, one of the purposes of that index is precisely to enforce uniqueness with those two attributes (the other is performance: SELECT queries on that table should be much more performant with that index), so that shouldn't happen again.

XNAT should run fine without that index, since it's really additive to what's there and nothing relies on the index existing. Like I said, the index is primarily intended to improve performance. That said, the error is ugly and it would be nice to have the performance improvement, so it's best fixed. The good thing that this is pretty easy! You have two options.

The nuclear option is to clear the xs_item_cache table. This can be done with a simple query: DELETE FROM xs_item_cache. This is totally fine to do: it's a cache so anything in it can just be retrieved from the database again. Shut XNAT down, run the query, re-start, everything should go swimmingly, including the index creation statement working fine.

The more nuanced option is to only remove rows with duplicate values for the desired unique index. You can find the IDs for those rows with a query like this:

WITH element_counts AS (SELECT DISTINCT elementname, ids, count(*) AS element_count
                        FROM xs_item_cache
                        GROUP BY elementname, ids),
     duplicate_elements AS (SELECT DISTINCT elementname, ids
                            FROM element_counts
                            WHERE element_count > 1),
     duplicate_ids AS (SELECT elementname, ids, id, max(id) OVER (PARTITION BY elementname, ids) AS max_id
                       FROM xs_item_cache
                       WHERE (elementname, ids) IN (SELECT elementname, ids FROM duplicate_elements))
SELECT id
FROM duplicate_ids
WHERE id < max_id
ORDER BY id;

You could make the query do the work for you as well:

WITH element_counts AS (SELECT DISTINCT elementname, ids, count(*) AS element_count
                        FROM xs_item_cache
                        GROUP BY elementname, ids),
     duplicate_elements AS (SELECT DISTINCT elementname, ids
                            FROM element_counts
                            WHERE element_count > 1),
     duplicate_ids AS (SELECT elementname, ids, id, max(id) OVER (PARTITION BY elementname, ids) AS max_id
                       FROM xs_item_cache
                       WHERE (elementname, ids) IN (SELECT elementname, ids FROM duplicate_elements)),
     removable_ids AS (SELECT id
                       FROM duplicate_ids
                       WHERE id < max_id)
DELETE
FROM xs_item_cache
WHERE id IN (SELECT id FROM removable_ids);

To be honest though I just went through the exercise to write that query because it was interesting. I'd probably just clear the table myself 😀

Rick Herrick 

Senior Software Developer

ri...@xnatworks.io

https://xnatworks.io | Find us on LinkedIn! 

--
You received this message because you are subscribed to the Google Groups "xnat_discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xnat_discussi...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/xnat_discussion/27ea8b7f-c292-4d6e-b46f-164f12a93921n%40googlegroups.com.

[Daniel Drucker]

That worked, thank you!

Daniel