Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Autogenerated by drop_old_column_promotion.erb
- -- author: siyer@prosperworks.com
- -- incept: 2018-09-19
- -- Drops the old_assignee_id column of the contacts table
- -- Locks that table for only O(1) with small K.
- -- Schema migration parameters:
- -- Table: contacts
- -- Column: old_assignee_id
- -- Pattern for use with Heroku Postgres:
- --
- -- $ psql -d `heroku config:get --app ali-production DATABASE_URL_NO_PGBOUNCER` -f drop_old_assignee_id_on_contacts.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('contacts');
- current_col text := quote_ident('assignee_id');
- int_col text := quote_ident('old_assignee_id');
- BEGIN
- -- If the bigint column does not exist, fail.
- IF NOT EXISTS (
- SELECT 1 FROM information_schema.columns
- WHERE table_name = 'contacts'
- AND column_name = 'assignee_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 = 'contacts'
- AND column_name = 'old_assignee_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 = 'contacts'
- AND column_name = 'old_assignee_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 = 'contacts'
- AND column_name = 'assignee_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(
- 'contacts',
- $cmd$
- SELECT count(*)
- FROM %s
- WHERE assignee_id != old_assignee_id
- OR (assignee_id IS NULL AND old_assignee_id IS NOT NULL)
- OR (assignee_id IS NOT NULL AND old_assignee_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('contacts',$cmd$ DROP TRIGGER IF EXISTS fill_assignee_id_to_old_assignee_id_on_contacts ON %s $cmd$);
- SELECT run_command_on_workers($cmd$ DROP FUNCTION IF EXISTS copy_assignee_id_to_old_assignee_id_on_contacts CASCADE; $cmd$);
- --
- -- DROP THE INTEGER COLUMN
- --
- ALTER TABLE contacts DROP COLUMN old_assignee_id;
- END;
- \echo Done!
Add Comment
Please, Sign In to add comment