1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
CREATE OR REPLACE FUNCTION pgl_ddl_deploy.retry_subscriber_log(p_subscriber_log_id integer)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
DECLARE
v_sql TEXT;
v_role TEXT;
v_return BOOLEAN;
BEGIN
IF (SELECT retrying FROM pgl_ddl_deploy.subscriber_logs
WHERE id = p_subscriber_log_id) = TRUE THEN
RAISE WARNING 'This subscriber_log_id is already executing. No action will be taken.';
RETURN FALSE;
END IF;
SELECT full_ddl_sql, executed_as_role
INTO v_sql, v_role
FROM pgl_ddl_deploy.subscriber_logs
WHERE id = p_subscriber_log_id;
UPDATE pgl_ddl_deploy.subscriber_logs
SET retrying = TRUE
WHERE id = p_subscriber_log_id;
BEGIN
/**
This needs to be a DO block because currently,the final SQL sent to subscriber is always within a DO block
*/
v_sql = $$
DO $RETRY$
BEGIN
SET ROLE $$||quote_ident(v_role)||$$;
$$||v_sql||$$
END$RETRY$;
$$;
EXECUTE v_sql;
RESET ROLE;
WITH success AS (
INSERT INTO pgl_ddl_deploy.subscriber_logs
(set_name,
provider_pid,
subscriber_pid,
ddl_sql,
full_ddl_sql,
origin_subscriber_log_id,
provider_node_name,
provider_set_config_id,
executed_as_role,
succeeded)
SELECT
set_name,
provider_pid,
pg_backend_pid(),
ddl_sql,
full_ddl_sql,
origin_subscriber_log_id,
provider_node_name,
provider_set_config_id,
executed_as_role,
TRUE
FROM pgl_ddl_deploy.subscriber_logs
WHERE id = p_subscriber_log_id
RETURNING *
)
UPDATE pgl_ddl_deploy.subscriber_logs
SET next_subscriber_log_id = (SELECT id FROM success)
WHERE id = p_subscriber_log_id;
v_return = TRUE;
EXCEPTION WHEN OTHERS THEN
PERFORM pgl_ddl_deploy.fail_queued_attempt(p_subscriber_log_id, SQLERRM);
v_return = FALSE;
END;
UPDATE pgl_ddl_deploy.subscriber_logs
SET retrying = FALSE
WHERE id = p_subscriber_log_id;
RETURN v_return;
END;
$function$
;
|
