· 6 years ago · Mar 06, 2019, 01:40 AM
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 user_id column of the zero_input_ingestions table to
8-- bigint. Locks that table for only O(1) with small K.
9
10-- Schema migration parameters:
11-- Table: zero_input_ingestions
12-- Column: user_id
13-- New column: tmp_promote_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: 7293602255705241146
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_user_id_on_zero_input_ingestions_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('zero_input_ingestions');
52 target_col text := quote_ident('user_id');
53 new_col text := quote_ident('tmp_promote_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 = 'zero_input_ingestions'
61 AND column_name = '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 = 'zero_input_ingestions'
71 AND column_name = 'tmp_promote_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 'zero_input_ingestions',
82 $cmd$
83 SELECT count(*)
84 FROM %s
85 WHERE user_id != tmp_promote_user_id
86 OR (user_id IS NULL AND tmp_promote_user_id IS NOT NULL)
87 OR (user_id IS NOT NULL AND tmp_promote_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 = 'zero_input_ingestions'
106 );
107
108 num_good_shards := (
109 SELECT count(*)
110 FROM run_command_on_shards(
111 'zero_input_ingestions',
112 $cmd$
113 SELECT count(*) FROM information_schema.triggers
114 WHERE trigger_schema = 'public'
115 AND trigger_name = 'backfill_user_id_to_tmp_promote_user_id_on_zero_input_ingestion'
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_index_on_identifier_and_type_and_scopes ON zero_input_ingestions USING btree (company_id,internal_identifier,type,tmp_promote_user_id);
130
131-- Cannot drop index and rename column on same transcation.
132
133 ALTER INDEX IF EXISTS old_unique_index_on_identifier_and_type_and_sco RENAME TO older_unique_index_on_identifier_and_type_and_s;
134 ALTER INDEX unique_index_on_identifier_and_type_and_scopes RENAME TO old_unique_index_on_identifier_and_type_and_sco;
135 ALTER INDEX tmp_unique_index_on_identifier_and_type_and_scopes RENAME TO unique_index_on_identifier_and_type_and_scopes;
136
137-- 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.
138
139BEGIN;
140
141 -- If we can't get a lock in 10s, give up.
142
143 SET LOCAL lock_timeout = '10s';
144
145 LOCK TABLE zero_input_ingestions IN ACCESS EXCLUSIVE MODE;
146
147 ALTER TABLE zero_input_ingestions RENAME COLUMN user_id to old_user_id;
148
149 ALTER TABLE zero_input_ingestions RENAME COLUMN tmp_promote_user_id to user_id;
150
151END;
152
153-- Remove the passive backfill trigger from all shards.
154
155 SELECT run_command_on_shards(
156 'zero_input_ingestions',
157 $cmd$
158 DROP TRIGGER IF EXISTS backfill_user_id_to_tmp_promote_user_id_on_zero_input_ingestion ON %s
159 $cmd$
160 );
161
162-- Remove the backfill function from all workers.
163
164 SELECT run_command_on_workers(
165 $cmd$
166 DROP FUNCTION IF EXISTS copy_user_id_to_tmp_promote_user_id_on_zero_input_ingestions CASCADE;
167 $cmd$
168 );
169
170--
171-- Create the passive backfill function.
172--
173
174 SELECT run_command_on_workers(
175 $cmd$
176 CREATE OR REPLACE FUNCTION copy_user_id_to_old_user_id_on_zero_input_ingestions()
177 RETURNS TRIGGER AS
178 $copy_user_id_to_old_user_id_on_zero_input_ingestions$
179 BEGIN
180 NEW.old_user_id := NEW.user_id;
181 RETURN NEW;
182 END;
183 $copy_user_id_to_old_user_id_on_zero_input_ingestions$ LANGUAGE plpgsql;
184 $cmd$
185 );
186
187--
188-- Create Trigger
189--
190
191 SELECT run_command_on_shards('zero_input_ingestions', $cmd$ CREATE TRIGGER fill_user_id_to_old_user_id_on_zero_input_ingestions
192 BEFORE INSERT OR UPDATE ON %s
193 FOR EACH ROW
194 EXECUTE PROCEDURE copy_user_id_to_old_user_id_on_zero_input_ingestions (user_id, old_user_id); $cmd$
195 );
196
197\echo Done!