-- Default set of tests for TOAST compression, independent on compression

-- methods supported by the build.

CREATE SCHEMA pglz;

SET search_path TO pglz, public;

\set HIDE_TOAST_COMPRESSION false

-- ensure we get stable results regardless of installation's default

SET default_toast_compression = 'pglz';

CREATE INDEX idx ON cmdata(f1);

INSERT INTO cmdata VALUES(repeat('1234567890', 1000));

\d+ cmdata

Table "public.cmdata"

Table "pglz.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);

INSERT INTO cmdata1 VALUES(repeat('1234567890', 1004));

\d+ cmdata1

Table "public.cmdata1"

Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description

--------+------+-----------+----------+---------+----------+-------------+--------------+-------------

f1 | text | | | | extended | lz4 | |

-- 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;

pg_column_compression

-----------------------

lz4

(1 row)

-- decompress data slice

SELECT SUBSTR(f1, 200, 5) FROM cmdata;

substr

--------

01234

(1 row)

SELECT SUBSTR(f1, 2000, 50) FROM cmdata1;

substr

----------------------------------------------------

01234567890123456789012345678901234567890123456789

(1 row)

-- copy with table creation

SELECT * INTO cmmove1 FROM cmdata;

\d+ cmmove1

Table "public.cmmove1"

Table "pglz.cmmove1"

Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description

--------+------+-----------+----------+---------+----------+-------------+--------------+-------------

f1 | text | | | | extended | | |

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;

SELECT pg_column_compression(f1) FROM cmmove3;

pg_column_compression

-----------------------

pglz

lz4

(2 rows)

-- test LIKE INCLUDING COMPRESSION

CREATE TABLE cmdata2 (LIKE cmdata1 INCLUDING COMPRESSION);

\d+ cmdata2

Table "public.cmdata2"

Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description

--------+------+-----------+----------+---------+----------+-------------+--------------+-------------

f1 | text | | | | extended | lz4 | |

DROP TABLE cmdata2;

-- 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;

SELECT pg_column_compression(f1) FROM cmmove2;

pg_column_compression

-----------------------

lz4

(1 row)

-- test externally stored compressed data

CREATE OR REPLACE FUNCTION large_val() RETURNS TEXT LANGUAGE SQL AS

'select array_agg(fipshash(g::text))::text from generate_series(1, 256) g';

pglz

(1 row)

INSERT INTO cmdata1 SELECT large_val() || repeat('a', 4000);

SELECT pg_column_compression(f1) FROM cmdata1;

pg_column_compression

-----------------------

lz4

lz4

(2 rows)

SELECT SUBSTR(f1, 200, 5) FROM cmdata1;

substr

--------

01234

79026

(2 rows)

SELECT SUBSTR(f1, 200, 5) FROM cmdata2;

substr

--------

--test column type update varlena/non-varlena

CREATE TABLE cmdata2 (f1 int);

\d+ cmdata2

Table "public.cmdata2"

Table "pglz.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"

Table "pglz.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"

Table "pglz.cmdata2"

Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description

--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------

f1 | integer | | | | plain | | |

ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE varchar;

ALTER TABLE cmdata2 ALTER COLUMN f1 SET COMPRESSION pglz;

\d+ cmdata2

Table "public.cmdata2"

Table "pglz.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"

Table "pglz.cmdata2"

Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description

--------+-------------------+-----------+----------+---------+---------+-------------+--------------+-------------

f1 | character varying | | | | plain | pglz | |

(1 row)

-- test compression with materialized view

CREATE MATERIALIZED VIEW compressmv(x) AS SELECT * FROM cmdata1;

\d+ compressmv

Materialized view "public.compressmv"

Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description

--------+------+-----------+----------+---------+----------+-------------+--------------+-------------

x | text | | | | extended | | |

View definition:

SELECT f1 AS x

FROM cmdata1;

SELECT pg_column_compression(f1) FROM cmdata1;

pg_column_compression

-----------------------

lz4

lz4

(2 rows)

SELECT pg_column_compression(x) FROM compressmv;

pg_column_compression

-----------------------

lz4

lz4

(2 rows)

-- test compression with partition

CREATE TABLE cmpart(f1 text COMPRESSION lz4) PARTITION BY HASH(f1);

