No unique index on site_settings name column

Dev
1

When updating today, this migration failed:

PG::CardinalityViolation: ERROR:  more than one row returned by a subquery used as an expression
: INSERT INTO site_settings(name, data_type, value, created_at, updated_at)
SELECT 'lounge_welcome_topic_id', 3, id, created_at, updated_at
FROM topics
WHERE title = 'Welcome to the Lounge' AND NOT EXISTS(
    SELECT 1
    FROM site_settings
    WHERE name = 'lounge_welcome_topic_id'
  ) AND category_id = (
  SELECT value::INT
  FROM site_settings
  WHERE name = 'lounge_category_id'
)
ORDER BY created_at
LIMIT 1

So I run the two subqueries, and… :face_with_raised_eyebrow:

SELECT *         
  FROM site_settings
  WHERE name = 'lounge_category_id';
 id |        name        | data_type | value |         created_at         |         updated_at         
----+--------------------+-----------+-------+----------------------------+----------------------------
 60 | lounge_category_id |         3 | 14    | 2014-02-17 23:45:49.295261 | 2014-02-17 23:45:49.295261
  2 | lounge_category_id |         3 | 2     | 2014-02-17 23:45:39.227589 | 2014-02-18 02:31:03.144083
(2 rows)

That second row points to the actual lounge category (id=2), there’s no category with id=14.

I have no idea how we got duplicate lounge_category_id settings way back in 2014, and if this is the result of a really legacy bug, or something weird that happened on our instance back then.

This is the only duplicated site setting I can see, but it highlights there’s no unique index on the name column, which I imagine there should be.

I’ve deleted the wrong row, and the migration ran fine.

8 Likes
2

I think this might be relevant to @gerhard

2 Likes
3

Definitely relevant to @gerhard but strongly agree we should have a unique index on name on the site_settings table (and throw away non unique rows in the migration)

We will make that happen.

5 Likes
5

Done in https://github.com/discourse/discourse/commit/467c17da53a9fe3c1f83c8c0321fd822ca5833fd and https://github.com/discourse/discourse/commit/cb70a328f51a25cd278df8f71d88f45df007740a

8 Likes