1-- predictability
2SET synchronous_commit = on;
3DROP TABLE IF EXISTS xpto;
4NOTICE:  table "xpto" does not exist, skipping
5SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
6 ?column?
7----------
8 init
9(1 row)
10
11CREATE SEQUENCE xpto_rand_seq START 79 INCREMENT 1499; -- portable "random"
12CREATE TABLE xpto (
13    id serial primary key,
14    toasted_col1 text,
15    rand1 float8 DEFAULT nextval('xpto_rand_seq'),
16    toasted_col2 text,
17    rand2 float8 DEFAULT nextval('xpto_rand_seq')
18);
19-- uncompressed external toast data
20INSERT INTO xpto (toasted_col1, toasted_col2) SELECT string_agg(g.i::text, ''), string_agg((g.i*2)::text, '') FROM generate_series(1, 2000) g(i);
21-- compressed external toast data
22INSERT INTO xpto (toasted_col2) SELECT repeat(string_agg(to_char(g.i, 'FM0000'), ''), 50) FROM generate_series(1, 500) g(i);
23-- update of existing column
24UPDATE xpto SET toasted_col1 = (SELECT string_agg(g.i::text, '') FROM generate_series(1, 2000) g(i)) WHERE id = 1;
25UPDATE xpto SET rand1 = 123.456 WHERE id = 1;
26-- updating external via INSERT ... ON CONFLICT DO UPDATE
27INSERT INTO xpto(id, toasted_col2) VALUES (2, 'toasted2-upsert')
28ON CONFLICT (id)
29DO UPDATE SET toasted_col2 = EXCLUDED.toasted_col2 || xpto.toasted_col2;
30DELETE FROM xpto WHERE id = 1;
31DROP TABLE IF EXISTS toasted_key;
32NOTICE:  table "toasted_key" does not exist, skipping
33CREATE TABLE toasted_key (
34    id serial,
35    toasted_key text PRIMARY KEY,
36    toasted_col1 text,
37    toasted_col2 text
38);
39ALTER TABLE toasted_key ALTER COLUMN toasted_key SET STORAGE EXTERNAL;
40ALTER TABLE toasted_key ALTER COLUMN toasted_col1 SET STORAGE EXTERNAL;
41INSERT INTO toasted_key(toasted_key, toasted_col1) VALUES(repeat('1234567890', 200), repeat('9876543210', 200));
42-- test update of a toasted key without changing it
43UPDATE toasted_key SET toasted_col2 = toasted_col1;
44-- test update of a toasted key, changing it
45UPDATE toasted_key SET toasted_key = toasted_key || '1';
46DELETE FROM toasted_key;
47-- Test that HEAP2_MULTI_INSERT insertions with and without toasted
48-- columns are handled correctly
49CREATE TABLE toasted_copy (
50    id int primary key, -- no default, copy didn't use to handle that with multi inserts
51    data text
52);
53ALTER TABLE toasted_copy ALTER COLUMN data SET STORAGE EXTERNAL;
54\copy toasted_copy FROM STDIN
55SELECT substr(data, 1, 200) FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
56                                                                                                  substr
57----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
58 BEGIN
59 table public.xpto: INSERT: id[integer]:1 toasted_col1[text]:'1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374
60 COMMIT
61 BEGIN
62 table public.xpto: INSERT: id[integer]:2 toasted_col1[text]:null rand1[double precision]:3077 toasted_col2[text]:'00010002000300040005000600070008000900100011001200130014001500160017001800190020002100
63 COMMIT
64 BEGIN
65 table public.xpto: UPDATE: id[integer]:1 toasted_col1[text]:'1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374
66 COMMIT
67 BEGIN
68 table public.xpto: UPDATE: id[integer]:1 toasted_col1[text]:unchanged-toast-datum rand1[double precision]:123.456 toasted_col2[text]:unchanged-toast-datum rand2[double precision]:1578
69 COMMIT
70 BEGIN
71 table public.xpto: UPDATE: id[integer]:2 toasted_col1[text]:null rand1[double precision]:3077 toasted_col2[text]:'toasted2-upsert00010002000300040005000600070008000900100011001200130014001500160017001
72 COMMIT
73 BEGIN
74 table public.xpto: DELETE: id[integer]:1
75 COMMIT
76 BEGIN
77 table public.toasted_key: INSERT: id[integer]:1 toasted_key[text]:'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123
78 COMMIT
79 BEGIN
80 table public.toasted_key: UPDATE: id[integer]:1 toasted_key[text]:unchanged-toast-datum toasted_col1[text]:unchanged-toast-datum toasted_col2[text]:'987654321098765432109876543210987654321098765432109
81 COMMIT
82 BEGIN
83 table public.toasted_key: UPDATE: old-key: toasted_key[text]:'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678
84 COMMIT
85 BEGIN
86 table public.toasted_key: DELETE: toasted_key[text]:'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567
87 COMMIT
88 BEGIN
89 table public.toasted_copy: INSERT: id[integer]:1 data[text]:'untoasted1'
90 table public.toasted_copy: INSERT: id[integer]:2 data[text]:'toasted1-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
91 table public.toasted_copy: INSERT: id[integer]:3 data[text]:'untoasted2'
92 table public.toasted_copy: INSERT: id[integer]:4 data[text]:'toasted2-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
93 table public.toasted_copy: INSERT: id[integer]:5 data[text]:'untoasted3'
94 table public.toasted_copy: INSERT: id[integer]:6 data[text]:'untoasted4'
95 table public.toasted_copy: INSERT: id[integer]:7 data[text]:'untoasted5'
96 table public.toasted_copy: INSERT: id[integer]:8 data[text]:'untoasted6'
97 table public.toasted_copy: INSERT: id[integer]:9 data[text]:'untoasted7'
98 table public.toasted_copy: INSERT: id[integer]:10 data[text]:'untoasted8'
99 table public.toasted_copy: INSERT: id[integer]:11 data[text]:'untoasted9'
100 table public.toasted_copy: INSERT: id[integer]:12 data[text]:'untoasted10'
101 table public.toasted_copy: INSERT: id[integer]:13 data[text]:'untoasted11'
102 table public.toasted_copy: INSERT: id[integer]:14 data[text]:'untoasted12'
103 table public.toasted_copy: INSERT: id[integer]:15 data[text]:'untoasted13'
104 table public.toasted_copy: INSERT: id[integer]:16 data[text]:'untoasted14'
105 table public.toasted_copy: INSERT: id[integer]:17 data[text]:'untoasted15'
106 table public.toasted_copy: INSERT: id[integer]:18 data[text]:'untoasted16'
107 table public.toasted_copy: INSERT: id[integer]:19 data[text]:'untoasted17'
108 table public.toasted_copy: INSERT: id[integer]:20 data[text]:'untoasted18'
109 table public.toasted_copy: INSERT: id[integer]:21 data[text]:'untoasted19'
110 table public.toasted_copy: INSERT: id[integer]:22 data[text]:'untoasted20'
111 table public.toasted_copy: INSERT: id[integer]:23 data[text]:'untoasted21'
112 table public.toasted_copy: INSERT: id[integer]:24 data[text]:'untoasted22'
113 table public.toasted_copy: INSERT: id[integer]:25 data[text]:'untoasted23'
114 table public.toasted_copy: INSERT: id[integer]:26 data[text]:'untoasted24'
115 table public.toasted_copy: INSERT: id[integer]:27 data[text]:'untoasted25'
116 table public.toasted_copy: INSERT: id[integer]:28 data[text]:'untoasted26'
117 table public.toasted_copy: INSERT: id[integer]:29 data[text]:'untoasted27'
118 table public.toasted_copy: INSERT: id[integer]:30 data[text]:'untoasted28'
119 table public.toasted_copy: INSERT: id[integer]:31 data[text]:'untoasted29'
120 table public.toasted_copy: INSERT: id[integer]:32 data[text]:'untoasted30'
121 table public.toasted_copy: INSERT: id[integer]:33 data[text]:'untoasted31'
122 table public.toasted_copy: INSERT: id[integer]:34 data[text]:'untoasted32'
123 table public.toasted_copy: INSERT: id[integer]:35 data[text]:'untoasted33'
124 table public.toasted_copy: INSERT: id[integer]:36 data[text]:'untoasted34'
125 table public.toasted_copy: INSERT: id[integer]:37 data[text]:'untoasted35'
126 table public.toasted_copy: INSERT: id[integer]:38 data[text]:'untoasted36'
127 table public.toasted_copy: INSERT: id[integer]:39 data[text]:'untoasted37'
128 table public.toasted_copy: INSERT: id[integer]:40 data[text]:'untoasted38'
129 table public.toasted_copy: INSERT: id[integer]:41 data[text]:'untoasted39'
130 table public.toasted_copy: INSERT: id[integer]:42 data[text]:'untoasted40'
131 table public.toasted_copy: INSERT: id[integer]:43 data[text]:'untoasted41'
132 table public.toasted_copy: INSERT: id[integer]:44 data[text]:'untoasted42'
133 table public.toasted_copy: INSERT: id[integer]:45 data[text]:'untoasted43'
134 table public.toasted_copy: INSERT: id[integer]:46 data[text]:'untoasted44'
135 table public.toasted_copy: INSERT: id[integer]:47 data[text]:'untoasted45'
136 table public.toasted_copy: INSERT: id[integer]:48 data[text]:'untoasted46'
137 table public.toasted_copy: INSERT: id[integer]:49 data[text]:'untoasted47'
138 table public.toasted_copy: INSERT: id[integer]:50 data[text]:'untoasted48'
139 table public.toasted_copy: INSERT: id[integer]:51 data[text]:'untoasted49'
140 table public.toasted_copy: INSERT: id[integer]:52 data[text]:'untoasted50'
141 table public.toasted_copy: INSERT: id[integer]:53 data[text]:'untoasted51'
142 table public.toasted_copy: INSERT: id[integer]:54 data[text]:'untoasted52'
143 table public.toasted_copy: INSERT: id[integer]:55 data[text]:'untoasted53'
144 table public.toasted_copy: INSERT: id[integer]:56 data[text]:'untoasted54'
145 table public.toasted_copy: INSERT: id[integer]:57 data[text]:'untoasted55'
146 table public.toasted_copy: INSERT: id[integer]:58 data[text]:'untoasted56'
147 table public.toasted_copy: INSERT: id[integer]:59 data[text]:'untoasted57'
148 table public.toasted_copy: INSERT: id[integer]:60 data[text]:'untoasted58'
149 table public.toasted_copy: INSERT: id[integer]:61 data[text]:'untoasted59'
150 table public.toasted_copy: INSERT: id[integer]:62 data[text]:'untoasted60'
151 table public.toasted_copy: INSERT: id[integer]:63 data[text]:'untoasted61'
152 table public.toasted_copy: INSERT: id[integer]:64 data[text]:'untoasted62'
153 table public.toasted_copy: INSERT: id[integer]:65 data[text]:'untoasted63'
154 table public.toasted_copy: INSERT: id[integer]:66 data[text]:'untoasted64'
155 table public.toasted_copy: INSERT: id[integer]:67 data[text]:'untoasted65'
156 table public.toasted_copy: INSERT: id[integer]:68 data[text]:'untoasted66'
157 table public.toasted_copy: INSERT: id[integer]:69 data[text]:'untoasted67'
158 table public.toasted_copy: INSERT: id[integer]:70 data[text]:'untoasted68'
159 table public.toasted_copy: INSERT: id[integer]:71 data[text]:'untoasted69'
160 table public.toasted_copy: INSERT: id[integer]:72 data[text]:'untoasted70'
161 table public.toasted_copy: INSERT: id[integer]:73 data[text]:'untoasted71'
162 table public.toasted_copy: INSERT: id[integer]:74 data[text]:'untoasted72'
163 table public.toasted_copy: INSERT: id[integer]:75 data[text]:'untoasted73'
164 table public.toasted_copy: INSERT: id[integer]:76 data[text]:'untoasted74'
165 table public.toasted_copy: INSERT: id[integer]:77 data[text]:'untoasted75'
166 table public.toasted_copy: INSERT: id[integer]:78 data[text]:'untoasted76'
167 table public.toasted_copy: INSERT: id[integer]:79 data[text]:'untoasted77'
168 table public.toasted_copy: INSERT: id[integer]:80 data[text]:'untoasted78'
169 table public.toasted_copy: INSERT: id[integer]:81 data[text]:'untoasted79'
170 table public.toasted_copy: INSERT: id[integer]:82 data[text]:'untoasted80'
171 table public.toasted_copy: INSERT: id[integer]:83 data[text]:'untoasted81'
172 table public.toasted_copy: INSERT: id[integer]:84 data[text]:'untoasted82'
173 table public.toasted_copy: INSERT: id[integer]:85 data[text]:'untoasted83'
174 table public.toasted_copy: INSERT: id[integer]:86 data[text]:'untoasted84'
175 table public.toasted_copy: INSERT: id[integer]:87 data[text]:'untoasted85'
176 table public.toasted_copy: INSERT: id[integer]:88 data[text]:'untoasted86'
177 table public.toasted_copy: INSERT: id[integer]:89 data[text]:'untoasted87'
178 table public.toasted_copy: INSERT: id[integer]:90 data[text]:'untoasted88'
179 table public.toasted_copy: INSERT: id[integer]:91 data[text]:'untoasted89'
180 table public.toasted_copy: INSERT: id[integer]:92 data[text]:'untoasted90'
181 table public.toasted_copy: INSERT: id[integer]:93 data[text]:'untoasted91'
182 table public.toasted_copy: INSERT: id[integer]:94 data[text]:'untoasted92'
183 table public.toasted_copy: INSERT: id[integer]:95 data[text]:'untoasted93'
184 table public.toasted_copy: INSERT: id[integer]:96 data[text]:'untoasted94'
185 table public.toasted_copy: INSERT: id[integer]:97 data[text]:'untoasted95'
186 table public.toasted_copy: INSERT: id[integer]:98 data[text]:'untoasted96'
187 table public.toasted_copy: INSERT: id[integer]:99 data[text]:'untoasted97'
188 table public.toasted_copy: INSERT: id[integer]:100 data[text]:'untoasted98'
189 table public.toasted_copy: INSERT: id[integer]:101 data[text]:'untoasted99'
190 table public.toasted_copy: INSERT: id[integer]:102 data[text]:'untoasted100'
191 table public.toasted_copy: INSERT: id[integer]:103 data[text]:'untoasted101'
192 table public.toasted_copy: INSERT: id[integer]:104 data[text]:'untoasted102'
193 table public.toasted_copy: INSERT: id[integer]:105 data[text]:'untoasted103'
194 table public.toasted_copy: INSERT: id[integer]:106 data[text]:'untoasted104'
195 table public.toasted_copy: INSERT: id[integer]:107 data[text]:'untoasted105'
196 table public.toasted_copy: INSERT: id[integer]:108 data[text]:'untoasted106'
197 table public.toasted_copy: INSERT: id[integer]:109 data[text]:'untoasted107'
198 table public.toasted_copy: INSERT: id[integer]:110 data[text]:'untoasted108'
199 table public.toasted_copy: INSERT: id[integer]:111 data[text]:'untoasted109'
200 table public.toasted_copy: INSERT: id[integer]:112 data[text]:'untoasted110'
201 table public.toasted_copy: INSERT: id[integer]:113 data[text]:'untoasted111'
202 table public.toasted_copy: INSERT: id[integer]:114 data[text]:'untoasted112'
203 table public.toasted_copy: INSERT: id[integer]:115 data[text]:'untoasted113'
204 table public.toasted_copy: INSERT: id[integer]:116 data[text]:'untoasted114'
205 table public.toasted_copy: INSERT: id[integer]:117 data[text]:'untoasted115'
206 table public.toasted_copy: INSERT: id[integer]:118 data[text]:'untoasted116'
207 table public.toasted_copy: INSERT: id[integer]:119 data[text]:'untoasted117'
208 table public.toasted_copy: INSERT: id[integer]:120 data[text]:'untoasted118'
209 table public.toasted_copy: INSERT: id[integer]:121 data[text]:'untoasted119'
210 table public.toasted_copy: INSERT: id[integer]:122 data[text]:'untoasted120'
211 table public.toasted_copy: INSERT: id[integer]:123 data[text]:'untoasted121'
212 table public.toasted_copy: INSERT: id[integer]:124 data[text]:'untoasted122'
213 table public.toasted_copy: INSERT: id[integer]:125 data[text]:'untoasted123'
214 table public.toasted_copy: INSERT: id[integer]:126 data[text]:'untoasted124'
215 table public.toasted_copy: INSERT: id[integer]:127 data[text]:'untoasted125'
216 table public.toasted_copy: INSERT: id[integer]:128 data[text]:'untoasted126'
217 table public.toasted_copy: INSERT: id[integer]:129 data[text]:'untoasted127'
218 table public.toasted_copy: INSERT: id[integer]:130 data[text]:'untoasted128'
219 table public.toasted_copy: INSERT: id[integer]:131 data[text]:'untoasted129'
220 table public.toasted_copy: INSERT: id[integer]:132 data[text]:'untoasted130'
221 table public.toasted_copy: INSERT: id[integer]:133 data[text]:'untoasted131'
222 table public.toasted_copy: INSERT: id[integer]:134 data[text]:'untoasted132'
223 table public.toasted_copy: INSERT: id[integer]:135 data[text]:'untoasted133'
224 table public.toasted_copy: INSERT: id[integer]:136 data[text]:'untoasted134'
225 table public.toasted_copy: INSERT: id[integer]:137 data[text]:'untoasted135'
226 table public.toasted_copy: INSERT: id[integer]:138 data[text]:'untoasted136'
227 table public.toasted_copy: INSERT: id[integer]:139 data[text]:'untoasted137'
228 table public.toasted_copy: INSERT: id[integer]:140 data[text]:'untoasted138'
229 table public.toasted_copy: INSERT: id[integer]:141 data[text]:'untoasted139'
230 table public.toasted_copy: INSERT: id[integer]:142 data[text]:'untoasted140'
231 table public.toasted_copy: INSERT: id[integer]:143 data[text]:'untoasted141'
232 table public.toasted_copy: INSERT: id[integer]:144 data[text]:'untoasted142'
233 table public.toasted_copy: INSERT: id[integer]:145 data[text]:'untoasted143'
234 table public.toasted_copy: INSERT: id[integer]:146 data[text]:'untoasted144'
235 table public.toasted_copy: INSERT: id[integer]:147 data[text]:'untoasted145'
236 table public.toasted_copy: INSERT: id[integer]:148 data[text]:'untoasted146'
237 table public.toasted_copy: INSERT: id[integer]:149 data[text]:'untoasted147'
238 table public.toasted_copy: INSERT: id[integer]:150 data[text]:'untoasted148'
239 table public.toasted_copy: INSERT: id[integer]:151 data[text]:'untoasted149'
240 table public.toasted_copy: INSERT: id[integer]:152 data[text]:'untoasted150'
241 table public.toasted_copy: INSERT: id[integer]:153 data[text]:'untoasted151'
242 table public.toasted_copy: INSERT: id[integer]:154 data[text]:'untoasted152'
243 table public.toasted_copy: INSERT: id[integer]:155 data[text]:'untoasted153'
244 table public.toasted_copy: INSERT: id[integer]:156 data[text]:'untoasted154'
245 table public.toasted_copy: INSERT: id[integer]:157 data[text]:'untoasted155'
246 table public.toasted_copy: INSERT: id[integer]:158 data[text]:'untoasted156'
247 table public.toasted_copy: INSERT: id[integer]:159 data[text]:'untoasted157'
248 table public.toasted_copy: INSERT: id[integer]:160 data[text]:'untoasted158'
249 table public.toasted_copy: INSERT: id[integer]:161 data[text]:'untoasted159'
250 table public.toasted_copy: INSERT: id[integer]:162 data[text]:'untoasted160'
251 table public.toasted_copy: INSERT: id[integer]:163 data[text]:'untoasted161'
252 table public.toasted_copy: INSERT: id[integer]:164 data[text]:'untoasted162'
253 table public.toasted_copy: INSERT: id[integer]:165 data[text]:'untoasted163'
254 table public.toasted_copy: INSERT: id[integer]:166 data[text]:'untoasted164'
255 table public.toasted_copy: INSERT: id[integer]:167 data[text]:'untoasted165'
256 table public.toasted_copy: INSERT: id[integer]:168 data[text]:'untoasted166'
257 table public.toasted_copy: INSERT: id[integer]:169 data[text]:'untoasted167'
258 table public.toasted_copy: INSERT: id[integer]:170 data[text]:'untoasted168'
259 table public.toasted_copy: INSERT: id[integer]:171 data[text]:'untoasted169'
260 table public.toasted_copy: INSERT: id[integer]:172 data[text]:'untoasted170'
261 table public.toasted_copy: INSERT: id[integer]:173 data[text]:'untoasted171'
262 table public.toasted_copy: INSERT: id[integer]:174 data[text]:'untoasted172'
263 table public.toasted_copy: INSERT: id[integer]:175 data[text]:'untoasted173'
264 table public.toasted_copy: INSERT: id[integer]:176 data[text]:'untoasted174'
265 table public.toasted_copy: INSERT: id[integer]:177 data[text]:'untoasted175'
266 table public.toasted_copy: INSERT: id[integer]:178 data[text]:'untoasted176'
267 table public.toasted_copy: INSERT: id[integer]:179 data[text]:'untoasted177'
268 table public.toasted_copy: INSERT: id[integer]:180 data[text]:'untoasted178'
269 table public.toasted_copy: INSERT: id[integer]:181 data[text]:'untoasted179'
270 table public.toasted_copy: INSERT: id[integer]:182 data[text]:'untoasted180'
271 table public.toasted_copy: INSERT: id[integer]:183 data[text]:'untoasted181'
272 table public.toasted_copy: INSERT: id[integer]:184 data[text]:'untoasted182'
273 table public.toasted_copy: INSERT: id[integer]:185 data[text]:'untoasted183'
274 table public.toasted_copy: INSERT: id[integer]:186 data[text]:'untoasted184'
275 table public.toasted_copy: INSERT: id[integer]:187 data[text]:'untoasted185'
276 table public.toasted_copy: INSERT: id[integer]:188 data[text]:'untoasted186'
277 table public.toasted_copy: INSERT: id[integer]:189 data[text]:'untoasted187'
278 table public.toasted_copy: INSERT: id[integer]:190 data[text]:'untoasted188'
279 table public.toasted_copy: INSERT: id[integer]:191 data[text]:'untoasted189'
280 table public.toasted_copy: INSERT: id[integer]:192 data[text]:'untoasted190'
281 table public.toasted_copy: INSERT: id[integer]:193 data[text]:'untoasted191'
282 table public.toasted_copy: INSERT: id[integer]:194 data[text]:'untoasted192'
283 table public.toasted_copy: INSERT: id[integer]:195 data[text]:'untoasted193'
284 table public.toasted_copy: INSERT: id[integer]:196 data[text]:'untoasted194'
285 table public.toasted_copy: INSERT: id[integer]:197 data[text]:'untoasted195'
286 table public.toasted_copy: INSERT: id[integer]:198 data[text]:'untoasted196'
287 table public.toasted_copy: INSERT: id[integer]:199 data[text]:'untoasted197'
288 table public.toasted_copy: INSERT: id[integer]:200 data[text]:'untoasted198'
289 table public.toasted_copy: INSERT: id[integer]:201 data[text]:'toasted3-12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678
290 table public.toasted_copy: INSERT: id[integer]:202 data[text]:'untoasted199'
291 table public.toasted_copy: INSERT: id[integer]:203 data[text]:'untoasted200'
292 COMMIT
293(235 rows)
294
295-- test we can decode "old" tuples bigger than the max heap tuple size correctly
296DROP TABLE IF EXISTS toasted_several;
297NOTICE:  table "toasted_several" does not exist, skipping
298CREATE TABLE toasted_several (
299    id serial unique not null,
300    toasted_key text primary key,
301    toasted_col1 text,
302    toasted_col2 text
303);
304ALTER TABLE toasted_several REPLICA IDENTITY FULL;
305ALTER TABLE toasted_several ALTER COLUMN toasted_key SET STORAGE EXTERNAL;
306ALTER TABLE toasted_several ALTER COLUMN toasted_col1 SET STORAGE EXTERNAL;
307ALTER TABLE toasted_several ALTER COLUMN toasted_col2 SET STORAGE EXTERNAL;
308INSERT INTO toasted_several(toasted_key) VALUES(repeat('9876543210', 10000));
309SELECT pg_column_size(toasted_key) > 2^16 FROM toasted_several;
310 ?column?
311----------
312 t
313(1 row)
314
315SELECT regexp_replace(data, '^(.{100}).*(.{100})$', '\1..\2') FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
316                                                                                               regexp_replace
317------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
318 BEGIN
319 table public.toasted_several: INSERT: id[integer]:1 toasted_key[text]:'98765432109876543210987654321..098765432109876543210987654321098765432109876543210' toasted_col1[text]:null toasted_col2[text]:null
320 COMMIT
321(3 rows)
322
323-- test update of a toasted key without changing it
324UPDATE toasted_several SET toasted_col1 = toasted_key;
325UPDATE toasted_several SET toasted_col2 = toasted_col1;
326SELECT regexp_replace(data, '^(.{100}).*(.{100})$', '\1..\2') FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
327                                                                                               regexp_replace
328------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
329 BEGIN
330 table public.toasted_several: INSERT: id[integer]:1 toasted_key[text]:'98765432109876543210987654321..098765432109876543210987654321098765432109876543210' toasted_col1[text]:null toasted_col2[text]:null
331 COMMIT
332 BEGIN
333 table public.toasted_several: UPDATE: old-key: id[integer]:1 toasted_key[text]:'98765432109876543210..432109876543210987654321098765432109876543210987654321098765432109876543210' toasted_col2[text]:null
334 COMMIT
335 BEGIN
336 table public.toasted_several: UPDATE: old-key: id[integer]:1 toasted_key[text]:'98765432109876543210..876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210'
337 COMMIT
338(9 rows)
339
340/*
341 * update with large tuplebuf, in a transaction large enough to force to spool to disk
342 */
343BEGIN;
344INSERT INTO toasted_several(toasted_key) SELECT * FROM generate_series(1, 10234);
345UPDATE toasted_several SET toasted_col1 = toasted_col2 WHERE id = 1;
346DELETE FROM toasted_several WHERE id = 1;
347COMMIT;
348DROP TABLE toasted_several;
349SELECT regexp_replace(data, '^(.{100}).*(.{100})$', '\1..\2') FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1')
350WHERE data NOT LIKE '%INSERT: %';
351                                                                                               regexp_replace
352------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
353 BEGIN
354 table public.toasted_several: UPDATE: old-key: id[integer]:1 toasted_key[text]:'98765432109876543210..7654321098765432109876543210987654321098765432109876543210' toasted_col2[text]:unchanged-toast-datum
355 table public.toasted_several: DELETE: id[integer]:1 toasted_key[text]:'98765432109876543210987654321..876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210'
356 COMMIT
357(4 rows)
358
359SELECT pg_drop_replication_slot('regression_slot');
360 pg_drop_replication_slot
361--------------------------
362
363(1 row)
364
365