1Ay9eb9U

1-- Autogenerated by column_promotion_rollback.erb
2--   author: siyer@prosperworks.com
3--   incept: 2018-09-04
4
5-- Reverts the company_user_id column of the gmail_msgid_mappings table to integer
6-- Locks that table for only O(1) with small K.
7
8-- Schema migration parameters:
9--   Table:       gmail_msgid_mappings
10--   Column:      company_user_id
11
12-- WARNING: This script uses session variables.  Therefore it is NOT compatible
13-- with pgbouncer in transaction or statement pooling modes.  Use this only with
14-- pgbouncer in session pooling mode, or avoid pgbouncer in the connection path.
15-- This script is likely to hold a connection for hours when operating on large
16-- tables, so connection pooling will be of dubious value anyhow.
17
18-- Pattern for use with Heroku Postgres:
19--
20-- $ psql -d `heroku config:get --app ali-staging DATABASE_URL` -f revert_company_user_id_on_gmail_msgid_mappings.sql
21
22--
23-- Set up the connection.
24--
25
26\set ECHO none
27\pset pager off
28\timing on
29\set ON_ERROR_STOP on
30SET maintenance_work_mem = '8GB';
31SET citus.multi_shard_commit_protocol = '2pc';
32\set ECHO queries
33
34--
35-- Validate the parameters of the migration.
36
37DO $$
38DECLARE
39  tbl text        := quote_ident('gmail_msgid_mappings');
40  current_col text := quote_ident('company_user_id');
41  int_col text    := quote_ident('old_company_user_id');
42  bigint_col text := quote_ident('bigint_company_user_id');
43
44BEGIN
45  -- If the bigint column does not exist, fail.
46
47  IF NOT EXISTS (
48    SELECT 1 FROM information_schema.columns
49      WHERE table_name = 'gmail_msgid_mappings'
50        AND column_name = 'company_user_id'
51    ) THEN
52    RAISE EXCEPTION 'The target column % does not exist', current_col;
53  END IF;
54
55  -- If the integer column does not exist, fail.
56
57  IF NOT EXISTS (
58    SELECT 1 FROM information_schema.columns
59      WHERE table_name = 'gmail_msgid_mappings'
60        AND column_name = 'old_company_user_id'
61    ) THEN
62    RAISE EXCEPTION 'The old integer column % does not exist', int_col;
63  END IF;
64
65  -- If the integer column exists, but is not of type integer, fail.
66
67  IF NOT EXISTS (
68    SELECT 1 FROM information_schema.columns
69      WHERE table_name = 'gmail_msgid_mappings'
70        AND column_name = 'old_company_user_id'
71        AND data_type = 'integer'
72    ) THEN
73    RAISE EXCEPTION 'The old column % must have type integer', int_col;
74  END IF;
75
76 -- If the column in question exists,but is not of type bigint, fail.
77
78  IF NOT EXISTS (
79    SELECT 1 FROM information_schema.columns
80      WHERE table_name = 'gmail_msgid_mappings'
81        AND column_name = 'company_user_id'
82        AND data_type = 'bigint'
83    ) THEN
84    RAISE EXCEPTION 'The target column % must have type bigint', current_col;
85  END IF;
86
87  -- If the new column already exists, fail.
88
89  IF EXISTS (
90    SELECT 1 FROM information_schema.columns
91      WHERE table_name = 'gmail_msgid_mappings'
92        AND column_name = 'bigint_company_user_id'
93    ) THEN
94    RAISE EXCEPTION
95      'Bigint reverted column % already exists! Manually confirm if it is a duplicate.',
96      bigint_col;
97  END IF;
98END
99$$;
100
101--
102-- Transaction to roll back.
103--
104
105-- 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
106
107--    
108-- Remove the fill function btwn bigint and int column.Get these from Last few lines of Step_3 SQL file.
109--   
110
111SELECT run_command_on_shards('gmail_msgid_mappings',$cmd$ DROP TRIGGER IF EXISTS fill_company_user_id_to_old_company_user_id_on_gmail_msgid_mapp ON %s $cmd$);
112
113SELECT run_command_on_workers($cmd$ DROP FUNCTION IF EXISTS copy_company_user_id_to_old_company_user_id_on_gmail_msgid_mapp CASCADE; $cmd$);
114
115BEGIN;
116
117  SET LOCAL lock_timeout = '10s';
118
119  --
120  -- Rename Columns. Keep the bigint column as bigint_column-name. Rename old_column-name to column-name
121  --
122
123  ALTER TABLE gmail_msgid_mappings RENAME COLUMN company_user_id to bigint_company_user_id;
124
125  ALTER TABLE gmail_msgid_mappings RENAME COLUMN old_company_user_id to company_user_id;  
126END;    
127
128--
129-- Create the passive backfill function to fill from integer column to bigint . 
130-- Refer Step 1 SQL file. Just rename tmp_promote to bigint
131--
132
133SELECT run_command_on_workers(
134  $cmd$
135  CREATE OR REPLACE FUNCTION copy_company_user_id_to_bigint_company_user_id_on_gmail_msgid_m()
136    RETURNS TRIGGER AS
137  $copy_company_user_id_to_bigint_company_user_id_on_gmail_msgid_m$
138  BEGIN
139    NEW.bigint_company_user_id := NEW.company_user_id;
140    RETURN NEW;
141  END;
142  $copy_company_user_id_to_bigint_company_user_id_on_gmail_msgid_m$ LANGUAGE plpgsql;
143  $cmd$
144);
145
146DROP TRIGGER IF EXISTS backfill_company_user_id_to_bigint_company_user_id_on_gmail_msg ON gmail_msgid_mappings;
147
148SELECT run_command_on_shards('gmail_msgid_mappings', $cmd$ CREATE TRIGGER backfill_company_user_id_to_bigint_company_user_id_on_gmail_msg
149  BEFORE INSERT OR UPDATE ON %s
150  FOR EACH ROW
151  EXECUTE PROCEDURE copy_company_user_id_to_bigint_company_user_id_on_gmail_msgid_m (company_user_id, bigint_company_user_id); $cmd$
152);
153
154\echo Done!