nh2 · May 8, 2025 17:16 · May 8, 2025 · May 8, 2025 · May 8, 2025
diff --git a/migration-014-middle-name.sql → sample-migration-014-middle-name.sql b/migration-014-middle-name.sql → sample-migration-014-middle-name.sql
diff --git a/014-middle-name.sql → migration-014-middle-name.sql b/014-middle-name.sql → migration-014-middle-name.sql
diff --git a/014-middle-name.sql b/014-middle-name.sql
@@ -0,0 +1,16 @@
+begin transaction isolation level serializable;
+do $$
+  declare migration integer;
+begin
+  migration := 14;
+
+  if (SELECT myproject.perform_migration(migration, 'Add "middle name" column')) then
+    RAISE NOTICE 'Performing migration %', migration;
+
+    ALTER TABLE myproject.users ADD COLUMN middle_name TEXT NULL;
+
+  else
+    raise notice 'Not performing migration %', migration;
+  end if;
+end$$;
+commit;
diff --git a/migrations.sql b/migrations.sql
@@ -0,0 +1,58 @@
+-- Creates the table recording the current migration.
+
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+
+BEGIN;
+
+-- Note: `create or replace function` cannot be run concurrently; postgres
+--       will error with "tuple concurrently updated" when that happens.
+--       That's why we wrap it in a transaction-level exclusive advisory lock.
+--       See http://stackoverflow.com/questions/40525684/tuple-concurrently-updated-when-creating-functions-in-postgresql-pl-pgsql/44101303
+--       Update: `create table` is racy too, it will fail with
+--         duplicate key value violates unique constraint "pg_type_typname_nsp_index"
+--       when run from different sessions.
+--       See https://www.postgresql.org/message-id/CA%2BTgmoZAdYVtwBfp1FL2sMZbiHCWT4UPrzRLNnX1Nb30Ku3-gg%40mail.gmail.com
+--       So we put the entire thing under our advisory lock.
+
+do $$ begin raise notice 'Before myproject.perform_migration creation advisory lock.'; end$$;
+
+SELECT pg_advisory_xact_lock(4195082422317945854); -- random 64-bit signed ('bigint') lock number
+
+do $$ begin raise notice 'After myproject.perform_migration creation advisory lock.'; end$$;
+
+create table if not exists myproject.migrations (
+  id integer primary key,
+  note text
+);
+
+-- Create only if it's not there already
+insert into myproject.migrations (id, note) select 0, 'Initial'
+where not exists (select id from myproject.migrations where id = 0);
+
+-- Returns whether we should carry on with the migration.
+create or replace function myproject.perform_migration(next_migration integer, note text) returns boolean AS $$
+declare latest_migration integer;
+declare ok boolean;
+begin
+  -- Do nothing if it's already performed
+  if exists (select * from myproject.migrations where id = next_migration) then
+    return false;
+  end if;
+
+  -- Check if the last migration is the one before next_migration
+  select id into latest_migration from myproject.migrations where id = (select max(id) from myproject.migrations);
+
+  if latest_migration = next_migration - 1 then
+    insert into myproject.migrations (id, note) values (next_migration, note);
+    return true;
+  else
+    raise 'Cannot perform migration % since the latest migration % performed is not its predecessor', next_migration, latest_migration;
+  end if;
+end;
+$$ LANGUAGE plpgsql;
+
+do $$ begin
+raise notice 'Created function myproject.perform_migration.';
+end$$;
+
+COMMIT;
No results found