1-- predictability 2SET synchronous_commit = on; 3SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding'); 4 ?column? 5---------- 6 init 7(1 row) 8 9CREATE TABLE spill_test(data text); 10-- consume DDL 11SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1'); 12 data 13------ 14(0 rows) 15 16-- spilling main xact 17BEGIN; 18INSERT INTO spill_test SELECT 'serialize-topbig--1:'||g.i FROM generate_series(1, 5000) g(i); 19COMMIT; 20SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)] 21FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT' 22GROUP BY 1 ORDER BY 1; 23 regexp_split_to_array | count | array_agg | array_agg 24-----------------------+-------+---------------------------------------------------------------------+------------------------------------------------------------------------ 25 'serialize-topbig--1 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-topbig--1:1' | table public.spill_test: INSERT: data[text]:'serialize-topbig--1:5000' 26(1 row) 27 28-- spilling subxact, nothing in main 29BEGIN; 30SAVEPOINT s; 31INSERT INTO spill_test SELECT 'serialize-subbig--1:'||g.i FROM generate_series(1, 5000) g(i); 32RELEASE SAVEPOINT s; 33COMMIT; 34SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)] 35FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT' 36GROUP BY 1 ORDER BY 1; 37 regexp_split_to_array | count | array_agg | array_agg 38-----------------------+-------+---------------------------------------------------------------------+------------------------------------------------------------------------ 39 'serialize-subbig--1 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-subbig--1:1' | table public.spill_test: INSERT: data[text]:'serialize-subbig--1:5000' 40(1 row) 41 42-- spilling subxact, spilling main xact 43BEGIN; 44SAVEPOINT s; 45INSERT INTO spill_test SELECT 'serialize-subbig-topbig--1:'||g.i FROM generate_series(1, 5000) g(i); 46RELEASE SAVEPOINT s; 47INSERT INTO spill_test SELECT 'serialize-subbig-topbig--2:'||g.i FROM generate_series(5001, 10000) g(i); 48COMMIT; 49SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)] 50FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT' 51GROUP BY 1 ORDER BY 1; 52 regexp_split_to_array | count | array_agg | array_agg 53-----------------------------+-------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------- 54 'serialize-subbig-topbig--1 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-subbig-topbig--1:1' | table public.spill_test: INSERT: data[text]:'serialize-subbig-topbig--1:5000' 55 'serialize-subbig-topbig--2 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-subbig-topbig--2:5001' | table public.spill_test: INSERT: data[text]:'serialize-subbig-topbig--2:10000' 56(2 rows) 57 58-- spilling subxact, non-spilling main xact 59BEGIN; 60SAVEPOINT s; 61INSERT INTO spill_test SELECT 'serialize-subbig-topsmall--1:'||g.i FROM generate_series(1, 5000) g(i); 62RELEASE SAVEPOINT s; 63INSERT INTO spill_test SELECT 'serialize-subbig-topsmall--2:'||g.i FROM generate_series(5001, 5001) g(i); 64COMMIT; 65SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)] 66FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT' 67GROUP BY 1 ORDER BY 1; 68 regexp_split_to_array | count | array_agg | array_agg 69-------------------------------+-------+---------------------------------------------------------------------------------+--------------------------------------------------------------------------------- 70 'serialize-subbig-topsmall--1 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-subbig-topsmall--1:1' | table public.spill_test: INSERT: data[text]:'serialize-subbig-topsmall--1:5000' 71 'serialize-subbig-topsmall--2 | 1 | table public.spill_test: INSERT: data[text]:'serialize-subbig-topsmall--2:5001' | table public.spill_test: INSERT: data[text]:'serialize-subbig-topsmall--2:5001' 72(2 rows) 73 74-- not-spilling subxact, spilling main xact 75BEGIN; 76SAVEPOINT s; 77INSERT INTO spill_test SELECT 'serialize-subbig-topbig--1:'||g.i FROM generate_series(1, 5000) g(i); 78RELEASE SAVEPOINT s; 79INSERT INTO spill_test SELECT 'serialize-subbig-topbig--2:'||g.i FROM generate_series(5001, 10000) g(i); 80COMMIT; 81SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)] 82FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT' 83GROUP BY 1 ORDER BY 1; 84 regexp_split_to_array | count | array_agg | array_agg 85-----------------------------+-------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------- 86 'serialize-subbig-topbig--1 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-subbig-topbig--1:1' | table public.spill_test: INSERT: data[text]:'serialize-subbig-topbig--1:5000' 87 'serialize-subbig-topbig--2 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-subbig-topbig--2:5001' | table public.spill_test: INSERT: data[text]:'serialize-subbig-topbig--2:10000' 88(2 rows) 89 90-- spilling main xact, spilling subxact 91BEGIN; 92INSERT INTO spill_test SELECT 'serialize-topbig-subbig--1:'||g.i FROM generate_series(1, 5000) g(i); 93SAVEPOINT s; 94INSERT INTO spill_test SELECT 'serialize-topbig-subbig--2:'||g.i FROM generate_series(5001, 10000) g(i); 95RELEASE SAVEPOINT s; 96COMMIT; 97SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)] 98FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT' 99GROUP BY 1 ORDER BY 1; 100 regexp_split_to_array | count | array_agg | array_agg 101-----------------------------+-------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------- 102 'serialize-topbig-subbig--1 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-topbig-subbig--1:1' | table public.spill_test: INSERT: data[text]:'serialize-topbig-subbig--1:5000' 103 'serialize-topbig-subbig--2 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-topbig-subbig--2:5001' | table public.spill_test: INSERT: data[text]:'serialize-topbig-subbig--2:10000' 104(2 rows) 105 106-- spilling main xact, not spilling subxact 107BEGIN; 108INSERT INTO spill_test SELECT 'serialize-topbig-subsmall--1:'||g.i FROM generate_series(1, 5000) g(i); 109SAVEPOINT s; 110INSERT INTO spill_test SELECT 'serialize-topbig-subsmall--2:'||g.i FROM generate_series(5001, 5001) g(i); 111RELEASE SAVEPOINT s; 112COMMIT; 113SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)] 114FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT' 115GROUP BY 1 ORDER BY 1; 116 regexp_split_to_array | count | array_agg | array_agg 117-------------------------------+-------+---------------------------------------------------------------------------------+--------------------------------------------------------------------------------- 118 'serialize-topbig-subsmall--1 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-topbig-subsmall--1:1' | table public.spill_test: INSERT: data[text]:'serialize-topbig-subsmall--1:5000' 119 'serialize-topbig-subsmall--2 | 1 | table public.spill_test: INSERT: data[text]:'serialize-topbig-subsmall--2:5001' | table public.spill_test: INSERT: data[text]:'serialize-topbig-subsmall--2:5001' 120(2 rows) 121 122-- spilling subxact, followed by another spilling subxact 123BEGIN; 124SAVEPOINT s1; 125INSERT INTO spill_test SELECT 'serialize-subbig-subbig--1:'||g.i FROM generate_series(1, 5000) g(i); 126RELEASE SAVEPOINT s1; 127SAVEPOINT s2; 128INSERT INTO spill_test SELECT 'serialize-subbig-subbig--2:'||g.i FROM generate_series(5001, 10000) g(i); 129RELEASE SAVEPOINT s2; 130COMMIT; 131SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)] 132FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT' 133GROUP BY 1 ORDER BY 1; 134 regexp_split_to_array | count | array_agg | array_agg 135-----------------------------+-------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------- 136 'serialize-subbig-subbig--1 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-subbig-subbig--1:1' | table public.spill_test: INSERT: data[text]:'serialize-subbig-subbig--1:5000' 137 'serialize-subbig-subbig--2 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-subbig-subbig--2:5001' | table public.spill_test: INSERT: data[text]:'serialize-subbig-subbig--2:10000' 138(2 rows) 139 140-- spilling subxact, followed by not spilling subxact 141BEGIN; 142SAVEPOINT s1; 143INSERT INTO spill_test SELECT 'serialize-subbig-subsmall--1:'||g.i FROM generate_series(1, 5000) g(i); 144RELEASE SAVEPOINT s1; 145SAVEPOINT s2; 146INSERT INTO spill_test SELECT 'serialize-subbig-subsmall--2:'||g.i FROM generate_series(5001, 5001) g(i); 147RELEASE SAVEPOINT s2; 148COMMIT; 149SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)] 150FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT' 151GROUP BY 1 ORDER BY 1; 152 regexp_split_to_array | count | array_agg | array_agg 153-------------------------------+-------+---------------------------------------------------------------------------------+--------------------------------------------------------------------------------- 154 'serialize-subbig-subsmall--1 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-subbig-subsmall--1:1' | table public.spill_test: INSERT: data[text]:'serialize-subbig-subsmall--1:5000' 155 'serialize-subbig-subsmall--2 | 1 | table public.spill_test: INSERT: data[text]:'serialize-subbig-subsmall--2:5001' | table public.spill_test: INSERT: data[text]:'serialize-subbig-subsmall--2:5001' 156(2 rows) 157 158-- not spilling subxact, followed by spilling subxact 159BEGIN; 160SAVEPOINT s1; 161INSERT INTO spill_test SELECT 'serialize-subsmall-subbig--1:'||g.i FROM generate_series(1, 1) g(i); 162RELEASE SAVEPOINT s1; 163SAVEPOINT s2; 164INSERT INTO spill_test SELECT 'serialize-subsmall-subbig--2:'||g.i FROM generate_series(2, 5001) g(i); 165RELEASE SAVEPOINT s2; 166COMMIT; 167SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)] 168FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT' 169GROUP BY 1 ORDER BY 1; 170 regexp_split_to_array | count | array_agg | array_agg 171-------------------------------+-------+------------------------------------------------------------------------------+--------------------------------------------------------------------------------- 172 'serialize-subsmall-subbig--1 | 1 | table public.spill_test: INSERT: data[text]:'serialize-subsmall-subbig--1:1' | table public.spill_test: INSERT: data[text]:'serialize-subsmall-subbig--1:1' 173 'serialize-subsmall-subbig--2 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-subsmall-subbig--2:2' | table public.spill_test: INSERT: data[text]:'serialize-subsmall-subbig--2:5001' 174(2 rows) 175 176-- spilling subxact, containing another spilling subxact 177BEGIN; 178SAVEPOINT s1; 179INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbig--1:'||g.i FROM generate_series(1, 5000) g(i); 180SAVEPOINT s2; 181INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbig--2:'||g.i FROM generate_series(5001, 10000) g(i); 182RELEASE SAVEPOINT s2; 183RELEASE SAVEPOINT s1; 184COMMIT; 185SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)] 186FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT' 187GROUP BY 1 ORDER BY 1; 188 regexp_split_to_array | count | array_agg | array_agg 189------------------------------------+-------+--------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------- 190 'serialize-nested-subbig-subbig--1 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-nested-subbig-subbig--1:1' | table public.spill_test: INSERT: data[text]:'serialize-nested-subbig-subbig--1:5000' 191 'serialize-nested-subbig-subbig--2 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-nested-subbig-subbig--2:5001' | table public.spill_test: INSERT: data[text]:'serialize-nested-subbig-subbig--2:10000' 192(2 rows) 193 194-- spilling subxact, containing a not spilling subxact 195BEGIN; 196SAVEPOINT s1; 197INSERT INTO spill_test SELECT 'serialize-nested-subbig-subsmall--1:'||g.i FROM generate_series(1, 5000) g(i); 198SAVEPOINT s2; 199INSERT INTO spill_test SELECT 'serialize-nested-subbig-subsmall--2:'||g.i FROM generate_series(5001, 5001) g(i); 200RELEASE SAVEPOINT s2; 201RELEASE SAVEPOINT s1; 202COMMIT; 203SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)] 204FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT' 205GROUP BY 1 ORDER BY 1; 206 regexp_split_to_array | count | array_agg | array_agg 207--------------------------------------+-------+----------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------- 208 'serialize-nested-subbig-subsmall--1 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-nested-subbig-subsmall--1:1' | table public.spill_test: INSERT: data[text]:'serialize-nested-subbig-subsmall--1:5000' 209 'serialize-nested-subbig-subsmall--2 | 1 | table public.spill_test: INSERT: data[text]:'serialize-nested-subbig-subsmall--2:5001' | table public.spill_test: INSERT: data[text]:'serialize-nested-subbig-subsmall--2:5001' 210(2 rows) 211 212-- not spilling subxact, containing a spilling subxact 213BEGIN; 214SAVEPOINT s1; 215INSERT INTO spill_test SELECT 'serialize-nested-subsmall-subbig--1:'||g.i FROM generate_series(1, 1) g(i); 216SAVEPOINT s2; 217INSERT INTO spill_test SELECT 'serialize-nested-subsmall-subbig--2:'||g.i FROM generate_series(2, 5001) g(i); 218RELEASE SAVEPOINT s2; 219RELEASE SAVEPOINT s1; 220COMMIT; 221SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)] 222FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT' 223GROUP BY 1 ORDER BY 1; 224 regexp_split_to_array | count | array_agg | array_agg 225--------------------------------------+-------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------- 226 'serialize-nested-subsmall-subbig--1 | 1 | table public.spill_test: INSERT: data[text]:'serialize-nested-subsmall-subbig--1:1' | table public.spill_test: INSERT: data[text]:'serialize-nested-subsmall-subbig--1:1' 227 'serialize-nested-subsmall-subbig--2 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-nested-subsmall-subbig--2:2' | table public.spill_test: INSERT: data[text]:'serialize-nested-subsmall-subbig--2:5001' 228(2 rows) 229 230-- not spilling subxact, containing a spilling subxact that aborts and one that commits 231BEGIN; 232SAVEPOINT s1; 233INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbigabort--1:'||g.i FROM generate_series(1, 5000) g(i); 234SAVEPOINT s2; 235INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbigabort--2:'||g.i FROM generate_series(5001, 10000) g(i); 236ROLLBACK TO SAVEPOINT s2; 237SAVEPOINT s3; 238INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbigabort-subbig-3:'||g.i FROM generate_series(5001, 10000) g(i); 239RELEASE SAVEPOINT s1; 240COMMIT; 241SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)] 242FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT' 243GROUP BY 1 ORDER BY 1; 244 regexp_split_to_array | count | array_agg | array_agg 245-----------------------------------------------+-------+-------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------- 246 'serialize-nested-subbig-subbigabort--1 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-nested-subbig-subbigabort--1:1' | table public.spill_test: INSERT: data[text]:'serialize-nested-subbig-subbigabort--1:5000' 247 'serialize-nested-subbig-subbigabort-subbig-3 | 5000 | table public.spill_test: INSERT: data[text]:'serialize-nested-subbig-subbigabort-subbig-3:5001' | table public.spill_test: INSERT: data[text]:'serialize-nested-subbig-subbigabort-subbig-3:10000' 248(2 rows) 249 250DROP TABLE spill_test; 251SELECT pg_drop_replication_slot('regression_slot'); 252 pg_drop_replication_slot 253-------------------------- 254 255(1 row) 256 257