Adding upstream version 14.5.upstream/14.5upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 362 insertions, 0 deletions

diff --git a/src/test/regress/expected/compression.out b/src/test/regress/expected/compression.out
new file mode 100644
index 0000000..4c997e2
--- /dev/null
+++ b/src/test/regress/expected/compression.out
@@ -0,0 +1,362 @@
+\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);
+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"
+ 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;
+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(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);
+SELECT pg_column_compression(f1) FROM cmdata1;
+ pg_column_compression 
+-----------------------
+ lz4
+ lz4
+(2 rows)
+
+SELECT SUBSTR(f1, 200, 5) FROM cmdata1;
+ substr 
+--------
+ 01234
+ 8f14e
+(2 rows)
+
+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;
+\d+ compressmv
+                                Materialized view "public.compressmv"
+ Column | Type | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ x      | text |           |          |         | extended |             |              | 
+View definition:
+ SELECT cmdata1.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, error
+CREATE TABLE cminh() INHERITS(cmdata, cmdata1);
+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);
+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, 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)
+
+SELECT pg_column_compression(f1) FROM cmdata;
+ pg_column_compression 
+-----------------------
+ pglz
+ lz4
+(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;
+\d+ compressmv
+                                Materialized view "public.compressmv"
+ Column | Type | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ x      | text |           |          |         | extended | lz4         |              | 
+View definition:
+ SELECT cmdata1.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)
+
+-- VACUUM FULL does not recompress
+SELECT pg_column_compression(f1) FROM cmdata;
+ pg_column_compression 
+-----------------------
+ pglz
+ lz4
+(2 rows)
+
+VACUUM FULL cmdata;
+SELECT pg_column_compression(f1) FROM cmdata;
+ pg_column_compression 
+-----------------------
+ pglz
+ lz4
+(2 rows)
+
+-- 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(md5(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)
+
+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);
+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