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