· 8 years ago · Jul 18, 2018, 12:56 AM
1-- Autogenerated by promote_id_column_step_1.rb
2-- author: neil@prosperworks.com,siyer@prosperworks.com
3-- incept: 2018-05-17
4
5-- Inspired in large part by jhw@prosperworks.com's index creation scripts and neil's column promotion effort.
6
7-- Promotes the target_entity_id column of the entity_associations table to
8-- bigint. Locks that table for only O(1) with small K.
9
10-- Schema migration parameters:
11-- Table: entity_associations
12-- Column: target_entity_id
13-- New column: tmp_promote_target_entity_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: 3209593577266311262
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_target_entity_id_on_entity_associations_step_1.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 $$
49DECLARE
50 tbl text := quote_ident('entity_associations');
51 target_col text := quote_ident('target_entity_id');
52 new_col text := quote_ident('tmp_promote_target_entity_id');
53
54BEGIN
55 -- If the target column does not exist, fail.
56
57 IF NOT EXISTS (
58 SELECT 1 FROM information_schema.columns
59 WHERE table_name = tbl
60 AND column_name = target_col
61 ) THEN
62 RAISE EXCEPTION 'target column % does not exist', target_col;
63 END IF;
64
65 -- If the target 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 = tbl
70 AND column_name = target_col
71 AND data_type = 'integer'
72 ) THEN
73 RAISE EXCEPTION 'target column % must have type integer', target_col;
74 END IF;
75
76 -- If the new column already exists, fail.
77
78 IF EXISTS (
79 SELECT 1 FROM information_schema.columns
80 WHERE table_name = tbl
81 AND column_name = new_col
82 ) THEN
83 RAISE EXCEPTION
84 'Temp column % already exists! To continue, confirm that this is in fact a temp column, and remove it.',
85 new_col;
86 END IF;
87END
88$$;
89
90--
91-- Create the passive backfill function.
92--
93
94SELECT run_command_on_workers(
95 $cmd$
96
97 CREATE OR REPLACE FUNCTION copy_target_entity_id_to_tmp_promote_target_entity_id_on_entity_associations()
98 RETURNS TRIGGER AS
99 $copy_target_entity_id_to_tmp_promote_target_entity_id_on_entity_associations$
100 BEGIN
101 NEW.tmp_promote_target_entity_id := NEW.target_entity_id;
102 RETURN NEW;
103 END;
104 $copy_target_entity_id_to_tmp_promote_target_entity_id_on_entity_associations$ LANGUAGE plpgsql;
105 $cmd$
106);
107
108--
109-- Create the temp column.
110--
111
112ALTER TABLE entity_associations ADD COLUMN tmp_promote_target_entity_id bigint;
113
114--
115-- Drop the trigger if it already exists
116--
117
118SELECT run_command_on_shards(
119 'entity_associations',
120 $cmd$
121 DROP TRIGGER IF EXISTS backfill_target_entity_id_to_tmp_promote_target_entity_id_on_entity_associations ON %s
122 $cmd$
123);
124
125--
126-- Create Trigger
127--
128
129SELECT run_command_on_shards('entity_associations', $cmd$ CREATE TRIGGER backfill_target_entity_id_to_tmp_promote_target_entity_id_on_entity_associations
130 BEFORE INSERT OR UPDATE ON %s
131 FOR EACH ROW
132 EXECUTE PROCEDURE copy_target_entity_id_to_tmp_promote_target_entity_id_on_entity_associations (target_entity_id, tmp_promote_target_entity_id); $cmd$
133);
134
135\echo Done!