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