· 6 years ago · Oct 16, 2019, 06:54 PM
1\timing ON
2DO
3$$
4DECLARE
5 temprow information_schema.schemata%ROWTYPE;
6 t timestamptz := clock_timestamp();
7 overall timestamptz := clock_timestamp();
8 createdAtLimit timestamptz := now() - interval '3 days';
9BEGIN
10FOR temprow IN
11 SELECT * FROM information_schema.schemata WHERE schema_name LIKE '\_%'
12 LOOP
13 RAISE NOTICE 'Migrating %', temprow.schema_name;
14 EXECUTE FORMAT ('SET SEARCH_PATH TO %I;', temprow.schema_name);
15
16 RAISE NOTICE 'Copy the structure of current tables to temporary tables';
17 overall = clock_timestamp();
18 t = clock_timestamp();
19 DROP TABLE IF EXISTS temp_transaction;
20
21 CREATE TABLE IF NOT EXISTS temp_transaction AS WITH RECURSIVE rec_transaction AS (SELECT t.* FROM transaction t WHERE parenttransactionid IS NULL AND createdAt > createdAtLimit UNION ALL SELECT c.* FROM transaction c JOIN rec_transaction p ON c.parenttransactionid = p.id AND c.environmentid = p.environmentid WHERE c.createdAt > createdAtLimit) SELECT * FROM rec_transaction;
22 INSERT INTO temp_transaction WITH RECURSIVE rec_transaction AS (SELECT t.* FROM transaction t WHERE originaltransactionid IS NULL AND createdAt > createdAtLimit UNION ALL SELECT c.* FROM transaction c JOIN rec_transaction p ON c.originaltransactionid = p.id AND c.environmentid = p.environmentid WHERE c.createdAt > createdAtLimit) SELECT * FROM rec_transaction;
23 RAISE NOTICE '-- spent % copying transaction', clock_timestamp() - t;
24
25 CREATE TABLE IF NOT EXISTS temp_transmission (LIKE transmission INCLUDING DEFAULTS INCLUDING STORAGE);
26 CREATE TABLE IF NOT EXISTS temp_as2_transmission_detail (LIKE as2_transmission_detail INCLUDING DEFAULTS INCLUDING STORAGE);
27 CREATE TABLE IF NOT EXISTS temp_ftp_transmission_detail (LIKE ftp_transmission_detail INCLUDING DEFAULTS INCLUDING STORAGE);
28 CREATE TABLE IF NOT EXISTS temp_error (LIKE error INCLUDING DEFAULTS INCLUDING STORAGE);
29 CREATE TABLE IF NOT EXISTS temp_process_step_instance (LIKE PROCESS_STEP_INSTANCE INCLUDING DEFAULTS INCLUDING STORAGE);
30 CREATE TABLE IF NOT EXISTS temp_process_instance (LIKE process_instance INCLUDING DEFAULTS INCLUDING STORAGE);
31 CREATE TABLE IF NOT EXISTS temp_business_document (LIKE business_document INCLUDING DEFAULTS INCLUDING STORAGE);
32 CREATE TABLE IF NOT EXISTS temp_document_property (LIKE document_property INCLUDING DEFAULTS INCLUDING STORAGE);
33 CREATE INDEX process_step_instance_transactionid_environmentid_new_idx ON process_step_instance (transactionid, environmentid);
34
35 RAISE NOTICE 'Copy the last 3 days of transactions and related data into the new temporary tables';
36
37 t = clock_timestamp();
38 RAISE NOTICE '-- copying transmission';
39 INSERT INTO temp_transmission SELECT * FROM transmission WHERE (transactionid, environmentid) IN (SELECT id, environmentid FROM temp_transaction);
40 RAISE NOTICE '-- spent % copying transmission', clock_timestamp() - t;
41
42 t = clock_timestamp();
43 RAISE NOTICE '-- copying as2_transmission_detail', clock_timestamp() - t;
44 INSERT INTO temp_as2_transmission_detail SELECT * FROM as2_transmission_detail WHERE (transmissionid, environmentid) IN (SELECT id, environmentid FROM temp_transmission);
45 RAISE NOTICE '-- spent % copying as2_transmission_detail', clock_timestamp() - t;
46
47 t = clock_timestamp();
48 RAISE NOTICE '-- copying ftp_transmission_detail', clock_timestamp() - t;
49 INSERT INTO temp_ftp_transmission_detail SELECT * FROM ftp_transmission_detail WHERE (transmissionid, environmentid) IN (SELECT id, environmentid FROM temp_transmission);
50 RAISE NOTICE '-- spent % copying ftp_transmission_detail', clock_timestamp() - t;
51
52 t = clock_timestamp();
53 RAISE NOTICE '-- copying error', clock_timestamp() - t;
54 INSERT INTO temp_error SELECT * FROM error WHERE (transactionid, environmentid) IN (SELECT id, environmentid FROM temp_transaction);
55 RAISE NOTICE '-- spent % copying error', clock_timestamp() - t;
56
57 t = clock_timestamp();
58 RAISE NOTICE '-- copying business_document', clock_timestamp() - t;
59 INSERT INTO temp_business_document SELECT * FROM business_document WHERE (transactionid, environmentid) IN (SELECT id, environmentid FROM temp_transaction);
60 RAISE NOTICE '-- spent % copying business_document', clock_timestamp() - t;
61
62 t = clock_timestamp();
63 RAISE NOTICE '-- copying document_property', clock_timestamp() - t;
64 INSERT INTO temp_document_property SELECT * FROM document_property WHERE (businessdocumentid, environmentid) IN (SELECT id, environmentid FROM temp_business_document);
65 RAISE NOTICE '-- spent % copying document_property', clock_timestamp() - t;
66
67 t = clock_timestamp();
68 RAISE NOTICE '-- copying process_instance', clock_timestamp() - t;
69 INSERT INTO temp_process_instance SELECT * FROM process_instance WHERE (id, environmentid) IN (SELECT processInstanceId, environmentid FROM temp_process_step_instance);
70 RAISE NOTICE '-- spent % copying process_instance', clock_timestamp() - t;
71
72 t = clock_timestamp();
73 RAISE NOTICE '-- copying process_step_instance', clock_timestamp() - t;
74 INSERT INTO temp_process_step_instance SELECT * FROM process_step_instance WHERE (transactionid, environmentid) IN (SELECT id, environmentid FROM temp_transaction);
75 RAISE NOTICE '-- spent % copying process_step_instance', clock_timestamp() - t;
76
77 t = clock_timestamp();
78 RAISE NOTICE 'Rename current tables to be archived tables';
79 ALTER TABLE transaction RENAME TO archived_transaction;
80 ALTER TABLE transmission RENAME TO archived_transmission;
81 ALTER TABLE as2_transmission_detail RENAME TO archived_as2_transmission_detail;
82 ALTER TABLE ftp_transmission_detail RENAME TO archived_ftp_transmission_detail;
83 ALTER TABLE error RENAME TO archived_error;
84 ALTER TABLE process_step_instance RENAME TO archived_process_step_instance;
85 ALTER TABLE process_instance RENAME TO archived_process_instance;
86 ALTER TABLE business_document RENAME TO archived_business_document;
87 ALTER TABLE document_property RENAME TO archived_document_property;
88 RAISE NOTICE '%', clock_timestamp() - t;
89
90 t = clock_timestamp();
91 RAISE NOTICE 'Rename temporary tables to become the current tables';
92 ALTER TABLE temp_transaction RENAME TO transaction;
93 ALTER TABLE temp_transmission RENAME TO transmission;
94 ALTER TABLE temp_as2_transmission_detail RENAME TO as2_transmission_detail;
95 ALTER TABLE temp_ftp_transmission_detail RENAME TO ftp_transmission_detail;
96 ALTER TABLE temp_error RENAME TO error;
97 ALTER TABLE temp_process_step_instance RENAME TO process_step_instance;
98 ALTER TABLE temp_process_instance RENAME TO process_instance;
99 ALTER TABLE temp_business_document RENAME TO business_document;
100 ALTER TABLE temp_document_property RENAME TO document_property;
101
102 RAISE NOTICE '%', clock_timestamp() - t;
103
104 t = clock_timestamp();
105
106-- RAISE NOTICE 'Incorporate possible missing original transactions';
107-- INSERT INTO transaction SELECT * FROM archived_transaction WHERE id IN (SELECT originaltransactionid FROM transaction WHERE originaltransactionid NOT IN (SELECT id FROM transaction)) ON CONFLICT DO NOTHING;
108
109 RAISE NOTICE 'Add missing constraints to current tables';
110 ALTER TABLE ONLY as2_transmission_detail
111 ADD CONSTRAINT as2_transmission_detail_new_pkey PRIMARY KEY (id, environmentid);
112
113 --
114 ALTER TABLE ONLY as2_transmission_detail
115 ADD CONSTRAINT as2_transmission_detail_transmissionid_environmentid_new_key UNIQUE (transmissionid, environmentid);
116
117 --
118 ALTER TABLE ONLY business_document
119 ADD CONSTRAINT business_document_new_pkey PRIMARY KEY (id, environmentid);
120
121 --
122 ALTER TABLE ONLY business_document
123 ADD CONSTRAINT business_document_transactionid_nonce_environmentid_new_key UNIQUE (transactionid, nonce, environmentid);
124
125 --
126 ALTER TABLE ONLY document_property
127 ADD CONSTRAINT document_property_new_pkey PRIMARY KEY (id, environmentid);
128
129 --
130
131 ALTER TABLE ONLY error
132 ADD CONSTRAINT error_new_pkey PRIMARY KEY (id, environmentid);
133
134
135 ALTER TABLE ONLY error
136 ADD CONSTRAINT error_transactionid_nonce_environmentid_new_key UNIQUE (transactionid, nonce, environmentid);
137
138 ALTER TABLE ONLY ftp_transmission_detail
139 ADD CONSTRAINT ftp_transmission_detail_new_pkey PRIMARY KEY (id, environmentid);
140
141
142 ALTER TABLE ONLY ftp_transmission_detail
143 ADD CONSTRAINT ftp_transmission_detail_transmissionid_environmentid_new_key UNIQUE (transmissionid, environmentid);
144
145
146 ALTER TABLE ONLY process_instance
147 ADD CONSTRAINT process_instance_new_pkey PRIMARY KEY (id, environmentid);
148
149
150 ALTER TABLE ONLY process_step_instance
151 ADD CONSTRAINT process_step_instance_new_pkey PRIMARY KEY (id, environmentid);
152
153
154 ALTER TABLE ONLY transaction
155 ADD CONSTRAINT transaction_new_pkey PRIMARY KEY (id, environmentid);
156
157
158 ALTER TABLE ONLY transmission
159 ADD CONSTRAINT transmission_new_pkey PRIMARY KEY (id, environmentid);
160
161
162 ALTER TABLE ONLY transmission
163 ADD CONSTRAINT transmission_transactionid_nonce_environmentid_new_key UNIQUE (transactionid, nonce, environmentid);
164
165
166 ALTER TABLE ONLY as2_transmission_detail
167 ADD CONSTRAINT as2_transmission_detail_transmissionid_new_fkey FOREIGN KEY (transmissionid, environmentid) REFERENCES transmission(id, environmentid) ON DELETE CASCADE;
168
169
170 ALTER TABLE ONLY business_document
171 ADD CONSTRAINT business_document_transactionid_new_fkey FOREIGN KEY (transactionid, environmentid) REFERENCES transaction(id, environmentid) ON DELETE CASCADE;
172
173
174 ALTER TABLE ONLY document_property
175 ADD CONSTRAINT document_property_businessdocumentid_new_fkey FOREIGN KEY (businessdocumentid, environmentid) REFERENCES business_document(id, environmentid) ON DELETE CASCADE;
176
177
178 ALTER TABLE ONLY error
179 ADD CONSTRAINT error_code_new_fkey FOREIGN KEY (code, environmentid) REFERENCES error_code(code, environmentid);
180
181
182 ALTER TABLE ONLY error
183 ADD CONSTRAINT error_transactionid_new_fkey FOREIGN KEY (transactionid, environmentid) REFERENCES transaction(id, environmentid) ON DELETE CASCADE;
184
185 ALTER TABLE ONLY ftp_transmission_detail
186 ADD CONSTRAINT ftp_transmission_detail_transmissionid_new_fkey FOREIGN KEY (transmissionid, environmentid) REFERENCES transmission(id, environmentid) ON DELETE CASCADE;
187
188
189 ALTER TABLE ONLY process_step_instance
190 ADD CONSTRAINT process_step_instance_businessdocumentid_new_fkey FOREIGN KEY (businessdocumentid, environmentid) REFERENCES business_document(id, environmentid) ON DELETE CASCADE;
191
192
193 ALTER TABLE ONLY process_step_instance
194 ADD CONSTRAINT process_step_instance_processinstanceid_new_fkey FOREIGN KEY (processinstanceid, environmentid) REFERENCES process_instance(id, environmentid) ON DELETE CASCADE;
195
196
197 ALTER TABLE ONLY process_step_instance
198 ADD CONSTRAINT process_step_instance_processinstanceid_new_fkey1 FOREIGN KEY (processinstanceid, environmentid) REFERENCES process_instance(id, environmentid) ON DELETE CASCADE;
199
200
201 ALTER TABLE ONLY process_step_instance
202 ADD CONSTRAINT process_step_instance_transactionid_new_fkey FOREIGN KEY (transactionid, environmentid) REFERENCES transaction(id, environmentid) ON DELETE CASCADE;
203
204-- ALTER TABLE ONLY transaction
205-- ADD CONSTRAINT transaction_originaltransactionid_new_fkey FOREIGN KEY (originaltransactionid, environmentid) REFERENCES transaction(id, environmentid) ON DELETE CASCADE;
206
207
208 ALTER TABLE ONLY transaction
209 ADD CONSTRAINT transaction_parenttransactionid_new_fkey FOREIGN KEY (parenttransactionid, environmentid) REFERENCES transaction(id, environmentid) ON DELETE CASCADE;
210
211
212 ALTER TABLE ONLY transmission
213 ADD CONSTRAINT transmission_transactionid_new_fkey FOREIGN KEY (transactionid, environmentid) REFERENCES transaction(id, environmentid) ON DELETE CASCADE;
214
215 RAISE NOTICE '%', clock_timestamp() - t;
216
217 t = clock_timestamp();
218 RAISE NOTICE 'Add missing check constraints';
219 ALTER TABLE transaction ADD CONSTRAINT "transaction_releasestatus_check" CHECK (releasestatus::text = 'NONE'::text OR releasestatus::text = 'ON HOLD'::text OR releasestatus::text = 'INACTIVE'::text OR releasestatus::text = 'RELEASING'::text OR releasestatus::text = 'RELEASED'::text);
220 ALTER TABLE transaction ADD CONSTRAINT "transaction_status_check" CHECK (status::text = 'ACTIVE'::text OR status::text = 'COMPLETE'::text);
221 ALTER TABLE error ADD CONSTRAINT "error_notificationstatus_check" CHECK (notificationstatus::text = 'NONE'::text OR notificationstatus::text = 'NOTIFYING'::text OR notificationstatus::text = 'NOTIFIED'::text);
222 ALTER TABLE process_step_instance ADD CONSTRAINT "process_step_instance_check" CHECK (state::text = 'PENDING'::text OR state::text = 'COMPLETE'::text AND businessdocumentid IS NOT NULL AND transactionid IS NOT NULL AND completedat IS NOT NULL);
223 ALTER TABLE process_instance ADD CONSTRAINT "process_instance_state_check" CHECK (state::text = 'PENDING'::text OR state::text = 'COMPLETE'::text);
224 RAISE NOTICE '%', clock_timestamp() - t;
225
226 t = clock_timestamp();
227 RAISE NOTICE 'Add missing indexes';
228 CREATE INDEX transaction_parenttransactionid_environmentid_new_idx ON transaction(parenttransactionid, environmentid);
229 CREATE INDEX transaction_processedat_environmentid_new_idx ON transaction(processedat, environmentid);
230 CREATE INDEX transmission_processedat_environmentid_new_idx ON transmission (processedat, environmentid);
231 CREATE INDEX transmission_transactionid_environmentid_new_idx ON transmission (transactionid, environmentid);
232 CREATE INDEX error_processedat_environmentid_new_idx ON error (processedat, environmentid);
233 CREATE INDEX error_sourceid_source_environmentid_new_idx ON error (sourceid, source, environmentid);
234 CREATE INDEX error_transactionid_environmentid_new_idx ON error (transactionid, environmentid);
235 CREATE INDEX business_document_processedat_environmentid_new_idx ON business_document (processedat, environmentid);
236 CREATE INDEX business_document_transactionid_environmentid_new_idx ON business_document (transactionid, environmentid);
237 CREATE INDEX document_property_businessdocumentid_environmentid_new_idx ON document_property (businessdocumentid, environmentid);
238 RAISE NOTICE '%', clock_timestamp() - t;
239
240 RAISE NOTICE '% took % ms', temprow.schema_name, clock_timestamp() - overall;
241 END LOOP;
242
243END$$;