forked from sqliteai/sqlite-sync
-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy path39_concurrent_write_apply.sql
More file actions
179 lines (148 loc) · 6.13 KB
/
39_concurrent_write_apply.sql
File metadata and controls
179 lines (148 loc) · 6.13 KB
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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
-- 'Test concurrent write lock during payload apply'
-- NOTE: The lock-contention portion requires dblink with table access.
-- On environments where dblink cannot lock the table (e.g. Supabase),
-- the lock test is skipped and only apply + consistency are verified.
\set testid '39'
\ir helper_test_init.sql
\connect postgres
\ir helper_psql_conn_setup.sql
DROP DATABASE IF EXISTS cloudsync_test_39_a;
DROP DATABASE IF EXISTS cloudsync_test_39_b;
CREATE DATABASE cloudsync_test_39_a;
CREATE DATABASE cloudsync_test_39_b;
-- Setup db_a
\connect cloudsync_test_39_a
\ir helper_psql_conn_setup.sql
CREATE EXTENSION IF NOT EXISTS cloudsync;
CREATE TABLE concurrent_tbl (id TEXT PRIMARY KEY, val TEXT);
SELECT cloudsync_init('concurrent_tbl', 'CLS', true) AS _init_a \gset
-- Setup db_b
\connect cloudsync_test_39_b
\ir helper_psql_conn_setup.sql
CREATE EXTENSION IF NOT EXISTS cloudsync;
CREATE TABLE concurrent_tbl (id TEXT PRIMARY KEY, val TEXT);
SELECT cloudsync_init('concurrent_tbl', 'CLS', true) AS _init_b \gset
-- Insert row1 on db_a and sync to db_b
\connect cloudsync_test_39_a
INSERT INTO concurrent_tbl VALUES ('row1', 'val_a');
SELECT CASE WHEN payload IS NULL OR octet_length(payload) = 0
THEN ''
ELSE '\x' || encode(payload, 'hex')
END AS payload_init,
(payload IS NOT NULL AND octet_length(payload) > 0) AS payload_init_ok
FROM (
SELECT cloudsync_payload_encode(tbl, pk, col_name, col_value, col_version,
db_version, site_id, cl, seq) AS payload
FROM cloudsync_changes WHERE site_id = cloudsync_siteid()
) AS p \gset
\connect cloudsync_test_39_b
\if :payload_init_ok
SELECT cloudsync_payload_apply(decode(substr(:'payload_init', 3), 'hex')) AS _apply_init \gset
\endif
-- Update row1 on db_a
\connect cloudsync_test_39_a
UPDATE concurrent_tbl SET val = 'val_a_updated' WHERE id = 'row1';
SELECT CASE WHEN payload IS NULL OR octet_length(payload) = 0
THEN ''
ELSE '\x' || encode(payload, 'hex')
END AS payload_upd,
(payload IS NOT NULL AND octet_length(payload) > 0) AS payload_upd_ok
FROM (
SELECT cloudsync_payload_encode(tbl, pk, col_name, col_value, col_version,
db_version, site_id, cl, seq) AS payload
FROM cloudsync_changes WHERE site_id = cloudsync_siteid()
) AS p \gset
-- Try to set up dblink and acquire a table lock
\connect cloudsync_test_39_b
CREATE EXTENSION IF NOT EXISTS dblink;
SELECT dblink_connect('locker', 'dbname=cloudsync_test_39_b') AS _conn \gset
SELECT dblink_exec('locker', 'BEGIN') AS _begin \gset
-- Try to acquire EXCLUSIVE lock — if this fails (e.g. permission denied on
-- Supabase), _lock won't be set and we skip the lock-contention test
\unset _lock
SELECT dblink_exec('locker', 'LOCK TABLE concurrent_tbl IN EXCLUSIVE MODE') AS _lock \gset
\if :{?_lock}
-- ===== Lock acquired — run lock-contention test =====
BEGIN;
\set ON_ERROR_ROLLBACK on
SET LOCAL lock_timeout = '500ms';
\if :payload_upd_ok
SELECT cloudsync_payload_apply(decode(substr(:'payload_upd', 3), 'hex')) AS _blocked_apply \gset
\endif
COMMIT;
\set ON_ERROR_ROLLBACK off
-- row1 should still have the OLD value because the apply was blocked
SELECT val AS row1_val_check FROM concurrent_tbl WHERE id = 'row1' \gset
SELECT (:'row1_val_check' = 'val_a') AS blocked_ok \gset
\if :blocked_ok
\echo [PASS] (:testid) Apply correctly blocked by concurrent table lock
\else
\echo [FAIL] (:testid) Expected val_a (blocked), got :'row1_val_check'
SELECT (:fail::int + 1) AS fail \gset
\endif
-- Release the table lock
SELECT dblink_exec('locker', 'COMMIT') AS _release \gset
SELECT dblink_disconnect('locker') AS _disconn \gset
-- Retry apply — should succeed now
\if :payload_upd_ok
SELECT cloudsync_payload_apply(decode(substr(:'payload_upd', 3), 'hex')) AS _apply_retry \gset
\endif
SELECT val AS row1_val FROM concurrent_tbl WHERE id = 'row1' \gset
SELECT (:'row1_val' = 'val_a_updated') AS retry_ok \gset
\if :retry_ok
\echo [PASS] (:testid) Apply succeeded after lock released
\else
\echo [FAIL] (:testid) Apply after unlock - expected val_a_updated, got :'row1_val'
SELECT (:fail::int + 1) AS fail \gset
\endif
\else
-- ===== Lock failed — skip contention test, apply directly =====
\echo [SKIP] (:testid) Lock-contention test skipped (dblink cannot lock table)
-- Clean up the dblink connection (transaction is aborted)
SELECT dblink_exec('locker', 'ROLLBACK') AS _rollback \gset
SELECT dblink_disconnect('locker') AS _disconn \gset
\if :payload_upd_ok
SELECT cloudsync_payload_apply(decode(substr(:'payload_upd', 3), 'hex')) AS _apply_direct \gset
\endif
SELECT val AS row1_val FROM concurrent_tbl WHERE id = 'row1' \gset
SELECT (:'row1_val' = 'val_a_updated') AS direct_ok \gset
\if :direct_ok
\echo [PASS] (:testid) Apply succeeded (no lock contention)
\else
\echo [FAIL] (:testid) Apply failed - expected val_a_updated, got :'row1_val'
SELECT (:fail::int + 1) AS fail \gset
\endif
\endif
-- Full cross-sync for consistency
SELECT CASE WHEN payload IS NULL OR octet_length(payload) = 0
THEN ''
ELSE '\x' || encode(payload, 'hex')
END AS payload_b_final,
(payload IS NOT NULL AND octet_length(payload) > 0) AS payload_b_final_ok
FROM (
SELECT cloudsync_payload_encode(tbl, pk, col_name, col_value, col_version,
db_version, site_id, cl, seq) AS payload
FROM cloudsync_changes WHERE site_id = cloudsync_siteid()
) AS p \gset
\connect cloudsync_test_39_a
\if :payload_b_final_ok
SELECT cloudsync_payload_apply(decode(substr(:'payload_b_final', 3), 'hex')) AS _apply_final \gset
\endif
SELECT md5(COALESCE(string_agg(id || ':' || COALESCE(val, ''), ',' ORDER BY id), '')) AS hash_a
FROM concurrent_tbl \gset
\connect cloudsync_test_39_b
SELECT md5(COALESCE(string_agg(id || ':' || COALESCE(val, ''), ',' ORDER BY id), '')) AS hash_b
FROM concurrent_tbl \gset
SELECT (:'hash_a' = :'hash_b') AS consistency_ok \gset
\if :consistency_ok
\echo [PASS] (:testid) Cross-database consistency verified
\else
\echo [FAIL] (:testid) Consistency failed (hash_a=:'hash_a' hash_b=:'hash_b')
SELECT (:fail::int + 1) AS fail \gset
\endif
-- Cleanup
\ir helper_test_cleanup.sql
\if :should_cleanup
DROP DATABASE IF EXISTS cloudsync_test_39_a;
DROP DATABASE IF EXISTS cloudsync_test_39_b;
\endif