CREATE TABLE cmpart1 PARTITION OF cmpart FOR VALUES WITH (MODULUS 2, REMAINDER 0);

CREATE TABLE cmpart2(f1 text COMPRESSION pglz);

ALTER TABLE cmpart ATTACH PARTITION cmpart2 FOR VALUES WITH (MODULUS 2, REMAINDER 1);

INSERT INTO cmpart VALUES (repeat('123456789', 1004));

INSERT INTO cmpart VALUES (repeat('123456789', 4004));

SELECT pg_column_compression(f1) FROM cmpart1;

pg_column_compression

-----------------------

lz4

(1 row)

SELECT pg_column_compression(f1) FROM cmpart2;

pg_column_compression

-----------------------

pglz

(1 row)

-- test compression with inheritance

CREATE TABLE cminh() INHERITS(cmdata, cmdata1); -- error

NOTICE: merging multiple inherited definitions of column "f1"

ERROR: column "f1" has a compression method conflict

DETAIL: pglz versus lz4

CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata); -- error

NOTICE: merging column "f1" with inherited definition

ERROR: column "f1" has a compression method conflict

DETAIL: pglz versus lz4

CREATE TABLE cmdata3(f1 text);

CREATE TABLE cminh() INHERITS (cmdata, cmdata3);

NOTICE: merging multiple inherited definitions of column "f1"

-- test default_toast_compression GUC

-- suppress machine-dependent details

\set VERBOSITY terse

SET default_toast_compression = '';

ERROR: invalid value for parameter "default_toast_compression": ""

HINT: Available values: pglz, lz4.

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, lz4.

SET default_toast_compression = 'lz4';

SET default_toast_compression = 'pglz';

-- test alter compression method

ALTER TABLE cmdata ALTER COLUMN f1 SET COMPRESSION lz4;

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 | lz4 | |

Indexes:

"idx" btree (f1)

Child tables: cminh

SELECT pg_column_compression(f1) FROM cmdata;

pg_column_compression

-----------------------

pglz

lz4

(2 rows)

\set VERBOSITY default

ALTER TABLE cmdata2 ALTER COLUMN f1 SET COMPRESSION default;

\d+ cmdata2

Table "public.cmdata2"

Table "pglz.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;

\d+ compressmv

Materialized view "public.compressmv"

Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description

--------+------+-----------+----------+---------+----------+-------------+--------------+-------------

x | text | | | | extended | lz4 | |

View definition:

SELECT f1 AS x

FROM cmdata1;

-- test alter compression method for partitioned tables

ALTER TABLE cmpart1 ALTER COLUMN f1 SET COMPRESSION pglz;

ALTER TABLE cmpart2 ALTER COLUMN f1 SET COMPRESSION lz4;

-- new data should be compressed with the current compression method

INSERT INTO cmpart VALUES (repeat('123456789', 1004));

INSERT INTO cmpart VALUES (repeat('123456789', 4004));

SELECT pg_column_compression(f1) FROM cmpart1;

pg_column_compression

-----------------------

lz4

pglz

(2 rows)

SELECT pg_column_compression(f1) FROM cmpart2;

pg_column_compression

-----------------------

pglz

lz4

(2 rows)

DROP TABLE cmdata2;

-- VACUUM FULL does not recompress

SELECT pg_column_compression(f1) FROM cmdata;

pg_column_compression

-----------------------

pglz

lz4

(2 rows)

(1 row)

VACUUM FULL cmdata;

SELECT pg_column_compression(f1) FROM cmdata;

pg_column_compression

-----------------------

pglz

lz4

(2 rows)

(1 row)

-- test expression index

DROP TABLE cmdata2;

CREATE TABLE cmdata2 (f1 TEXT COMPRESSION pglz, f2 TEXT COMPRESSION lz4);

CREATE UNIQUE INDEX idx1 ON cmdata2 ((f1 || f2));

INSERT INTO cmdata2 VALUES((SELECT array_agg(fipshash(g::TEXT))::TEXT FROM

generate_series(1, 50) g), VERSION());

-- check data is ok

SELECT length(f1) FROM cmdata;

length

--------

10000

36036

(2 rows)

SELECT length(f1) FROM cmdata1;

length

--------

10040

12449

(2 rows)

(1 row)

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

10040

(2 rows)

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);