Postgres migrations with advisory lock for DDL transactions

migrations.sql

-- 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;

sample-migration-014-middle-name.sql

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;