1\set HIDE_TOAST_COMPRESSION false 2-- ensure we get stable results regardless of installation's default 3SET default_toast_compression = 'pglz'; 4-- test creating table with compression method 5CREATE TABLE cmdata(f1 text COMPRESSION pglz); 6CREATE INDEX idx ON cmdata(f1); 7INSERT INTO cmdata VALUES(repeat('1234567890', 1000)); 8\d+ cmdata 9 Table "public.cmdata" 10 Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description 11--------+------+-----------+----------+---------+----------+-------------+--------------+------------- 12 f1 | text | | | | extended | pglz | | 13Indexes: 14 "idx" btree (f1) 15 16CREATE TABLE cmdata1(f1 TEXT COMPRESSION lz4); 17ERROR: compression method lz4 not supported 18DETAIL: This functionality requires the server to be built with lz4 support. 19HINT: You need to rebuild PostgreSQL using --with-lz4. 20INSERT INTO cmdata1 VALUES(repeat('1234567890', 1004)); 21ERROR: relation "cmdata1" does not exist 22LINE 1: INSERT INTO cmdata1 VALUES(repeat('1234567890', 1004)); 23 ^ 24\d+ cmdata1 25-- verify stored compression method in the data 26SELECT pg_column_compression(f1) FROM cmdata; 27 pg_column_compression 28----------------------- 29 pglz 30(1 row) 31 32SELECT pg_column_compression(f1) FROM cmdata1; 33ERROR: relation "cmdata1" does not exist 34LINE 1: SELECT pg_column_compression(f1) FROM cmdata1; 35 ^ 36-- decompress data slice 37SELECT SUBSTR(f1, 200, 5) FROM cmdata; 38 substr 39-------- 40 01234 41(1 row) 42 43SELECT SUBSTR(f1, 2000, 50) FROM cmdata1; 44ERROR: relation "cmdata1" does not exist 45LINE 1: SELECT SUBSTR(f1, 2000, 50) FROM cmdata1; 46 ^ 47-- copy with table creation 48SELECT * INTO cmmove1 FROM cmdata; 49\d+ cmmove1 50 Table "public.cmmove1" 51 Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description 52--------+------+-----------+----------+---------+----------+-------------+--------------+------------- 53 f1 | text | | | | extended | | | 54 55SELECT pg_column_compression(f1) FROM cmmove1; 56 pg_column_compression 57----------------------- 58 pglz 59(1 row) 60 61-- copy to existing table 62CREATE TABLE cmmove3(f1 text COMPRESSION pglz); 63INSERT INTO cmmove3 SELECT * FROM cmdata; 64INSERT INTO cmmove3 SELECT * FROM cmdata1; 65ERROR: relation "cmdata1" does not exist 66LINE 1: INSERT INTO cmmove3 SELECT * FROM cmdata1; 67 ^ 68SELECT pg_column_compression(f1) FROM cmmove3; 69 pg_column_compression 70----------------------- 71 pglz 72(1 row) 73 74-- test LIKE INCLUDING COMPRESSION 75CREATE TABLE cmdata2 (LIKE cmdata1 INCLUDING COMPRESSION); 76ERROR: relation "cmdata1" does not exist 77LINE 1: CREATE TABLE cmdata2 (LIKE cmdata1 INCLUDING COMPRESSION); 78 ^ 79\d+ cmdata2 80DROP TABLE cmdata2; 81ERROR: table "cmdata2" does not exist 82-- try setting compression for incompressible data type 83CREATE TABLE cmdata2 (f1 int COMPRESSION pglz); 84ERROR: column data type integer does not support compression 85-- update using datum from different table 86CREATE TABLE cmmove2(f1 text COMPRESSION pglz); 87INSERT INTO cmmove2 VALUES (repeat('1234567890', 1004)); 88SELECT pg_column_compression(f1) FROM cmmove2; 89 pg_column_compression 90----------------------- 91 pglz 92(1 row) 93 94UPDATE cmmove2 SET f1 = cmdata1.f1 FROM cmdata1; 95ERROR: relation "cmdata1" does not exist 96LINE 1: UPDATE cmmove2 SET f1 = cmdata1.f1 FROM cmdata1; 97 ^ 98SELECT pg_column_compression(f1) FROM cmmove2; 99 pg_column_compression 100----------------------- 101 pglz 102(1 row) 103 104-- test externally stored compressed data 105CREATE OR REPLACE FUNCTION large_val() RETURNS TEXT LANGUAGE SQL AS 106'select array_agg(md5(g::text))::text from generate_series(1, 256) g'; 107CREATE TABLE cmdata2 (f1 text COMPRESSION pglz); 108INSERT INTO cmdata2 SELECT large_val() || repeat('a', 4000); 109SELECT pg_column_compression(f1) FROM cmdata2; 110 pg_column_compression 111----------------------- 112 pglz 113(1 row) 114 115INSERT INTO cmdata1 SELECT large_val() || repeat('a', 4000); 116ERROR: relation "cmdata1" does not exist 117LINE 1: INSERT INTO cmdata1 SELECT large_val() || repeat('a', 4000); 118 ^ 119SELECT pg_column_compression(f1) FROM cmdata1; 120ERROR: relation "cmdata1" does not exist 121LINE 1: SELECT pg_column_compression(f1) FROM cmdata1; 122 ^ 123SELECT SUBSTR(f1, 200, 5) FROM cmdata1; 124ERROR: relation "cmdata1" does not exist 125LINE 1: SELECT SUBSTR(f1, 200, 5) FROM cmdata1; 126 ^ 127SELECT SUBSTR(f1, 200, 5) FROM cmdata2; 128 substr 129-------- 130 8f14e 131(1 row) 132 133DROP TABLE cmdata2; 134--test column type update varlena/non-varlena 135CREATE TABLE cmdata2 (f1 int); 136\d+ cmdata2 137 Table "public.cmdata2" 138 Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description 139--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- 140 f1 | integer | | | | plain | | | 141 142ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE varchar; 143\d+ cmdata2 144 Table "public.cmdata2" 145 Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description 146--------+-------------------+-----------+----------+---------+----------+-------------+--------------+------------- 147 f1 | character varying | | | | extended | | | 148 149ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE int USING f1::integer; 150\d+ cmdata2 151 Table "public.cmdata2" 152 Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description 153--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- 154 f1 | integer | | | | plain | | | 155 156--changing column storage should not impact the compression method 157--but the data should not be compressed 158ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE varchar; 159ALTER TABLE cmdata2 ALTER COLUMN f1 SET COMPRESSION pglz; 160\d+ cmdata2 161 Table "public.cmdata2" 162 Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description 163--------+-------------------+-----------+----------+---------+----------+-------------+--------------+------------- 164 f1 | character varying | | | | extended | pglz | | 165 166ALTER TABLE cmdata2 ALTER COLUMN f1 SET STORAGE plain; 167\d+ cmdata2 168 Table "public.cmdata2" 169 Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description 170--------+-------------------+-----------+----------+---------+---------+-------------+--------------+------------- 171 f1 | character varying | | | | plain | pglz | | 172 173INSERT INTO cmdata2 VALUES (repeat('123456789', 800)); 174SELECT pg_column_compression(f1) FROM cmdata2; 175 pg_column_compression 176----------------------- 177 178(1 row) 179 180-- test compression with materialized view 181CREATE MATERIALIZED VIEW compressmv(x) AS SELECT * FROM cmdata1; 182ERROR: relation "cmdata1" does not exist 183LINE 1: ...TE MATERIALIZED VIEW compressmv(x) AS SELECT * FROM cmdata1; 184 ^ 185\d+ compressmv 186SELECT pg_column_compression(f1) FROM cmdata1; 187ERROR: relation "cmdata1" does not exist 188LINE 1: SELECT pg_column_compression(f1) FROM cmdata1; 189 ^ 190SELECT pg_column_compression(x) FROM compressmv; 191ERROR: relation "compressmv" does not exist 192LINE 1: SELECT pg_column_compression(x) FROM compressmv; 193 ^ 194-- test compression with partition 195CREATE TABLE cmpart(f1 text COMPRESSION lz4) PARTITION BY HASH(f1); 196ERROR: compression method lz4 not supported 197DETAIL: This functionality requires the server to be built with lz4 support. 198HINT: You need to rebuild PostgreSQL using --with-lz4. 199CREATE TABLE cmpart1 PARTITION OF cmpart FOR VALUES WITH (MODULUS 2, REMAINDER 0); 200ERROR: relation "cmpart" does not exist 201CREATE TABLE cmpart2(f1 text COMPRESSION pglz); 202ALTER TABLE cmpart ATTACH PARTITION cmpart2 FOR VALUES WITH (MODULUS 2, REMAINDER 1); 203ERROR: relation "cmpart" does not exist 204INSERT INTO cmpart VALUES (repeat('123456789', 1004)); 205ERROR: relation "cmpart" does not exist 206LINE 1: INSERT INTO cmpart VALUES (repeat('123456789', 1004)); 207 ^ 208INSERT INTO cmpart VALUES (repeat('123456789', 4004)); 209ERROR: relation "cmpart" does not exist 210LINE 1: INSERT INTO cmpart VALUES (repeat('123456789', 4004)); 211 ^ 212SELECT pg_column_compression(f1) FROM cmpart1; 213ERROR: relation "cmpart1" does not exist 214LINE 1: SELECT pg_column_compression(f1) FROM cmpart1; 215 ^ 216SELECT pg_column_compression(f1) FROM cmpart2; 217 pg_column_compression 218----------------------- 219(0 rows) 220 221-- test compression with inheritance, error 222CREATE TABLE cminh() INHERITS(cmdata, cmdata1); 223ERROR: relation "cmdata1" does not exist 224CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata); 225NOTICE: merging column "f1" with inherited definition 226ERROR: column "f1" has a compression method conflict 227DETAIL: pglz versus lz4 228-- test default_toast_compression GUC 229SET default_toast_compression = ''; 230ERROR: invalid value for parameter "default_toast_compression": "" 231HINT: Available values: pglz. 232SET default_toast_compression = 'I do not exist compression'; 233ERROR: invalid value for parameter "default_toast_compression": "I do not exist compression" 234HINT: Available values: pglz. 235SET default_toast_compression = 'lz4'; 236ERROR: invalid value for parameter "default_toast_compression": "lz4" 237HINT: Available values: pglz. 238SET default_toast_compression = 'pglz'; 239-- test alter compression method 240ALTER TABLE cmdata ALTER COLUMN f1 SET COMPRESSION lz4; 241ERROR: compression method lz4 not supported 242DETAIL: This functionality requires the server to be built with lz4 support. 243HINT: You need to rebuild PostgreSQL using --with-lz4. 244INSERT INTO cmdata VALUES (repeat('123456789', 4004)); 245\d+ cmdata 246 Table "public.cmdata" 247 Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description 248--------+------+-----------+----------+---------+----------+-------------+--------------+------------- 249 f1 | text | | | | extended | pglz | | 250Indexes: 251 "idx" btree (f1) 252 253SELECT pg_column_compression(f1) FROM cmdata; 254 pg_column_compression 255----------------------- 256 pglz 257 pglz 258(2 rows) 259 260ALTER TABLE cmdata2 ALTER COLUMN f1 SET COMPRESSION default; 261\d+ cmdata2 262 Table "public.cmdata2" 263 Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description 264--------+-------------------+-----------+----------+---------+---------+-------------+--------------+------------- 265 f1 | character varying | | | | plain | | | 266 267-- test alter compression method for materialized views 268ALTER MATERIALIZED VIEW compressmv ALTER COLUMN x SET COMPRESSION lz4; 269ERROR: relation "compressmv" does not exist 270\d+ compressmv 271-- test alter compression method for partitioned tables 272ALTER TABLE cmpart1 ALTER COLUMN f1 SET COMPRESSION pglz; 273ERROR: relation "cmpart1" does not exist 274ALTER TABLE cmpart2 ALTER COLUMN f1 SET COMPRESSION lz4; 275ERROR: compression method lz4 not supported 276DETAIL: This functionality requires the server to be built with lz4 support. 277HINT: You need to rebuild PostgreSQL using --with-lz4. 278-- new data should be compressed with the current compression method 279INSERT INTO cmpart VALUES (repeat('123456789', 1004)); 280ERROR: relation "cmpart" does not exist 281LINE 1: INSERT INTO cmpart VALUES (repeat('123456789', 1004)); 282 ^ 283INSERT INTO cmpart VALUES (repeat('123456789', 4004)); 284ERROR: relation "cmpart" does not exist 285LINE 1: INSERT INTO cmpart VALUES (repeat('123456789', 4004)); 286 ^ 287SELECT pg_column_compression(f1) FROM cmpart1; 288ERROR: relation "cmpart1" does not exist 289LINE 1: SELECT pg_column_compression(f1) FROM cmpart1; 290 ^ 291SELECT pg_column_compression(f1) FROM cmpart2; 292 pg_column_compression 293----------------------- 294(0 rows) 295 296-- VACUUM FULL does not recompress 297SELECT pg_column_compression(f1) FROM cmdata; 298 pg_column_compression 299----------------------- 300 pglz 301 pglz 302(2 rows) 303 304VACUUM FULL cmdata; 305SELECT pg_column_compression(f1) FROM cmdata; 306 pg_column_compression 307----------------------- 308 pglz 309 pglz 310(2 rows) 311 312-- test expression index 313DROP TABLE cmdata2; 314CREATE TABLE cmdata2 (f1 TEXT COMPRESSION pglz, f2 TEXT COMPRESSION lz4); 315ERROR: compression method lz4 not supported 316DETAIL: This functionality requires the server to be built with lz4 support. 317HINT: You need to rebuild PostgreSQL using --with-lz4. 318CREATE UNIQUE INDEX idx1 ON cmdata2 ((f1 || f2)); 319ERROR: relation "cmdata2" does not exist 320INSERT INTO cmdata2 VALUES((SELECT array_agg(md5(g::TEXT))::TEXT FROM 321generate_series(1, 50) g), VERSION()); 322ERROR: relation "cmdata2" does not exist 323LINE 1: INSERT INTO cmdata2 VALUES((SELECT array_agg(md5(g::TEXT))::... 324 ^ 325-- check data is ok 326SELECT length(f1) FROM cmdata; 327 length 328-------- 329 10000 330 36036 331(2 rows) 332 333SELECT length(f1) FROM cmdata1; 334ERROR: relation "cmdata1" does not exist 335LINE 1: SELECT length(f1) FROM cmdata1; 336 ^ 337SELECT length(f1) FROM cmmove1; 338 length 339-------- 340 10000 341(1 row) 342 343SELECT length(f1) FROM cmmove2; 344 length 345-------- 346 10040 347(1 row) 348 349SELECT length(f1) FROM cmmove3; 350 length 351-------- 352 10000 353(1 row) 354 355CREATE TABLE badcompresstbl (a text COMPRESSION I_Do_Not_Exist_Compression); -- fails 356ERROR: invalid compression method "i_do_not_exist_compression" 357CREATE TABLE badcompresstbl (a text); 358ALTER TABLE badcompresstbl ALTER a SET COMPRESSION I_Do_Not_Exist_Compression; -- fails 359ERROR: invalid compression method "i_do_not_exist_compression" 360DROP TABLE badcompresstbl; 361\set HIDE_TOAST_COMPRESSION true 362