1-- 2-- MULTI_QUERY_DIRECTORY_CLEANUP 3-- 4-- We execute sub-queries on worker nodes, and copy query results to a directory 5-- on the master node for final processing. When the query completes or fails, 6-- the resource owner should automatically clean up these intermediate query 7-- result files. 8SET citus.next_shard_id TO 810000; 9SET citus.enable_unique_job_ids TO off; 10CREATE FUNCTION citus_rm_job_directory(bigint) 11 RETURNS void 12 AS 'citus' 13 LANGUAGE C STRICT; 14with silence as ( 15 SELECT citus_rm_job_directory(split_part(f, '_', 2)::bigint) 16 from pg_ls_dir('base/pgsql_job_cache') f 17) 18select count(*) * 0 zero 19from silence; 20 zero 21--------------------------------------------------------------------- 22 0 23(1 row) 24 25BEGIN; 26-- pg_ls_dir() displays jobids. We explicitly set the jobId sequence 27-- here so that the regression output becomes independent of the 28-- number of jobs executed prior to running this test. 29SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 30 revenue 31--------------------------------------------------------------------- 32 22770844.7654 33(1 row) 34 35SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 36 revenue 37--------------------------------------------------------------------- 38 22770844.7654 39(1 row) 40 41SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 42 revenue 43--------------------------------------------------------------------- 44 22770844.7654 45(1 row) 46 47SELECT pg_ls_dir('base/pgsql_job_cache'); 48 pg_ls_dir 49--------------------------------------------------------------------- 50(0 rows) 51 52COMMIT; 53SELECT pg_ls_dir('base/pgsql_job_cache'); 54 pg_ls_dir 55--------------------------------------------------------------------- 56(0 rows) 57 58BEGIN; 59SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 60 revenue 61--------------------------------------------------------------------- 62 22770844.7654 63(1 row) 64 65SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 66 revenue 67--------------------------------------------------------------------- 68 22770844.7654 69(1 row) 70 71SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 72 revenue 73--------------------------------------------------------------------- 74 22770844.7654 75(1 row) 76 77SELECT pg_ls_dir('base/pgsql_job_cache'); 78 pg_ls_dir 79--------------------------------------------------------------------- 80(0 rows) 81 82ROLLBACK; 83SELECT pg_ls_dir('base/pgsql_job_cache'); 84 pg_ls_dir 85--------------------------------------------------------------------- 86(0 rows) 87 88-- Test that multiple job directories are all cleaned up correctly, 89-- both individually (by closing a cursor) and in bulk when ending a 90-- transaction. 91BEGIN; 92DECLARE c_00 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 93FETCH 1 FROM c_00; 94 revenue 95--------------------------------------------------------------------- 96 22770844.7654 97(1 row) 98 99DECLARE c_01 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 100FETCH 1 FROM c_01; 101 revenue 102--------------------------------------------------------------------- 103 22770844.7654 104(1 row) 105 106DECLARE c_02 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 107FETCH 1 FROM c_02; 108 revenue 109--------------------------------------------------------------------- 110 22770844.7654 111(1 row) 112 113DECLARE c_03 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 114FETCH 1 FROM c_03; 115 revenue 116--------------------------------------------------------------------- 117 22770844.7654 118(1 row) 119 120DECLARE c_04 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 121FETCH 1 FROM c_04; 122 revenue 123--------------------------------------------------------------------- 124 22770844.7654 125(1 row) 126 127DECLARE c_05 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 128FETCH 1 FROM c_05; 129 revenue 130--------------------------------------------------------------------- 131 22770844.7654 132(1 row) 133 134DECLARE c_06 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 135FETCH 1 FROM c_06; 136 revenue 137--------------------------------------------------------------------- 138 22770844.7654 139(1 row) 140 141DECLARE c_07 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 142FETCH 1 FROM c_07; 143 revenue 144--------------------------------------------------------------------- 145 22770844.7654 146(1 row) 147 148DECLARE c_08 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 149FETCH 1 FROM c_08; 150 revenue 151--------------------------------------------------------------------- 152 22770844.7654 153(1 row) 154 155DECLARE c_09 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 156FETCH 1 FROM c_09; 157 revenue 158--------------------------------------------------------------------- 159 22770844.7654 160(1 row) 161 162DECLARE c_10 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 163FETCH 1 FROM c_10; 164 revenue 165--------------------------------------------------------------------- 166 22770844.7654 167(1 row) 168 169DECLARE c_11 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 170FETCH 1 FROM c_11; 171 revenue 172--------------------------------------------------------------------- 173 22770844.7654 174(1 row) 175 176DECLARE c_12 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 177FETCH 1 FROM c_12; 178 revenue 179--------------------------------------------------------------------- 180 22770844.7654 181(1 row) 182 183DECLARE c_13 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 184FETCH 1 FROM c_13; 185 revenue 186--------------------------------------------------------------------- 187 22770844.7654 188(1 row) 189 190DECLARE c_14 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 191FETCH 1 FROM c_14; 192 revenue 193--------------------------------------------------------------------- 194 22770844.7654 195(1 row) 196 197DECLARE c_15 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 198FETCH 1 FROM c_15; 199 revenue 200--------------------------------------------------------------------- 201 22770844.7654 202(1 row) 203 204DECLARE c_16 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 205FETCH 1 FROM c_16; 206 revenue 207--------------------------------------------------------------------- 208 22770844.7654 209(1 row) 210 211DECLARE c_17 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 212FETCH 1 FROM c_17; 213 revenue 214--------------------------------------------------------------------- 215 22770844.7654 216(1 row) 217 218DECLARE c_18 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 219FETCH 1 FROM c_18; 220 revenue 221--------------------------------------------------------------------- 222 22770844.7654 223(1 row) 224 225DECLARE c_19 CURSOR FOR SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem; 226FETCH 1 FROM c_19; 227 revenue 228--------------------------------------------------------------------- 229 22770844.7654 230(1 row) 231 232SELECT * FROM pg_ls_dir('base/pgsql_job_cache') f ORDER BY f; 233 f 234--------------------------------------------------------------------- 235(0 rows) 236 237-- close first, 17th (first after re-alloc) and last cursor. 238CLOSE c_00; 239CLOSE c_16; 240CLOSE c_19; 241SELECT * FROM pg_ls_dir('base/pgsql_job_cache') f ORDER BY f; 242 f 243--------------------------------------------------------------------- 244(0 rows) 245 246ROLLBACK; 247SELECT pg_ls_dir('base/pgsql_job_cache'); 248 pg_ls_dir 249--------------------------------------------------------------------- 250(0 rows) 251 252