B2TuFXM1

1-- Autogenerated by column_promotion_rollback.erb
2--   author: siyer@prosperworks.com
3--   incept: 2018-09-04
4
5-- Reverts the target_entity_id column of the entity_associations table to integer
6-- Locks that table for only O(1) with small K.
7
8-- Schema migration parameters:
9--   Table:       entity_associations
10--   Column:      target_entity_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_target_entity_id_on_entity_associations.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('entity_associations');
40  current_col text := quote_ident('target_entity_id');
41  int_col text    := quote_ident('old_target_entity_id');
42  bigint_col text := quote_ident('bigint_target_entity_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 = 'entity_associations'
50        AND column_name = 'target_entity_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 = 'entity_associations'
60        AND column_name = 'old_target_entity_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 = 'entity_associations'
70        AND column_name = 'old_target_entity_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 = 'entity_associations'
81        AND column_name = 'target_entity_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 = 'entity_associations'
92        AND column_name = 'bigint_target_entity_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
107BEGIN;
108
109  SET LOCAL lock_timeout = '10s';
110
111  --
112  -- Rename Columns. Keep the bigint column as bigint_column-name. Rename old_column-name to column-name
113  --
114
115  ALTER TABLE entity_associations RENAME COLUMN target_entity_id to bigint_target_entity_id;
116
117  ALTER TABLE entity_associations RENAME COLUMN old_target_entity_id to target_entity_id;  
118END;    
119  
120--    
121-- Remove the fill function btwn bigint and int column.Get these from Last few lines of Step_3 SQL file.
122--   
123
124SELECT run_command_on_shards('entity_associations',$cmd$ DROP TRIGGER IF EXISTS fill_target_entity_id_to_old_target_entity_id_on_entity_associa ON %s $cmd$);
125SELECT run_command_on_workers($cmd$ DROP FUNCTION IF EXISTS copy_target_entity_id_to_old_target_entity_id_on_entity_associa CASCADE; $cmd$);
126
127--
128-- Create the passive backfill function to fill from integer column to bigint . 
129-- Refer Step 1 SQL file. Just rename tmp_promote to bigint
130--
131
132SELECT run_command_on_workers(
133  $cmd$
134  CREATE OR REPLACE FUNCTION copy_target_entity_id_to_bigint_target_entity_id_on_entity_asso()
135    RETURNS TRIGGER AS
136  $copy_target_entity_id_to_bigint_target_entity_id_on_entity_asso$
137  BEGIN
138    NEW.bigint_target_entity_id := NEW.target_entity_id;
139    RETURN NEW;
140  END;
141  $copy_target_entity_id_to_bigint_target_entity_id_on_entity_asso$ LANGUAGE plpgsql;
142  $cmd$
143);
144
145DROP TRIGGER IF EXISTS backfill_target_entity_id_to_bigint_target_entity_id_on_entity_ ON entity_associations;
146
147SELECT run_command_on_shards('entity_associations', $cmd$ CREATE TRIGGER backfill_target_entity_id_to_bigint_target_entity_id_on_entity_
148  BEFORE INSERT OR UPDATE ON %s
149  FOR EACH ROW
150  EXECUTE PROCEDURE copy_target_entity_id_to_bigint_target_entity_id_on_entity_asso (target_entity_id, bigint_target_entity_id); $cmd$
151);
152
153\echo Done!