Untitled

-- Autogenerated by drop_old_column_promotion.erb
--   author: siyer@prosperworks.com
--   incept: 2018-09-19

-- Drops the old_property_owner_id column of the typed_properties table
-- Locks that table for only O(1) with small K.

-- Schema migration parameters:
--   Table:       typed_properties
--   Column:      old_property_owner_id

-- Pattern for use with Heroku Postgres:
--
-- $ psql -d `heroku config:get --app ali-staging DATABASE_URL` -f drop_old_property_owner_id_on_typed_properties.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('typed_properties');
  current_col text := quote_ident('property_owner_id');
  int_col text    := quote_ident('old_property_owner_id');

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

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
      WHERE table_name = 'typed_properties'
        AND column_name = 'property_owner_id'
    ) THEN
    RAISE EXCEPTION 'The bigint column % does not exist', current_col;
  END IF;

  -- If the integer column does not exist, fail.

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
      WHERE table_name = 'typed_properties'
        AND column_name = 'old_property_owner_id'
    ) THEN
    RAISE EXCEPTION 'The old integer column % does not exist', int_col;
  END IF;

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

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
      WHERE table_name = 'typed_properties'
        AND column_name = 'old_property_owner_id'
        AND data_type = 'integer'
    ) THEN
    RAISE EXCEPTION 'The old column % must have type integer', int_col;
  END IF;

 -- If the column in question exists,but is not of type bigint, fail.

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
      WHERE table_name = 'typed_properties'
        AND column_name = 'property_owner_id'
        AND data_type = 'bigint'
    ) THEN
    RAISE EXCEPTION 'The expected bigint column % does not have type bigint', current_col;
  END IF;

  -- Check if the integer and bigint columns have the same values.Raise exception if not

  IF EXISTS (
    SELECT 1
    FROM run_command_on_shards(
      'typed_properties',
      $cmd$
      SELECT count(*)
      FROM   %s
      WHERE property_owner_id != old_property_owner_id
        OR (property_owner_id IS NULL AND old_property_owner_id IS NOT NULL)
        OR (property_owner_id IS NOT NULL AND old_property_owner_id IS NULL)
      $cmd$
    )
    WHERE result::integer != 0
  ) THEN
    RAISE EXCEPTION 'Values of old and new column do not match';
  END IF;
END
$$;

--
-- Transaction to roll back.
--

-- These are the function and trigger created after the column promotion to bigint. These need to be dropped. Refer Step_3 SQL file during column promotion

BEGIN;

  SET LOCAL lock_timeout = '10s';

  --
  -- Remove the fill function btwn bigint and int column.Get these from Last few lines of Step_3 SQL file.
  --

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

  SELECT run_command_on_workers($cmd$ DROP FUNCTION IF EXISTS copy_property_owner_id_to_old_property_owner_id_on_typed_proper CASCADE; $cmd$);

  --
  -- DROP THE INTEGER COLUMN
  --
  ALTER TABLE typed_properties DROP COLUMN old_property_owner_id;

END;

\echo Done!