On Wed, Jun 11, 2025 at 11:42:02AM +0900, Michael Paquier wrote: > The split of the tests is not completely clean as presented in your > patch, though. Your patch only does a copy-paste of the original > file. Some of the basic tests of compression.sql check the > interactions between the use of two compression methods, and the > "basic" compression.sql could just cut them and rely on the LZ4 > scripts to do the job, because we want two active different > compression methods for these scenarios. For example, cmdata1 > switched to use pglz has little uses. The trick is to have a minimal > set of tests to minimize the run time, while we don't lose in > coverage. Coverage report numbers are useful to compile when it comes > to such exercises, even if it can be an ant's work sometimes.
I have no idea yet about the fate of the other TOAST patches I have proposed for this commit fest, but let's move on with this part of the refactoring by splitting the TOAST regression tests for LZ4 and pglz, with the new pg_compression_available() that would reduce the diffs with the alternate outputs. This has required a bit more work than I suspected. Based on my notes, first for pg_compression_available(): - Code moved to misc.c, with comments related to TOAST removed. - Addition of gzip as an acceptable value. - Error if the compression method is unknown. - Some regression tests. - Documentation should list the functions alphabetically. Then for the refactoring of the tests, a few notes: - There is no need for cmdata1 in compression.sql, using the same compression method as cmdata, aka pglz. So we can trim down the tests. - In compression.sql, we can remove cmmove2, cmmove3 and cmdata2 which have a compression method of pglz, and that we want to check where the origin has LZ4 data. These should be only in compression_lz4.sql, perhaps also in the zstd portion if needed later for your patch. - The error cases with I_Do_Not_Exist_Compression at the bottom of compression.sql can be kept, we don't need them in compression_lz4.sql. - It would be tempting to keep the test for LIKE INCLUDING COMPRESSION in compression.sql, but we cannot do that as there is a dependency with default_toast_compression so we want the GUC at pglz but the table we are copying the data from at LZ4. compression.sql, there is no need for it to depend on LZ4. - The tests related to cmdata2 depend on LZ4 TOAST, which were a bit duplicated. - "test column type update varlena/non-varlena" was duplicated. Same for "changing column storage should not impact the compression method". - The materialized view test in compression.sql depends on LZ4, can be moved to compression_lz4.sql. - The test with partitions and compression methods expects multiple compression methods, can be moved to compression_lz4.sql - "test alter compression method" expects two compression methods, can be moved to compression_lz4.sql. - The tests with SET default_toast_compression report a hint with the list of values supported. This is not portable because the list of values depends on what the build supports. We should use a trick based on "\set VERBOSITY terse", removing the HINT to reduce the noise. - The tables specific to pglz and lz4 data are both still required in compression_lz4.sql, for one test with inheritance. I have renamed both to cmdata_pglz and cmdata_lz4, for clarity. At the end, the gain in diffs is here per the following numbers in the attached 0002 as we remove the alternal output of compression.sql when lz4 is disabled: 7 files changed, 319 insertions(+), 724 deletions(-) Attached are two patches for all that: - 0001: Introduction of the new function pg_compression_available(). - 0002: Refactoring of the TOAST compression tests. With this infrastructure in place, the addition of a new TOAST compression method becomes easier for the test part: no more cross-build specific diffs. Thought, comments or objections? -- Michael
From 776ef0cfffa2a774bf255c4707da4f2af2b7bd06 Mon Sep 17 00:00:00 2001
From: Michael Paquier <mich...@paquier.xyz>
Date: Wed, 16 Jul 2025 12:06:57 +0900
Subject: [PATCH 1/2] Add function pg_compression_available
This is able to check if compression methods are available in a given
build, in a platform and build-transparent manner. The values that can
be checked in the backend are: gzip, pglz, lz4 and zstd.
This function will be useful in an upcoming patch that refactors the
TOAST tests.
---
src/include/catalog/pg_proc.dat | 4 ++
src/backend/utils/adt/misc.c | 51 ++++++++++++++++++++
src/test/regress/expected/misc_functions.out | 15 ++++++
src/test/regress/sql/misc_functions.sql | 5 ++
doc/src/sgml/func.sgml | 18 +++++++
5 files changed, 93 insertions(+)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 1fc19146f467..6b3aaba4a90e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6876,6 +6876,10 @@
{ oid => '315', descr => 'Is JIT compilation available in this session?',
proname => 'pg_jit_available', provolatile => 'v', prorettype => 'bool',
proargtypes => '', prosrc => 'pg_jit_available' },
+{ oid => 9474, descr => 'Is compression method available?',
+ proname => 'pg_compression_available', prokind => 'f',
+ provolatile => 'i', prorettype => 'bool', proargtypes => 'text',
+ prosrc => 'pg_compression_available' },
{ oid => '2971', descr => 'convert boolean to text',
proname => 'text', prorettype => 'text', proargtypes => 'bool',
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 6fcfd031428e..a41b19c6250a 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -1122,3 +1122,54 @@ any_value_transfn(PG_FUNCTION_ARGS)
{
PG_RETURN_DATUM(PG_GETARG_DATUM(0));
}
+
+/*
+ * pg_compression_available
+ *
+ * True if the named compression method is available in this server.
+ */
+Datum
+pg_compression_available(PG_FUNCTION_ARGS)
+{
+ text *name = PG_GETARG_TEXT_PP(0);
+ char *cname = downcase_truncate_identifier(text_to_cstring(name),
+ NAMEDATALEN, false);
+
+ /* pglz is always there */
+ if (strcmp(cname, "pglz") == 0)
+ PG_RETURN_BOOL(true);
+
+ if (strcmp(cname, "gzip") == 0)
+ {
+#ifdef HAVE_LIBZ
+ PG_RETURN_BOOL(true);
+#else
+ PG_RETURN_BOOL(false);
+#endif
+ }
+
+ if (strcmp(cname, "lz4") == 0)
+ {
+#ifdef USE_LZ4
+ PG_RETURN_BOOL(true);
+#else
+ PG_RETURN_BOOL(false);
+#endif
+ }
+
+ if (strcmp(cname, "zstd") == 0)
+ {
+#ifdef USE_ZSTD
+ PG_RETURN_BOOL(true);
+#else
+ PG_RETURN_BOOL(false);
+#endif
+ }
+
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("%s compression is not supported by this build",
+ cname)));
+
+ PG_RETURN_BOOL(false); /* keep compiler quiet */
+}
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index c3b2b9d86034..8158839fc6f2 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -918,3 +918,18 @@ SELECT test_relpath();
SELECT pg_replication_origin_create('regress_' || repeat('a', 505));
ERROR: replication origin name is too long
DETAIL: Replication origin names must be no longer than 512 bytes.
+-- pg_compression_available
+SELECT pg_compression_available(NULL);
+ pg_compression_available
+--------------------------
+
+(1 row)
+
+SELECT pg_compression_available('pglz');
+ pg_compression_available
+--------------------------
+ t
+(1 row)
+
+SELECT pg_compression_available('non_existent');
+ERROR: non_existent compression is not supported by this build
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 23792c4132a1..5a59eb35197b 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -414,3 +414,8 @@ SELECT test_relpath();
-- pg_replication_origin.roname limit
SELECT pg_replication_origin_create('regress_' || repeat('a', 505));
+
+-- pg_compression_available
+SELECT pg_compression_available(NULL);
+SELECT pg_compression_available('pglz');
+SELECT pg_compression_available('non_existent');
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f5a0e0954a15..ddeda431b2be 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25020,6 +25020,24 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_compression_available</primary>
+ </indexterm>
+ <function>pg_compression_available</function> ( <type>text</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Returns <literal>true</literal> if the given compression method name
+ is supported in the <productname>PostgreSQL</productname> build.
+ The supported compression methods that can be checked are
+ <literal>gzip</literal>, <literal>pglz</literal> (always available),
+ <literal>lz4</literal> and <literal>zstd</literal>.
+ </para>
+ </entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
--
2.50.0
From 11dbd68b454db53721f21f3b7624b9f95fb64fc6 Mon Sep 17 00:00:00 2001
From: Michael Paquier <mich...@paquier.xyz>
Date: Wed, 16 Jul 2025 13:42:54 +0900
Subject: [PATCH 2/2] Split TOAST compression tests into two files
The parts specific to LZ4 and interactions between two different TOAST
compression methods are moved to a new file, called compression_lz4.
The new test is skipped if the build does not support LZ4 compression.
Both files are independent, with the same coverage as previously.
---
src/test/regress/expected/compression.out | 235 +-----------
src/test/regress/expected/compression_1.out | 360 ------------------
src/test/regress/expected/compression_lz4.out | 247 ++++++++++++
.../regress/expected/compression_lz4_1.out | 6 +
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/compression.sql | 84 +---
.../{compression.sql => compression_lz4.sql} | 116 +++---
7 files changed, 326 insertions(+), 724 deletions(-)
delete mode 100644 src/test/regress/expected/compression_1.out
create mode 100644 src/test/regress/expected/compression_lz4.out
create mode 100644 src/test/regress/expected/compression_lz4_1.out
copy src/test/regress/sql/{compression.sql => compression_lz4.sql} (50%)
diff --git a/src/test/regress/expected/compression.out b/src/test/regress/expected/compression.out
index 4dd9ee7200d1..09f198149aa4 100644
--- a/src/test/regress/expected/compression.out
+++ b/src/test/regress/expected/compression.out
@@ -1,3 +1,7 @@
+-- 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';
@@ -6,21 +10,13 @@ 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"
+ 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
@@ -28,12 +24,6 @@ SELECT pg_column_compression(f1) FROM cmdata;
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
@@ -41,16 +31,10 @@ SELECT SUBSTR(f1, 200, 5) FROM cmdata;
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 | | |
@@ -61,45 +45,9 @@ SELECT pg_column_compression(f1) FROM cmmove1;
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';
@@ -111,21 +59,6 @@ SELECT pg_column_compression(f1) FROM cmdata2;
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
--------
@@ -136,21 +69,21 @@ DROP TABLE cmdata2;
--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 | | |
@@ -160,14 +93,14 @@ ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE int USING f1::integer;
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 | |
@@ -179,164 +112,47 @@ SELECT pg_column_compression(f1) FROM cmdata2;
(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
@@ -344,19 +160,6 @@ SELECT length(f1) FROM cmmove1;
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);
diff --git a/src/test/regress/expected/compression_1.out b/src/test/regress/expected/compression_1.out
deleted file mode 100644
index 7bd7642b4b94..000000000000
--- a/src/test/regress/expected/compression_1.out
+++ /dev/null
@@ -1,360 +0,0 @@
-\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(fipshash(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
---------
- 79026
-(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
-CREATE TABLE cminh() INHERITS(cmdata, cmdata1); -- error
-ERROR: relation "cmdata1" does not exist
-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
-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)
-Child tables: cminh
-
-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(fipshash(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(fipshash(g::TEX...
- ^
--- 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
diff --git a/src/test/regress/expected/compression_lz4.out b/src/test/regress/expected/compression_lz4.out
new file mode 100644
index 000000000000..6c8309bc7b14
--- /dev/null
+++ b/src/test/regress/expected/compression_lz4.out
@@ -0,0 +1,247 @@
+-- Tests for TOAST compression with lz4
+SELECT NOT(pg_compression_available('lz4')) AS skip_test \gset
+\if :skip_test
+ \echo '*** skipping lz4 tests (lz4 not available) ***'
+ \quit
+\endif
+CREATE SCHEMA lz4;
+SET search_path TO lz4, public;
+\set HIDE_TOAST_COMPRESSION false
+-- Ensure we get stable results regardless of installation's default
+-- We rely on this GUC value for a few tests.
+SET default_toast_compression = 'pglz';
+-- test creating table with compression method
+CREATE TABLE cmdata_pglz(f1 text COMPRESSION pglz);
+CREATE INDEX idx ON cmdata_pglz(f1);
+INSERT INTO cmdata_pglz VALUES(repeat('1234567890', 1000));
+\d+ cmdata
+CREATE TABLE cmdata_lz4(f1 TEXT COMPRESSION lz4);
+INSERT INTO cmdata_lz4 VALUES(repeat('1234567890', 1004));
+\d+ cmdata1
+-- verify stored compression method in the data
+SELECT pg_column_compression(f1) FROM cmdata_lz4;
+ pg_column_compression
+-----------------------
+ lz4
+(1 row)
+
+-- decompress data slice
+SELECT SUBSTR(f1, 200, 5) FROM cmdata_pglz;
+ substr
+--------
+ 01234
+(1 row)
+
+SELECT SUBSTR(f1, 2000, 50) FROM cmdata_lz4;
+ substr
+----------------------------------------------------
+ 01234567890123456789012345678901234567890123456789
+(1 row)
+
+-- copy with table creation
+SELECT * INTO cmmove1 FROM cmdata_lz4;
+\d+ cmmove1
+ Table "lz4.cmmove1"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ f1 | text | | | | extended | | |
+
+SELECT pg_column_compression(f1) FROM cmmove1;
+ pg_column_compression
+-----------------------
+ lz4
+(1 row)
+
+-- test LIKE INCLUDING COMPRESSION. The GUC default_toast_compression
+-- has no effect, the compression method from the table being copied.
+CREATE TABLE cmdata2 (LIKE cmdata_lz4 INCLUDING COMPRESSION);
+\d+ cmdata2
+ Table "lz4.cmdata2"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ f1 | text | | | | extended | lz4 | |
+
+DROP TABLE cmdata2;
+-- copy to existing table
+CREATE TABLE cmmove3(f1 text COMPRESSION pglz);
+INSERT INTO cmmove3 SELECT * FROM cmdata_pglz;
+INSERT INTO cmmove3 SELECT * FROM cmdata_lz4;
+SELECT pg_column_compression(f1) FROM cmmove3;
+ pg_column_compression
+-----------------------
+ pglz
+ lz4
+(2 rows)
+
+-- update using datum from different table with LZ4 data.
+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 = cmdata_lz4.f1 FROM cmdata_lz4;
+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';
+CREATE TABLE cmdata2 (f1 text COMPRESSION lz4);
+INSERT INTO cmdata2 SELECT large_val() || repeat('a', 4000);
+SELECT pg_column_compression(f1) FROM cmdata2;
+ pg_column_compression
+-----------------------
+ lz4
+(1 row)
+
+SELECT SUBSTR(f1, 200, 5) FROM cmdata2;
+ substr
+--------
+ 79026
+(1 row)
+
+DROP TABLE cmdata2;
+-- test compression with materialized view
+CREATE MATERIALIZED VIEW compressmv(x) AS SELECT * FROM cmdata_lz4;
+\d+ compressmv
+ Materialized view "lz4.compressmv"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ x | text | | | | extended | | |
+View definition:
+ SELECT f1 AS x
+ FROM cmdata_lz4;
+
+SELECT pg_column_compression(f1) FROM cmdata_lz4;
+ pg_column_compression
+-----------------------
+ lz4
+(1 row)
+
+SELECT pg_column_compression(x) FROM compressmv;
+ pg_column_compression
+-----------------------
+ lz4
+(1 row)
+
+-- 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_pglz, cmdata_lz4); -- 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_pglz); -- 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_pglz, cmdata3);
+NOTICE: merging multiple inherited definitions of column "f1"
+-- test default_toast_compression GUC
+SET default_toast_compression = 'lz4';
+-- test alter compression method
+ALTER TABLE cmdata_pglz ALTER COLUMN f1 SET COMPRESSION lz4;
+INSERT INTO cmdata_pglz VALUES (repeat('123456789', 4004));
+\d+ cmdata
+SELECT pg_column_compression(f1) FROM cmdata_pglz;
+ pg_column_compression
+-----------------------
+ pglz
+ lz4
+(2 rows)
+
+ALTER TABLE cmdata_pglz ALTER COLUMN f1 SET COMPRESSION pglz;
+-- test alter compression method for materialized views
+ALTER MATERIALIZED VIEW compressmv ALTER COLUMN x SET COMPRESSION lz4;
+\d+ compressmv
+ Materialized view "lz4.compressmv"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ x | text | | | | extended | lz4 | |
+View definition:
+ SELECT f1 AS x
+ FROM cmdata_lz4;
+
+-- 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)
+
+-- test expression index
+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_pglz;
+ length
+--------
+ 10000
+ 36036
+(2 rows)
+
+SELECT length(f1) FROM cmdata_lz4;
+ length
+--------
+ 10040
+(1 row)
+
+SELECT length(f1) FROM cmmove1;
+ length
+--------
+ 10040
+(1 row)
+
+SELECT length(f1) FROM cmmove2;
+ length
+--------
+ 10040
+(1 row)
+
+SELECT length(f1) FROM cmmove3;
+ length
+--------
+ 10000
+ 10040
+(2 rows)
+
+\set HIDE_TOAST_COMPRESSION true
diff --git a/src/test/regress/expected/compression_lz4_1.out b/src/test/regress/expected/compression_lz4_1.out
new file mode 100644
index 000000000000..daf83614fade
--- /dev/null
+++ b/src/test/regress/expected/compression_lz4_1.out
@@ -0,0 +1,6 @@
+-- Tests for TOAST compression with lz4
+SELECT NOT(pg_compression_available('lz4')) AS skip_test \gset
+\if :skip_test
+ \echo '*** skipping lz4 tests (lz4 not available) ***'
+*** skipping lz4 tests (lz4 not available) ***
+ \quit
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index a424be2a6bf0..fbffc67ae601 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -123,7 +123,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion tr
# The stats test resets stats, so nothing else needing stats access can be in
# this group.
# ----------
-test: partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain compression memoize stats predicate numa
+test: partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain compression compression_lz4 memoize stats predicate numa
# event_trigger depends on create_am and cannot run concurrently with
# any test that runs DDL
diff --git a/src/test/regress/sql/compression.sql b/src/test/regress/sql/compression.sql
index 490595fcfb26..ce5ea37a660c 100644
--- a/src/test/regress/sql/compression.sql
+++ b/src/test/regress/sql/compression.sql
@@ -1,3 +1,8 @@
+-- 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
@@ -8,53 +13,27 @@ CREATE TABLE cmdata(f1 text COMPRESSION pglz);
CREATE INDEX idx ON cmdata(f1);
INSERT INTO cmdata VALUES(repeat('1234567890', 1000));
\d+ cmdata
-CREATE TABLE cmdata1(f1 TEXT COMPRESSION lz4);
-INSERT INTO cmdata1 VALUES(repeat('1234567890', 1004));
-\d+ cmdata1
-- verify stored compression method in the data
SELECT pg_column_compression(f1) FROM cmdata;
-SELECT pg_column_compression(f1) FROM cmdata1;
-- decompress data slice
SELECT SUBSTR(f1, 200, 5) FROM cmdata;
-SELECT SUBSTR(f1, 2000, 50) FROM cmdata1;
-- copy with table creation
SELECT * INTO cmmove1 FROM cmdata;
\d+ cmmove1
SELECT pg_column_compression(f1) FROM cmmove1;
--- 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;
-
--- test LIKE INCLUDING COMPRESSION
-CREATE TABLE cmdata2 (LIKE cmdata1 INCLUDING COMPRESSION);
-\d+ cmdata2
-DROP TABLE cmdata2;
-
-- try setting compression for incompressible data type
CREATE TABLE cmdata2 (f1 int COMPRESSION pglz);
--- 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;
-UPDATE cmmove2 SET f1 = cmdata1.f1 FROM cmdata1;
-SELECT pg_column_compression(f1) FROM cmmove2;
-
-- 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';
CREATE TABLE cmdata2 (f1 text COMPRESSION pglz);
INSERT INTO cmdata2 SELECT large_val() || repeat('a', 4000);
SELECT pg_column_compression(f1) FROM cmdata2;
-INSERT INTO cmdata1 SELECT large_val() || repeat('a', 4000);
-SELECT pg_column_compression(f1) FROM cmdata1;
-SELECT SUBSTR(f1, 200, 5) FROM cmdata1;
SELECT SUBSTR(f1, 200, 5) FROM cmdata2;
DROP TABLE cmdata2;
@@ -76,76 +55,31 @@ ALTER TABLE cmdata2 ALTER COLUMN f1 SET STORAGE plain;
INSERT INTO cmdata2 VALUES (repeat('123456789', 800));
SELECT pg_column_compression(f1) FROM cmdata2;
--- test compression with materialized view
-CREATE MATERIALIZED VIEW compressmv(x) AS SELECT * FROM cmdata1;
-\d+ compressmv
-SELECT pg_column_compression(f1) FROM cmdata1;
-SELECT pg_column_compression(x) FROM compressmv;
-
--- 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;
-SELECT pg_column_compression(f1) FROM cmpart2;
-
-- test compression with inheritance
-CREATE TABLE cminh() INHERITS(cmdata, cmdata1); -- error
-CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata); -- error
CREATE TABLE cmdata3(f1 text);
CREATE TABLE cminh() INHERITS (cmdata, cmdata3);
-- test default_toast_compression GUC
+-- suppress machine-dependent details
+\set VERBOSITY terse
SET default_toast_compression = '';
SET default_toast_compression = 'I do not exist compression';
-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
-SELECT pg_column_compression(f1) FROM cmdata;
+\set VERBOSITY default
ALTER TABLE cmdata2 ALTER COLUMN f1 SET COMPRESSION default;
\d+ cmdata2
--- test alter compression method for materialized views
-ALTER MATERIALIZED VIEW compressmv ALTER COLUMN x SET COMPRESSION lz4;
-\d+ compressmv
-
--- 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;
-SELECT pg_column_compression(f1) FROM cmpart2;
+DROP TABLE cmdata2;
-- VACUUM FULL does not recompress
SELECT pg_column_compression(f1) FROM cmdata;
VACUUM FULL cmdata;
SELECT pg_column_compression(f1) FROM cmdata;
--- 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;
-SELECT length(f1) FROM cmdata1;
SELECT length(f1) FROM cmmove1;
-SELECT length(f1) FROM cmmove2;
-SELECT length(f1) FROM cmmove3;
CREATE TABLE badcompresstbl (a text COMPRESSION I_Do_Not_Exist_Compression); -- fails
CREATE TABLE badcompresstbl (a text);
diff --git a/src/test/regress/sql/compression.sql b/src/test/regress/sql/compression_lz4.sql
similarity index 50%
copy from src/test/regress/sql/compression.sql
copy to src/test/regress/sql/compression_lz4.sql
index 490595fcfb26..37c247742c90 100644
--- a/src/test/regress/sql/compression.sql
+++ b/src/test/regress/sql/compression_lz4.sql
@@ -1,85 +1,73 @@
+-- Tests for TOAST compression with lz4
+
+SELECT NOT(pg_compression_available('lz4')) AS skip_test \gset
+\if :skip_test
+ \echo '*** skipping lz4 tests (lz4 not available) ***'
+ \quit
+\endif
+
+CREATE SCHEMA lz4;
+SET search_path TO lz4, public;
+
\set HIDE_TOAST_COMPRESSION false
--- ensure we get stable results regardless of installation's default
+-- Ensure we get stable results regardless of installation's default
+-- We rely on this GUC value for a few tests.
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));
+CREATE TABLE cmdata_pglz(f1 text COMPRESSION pglz);
+CREATE INDEX idx ON cmdata_pglz(f1);
+INSERT INTO cmdata_pglz VALUES(repeat('1234567890', 1000));
\d+ cmdata
-CREATE TABLE cmdata1(f1 TEXT COMPRESSION lz4);
-INSERT INTO cmdata1 VALUES(repeat('1234567890', 1004));
+CREATE TABLE cmdata_lz4(f1 TEXT COMPRESSION lz4);
+INSERT INTO cmdata_lz4 VALUES(repeat('1234567890', 1004));
\d+ cmdata1
-- verify stored compression method in the data
-SELECT pg_column_compression(f1) FROM cmdata;
-SELECT pg_column_compression(f1) FROM cmdata1;
+SELECT pg_column_compression(f1) FROM cmdata_lz4;
-- decompress data slice
-SELECT SUBSTR(f1, 200, 5) FROM cmdata;
-SELECT SUBSTR(f1, 2000, 50) FROM cmdata1;
+SELECT SUBSTR(f1, 200, 5) FROM cmdata_pglz;
+SELECT SUBSTR(f1, 2000, 50) FROM cmdata_lz4;
-- copy with table creation
-SELECT * INTO cmmove1 FROM cmdata;
+SELECT * INTO cmmove1 FROM cmdata_lz4;
\d+ cmmove1
SELECT pg_column_compression(f1) FROM cmmove1;
--- 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;
-
--- test LIKE INCLUDING COMPRESSION
-CREATE TABLE cmdata2 (LIKE cmdata1 INCLUDING COMPRESSION);
+-- test LIKE INCLUDING COMPRESSION. The GUC default_toast_compression
+-- has no effect, the compression method from the table being copied.
+CREATE TABLE cmdata2 (LIKE cmdata_lz4 INCLUDING COMPRESSION);
\d+ cmdata2
DROP TABLE cmdata2;
--- try setting compression for incompressible data type
-CREATE TABLE cmdata2 (f1 int COMPRESSION pglz);
+-- copy to existing table
+CREATE TABLE cmmove3(f1 text COMPRESSION pglz);
+INSERT INTO cmmove3 SELECT * FROM cmdata_pglz;
+INSERT INTO cmmove3 SELECT * FROM cmdata_lz4;
+SELECT pg_column_compression(f1) FROM cmmove3;
--- update using datum from different table
+-- update using datum from different table with LZ4 data.
CREATE TABLE cmmove2(f1 text COMPRESSION pglz);
INSERT INTO cmmove2 VALUES (repeat('1234567890', 1004));
SELECT pg_column_compression(f1) FROM cmmove2;
-UPDATE cmmove2 SET f1 = cmdata1.f1 FROM cmdata1;
+UPDATE cmmove2 SET f1 = cmdata_lz4.f1 FROM cmdata_lz4;
SELECT pg_column_compression(f1) FROM cmmove2;
-- 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';
-CREATE TABLE cmdata2 (f1 text COMPRESSION pglz);
+CREATE TABLE cmdata2 (f1 text COMPRESSION lz4);
INSERT INTO cmdata2 SELECT large_val() || repeat('a', 4000);
SELECT pg_column_compression(f1) FROM cmdata2;
-INSERT INTO cmdata1 SELECT large_val() || repeat('a', 4000);
-SELECT pg_column_compression(f1) FROM cmdata1;
-SELECT SUBSTR(f1, 200, 5) FROM cmdata1;
SELECT SUBSTR(f1, 200, 5) FROM cmdata2;
DROP TABLE cmdata2;
---test column type update varlena/non-varlena
-CREATE TABLE cmdata2 (f1 int);
-\d+ cmdata2
-ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE varchar;
-\d+ cmdata2
-ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE int USING f1::integer;
-\d+ cmdata2
-
---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
-ALTER TABLE cmdata2 ALTER COLUMN f1 SET STORAGE plain;
-\d+ cmdata2
-INSERT INTO cmdata2 VALUES (repeat('123456789', 800));
-SELECT pg_column_compression(f1) FROM cmdata2;
-
-- test compression with materialized view
-CREATE MATERIALIZED VIEW compressmv(x) AS SELECT * FROM cmdata1;
+CREATE MATERIALIZED VIEW compressmv(x) AS SELECT * FROM cmdata_lz4;
\d+ compressmv
-SELECT pg_column_compression(f1) FROM cmdata1;
+SELECT pg_column_compression(f1) FROM cmdata_lz4;
SELECT pg_column_compression(x) FROM compressmv;
-- test compression with partition
@@ -94,25 +82,20 @@ SELECT pg_column_compression(f1) FROM cmpart1;
SELECT pg_column_compression(f1) FROM cmpart2;
-- test compression with inheritance
-CREATE TABLE cminh() INHERITS(cmdata, cmdata1); -- error
-CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata); -- error
+CREATE TABLE cminh() INHERITS(cmdata_pglz, cmdata_lz4); -- error
+CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata_pglz); -- error
CREATE TABLE cmdata3(f1 text);
-CREATE TABLE cminh() INHERITS (cmdata, cmdata3);
+CREATE TABLE cminh() INHERITS (cmdata_pglz, cmdata3);
-- test default_toast_compression GUC
-SET default_toast_compression = '';
-SET default_toast_compression = 'I do not exist compression';
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));
+ALTER TABLE cmdata_pglz ALTER COLUMN f1 SET COMPRESSION lz4;
+INSERT INTO cmdata_pglz VALUES (repeat('123456789', 4004));
\d+ cmdata
-SELECT pg_column_compression(f1) FROM cmdata;
-
-ALTER TABLE cmdata2 ALTER COLUMN f1 SET COMPRESSION default;
-\d+ cmdata2
+SELECT pg_column_compression(f1) FROM cmdata_pglz;
+ALTER TABLE cmdata_pglz ALTER COLUMN f1 SET COMPRESSION pglz;
-- test alter compression method for materialized views
ALTER MATERIALIZED VIEW compressmv ALTER COLUMN x SET COMPRESSION lz4;
@@ -128,28 +111,17 @@ INSERT INTO cmpart VALUES (repeat('123456789', 4004));
SELECT pg_column_compression(f1) FROM cmpart1;
SELECT pg_column_compression(f1) FROM cmpart2;
--- VACUUM FULL does not recompress
-SELECT pg_column_compression(f1) FROM cmdata;
-VACUUM FULL cmdata;
-SELECT pg_column_compression(f1) FROM cmdata;
-
-- 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;
-SELECT length(f1) FROM cmdata1;
+SELECT length(f1) FROM cmdata_pglz;
+SELECT length(f1) FROM cmdata_lz4;
SELECT length(f1) FROM cmmove1;
SELECT length(f1) FROM cmmove2;
SELECT length(f1) FROM cmmove3;
-CREATE TABLE badcompresstbl (a text COMPRESSION I_Do_Not_Exist_Compression); -- fails
-CREATE TABLE badcompresstbl (a text);
-ALTER TABLE badcompresstbl ALTER a SET COMPRESSION I_Do_Not_Exist_Compression; -- fails
-DROP TABLE badcompresstbl;
-
\set HIDE_TOAST_COMPRESSION true
--
2.50.0
signature.asc
Description: PGP signature
