contrib/test_decoding/sql/toast.sql

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