Index "dangerous_but_trivial_idx" is not idempotent

[ruiengana]

Environment

self-hosted (https://develop.sentry.dev/self-hosted/)

Steps to Reproduce

│ Running migrations for default │ │ Operations to perform: │ │ Apply all migrations: auth, contenttypes, feedback, hybridcloud, nodestore, replays, sentry, sessions, sites, social_auth │ │ Running migrations: │ │ File "/usr/local/lib/python3.8/site-packages/sentry/db/postgres/decorators.py", line 77, in inner │ │ raise_the_exception(self.db, e) │ │ File "/usr/local/lib/python3.8/site-packages/sentry/db/postgres/decorators.py", line 75, in inner │ │ return func(self, *args, **kwargs) │ │ File "/usr/local/lib/python3.8/site-packages/sentry/db/postgres/decorators.py", line 18, in inner │ │ return func(self, *args, **kwargs) │ │ File "/usr/local/lib/python3.8/site-packages/sentry/db/postgres/decorators.py", line 95, in inner │ │ raise exc_info0.with_traceback(exc_info[2]) │ │ File "/usr/local/lib/python3.8/site-packages/sentry/db/postgres/decorators.py", line 91, in inner │ │ return func(self, sql, *args, **kwargs) │ │ File "/usr/local/lib/python3.8/site-packages/sentry/db/postgres/base.py", line 87, in execute │ │ return self.cursor.execute(sql) │ │ psycopg2.errors.DuplicateTable: DuplicateTable('relation "dangerous_but_trivial_idx" already exists\n') │ │ SQL: CREATE INDEX CONCURRENTLY "dangerous_but_trivial_idx" ON "sentry_broadcast" ("date_added")

Expected Result

Database migrations should execute successfully when migrations are already applied.

Actual Result

Database migrations failed.

Product Area

Unknown

Link

No response

DSN

No response

Version

21.11.2

[getsantry[bot]]

Assigning to @getsentry/support for routing ⏲️

[azaslavsky]

It seems that you've already applied this migration. Is the table dangerous_but_trivial_idx empty?

[sschamp]

For those having this issue on Sentry Kubernetes while upgrading to a newer Chart Version (for me it was 20.9.3 to 20.12.2). Or for those upgrading from appVersion 23.10.1 to 23.11.2 (or perhaps even simpler 23.10 to 23.11)

And getting the following in the logs of a failed sentry-db-init job:

SQL: CREATE INDEX CONCURRENTLY "sentry_moni_monitor_7ed5ce_idx" ON "sentry_monitorcheckin" ("monitor_id", "status", "date_added")
django.db.utils.ProgrammingError: DuplicateTable('relation "sentry_moni_monitor_7ed5ce_idx" already exists

I had to run the following during Helm Upgrade, but before the sentry-db-init Job is launched, or right after it failed a first time, on the Pod sentry-sentry-postgresql-0:

PGPASSWORD="${POSTGRES_PASSWORD}" psql -U postgres "${POSTGRES_DB}"

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'sentry'
ORDER BY
    tablename,
    indexname;

SELECT 
  indexname, 
  indexdef 
FROM 
  pg_indexes 
WHERE 
  tablename = 'sentry_monitorcheckin';


DROP INDEX sentry_moni_monitor_7ed5ce_idx, sentry_moni_monitor_d75fdf_idx;

If you'd like to script this (somehow), don't forget to add IF EXISTS eg (untested): DROP INDEX IF EXISTS sentry_moni_monitor_7ed5ce_idx, sentry_moni_monitor_d75fdf_idx;

After this the helm upgrade happily continued and the new Sentry version happily applied the migrations again.

But indeed, please add idempotency. A simple CREATE INDEX CONCURRENTLY IF NOT EXISTS should fix this, no?

[getsantry[bot]]

This issue has gone three weeks without activity. In another week, I will close it.

But! If you comment or otherwise update it, I will reset the clock, and if you remove the label Waiting for: Community, I will leave it alone ... forever!

---

"A weed is but an unloved flower." ― Ella Wheeler Wilcox 🥀

[sschamp]

@aldy505 I see this had a label change to: Waiting for: Community But I am wondering which added input is expected from Community

The issue seems clear no? And there is a suggested fix.

[aldy505]

@aldy505 I see this had a label change to: Waiting for: Community

But I am wondering which added input is expected from Community

The issue seems clear no? And there is a suggested fix.

@sschamp Ah sorry for the misunderstanding. I was expecting a response from the issue author, whether the solution works for them. Thanks for bringing that up!