· 7 years ago · Sep 05, 2018, 09:34 PM
1-- Autogenerated by promote_id_column_step_post_backfill.erb
2-- author: siyer@prosperworks.com
3-- incept: 2018-06-29
4
5-- Inspired in large part by jhw@prosperworks.com's index creation scripts and neil's column promotion effort.
6
7-- Promotes the company_user_id column of the gmail_msgid_mappings table to
8-- bigint. Locks that table for only O(1) with small K.
9
10-- Schema migration parameters:
11-- Table: gmail_msgid_mappings
12-- Column: company_user_id
13-- New column: tmp_promote_company_user_id
14-- Primary Key: false
15
16-- Direct backfill parameters:
17-- Batch size: 10000
18
19-- Locking parameters:
20-- Type: Advisory session lock.
21-- Key: 8868801449617709285
22
23-- WARNING: This script uses session variables. Therefore it is NOT compatible
24-- with pgbouncer in transaction or statement pooling modes. Use this only with
25-- pgbouncer in session pooling mode, or avoid pgbouncer in the connection path.
26-- This script is likely to hold a connection for hours when operating on large
27-- tables, so connection pooling will be of dubious value anyhow.
28
29-- Pattern for use with Heroku Postgres:
30--
31-- $ psql -d `heroku config:get --app ali-staging DATABASE_URL` -f promote_company_user_id_on_gmail_msgid_mappings_step_3.sql
32
33--
34-- Set up the connection.
35--
36
37\set ECHO none
38\pset pager off
39\timing on
40\set ON_ERROR_STOP on
41SET maintenance_work_mem = '8GB';
42SET citus.multi_shard_commit_protocol = '2pc';
43\set ECHO queries
44
45--
46-- Validate the parameters of the migration.
47
48DO $$
49
50DECLARE
51 tbl text := quote_ident('gmail_msgid_mappings');
52 target_col text := quote_ident('company_user_id');
53 new_col text := quote_ident('tmp_promote_company_user_id');
54
55BEGIN
56 -- If the target column does not exist, fail.
57
58 IF NOT EXISTS (
59 SELECT 1 FROM information_schema.columns
60 WHERE table_name = 'gmail_msgid_mappings'
61 AND column_name = 'company_user_id'
62 ) THEN
63 RAISE EXCEPTION 'target column % does not exist', target_col;
64 END IF;
65
66 -- If the new column does not exist, fail.
67
68 IF NOT EXISTS (
69 SELECT 1 FROM information_schema.columns
70 WHERE table_name = 'gmail_msgid_mappings'
71 AND column_name = 'tmp_promote_company_user_id'
72 ) THEN
73 RAISE EXCEPTION 'new column % does not exist', new_col;
74 END IF;
75
76-- Verify the backfill
77
78 IF EXISTS (
79 SELECT 1
80 FROM run_command_on_shards(
81 'gmail_msgid_mappings',
82 $cmd$
83 SELECT count(*)
84 FROM %s
85 WHERE company_user_id != tmp_promote_company_user_id
86 OR (company_user_id IS NULL AND tmp_promote_company_user_id IS NOT NULL)
87 OR (company_user_id IS NOT NULL AND tmp_promote_company_user_id IS NULL)
88 $cmd$
89 )
90 WHERE result::integer != 0
91 ) THEN
92 RAISE EXCEPTION 'backfill of % incomplete',target_col;
93 END IF;
94
95END
96$$;
97
98DO $$
99DECLARE
100 num_shards integer;
101 num_good_shards integer;
102
103BEGIN
104 num_shards := (
105 SELECT count(*) FROM pg_dist_shard WHERE logicalrelid::text = 'gmail_msgid_mappings'
106 );
107
108 num_good_shards := (
109 SELECT count(*)
110 FROM run_command_on_shards(
111 'gmail_msgid_mappings',
112 $cmd$
113 SELECT count(*) FROM information_schema.triggers
114 WHERE trigger_schema = 'public'
115 AND trigger_name = 'backfill_company_user_id_to_tmp_promote_company_user_id_on_gmai'
116 AND event_object_table::text = '%s'
117 $cmd$
118 )
119 WHERE result::integer = 2 -- One for INSERT, one for UPDATE.
120 );
121
122END
123$$;
124
125--
126-- Create indexes if index(s) exists on the column
127--
128
129 CREATE UNIQUE INDEX CONCURRENTLY tmp_unique_on_user_msgid ON gmail_msgid_mappings USING btree (tmp_promote_company_user_id,gmail_msgid,company_id);
130
131 CREATE UNIQUE INDEX CONCURRENTLY tmp_unique_on_user_msgid_hashid ON gmail_msgid_mappings USING btree (tmp_promote_company_user_id,gmail_msgid,correspondence_hash_id,company_id);
132
133-- Cannot drop index and rename column on same transcation.
134
135 ALTER INDEX IF EXISTS old_unique_on_user_msgid RENAME TO older_unique_on_user_msgid;
136 ALTER INDEX unique_on_user_msgid RENAME TO old_unique_on_user_msgid;
137 ALTER INDEX tmp_unique_on_user_msgid RENAME TO unique_on_user_msgid;
138
139 ALTER INDEX IF EXISTS old_unique_on_user_msgid_hashid RENAME TO older_unique_on_user_msgid_hashid;
140 ALTER INDEX unique_on_user_msgid_hashid RENAME TO old_unique_on_user_msgid_hashid;
141 ALTER INDEX tmp_unique_on_user_msgid_hashid RENAME TO unique_on_user_msgid_hashid;
142
143-- This is the critical section: We obtain a full lock on the entire table, drop the old column, and rename the new column to have the old column's name.
144
145BEGIN;
146
147 -- If we can't get a lock in 10s, give up.
148
149 SET LOCAL lock_timeout = '10s';
150
151 LOCK TABLE gmail_msgid_mappings IN ACCESS EXCLUSIVE MODE;
152
153 ALTER TABLE gmail_msgid_mappings RENAME COLUMN company_user_id to old_company_user_id;
154
155 ALTER TABLE gmail_msgid_mappings RENAME COLUMN tmp_promote_company_user_id to company_user_id;
156
157END;
158
159-- Remove the passive backfill trigger from all shards.
160
161 SELECT run_command_on_shards(
162 'gmail_msgid_mappings',
163 $cmd$
164 DROP TRIGGER IF EXISTS backfill_company_user_id_to_tmp_promote_company_user_id_on_gmai ON %s
165 $cmd$
166 );
167
168-- Remove the backfill function from all workers.
169
170 SELECT run_command_on_workers(
171 $cmd$
172 DROP FUNCTION IF EXISTS copy_company_user_id_to_tmp_promote_company_user_id_on_gmail_ms CASCADE;
173 $cmd$
174 );
175
176--
177-- Create the passive backfill function.
178--
179
180 SELECT run_command_on_workers(
181 $cmd$
182 CREATE OR REPLACE FUNCTION copy_company_user_id_to_old_company_user_id_on_gmail_msgid_mapp()
183 RETURNS TRIGGER AS
184 $copy_company_user_id_to_old_company_user_id_on_gmail_msgid_mapp$
185 BEGIN
186 NEW.old_company_user_id := NEW.company_user_id;
187 RETURN NEW;
188 END;
189 $copy_company_user_id_to_old_company_user_id_on_gmail_msgid_mapp$ LANGUAGE plpgsql;
190 $cmd$
191 );
192
193--
194-- Create Trigger
195--
196
197 SELECT run_command_on_shards('gmail_msgid_mappings', $cmd$ CREATE TRIGGER fill_company_user_id_to_old_company_user_id_on_gmail_msgid_mapp
198 BEFORE INSERT OR UPDATE ON %s
199 FOR EACH ROW
200 EXECUTE PROCEDURE copy_company_user_id_to_old_company_user_id_on_gmail_msgid_mapp (company_user_id, old_company_user_id); $cmd$
201 );
202
203\echo Done!