[4.2.0 Beta 1/2] 20230803082451 AddUniqueIndexOnPreviewCardsStatuses Migration Fails, table already exists #2387
Labels
Comments
|
That is weird. Have you ever manually added an index? Can you open a PostgreSQL shell and type |
|
Sorry for the late reply. bash-5.1# psql -d mastodon_production -U mastodon Using drop index as you requested doesn't seem to have helped as I get the same error when I run the migration again. |
|
Can you drop the index again, and run the migrations again? Please share the first error message from the migration, I suspect you ran it multiple times and it resulted in different error messages! |
|
Attempt #2: Should I be running these with Mastodon's docker instances stopped, perhaps? I believe they were stopped when I first hit this issue. This second run was with it still running. Also, to answer one of your original questions, no, I did not make any manual changes to the database at all as I am unfamiliar with postgres to the point where I had to look up how to log into the database. |
|
Oh, in SQL, type |
|
Okay, dropping the table appeared to succeed this time. Here's the output from the migration attempt afterwards. In case it matters, here's the postgres container I'm using: postgres:9.6-alpine |
|
Thanks, that new trace is very helpful! This migration unfortunately uses something that is only available from PostgreSQL 12. I will patch it to be compatible with earlier versions of PostgreSQL. |
|
can you drop the index again, and retry with this patch? diff --git a/db/post_migrate/20230803082451_add_unique_index_on_preview_cards_statuses.rb b/db/post_migrate/20230803082451_add_unique_index_on_preview_cards_statuses.rb
index c35ad80028..3e9ab134b7 100644
--- a/db/post_migrate/20230803082451_add_unique_index_on_preview_cards_statuses.rb
+++ b/db/post_migrate/20230803082451_add_unique_index_on_preview_cards_statuses.rb
@@ -15,10 +15,22 @@ class AddUniqueIndexOnPreviewCardsStatuses < ActiveRecord::Migration[6.1]
private
+ def supports_concurrent_reindex?
+ @supports_concurrent_reindex ||= begin
+ version = select_one("SELECT current_setting('server_version_num') AS v")['v'].to_i
+ version >= 12_000
+ end
+ end
+
def deduplicate_and_reindex!
deduplicate_preview_cards!
- safety_assured { execute 'REINDEX INDEX CONCURRENTLY preview_cards_statuses_pkey' }
+ if supports_concurrent_reindex?
+ safety_assured { execute 'REINDEX INDEX CONCURRENTLY preview_cards_statuses_pkey' }
+ else
+ remove_index :preview_cards_statuses, name: :preview_cards_statuses_pkey
+ add_index :preview_cards_statuses, [:status_id, :preview_card_id], name: :preview_cards_statuses_pkey, algorithm: :concurrently, unique: true
+ end
rescue ActiveRecord::RecordNotUnique
retry
end |
|
Sorry, I'm running the docker images so I'm unable to apply the patch directly. I may just attempt to upgrade my postgres server to v12 or higher and see how it goes in the meantime. Thank you for the help. |
|
The migrating to postgres 12 worked fine for me with the latest glitch docker image and the migrations were successful. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Steps to reproduce the problem
Expected behaviour
Database migrations should succeed successfully.
Actual behaviour
Database migrations fail due to PG::DuplicateTable: ERROR: relation "preview_cards_statuses_pkey" already exists
Detailed description
It appears that database migrations fail due to a duplicate table already existing in Postgres database. Potentially related to mastodon#26446.
Mastodon instance
toot.zerojay.com
Mastodon version
4.2.0-beta2+glitch_0827_efb1888
Technical details
All software versions (ruby and such) are those provided by the prebuilt glitch-soc docker images and have not been changed by me before deploying.
Log output:
The text was updated successfully, but these errors were encountered: