mirror of
https://github.com/coder/coder.git
synced 2025-07-03 16:13:58 +00:00
8e684c8195
Updates coder/customers#365 This PR updates our migration framework to run all migrations in a single transaction. This is the same behavior we had in v1 and ensures that failed migrations don't bring the whole deployment down. If a migration fails now, it will automatically be rolled back to the previous version, allowing the deployment to continue functioning.
38 lines
1.1 KiB
SQL
38 lines
1.1 KiB
SQL
-- Add back the storage_source column. This must be nullable temporarily.
|
|
ALTER TABLE provisioner_jobs ADD COLUMN storage_source text;
|
|
|
|
-- Set the storage_source to the hash of the files.id reference.
|
|
UPDATE
|
|
provisioner_jobs
|
|
SET
|
|
storage_source=files.hash
|
|
FROM
|
|
files
|
|
WHERE
|
|
provisioner_jobs.file_id = files.id;
|
|
|
|
-- Now that we've populated storage_source drop the file_id column.
|
|
ALTER TABLE provisioner_jobs DROP COLUMN file_id;
|
|
-- We can set the storage_source column as NOT NULL now.
|
|
ALTER TABLE provisioner_jobs ALTER COLUMN storage_source SET NOT NULL;
|
|
|
|
-- Delete all the duplicate rows where hashes collide.
|
|
-- We filter on 'id' to ensure only 1 unique row.
|
|
DELETE FROM
|
|
files a
|
|
USING
|
|
files b
|
|
WHERE
|
|
a.created_by < b.created_by
|
|
AND
|
|
a.hash = b.hash;
|
|
|
|
-- Drop the primary key on files.id.
|
|
ALTER TABLE files DROP CONSTRAINT files_pkey;
|
|
-- Drop the id column.
|
|
ALTER TABLE files DROP COLUMN id;
|
|
-- Drop the unique constraint on hash + owner.
|
|
ALTER TABLE files DROP CONSTRAINT files_hash_created_by_key;
|
|
-- Set the primary key back to hash.
|
|
ALTER TABLE files ADD PRIMARY KEY (hash);
|