-- Autogenerated by promote_id_column_step_1.rb
--   author: neil@prosperworks.com,siyer@prosperworks.com
--   incept: 2018-05-17

-- Inspired in large part by jhw@prosperworks.com's index creation scripts and neil's column promotion effort.

-- Promotes the target_entity_id column of the entity_associations table to
-- bigint. Locks that table for only O(1) with small K.

-- Schema migration parameters:
--   Table:       entity_associations
--   Column:      target_entity_id
--   New column:  tmp_promote_target_entity_id
--   Primary Key: false

-- Direct backfill parameters:
--   Batch size: 10000

-- Locking parameters:
--   Type: Advisory session lock.
--   Key:  3209593577266311262

-- WARNING: This script uses session variables.  Therefore it is NOT compatible
-- with pgbouncer in transaction or statement pooling modes.  Use this only with
-- pgbouncer in session pooling mode, or avoid pgbouncer in the connection path.
-- This script is likely to hold a connection for hours when operating on large
-- tables, so connection pooling will be of dubious value anyhow.

-- Pattern for use with Heroku Postgres:
--
-- $ psql -d `heroku config:get --app ali-staging DATABASE_URL` -f promote_target_entity_id_on_entity_associations_step_1.sql

--
-- Set up the connection.
--

\set ECHO none
\pset pager off
\timing on
\set ON_ERROR_STOP on
SET maintenance_work_mem = '8GB';
SET citus.multi_shard_commit_protocol = '2pc';
\set ECHO queries

--
-- Validate the parameters of the migration.

DO $$
DECLARE
  tbl text        := quote_ident('entity_associations');
  target_col text := quote_ident('target_entity_id');
  new_col text    := quote_ident('tmp_promote_target_entity_id');

BEGIN
  -- If the target column does not exist, fail.

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
      WHERE table_name = tbl
        AND column_name = target_col
    ) THEN
    RAISE EXCEPTION 'target column % does not exist', target_col;
  END IF;

  -- If the target column exists, but is not of type integer, fail.

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
      WHERE table_name = tbl
        AND column_name = target_col
        AND data_type = 'integer'
    ) THEN
    RAISE EXCEPTION 'target column % must have type integer', target_col;
  END IF;

  -- If the new column already exists, fail.

  IF EXISTS (
    SELECT 1 FROM information_schema.columns
      WHERE table_name = tbl
        AND column_name = new_col
    ) THEN
    RAISE EXCEPTION
      'Temp column % already exists! To continue, confirm that this is in fact a temp column, and remove it.',
      new_col;
  END IF;
END
$$;

--
-- Create the passive backfill function.
--

SELECT run_command_on_workers(
  $cmd$

  CREATE OR REPLACE FUNCTION copy_target_entity_id_to_tmp_promote_target_entity_id_on_entity_associations()
    RETURNS TRIGGER AS
  $copy_target_entity_id_to_tmp_promote_target_entity_id_on_entity_associations$
  BEGIN
    NEW.tmp_promote_target_entity_id := NEW.target_entity_id;
    RETURN NEW;
  END;
  $copy_target_entity_id_to_tmp_promote_target_entity_id_on_entity_associations$ LANGUAGE plpgsql;
  $cmd$
);

--
-- Create the temp column.
--

ALTER TABLE entity_associations ADD COLUMN tmp_promote_target_entity_id bigint;

--
-- Drop the trigger if it already exists
--

SELECT run_command_on_shards(
  'entity_associations',
  $cmd$
  DROP TRIGGER IF EXISTS backfill_target_entity_id_to_tmp_promote_target_entity_id_on_entity_associations ON %s
  $cmd$
);

--
-- Create Trigger
--

SELECT run_command_on_shards('entity_associations', $cmd$ CREATE TRIGGER backfill_target_entity_id_to_tmp_promote_target_entity_id_on_entity_associations
  BEFORE INSERT OR UPDATE ON %s
  FOR EACH ROW
  EXECUTE PROCEDURE copy_target_entity_id_to_tmp_promote_target_entity_id_on_entity_associations (target_entity_id, tmp_promote_target_entity_id); $cmd$
);

\echo Done!