diff options
Diffstat (limited to 'src/test/regress/expected/compression_1.out')
| -rw-r--r-- | src/test/regress/expected/compression_1.out | 356 |
1 files changed, 356 insertions, 0 deletions
diff --git a/src/test/regress/expected/compression_1.out b/src/test/regress/expected/compression_1.out new file mode 100644 index 0000000..c0a4764 --- /dev/null +++ b/src/test/regress/expected/compression_1.out @@ -0,0 +1,356 @@ +\set HIDE_TOAST_COMPRESSION false +-- ensure we get stable results regardless of installation's default +SET default_toast_compression = 'pglz'; +-- test creating table with compression method +CREATE TABLE cmdata(f1 text COMPRESSION pglz); +CREATE INDEX idx ON cmdata(f1); +INSERT INTO cmdata VALUES(repeat('1234567890', 1000)); +\d+ cmdata + Table "public.cmdata" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | text | | | | extended | pglz | | +Indexes: + "idx" btree (f1) + +CREATE TABLE cmdata1(f1 TEXT COMPRESSION lz4); +ERROR: compression method lz4 not supported +DETAIL: This functionality requires the server to be built with lz4 support. +INSERT INTO cmdata1 VALUES(repeat('1234567890', 1004)); +ERROR: relation "cmdata1" does not exist +LINE 1: INSERT INTO cmdata1 VALUES(repeat('1234567890', 1004)); + ^ +\d+ cmdata1 +-- verify stored compression method in the data +SELECT pg_column_compression(f1) FROM cmdata; + pg_column_compression +----------------------- + pglz +(1 row) + +SELECT pg_column_compression(f1) FROM cmdata1; +ERROR: relation "cmdata1" does not exist +LINE 1: SELECT pg_column_compression(f1) FROM cmdata1; + ^ +-- decompress data slice +SELECT SUBSTR(f1, 200, 5) FROM cmdata; + substr +-------- + 01234 +(1 row) + +SELECT SUBSTR(f1, 2000, 50) FROM cmdata1; +ERROR: relation "cmdata1" does not exist +LINE 1: SELECT SUBSTR(f1, 2000, 50) FROM cmdata1; + ^ +-- copy with table creation +SELECT * INTO cmmove1 FROM cmdata; +\d+ cmmove1 + Table "public.cmmove1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | text | | | | extended | | | + +SELECT pg_column_compression(f1) FROM cmmove1; + pg_column_compression +----------------------- + pglz +(1 row) + +-- copy to existing table +CREATE TABLE cmmove3(f1 text COMPRESSION pglz); +INSERT INTO cmmove3 SELECT * FROM cmdata; +INSERT INTO cmmove3 SELECT * FROM cmdata1; +ERROR: relation "cmdata1" does not exist +LINE 1: INSERT INTO cmmove3 SELECT * FROM cmdata1; + ^ +SELECT pg_column_compression(f1) FROM cmmove3; + pg_column_compression +----------------------- + pglz +(1 row) + +-- test LIKE INCLUDING COMPRESSION +CREATE TABLE cmdata2 (LIKE cmdata1 INCLUDING COMPRESSION); +ERROR: relation "cmdata1" does not exist +LINE 1: CREATE TABLE cmdata2 (LIKE cmdata1 INCLUDING COMPRESSION); + ^ +\d+ cmdata2 +DROP TABLE cmdata2; +ERROR: table "cmdata2" does not exist +-- try setting compression for incompressible data type +CREATE TABLE cmdata2 (f1 int COMPRESSION pglz); +ERROR: column data type integer does not support compression +-- update using datum from different table +CREATE TABLE cmmove2(f1 text COMPRESSION pglz); +INSERT INTO cmmove2 VALUES (repeat('1234567890', 1004)); +SELECT pg_column_compression(f1) FROM cmmove2; + pg_column_compression +----------------------- + pglz +(1 row) + +UPDATE cmmove2 SET f1 = cmdata1.f1 FROM cmdata1; +ERROR: relation "cmdata1" does not exist +LINE 1: UPDATE cmmove2 SET f1 = cmdata1.f1 FROM cmdata1; + ^ +SELECT pg_column_compression(f1) FROM cmmove2; + pg_column_compression +----------------------- + pglz +(1 row) + +-- test externally stored compressed data +CREATE OR REPLACE FUNCTION large_val() RETURNS TEXT LANGUAGE SQL AS +'select array_agg(md5(g::text))::text from generate_series(1, 256) g'; +CREATE TABLE cmdata2 (f1 text COMPRESSION pglz); +INSERT INTO cmdata2 SELECT large_val() || repeat('a', 4000); +SELECT pg_column_compression(f1) FROM cmdata2; + pg_column_compression +----------------------- + pglz +(1 row) + +INSERT INTO cmdata1 SELECT large_val() || repeat('a', 4000); +ERROR: relation "cmdata1" does not exist +LINE 1: INSERT INTO cmdata1 SELECT large_val() || repeat('a', 4000); + ^ +SELECT pg_column_compression(f1) FROM cmdata1; +ERROR: relation "cmdata1" does not exist +LINE 1: SELECT pg_column_compression(f1) FROM cmdata1; + ^ +SELECT SUBSTR(f1, 200, 5) FROM cmdata1; +ERROR: relation "cmdata1" does not exist +LINE 1: SELECT SUBSTR(f1, 200, 5) FROM cmdata1; + ^ +SELECT SUBSTR(f1, 200, 5) FROM cmdata2; + substr +-------- + 8f14e +(1 row) + +DROP TABLE cmdata2; +--test column type update varlena/non-varlena +CREATE TABLE cmdata2 (f1 int); +\d+ cmdata2 + Table "public.cmdata2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + f1 | integer | | | | plain | | | + +ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE varchar; +\d+ cmdata2 + Table "public.cmdata2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+-------------------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | character varying | | | | extended | | | + +ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE int USING f1::integer; +\d+ cmdata2 + Table "public.cmdata2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + f1 | integer | | | | plain | | | + +--changing column storage should not impact the compression method +--but the data should not be compressed +ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE varchar; +ALTER TABLE cmdata2 ALTER COLUMN f1 SET COMPRESSION pglz; +\d+ cmdata2 + Table "public.cmdata2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+-------------------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | character varying | | | | extended | pglz | | + +ALTER TABLE cmdata2 ALTER COLUMN f1 SET STORAGE plain; +\d+ cmdata2 + Table "public.cmdata2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+-------------------+-----------+----------+---------+---------+-------------+--------------+------------- + f1 | character varying | | | | plain | pglz | | + +INSERT INTO cmdata2 VALUES (repeat('123456789', 800)); +SELECT pg_column_compression(f1) FROM cmdata2; + pg_column_compression +----------------------- + +(1 row) + +-- test compression with materialized view +CREATE MATERIALIZED VIEW compressmv(x) AS SELECT * FROM cmdata1; +ERROR: relation "cmdata1" does not exist +LINE 1: ...TE MATERIALIZED VIEW compressmv(x) AS SELECT * FROM cmdata1; + ^ +\d+ compressmv +SELECT pg_column_compression(f1) FROM cmdata1; +ERROR: relation "cmdata1" does not exist +LINE 1: SELECT pg_column_compression(f1) FROM cmdata1; + ^ +SELECT pg_column_compression(x) FROM compressmv; +ERROR: relation "compressmv" does not exist +LINE 1: SELECT pg_column_compression(x) FROM compressmv; + ^ +-- test compression with partition +CREATE TABLE cmpart(f1 text COMPRESSION lz4) PARTITION BY HASH(f1); +ERROR: compression method lz4 not supported +DETAIL: This functionality requires the server to be built with lz4 support. +CREATE TABLE cmpart1 PARTITION OF cmpart FOR VALUES WITH (MODULUS 2, REMAINDER 0); +ERROR: relation "cmpart" does not exist +CREATE TABLE cmpart2(f1 text COMPRESSION pglz); +ALTER TABLE cmpart ATTACH PARTITION cmpart2 FOR VALUES WITH (MODULUS 2, REMAINDER 1); +ERROR: relation "cmpart" does not exist +INSERT INTO cmpart VALUES (repeat('123456789', 1004)); +ERROR: relation "cmpart" does not exist +LINE 1: INSERT INTO cmpart VALUES (repeat('123456789', 1004)); + ^ +INSERT INTO cmpart VALUES (repeat('123456789', 4004)); +ERROR: relation "cmpart" does not exist +LINE 1: INSERT INTO cmpart VALUES (repeat('123456789', 4004)); + ^ +SELECT pg_column_compression(f1) FROM cmpart1; +ERROR: relation "cmpart1" does not exist +LINE 1: SELECT pg_column_compression(f1) FROM cmpart1; + ^ +SELECT pg_column_compression(f1) FROM cmpart2; + pg_column_compression +----------------------- +(0 rows) + +-- test compression with inheritance, error +CREATE TABLE cminh() INHERITS(cmdata, cmdata1); +ERROR: relation "cmdata1" does not exist +CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata); +NOTICE: merging column "f1" with inherited definition +ERROR: column "f1" has a compression method conflict +DETAIL: pglz versus lz4 +-- test default_toast_compression GUC +SET default_toast_compression = ''; +ERROR: invalid value for parameter "default_toast_compression": "" +HINT: Available values: pglz. +SET default_toast_compression = 'I do not exist compression'; +ERROR: invalid value for parameter "default_toast_compression": "I do not exist compression" +HINT: Available values: pglz. +SET default_toast_compression = 'lz4'; +ERROR: invalid value for parameter "default_toast_compression": "lz4" +HINT: Available values: pglz. +SET default_toast_compression = 'pglz'; +-- test alter compression method +ALTER TABLE cmdata ALTER COLUMN f1 SET COMPRESSION lz4; +ERROR: compression method lz4 not supported +DETAIL: This functionality requires the server to be built with lz4 support. +INSERT INTO cmdata VALUES (repeat('123456789', 4004)); +\d+ cmdata + Table "public.cmdata" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | text | | | | extended | pglz | | +Indexes: + "idx" btree (f1) + +SELECT pg_column_compression(f1) FROM cmdata; + pg_column_compression +----------------------- + pglz + pglz +(2 rows) + +ALTER TABLE cmdata2 ALTER COLUMN f1 SET COMPRESSION default; +\d+ cmdata2 + Table "public.cmdata2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+-------------------+-----------+----------+---------+---------+-------------+--------------+------------- + f1 | character varying | | | | plain | | | + +-- test alter compression method for materialized views +ALTER MATERIALIZED VIEW compressmv ALTER COLUMN x SET COMPRESSION lz4; +ERROR: relation "compressmv" does not exist +\d+ compressmv +-- test alter compression method for partitioned tables +ALTER TABLE cmpart1 ALTER COLUMN f1 SET COMPRESSION pglz; +ERROR: relation "cmpart1" does not exist +ALTER TABLE cmpart2 ALTER COLUMN f1 SET COMPRESSION lz4; +ERROR: compression method lz4 not supported +DETAIL: This functionality requires the server to be built with lz4 support. +-- new data should be compressed with the current compression method +INSERT INTO cmpart VALUES (repeat('123456789', 1004)); +ERROR: relation "cmpart" does not exist +LINE 1: INSERT INTO cmpart VALUES (repeat('123456789', 1004)); + ^ +INSERT INTO cmpart VALUES (repeat('123456789', 4004)); +ERROR: relation "cmpart" does not exist +LINE 1: INSERT INTO cmpart VALUES (repeat('123456789', 4004)); + ^ +SELECT pg_column_compression(f1) FROM cmpart1; +ERROR: relation "cmpart1" does not exist +LINE 1: SELECT pg_column_compression(f1) FROM cmpart1; + ^ +SELECT pg_column_compression(f1) FROM cmpart2; + pg_column_compression +----------------------- +(0 rows) + +-- VACUUM FULL does not recompress +SELECT pg_column_compression(f1) FROM cmdata; + pg_column_compression +----------------------- + pglz + pglz +(2 rows) + +VACUUM FULL cmdata; +SELECT pg_column_compression(f1) FROM cmdata; + pg_column_compression +----------------------- + pglz + pglz +(2 rows) + +-- test expression index +DROP TABLE cmdata2; +CREATE TABLE cmdata2 (f1 TEXT COMPRESSION pglz, f2 TEXT COMPRESSION lz4); +ERROR: compression method lz4 not supported +DETAIL: This functionality requires the server to be built with lz4 support. +CREATE UNIQUE INDEX idx1 ON cmdata2 ((f1 || f2)); +ERROR: relation "cmdata2" does not exist +INSERT INTO cmdata2 VALUES((SELECT array_agg(md5(g::TEXT))::TEXT FROM +generate_series(1, 50) g), VERSION()); +ERROR: relation "cmdata2" does not exist +LINE 1: INSERT INTO cmdata2 VALUES((SELECT array_agg(md5(g::TEXT))::... + ^ +-- check data is ok +SELECT length(f1) FROM cmdata; + length +-------- + 10000 + 36036 +(2 rows) + +SELECT length(f1) FROM cmdata1; +ERROR: relation "cmdata1" does not exist +LINE 1: SELECT length(f1) FROM cmdata1; + ^ +SELECT length(f1) FROM cmmove1; + length +-------- + 10000 +(1 row) + +SELECT length(f1) FROM cmmove2; + length +-------- + 10040 +(1 row) + +SELECT length(f1) FROM cmmove3; + length +-------- + 10000 +(1 row) + +CREATE TABLE badcompresstbl (a text COMPRESSION I_Do_Not_Exist_Compression); -- fails +ERROR: invalid compression method "i_do_not_exist_compression" +CREATE TABLE badcompresstbl (a text); +ALTER TABLE badcompresstbl ALTER a SET COMPRESSION I_Do_Not_Exist_Compression; -- fails +ERROR: invalid compression method "i_do_not_exist_compression" +DROP TABLE badcompresstbl; +\set HIDE_TOAST_COMPRESSION true |