· 6 years ago · Oct 25, 2019, 07:00 AM
1DROP FUNCTION IF EXISTS public.archive_stale_data_step_1(inclusions text, exclusions text[]);
2CREATE FUNCTION public.archive_stale_data_step_1(inclusions text, exclusions text[]) RETURNS void AS
3$$
4DECLARE
5
6 temprow information_schema.schemata%ROWTYPE;
7 t timestamptz := clock_timestamp();
8 overall timestamptz := clock_timestamp();
9 createdAtLimit timestamptz := now() - interval '3 days';
10 txCount INTEGER;
11
12BEGIN
13 FOR temprow IN SELECT * FROM information_schema.schemata WHERE schema_name LIKE inclusions AND schema_name <> ALL (exclusions)
14 LOOP
15
16 overall = clock_timestamp();
17 t = clock_timestamp();
18
19 RAISE NOTICE 'Migrating %', temprow.schema_name;
20 EXECUTE FORMAT ('SET SEARCH_PATH TO %I;', temprow.schema_name);
21
22 RAISE NOTICE 'Create temporary tables';
23
24 CREATE TABLE IF NOT EXISTS temp_transaction (LIKE transaction INCLUDING DEFAULTS INCLUDING STORAGE);
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 IF NOT EXISTS 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 RAISE NOTICE '-- backing up transaction';
37 WITH RECURSIVE rec_transaction AS (
38
39 SELECT t.*
40 FROM transaction t
41 WHERE originaltransactionid IS NULL AND parenttransactionid IS NULL AND createdAt > createdAtLimit
42
43 UNION ALL
44
45 SELECT c.*
46 FROM transaction c
47 JOIN rec_transaction p ON
48 (c.originaltransactionid = p.id AND c.environmentid = p.environmentid) OR
49 (c.parenttransactionid = p.id AND c.environmentid = p.environmentid)
50 WHERE p.createdAt > createdAtLimit
51
52 ) INSERT INTO temp_transaction SELECT * FROM rec_transaction;
53
54 SELECT count(id) FROM temp_transaction INTO txCount;
55 RAISE NOTICE '-- spent % backing up % transactions', clock_timestamp() - t, txCount;
56
57 t = clock_timestamp();
58 RAISE NOTICE '-- backing up transmission';
59 INSERT INTO temp_transmission SELECT * FROM transmission WHERE (transactionid, environmentid) IN (SELECT id, environmentid FROM temp_transaction);
60 RAISE NOTICE '-- spent % backing up transmission', clock_timestamp() - t;
61
62 t = clock_timestamp();
63 RAISE NOTICE '-- backing up as2_transmission_detail';
64 INSERT INTO temp_as2_transmission_detail SELECT * FROM as2_transmission_detail WHERE (transmissionid, environmentid) IN (SELECT id, environmentid FROM temp_transmission);
65 RAISE NOTICE '-- spent % backing up as2_transmission_detail', clock_timestamp() - t;
66
67 t = clock_timestamp();
68 RAISE NOTICE '-- backing up ftp_transmission_detail';
69 INSERT INTO temp_ftp_transmission_detail SELECT * FROM ftp_transmission_detail WHERE (transmissionid, environmentid) IN (SELECT id, environmentid FROM temp_transmission);
70 RAISE NOTICE '-- spent % backing up ftp_transmission_detail', clock_timestamp() - t;
71
72 t = clock_timestamp();
73 RAISE NOTICE '-- backing up error';
74 INSERT INTO temp_error SELECT * FROM error WHERE (transactionid, environmentid) IN (SELECT id, environmentid FROM temp_transaction);
75 RAISE NOTICE '-- spent % backing up error', clock_timestamp() - t;
76
77 t = clock_timestamp();
78 RAISE NOTICE '-- backing up business_document';
79 INSERT INTO temp_business_document SELECT * FROM business_document WHERE (transactionid, environmentid) IN (SELECT id, environmentid FROM temp_transaction);
80 RAISE NOTICE '-- spent % backing up business_document', clock_timestamp() - t;
81
82 t = clock_timestamp();
83 RAISE NOTICE '-- backing up document_property';
84 INSERT INTO temp_document_property SELECT * FROM document_property WHERE (businessdocumentid, environmentid) IN (SELECT id, environmentid FROM temp_business_document);
85 RAISE NOTICE '-- spent % backing up document_property', clock_timestamp() - t;
86
87 t = clock_timestamp();
88 RAISE NOTICE '-- backing up process_step_instance';
89 INSERT INTO temp_process_step_instance SELECT * FROM process_step_instance WHERE (transactionid, environmentid) IN (SELECT id, environmentid FROM temp_transaction);
90 RAISE NOTICE '-- spent % backing up process_step_instance', clock_timestamp() - t;
91
92 t = clock_timestamp();
93 RAISE NOTICE '-- backing up process_instance';
94 INSERT INTO temp_process_instance SELECT * FROM process_instance WHERE (id, environmentid) IN (SELECT processInstanceId, environmentid FROM temp_process_step_instance);
95 RAISE NOTICE '-- spent % backing up process_instance', clock_timestamp() - t;
96
97 t = clock_timestamp();
98 RAISE NOTICE '-- backing up last 2 hours transaction';
99 createdAtLimit = now() - interval '2 hours';
100
101 CREATE TABLE IF NOT EXISTS temp_lh_transaction AS WITH RECURSIVE rec_transaction AS (
102
103 SELECT t.*
104 FROM transaction t
105 WHERE originaltransactionid IS NULL AND parenttransactionid IS NULL AND createdAt > createdAtLimit
106
107 UNION ALL
108
109 SELECT c.*
110 FROM transaction c
111 JOIN rec_transaction p ON
112 (c.originaltransactionid = p.id AND c.environmentid = p.environmentid) OR
113 (c.parenttransactionid = p.id AND c.environmentid = p.environmentid)
114 WHERE p.createdAt > createdAtLimit
115
116 ) SELECT * FROM rec_transaction WHERE (id,environmentid) NOT IN (SELECT id,environmentid FROM temp_transaction);
117
118 INSERT INTO temp_transaction SELECT * FROM temp_lh_transaction ON CONFLICT DO NOTHING;
119 SELECT count(id) FROM temp_lh_transaction INTO txCount;
120 RAISE NOTICE '-- spent % backing up last 2 hours % transactions', clock_timestamp() - t, txCount;
121
122 t = clock_timestamp();
123 RAISE NOTICE '-- backing up last 2 hours transmission';
124 INSERT INTO temp_transmission SELECT * FROM transmission WHERE (transactionid, environmentid) IN (SELECT id, environmentid FROM temp_lh_transaction);
125 RAISE NOTICE '-- spent % backing up last 2 hours transmission', clock_timestamp() - t;
126
127 t = clock_timestamp();
128 INSERT INTO temp_as2_transmission_detail SELECT * FROM as2_transmission_detail WHERE (transmissionid, environmentid) IN (SELECT id, environmentid FROM temp_transmission) AND (id, environmentid) NOT IN (SELECT id, environmentid FROM as2_transmission_detail);
129 RAISE NOTICE '-- backing up last 2 hours as2_transmission_detail';
130 RAISE NOTICE '-- spent % backing up last 2 hours as2_transmission_detail', clock_timestamp() - t;
131
132 t = clock_timestamp();
133 RAISE NOTICE '-- backing up last 2 hours ftp_transmission_detail';
134 INSERT INTO temp_ftp_transmission_detail SELECT * FROM ftp_transmission_detail WHERE (transmissionid, environmentid) IN (SELECT id, environmentid FROM temp_transmission) AND (id, environmentid) NOT IN (SELECT id, environmentid FROM ftp_transmission_detail);
135 RAISE NOTICE '-- spent % backing up last 2 hours ftp_transmission_detail', clock_timestamp() - t;
136
137 t = clock_timestamp();
138 RAISE NOTICE '-- backing up last 2 hours error';
139 INSERT INTO temp_error SELECT * FROM error WHERE (transactionid, environmentid) IN (SELECT id, environmentid FROM temp_lh_transaction);
140 RAISE NOTICE '-- spent % backing up last 2 hours error', clock_timestamp() - t;
141
142 t = clock_timestamp();
143 RAISE NOTICE '-- backing up last 2 hours business_document';
144 INSERT INTO temp_business_document SELECT * FROM business_document WHERE (transactionid, environmentid) IN (SELECT id, environmentid FROM temp_lh_transaction);
145 RAISE NOTICE '-- spent % backing up last 2 hours business_document', clock_timestamp() - t;
146
147 t = clock_timestamp();
148 RAISE NOTICE '-- backing up last 2 hours document_property';
149 INSERT INTO temp_document_property SELECT * FROM document_property WHERE (businessdocumentid, environmentid) IN (SELECT id, environmentid FROM temp_business_document) AND (id, environmentid) NOT IN (SELECT id, environmentid FROM temp_document_property);
150 RAISE NOTICE '-- spent % backing up last 2 hours document_property', clock_timestamp() - t;
151
152 t = clock_timestamp();
153 RAISE NOTICE '-- backing up last 2 hours process_step_instance';
154 INSERT INTO temp_process_step_instance SELECT * FROM process_step_instance WHERE (transactionid, environmentid) IN (SELECT id, environmentid FROM temp_lh_transaction);
155 RAISE NOTICE '-- spent % backing up last 2 hours process_step_instance', clock_timestamp() - t;
156
157 t = clock_timestamp();
158 RAISE NOTICE '-- backing up last 2 hours process_instance';
159 INSERT INTO temp_process_instance SELECT * FROM process_instance WHERE (id, environmentid) IN (SELECT processInstanceId, environmentid FROM temp_process_step_instance) AND (id, environmentid) NOT IN (SELECT id, environmentid FROM temp_process_instance);
160 RAISE NOTICE '-- spent % backing up last 2 hours process_instance', clock_timestamp() - t;
161
162 t = clock_timestamp();
163 RAISE NOTICE 'Rename current tables to be archived tables';
164 ALTER TABLE transaction RENAME TO archived_transaction;
165 ALTER TABLE transmission RENAME TO archived_transmission;
166 ALTER TABLE as2_transmission_detail RENAME TO archived_as2_transmission_detail;
167 ALTER TABLE ftp_transmission_detail RENAME TO archived_ftp_transmission_detail;
168 ALTER TABLE error RENAME TO archived_error;
169 ALTER TABLE process_step_instance RENAME TO archived_process_step_instance;
170 ALTER TABLE process_instance RENAME TO archived_process_instance;
171 ALTER TABLE business_document RENAME TO archived_business_document;
172 ALTER TABLE document_property RENAME TO archived_document_property;
173 RAISE NOTICE '%', clock_timestamp() - t;
174
175 t = clock_timestamp();
176 RAISE NOTICE 'Rename temporary tables to become the current tables';
177 ALTER TABLE temp_transaction RENAME TO transaction;
178 ALTER TABLE temp_transmission RENAME TO transmission;
179 ALTER TABLE temp_as2_transmission_detail RENAME TO as2_transmission_detail;
180 ALTER TABLE temp_ftp_transmission_detail RENAME TO ftp_transmission_detail;
181 ALTER TABLE temp_error RENAME TO error;
182 ALTER TABLE temp_process_step_instance RENAME TO process_step_instance;
183 ALTER TABLE temp_process_instance RENAME TO process_instance;
184 ALTER TABLE temp_business_document RENAME TO business_document;
185 ALTER TABLE temp_document_property RENAME TO document_property;
186
187 RAISE NOTICE '%', clock_timestamp() - t;
188
189 t = clock_timestamp();
190
191 RAISE NOTICE 'Add missing constraints to current tables';
192 ALTER TABLE ONLY as2_transmission_detail
193 ADD CONSTRAINT as2_transmission_detail_new_pkey PRIMARY KEY (id, environmentid);
194
195 --
196 ALTER TABLE ONLY as2_transmission_detail
197 ADD CONSTRAINT as2_transmission_detail_transmissionid_environmentid_new_key UNIQUE (transmissionid, environmentid);
198
199 --
200 ALTER TABLE ONLY business_document
201 ADD CONSTRAINT business_document_new_pkey PRIMARY KEY (id, environmentid);
202
203 --
204 ALTER TABLE ONLY business_document
205 ADD CONSTRAINT business_document_transactionid_nonce_environmentid_new_key UNIQUE (transactionid, nonce, environmentid);
206
207 --
208 ALTER TABLE ONLY document_property
209 ADD CONSTRAINT document_property_new_pkey PRIMARY KEY (id, environmentid);
210
211 --
212
213 ALTER TABLE ONLY error
214 ADD CONSTRAINT error_new_pkey PRIMARY KEY (id, environmentid);
215
216
217 ALTER TABLE ONLY error
218 ADD CONSTRAINT error_transactionid_nonce_environmentid_new_key UNIQUE (transactionid, nonce, environmentid);
219
220 ALTER TABLE ONLY ftp_transmission_detail
221 ADD CONSTRAINT ftp_transmission_detail_new_pkey PRIMARY KEY (id, environmentid);
222
223
224 ALTER TABLE ONLY ftp_transmission_detail
225 ADD CONSTRAINT ftp_transmission_detail_transmissionid_environmentid_new_key UNIQUE (transmissionid, environmentid);
226
227
228 ALTER TABLE ONLY process_instance
229 ADD CONSTRAINT process_instance_new_pkey PRIMARY KEY (id, environmentid);
230
231
232 ALTER TABLE ONLY process_step_instance
233 ADD CONSTRAINT process_step_instance_new_pkey PRIMARY KEY (id, environmentid);
234
235
236 ALTER TABLE ONLY transaction
237 ADD CONSTRAINT transaction_new_pkey PRIMARY KEY (id, environmentid);
238
239
240 ALTER TABLE ONLY transmission
241 ADD CONSTRAINT transmission_new_pkey PRIMARY KEY (id, environmentid);
242
243
244 ALTER TABLE ONLY transmission
245 ADD CONSTRAINT transmission_transactionid_nonce_environmentid_new_key UNIQUE (transactionid, nonce, environmentid);
246
247
248 ALTER TABLE ONLY as2_transmission_detail
249 ADD CONSTRAINT as2_transmission_detail_transmissionid_new_fkey FOREIGN KEY (transmissionid, environmentid) REFERENCES transmission(id, environmentid) ON DELETE CASCADE;
250
251
252 ALTER TABLE ONLY business_document
253 ADD CONSTRAINT business_document_transactionid_new_fkey FOREIGN KEY (transactionid, environmentid) REFERENCES transaction(id, environmentid) ON DELETE CASCADE;
254
255
256 ALTER TABLE ONLY document_property
257 ADD CONSTRAINT document_property_businessdocumentid_new_fkey FOREIGN KEY (businessdocumentid, environmentid) REFERENCES business_document(id, environmentid) ON DELETE CASCADE;
258
259
260 ALTER TABLE ONLY error
261 ADD CONSTRAINT error_code_new_fkey FOREIGN KEY (code, environmentid) REFERENCES error_code(code, environmentid);
262
263
264 ALTER TABLE ONLY error
265 ADD CONSTRAINT error_transactionid_new_fkey FOREIGN KEY (transactionid, environmentid) REFERENCES transaction(id, environmentid) ON DELETE CASCADE;
266
267 ALTER TABLE ONLY ftp_transmission_detail
268 ADD CONSTRAINT ftp_transmission_detail_transmissionid_new_fkey FOREIGN KEY (transmissionid, environmentid) REFERENCES transmission(id, environmentid) ON DELETE CASCADE;
269
270
271 ALTER TABLE ONLY process_step_instance
272 ADD CONSTRAINT process_step_instance_businessdocumentid_new_fkey FOREIGN KEY (businessdocumentid, environmentid) REFERENCES business_document(id, environmentid) ON DELETE CASCADE;
273
274
275 ALTER TABLE ONLY process_step_instance
276 ADD CONSTRAINT process_step_instance_processinstanceid_new_fkey FOREIGN KEY (processinstanceid, environmentid) REFERENCES process_instance(id, environmentid) ON DELETE CASCADE;
277
278
279 ALTER TABLE ONLY process_step_instance
280 ADD CONSTRAINT process_step_instance_processinstanceid_new_fkey1 FOREIGN KEY (processinstanceid, environmentid) REFERENCES process_instance(id, environmentid) ON DELETE CASCADE;
281
282
283 ALTER TABLE ONLY process_step_instance
284 ADD CONSTRAINT process_step_instance_transactionid_new_fkey FOREIGN KEY (transactionid, environmentid) REFERENCES transaction(id, environmentid) ON DELETE CASCADE;
285
286 ALTER TABLE ONLY transaction
287 ADD CONSTRAINT transaction_originaltransactionid_new_fkey FOREIGN KEY (originaltransactionid, environmentid) REFERENCES transaction(id, environmentid) ON DELETE CASCADE;
288
289
290 ALTER TABLE ONLY transaction
291 ADD CONSTRAINT transaction_parenttransactionid_new_fkey FOREIGN KEY (parenttransactionid, environmentid) REFERENCES transaction(id, environmentid) ON DELETE CASCADE;
292
293
294 ALTER TABLE ONLY transmission
295 ADD CONSTRAINT transmission_transactionid_new_fkey FOREIGN KEY (transactionid, environmentid) REFERENCES transaction(id, environmentid) ON DELETE CASCADE;
296
297 RAISE NOTICE '%', clock_timestamp() - t;
298
299 t = clock_timestamp();
300 RAISE NOTICE 'Add missing check constraints';
301 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);
302 ALTER TABLE transaction ADD CONSTRAINT "transaction_status_check" CHECK (status::text = 'ACTIVE'::text OR status::text = 'COMPLETE'::text);
303 ALTER TABLE error ADD CONSTRAINT "error_notificationstatus_check" CHECK (notificationstatus::text = 'NONE'::text OR notificationstatus::text = 'NOTIFYING'::text OR notificationstatus::text = 'NOTIFIED'::text);
304 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);
305 ALTER TABLE process_instance ADD CONSTRAINT "process_instance_state_check" CHECK (state::text = 'PENDING'::text OR state::text = 'COMPLETE'::text);
306 RAISE NOTICE '%', clock_timestamp() - t;
307
308 t = clock_timestamp();
309 RAISE NOTICE 'Add missing indexes';
310 CREATE INDEX transaction_parenttransactionid_environmentid_new_idx ON transaction(parenttransactionid, environmentid);
311 CREATE INDEX transaction_processedat_environmentid_new_idx ON transaction(processedat, environmentid);
312 CREATE INDEX transmission_processedat_environmentid_new_idx ON transmission (processedat, environmentid);
313 CREATE INDEX transmission_transactionid_environmentid_new_idx ON transmission (transactionid, environmentid);
314 CREATE INDEX error_processedat_environmentid_new_idx ON error (processedat, environmentid);
315 CREATE INDEX error_sourceid_source_environmentid_new_idx ON error (sourceid, source, environmentid);
316 CREATE INDEX error_transactionid_environmentid_new_idx ON error (transactionid, environmentid);
317 CREATE INDEX business_document_processedat_environmentid_new_idx ON business_document (processedat, environmentid);
318 CREATE INDEX business_document_transactionid_environmentid_new_idx ON business_document (transactionid, environmentid);
319 CREATE INDEX document_property_businessdocumentid_environmentid_new_idx ON document_property (businessdocumentid, environmentid);
320 RAISE NOTICE '%', clock_timestamp() - t;
321
322 DROP TABLE temp_lh_transaction;
323 RAISE NOTICE '% took % ms', temprow.schema_name, clock_timestamp() - overall;
324 END LOOP;
325
326END$$ LANGUAGE plpgsql;
327
328
329SELECT public.archive_stale_data_step_1(:'inclusions', :'exclusions');