4thdoctor_scarf

fix for postgresql 10

Aug 28th, 2018
336
0
Never
1
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.74 KB | None | 0 0
  1. CREATE OR REPLACE FUNCTION sch_chameleon.fn_replay_mysql(integer,integer,boolean)
  2. RETURNS sch_chameleon.ty_replay_status AS
  3. $BODY$
  4. DECLARE
  5. p_i_max_events ALIAS FOR $1;
  6. p_i_id_source ALIAS FOR $2;
  7. p_b_exit_on_error ALIAS FOR $3;
  8. v_ty_status sch_chameleon.ty_replay_status;
  9. v_r_statements record;
  10. v_i_id_batch bigint;
  11. v_v_log_table text;
  12. v_t_ddl text;
  13. v_t_main_sql text;
  14. v_t_delete_sql text;
  15. v_i_replayed integer;
  16. v_i_skipped integer;
  17. v_i_ddl integer;
  18. v_i_evt_replay bigint[];
  19. v_i_evt_queue bigint[];
  20. v_ts_evt_source timestamp without time zone;
  21. v_tab_enabled boolean;
  22.  
  23. BEGIN
  24. v_i_replayed:=0;
  25. v_i_ddl:=0;
  26. v_i_skipped:=0;
  27. v_ty_status.b_continue:=FALSE;
  28. v_ty_status.b_error:=FALSE;
  29. RAISE DEBUG 'Searching batches to replay for source id: %', p_i_id_source;
  30. v_i_id_batch:= (
  31. SELECT
  32. bat.i_id_batch
  33. FROM
  34. sch_chameleon.t_replica_batch bat
  35. INNER JOIN sch_chameleon.t_batch_events evt
  36. ON
  37. evt.i_id_batch=bat.i_id_batch
  38. WHERE
  39. bat.b_started
  40. AND bat.b_processed
  41. AND NOT bat.b_replayed
  42. AND bat.i_id_source=p_i_id_source
  43. ORDER BY
  44. bat.ts_created
  45. LIMIT 1
  46. )
  47. ;
  48.  
  49. v_v_log_table:=(
  50. SELECT
  51. v_log_table
  52. FROM
  53. sch_chameleon.t_replica_batch
  54. WHERE
  55. i_id_batch=v_i_id_batch
  56. )
  57. ;
  58. IF v_i_id_batch IS NULL
  59. THEN
  60. RAISE DEBUG 'There are no batches available for replay';
  61. RETURN v_ty_status;
  62. END IF;
  63.  
  64. RAISE DEBUG 'Found id_batch %, data in log table %', v_i_id_batch,v_v_log_table;
  65. RAISE DEBUG 'Building a list of event id with max length %...', p_i_max_events;
  66. v_i_evt_replay:=(
  67. SELECT
  68. i_id_event[1:p_i_max_events]
  69. FROM
  70. sch_chameleon.t_batch_events
  71. WHERE
  72. i_id_batch=v_i_id_batch
  73. );
  74.  
  75.  
  76. v_i_evt_queue:=(
  77. SELECT
  78. i_id_event[p_i_max_events+1:array_length(i_id_event,1)]
  79. FROM
  80. sch_chameleon.t_batch_events
  81. WHERE
  82. i_id_batch=v_i_id_batch
  83. );
  84.  
  85. RAISE DEBUG 'Finding the last executed event''s timestamp...';
  86. v_ts_evt_source:=(
  87. SELECT
  88. to_timestamp(i_my_event_time)
  89. FROM
  90. sch_chameleon.t_log_replica
  91. WHERE
  92. i_id_event=v_i_evt_replay[array_length(v_i_evt_replay,1)]
  93. AND i_id_batch=v_i_id_batch
  94. );
  95.  
  96. RAISE DEBUG 'Generating the main loop sql';
  97.  
  98. v_t_main_sql:=format('
  99. SELECT
  100. i_id_event AS i_id_event,
  101. enm_binlog_event,
  102. (enm_binlog_event=''ddl'')::integer as i_ddl,
  103. (enm_binlog_event<>''ddl'')::integer as i_replay,
  104. t_binlog_name,
  105. i_binlog_position,
  106. v_table_name,
  107. v_schema_name,
  108. t_pk_data,
  109. CASE
  110. WHEN enm_binlog_event = ''ddl''
  111. THEN
  112. t_query
  113. WHEN enm_binlog_event = ''insert''
  114. THEN
  115. format(
  116. ''INSERT INTO %%I.%%I %%s;'',
  117. v_schema_name,
  118. v_table_name,
  119. t_dec_data
  120.  
  121. )
  122. WHEN enm_binlog_event = ''update''
  123. THEN
  124. format(
  125. ''UPDATE %%I.%%I SET %%s WHERE %%s;'',
  126. v_schema_name,
  127. v_table_name,
  128. t_dec_data,
  129. t_pk_data
  130. )
  131. WHEN enm_binlog_event = ''delete''
  132. THEN
  133. format(
  134. ''DELETE FROM %%I.%%I WHERE %%s;'',
  135. v_schema_name,
  136. v_table_name,
  137. t_pk_data
  138. )
  139.  
  140. END AS t_sql
  141. FROM
  142. (
  143. SELECT
  144. dec.i_id_event,
  145. dec.v_table_name,
  146. dec.v_schema_name,
  147. dec.enm_binlog_event,
  148. dec.t_binlog_name,
  149. dec.i_binlog_position,
  150. dec.t_query as t_query,
  151. dec.ts_event_datetime,
  152. CASE
  153. WHEN dec.enm_binlog_event = ''insert''
  154. THEN
  155. format(''(%%s) VALUES (%%s)'',string_agg(format(''%%I'',dec.t_column),'',''),string_agg(format(''%%L'',jsb_event_after->>t_column),'',''))
  156. WHEN dec.enm_binlog_event = ''update''
  157. THEN
  158. string_agg(format(''%%I=%%L'',dec.t_column,jsb_event_after->>t_column),'','')
  159.  
  160. END AS t_dec_data,
  161. string_agg(DISTINCT
  162. CASE
  163. WHEN dec.v_table_pkey IS NOT NULL
  164. THEN
  165. format(
  166. ''%%I=%%L'',
  167. dec.v_table_pkey,
  168. CASE
  169. WHEN dec.enm_binlog_event = ''update''
  170. THEN
  171. jsb_event_before->>v_table_pkey
  172. ELSE
  173. jsb_event_after->>v_table_pkey
  174. END
  175.  
  176. )
  177. END
  178. ,'' AND '') as t_pk_data
  179. FROM
  180. (
  181. SELECT
  182. log.i_id_event,
  183. log.v_table_name,
  184. log.v_schema_name,
  185. log.enm_binlog_event,
  186. log.t_binlog_name,
  187. log.i_binlog_position,
  188. coalesce(log.jsb_event_after,''{"foo":"bar"}''::jsonb) as jsb_event_after,
  189. (jsonb_each_text(coalesce(log.jsb_event_after,''{"foo":"bar"}''::jsonb))).key AS t_column,
  190. log.jsb_event_before,
  191. log.t_query as t_query,
  192. log.ts_event_datetime,
  193. unnest(v_table_pkey) as v_table_pkey
  194. FROM
  195. sch_chameleon.%I log
  196. INNER JOIN sch_chameleon.t_replica_tables tab
  197. ON
  198. tab.v_table_name=log.v_table_name
  199. AND tab.v_schema_name=log.v_schema_name
  200. WHERE
  201. tab.b_replica_enabled
  202. AND i_id_event = ANY(%L)
  203. ) dec
  204. GROUP BY
  205. dec.i_id_event,
  206. dec.v_table_name,
  207. dec.v_schema_name,
  208. dec.enm_binlog_event,
  209. dec.t_query,
  210. dec.ts_event_datetime,
  211. dec.t_binlog_name,
  212. dec.i_binlog_position
  213. ) par
  214. ORDER BY
  215. i_id_event ASC
  216. ;
  217.  
  218. ',v_v_log_table,v_i_evt_replay);
  219. FOR v_r_statements IN EXECUTE v_t_main_sql
  220. LOOP
  221.  
  222. BEGIN
  223. EXECUTE v_r_statements.t_sql;
  224. v_i_ddl:=v_i_ddl+v_r_statements.i_ddl;
  225. v_i_replayed:=v_i_replayed+v_r_statements.i_replay;
  226.  
  227.  
  228. EXCEPTION
  229. WHEN OTHERS
  230. THEN
  231. RAISE NOTICE 'An error occurred when replaying data for the table %.%',v_r_statements.v_schema_name,v_r_statements.v_table_name;
  232. RAISE NOTICE 'SQLSTATE: % - ERROR MESSAGE %',SQLSTATE, SQLERRM;
  233. RAISE DEBUG 'SQL EXECUTED: % ',v_r_statements.t_sql;
  234. RAISE NOTICE 'The table %.% has been removed from the replica',v_r_statements.v_schema_name,v_r_statements.v_table_name;
  235. v_ty_status.v_table_error:=array_append(v_ty_status.v_table_error, format('%I.%I SQLSTATE: %s - ERROR MESSAGE: %s',v_r_statements.v_schema_name,v_r_statements.v_table_name,SQLSTATE, SQLERRM)::character varying) ;
  236. RAISE NOTICE 'Adding error log entry for table %.% ',v_r_statements.v_schema_name,v_r_statements.v_table_name;
  237. INSERT INTO sch_chameleon.t_error_log
  238. (
  239. i_id_batch,
  240. i_id_source,
  241. v_schema_name,
  242. v_table_name,
  243. t_table_pkey,
  244. t_binlog_name,
  245. i_binlog_position,
  246. ts_error,
  247. t_sql,
  248. t_error_message
  249. )
  250. SELECT
  251. i_id_batch,
  252. p_i_id_source,
  253. v_schema_name,
  254. v_table_name,
  255. v_r_statements.t_pk_data as t_table_pkey,
  256. t_binlog_name,
  257. i_binlog_position,
  258. clock_timestamp(),
  259. quote_literal(v_r_statements.t_sql) as t_sql,
  260. format('%s - %s',SQLSTATE, SQLERRM) as t_error_message
  261. FROM
  262. sch_chameleon.t_log_replica log
  263. WHERE
  264. log.i_id_event=v_r_statements.i_id_event
  265. ;
  266. IF p_b_exit_on_error
  267. THEN
  268. v_ty_status.b_continue:=FALSE;
  269. v_ty_status.b_error:=TRUE;
  270. RETURN v_ty_status;
  271. ELSE
  272.  
  273. RAISE NOTICE 'Statement %', v_r_statements.t_sql;
  274. UPDATE sch_chameleon.t_replica_tables
  275. SET
  276. b_replica_enabled=FALSE
  277. WHERE
  278. v_schema_name=v_r_statements.v_schema_name
  279. AND v_table_name=v_r_statements.v_table_name
  280. ;
  281.  
  282. RAISE NOTICE 'Deleting the log entries for the table %.% ',v_r_statements.v_schema_name,v_r_statements.v_table_name;
  283. DELETE FROM sch_chameleon.t_log_replica log
  284. WHERE
  285. v_table_name=v_r_statements.v_table_name
  286. AND v_schema_name=v_r_statements.v_schema_name
  287. AND i_id_batch=v_i_id_batch
  288. ;
  289. END IF;
  290. END;
  291. END LOOP;
  292. IF v_ts_evt_source IS NOT NULL
  293. THEN
  294. UPDATE sch_chameleon.t_last_replayed
  295. SET
  296. ts_last_replayed=v_ts_evt_source
  297. WHERE
  298. i_id_source=p_i_id_source
  299. ;
  300. END IF;
  301. IF v_i_replayed=0 AND v_i_ddl=0
  302. THEN
  303. DELETE FROM sch_chameleon.t_log_replica
  304. WHERE
  305. i_id_batch=v_i_id_batch
  306. ;
  307.  
  308. GET DIAGNOSTICS v_i_skipped = ROW_COUNT;
  309. RAISE DEBUG 'SKIPPED ROWS: % ',v_i_skipped;
  310.  
  311. UPDATE ONLY sch_chameleon.t_replica_batch
  312. SET
  313. b_replayed=True,
  314. i_skipped=v_i_skipped,
  315. ts_replayed=clock_timestamp()
  316.  
  317. WHERE
  318. i_id_batch=v_i_id_batch
  319. ;
  320.  
  321. DELETE FROM sch_chameleon.t_batch_events
  322. WHERE
  323. i_id_batch=v_i_id_batch
  324. ;
  325.  
  326. v_ty_status.b_continue:=FALSE;
  327. ELSE
  328. UPDATE ONLY sch_chameleon.t_replica_batch
  329. SET
  330. i_ddl=coalesce(i_ddl,0)+v_i_ddl,
  331. i_replayed=coalesce(i_replayed,0)+v_i_replayed,
  332. i_skipped=v_i_skipped,
  333. ts_replayed=clock_timestamp()
  334.  
  335. WHERE
  336. i_id_batch=v_i_id_batch
  337. ;
  338.  
  339. UPDATE sch_chameleon.t_batch_events
  340. SET
  341. i_id_event = v_i_evt_queue
  342. WHERE
  343. i_id_batch=v_i_id_batch
  344. ;
  345.  
  346. DELETE FROM sch_chameleon.t_log_replica
  347. WHERE
  348. i_id_batch=v_i_id_batch
  349. AND i_id_event=ANY(v_i_evt_replay)
  350. ;
  351. v_ty_status.b_continue:=TRUE;
  352. RETURN v_ty_status;
  353. END IF;
  354. v_i_id_batch:= (
  355. SELECT
  356. bat.i_id_batch
  357. FROM
  358. sch_chameleon.t_replica_batch bat
  359. INNER JOIN sch_chameleon.t_batch_events evt
  360. ON
  361. evt.i_id_batch=bat.i_id_batch
  362. WHERE
  363. bat.b_started
  364. AND bat.b_processed
  365. AND NOT bat.b_replayed
  366. AND bat.i_id_source=p_i_id_source
  367. ORDER BY
  368. bat.ts_created
  369. LIMIT 1
  370. )
  371. ;
  372.  
  373. IF v_i_id_batch IS NOT NULL
  374. THEN
  375. v_ty_status.b_continue:=TRUE;
  376. END IF;
  377.  
  378.  
  379. RETURN v_ty_status;
  380.  
  381.  
  382.  
  383. END;
  384.  
  385. $BODY$
  386. LANGUAGE plpgsql;
Advertisement
Comments
  • User was banned
Add Comment
Please, Sign In to add comment