diff options
23 files changed, 517 insertions, 712 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index aa5b8772436..0d23bc1b122 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -3158,7 +3158,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <structfield>datcollate</structfield> <type>text</type> </para> <para> - LC_COLLATE for this database + LC_COLLATE for this database (ignored unless <structfield>datlocprovider</structfield> is <literal>c</literal>) </para></entry> </row> diff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgml index 5a0e97f6f31..59b27c3c370 100644 --- a/doc/src/sgml/charset.sgml +++ b/doc/src/sgml/charset.sgml @@ -100,7 +100,7 @@ initdb --locale=sv_SE <tbody> <row> <entry><envar>LC_COLLATE</envar></entry> - <entry>String sort order</entry> + <entry>String sort order (ignored unless the provider is <literal>libc</literal>)</entry> </row> <row> <entry><envar>LC_CTYPE</envar></entry> diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgml index 727a0cb185f..b190f28d41e 100644 --- a/doc/src/sgml/query.sgml +++ b/doc/src/sgml/query.sgml @@ -264,8 +264,18 @@ COPY weather FROM '/home/user/weather.txt'; where the file name for the source file must be available on the machine running the backend process, not the client, since the backend process - reads the file directly. You can read more about the - <command>COPY</command> command in <xref linkend="sql-copy"/>. + reads the file directly. The data inserted above into the weather table + could also be inserted from a file containing (values are separated by a + tab character): + +<programlisting> +San Francisco 46 50 0.25 1994-11-27 +San Francisco 43 57 0.0 1994-11-29 +Hayward 37 54 \N 1994-11-29 +</programlisting> + + You can read more about the <command>COPY</command> command in + <xref linkend="sql-copy"/>. </para> </sect1> diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml index 4da8aeebb50..3544b15efda 100644 --- a/doc/src/sgml/ref/create_database.sgml +++ b/doc/src/sgml/ref/create_database.sgml @@ -150,12 +150,12 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable> <para> Sets the default collation order and character classification in the new database. Collation affects the sort order applied to strings, - e.g., in queries with <literal>ORDER BY</literal>, as well as the order used in indexes - on text columns. Character classification affects the categorization - of characters, e.g., lower, upper, and digit. Also sets the - associated aspects of the operating system environment, - <literal>LC_COLLATE</literal> and <literal>LC_CTYPE</literal>. The - default is the same setting as the template database. See <xref + e.g., in queries with <literal>ORDER BY</literal>, as well as the + order used in indexes on text columns. Character classification + affects the categorization of characters, e.g., lower, upper, and + digit. Also sets the <literal>LC_CTYPE</literal> aspect of the + operating system environment. The default is the same setting as the + template database. See <xref linkend="collation-managing-create-libc"/> and <xref linkend="collation-managing-create-icu"/> for details. </para> @@ -189,17 +189,16 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable> <term><replaceable class="parameter">lc_collate</replaceable></term> <listitem> <para> - Sets <literal>LC_COLLATE</literal> in the database server's operating - system environment. The default is the setting of <xref - linkend="create-database-locale"/> if specified, otherwise the same - setting as the template database. See below for additional - restrictions. + If <xref linkend="create-database-locale-provider"/> is + <literal>libc</literal>, sets the default collation order to use in + the new database, overriding the setting <xref + linkend="create-database-locale"/>. Otherwise, this setting is + ignored. </para> <para> - If <xref linkend="create-database-locale-provider"/> is - <literal>libc</literal>, also sets the default collation order to use - in the new database, overriding the setting <xref - linkend="create-database-locale"/>. + The default is the setting of <xref linkend="create-database-locale"/> + if specified, otherwise the same setting as the template database. + See below for additional restrictions. </para> </listitem> </varlistentry> @@ -208,16 +207,18 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable> <listitem> <para> Sets <literal>LC_CTYPE</literal> in the database server's operating - system environment. The default is the setting of <xref - linkend="create-database-locale"/> if specified, otherwise the same - setting as the template database. See below for additional - restrictions. + system environment. </para> <para> If <xref linkend="create-database-locale-provider"/> is - <literal>libc</literal>, also sets the default character - classification to use in the new database, overriding the setting - <xref linkend="create-database-locale"/>. + <literal>libc</literal>, sets the default character classification to + use in the new database, overriding the setting <xref + linkend="create-database-locale"/>. + </para> + <para> + The default is the setting of <xref linkend="create-database-locale"/> + if specified, otherwise the same setting as the template database. + See below for additional restrictions. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/createdb.sgml b/doc/src/sgml/ref/createdb.sgml index 5c4e0465ed9..2ccbe13f390 100644 --- a/doc/src/sgml/ref/createdb.sgml +++ b/doc/src/sgml/ref/createdb.sgml @@ -136,7 +136,8 @@ PostgreSQL documentation <term><option>--lc-collate=<replaceable class="parameter">locale</replaceable></option></term> <listitem> <para> - Specifies the LC_COLLATE setting to be used in this database. + Specifies the LC_COLLATE setting to be used in this database (ignored + unless the locale provider is <literal>libc</literal>). </para> </listitem> </varlistentry> diff --git a/src/backend/access/common/toast_compression.c b/src/backend/access/common/toast_compression.c index 21f2f4af97e..926f1e4008a 100644 --- a/src/backend/access/common/toast_compression.c +++ b/src/backend/access/common/toast_compression.c @@ -25,11 +25,11 @@ /* GUC */ int default_toast_compression = TOAST_PGLZ_COMPRESSION; -#define NO_LZ4_SUPPORT() \ +#define NO_COMPRESSION_SUPPORT(method) \ ereport(ERROR, \ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), \ - errmsg("compression method lz4 not supported"), \ - errdetail("This functionality requires the server to be built with lz4 support."))) + errmsg("compression method %s not supported", method), \ + errdetail("This functionality requires the server to be built with %s support.", method))) /* * Compress a varlena using PGLZ. @@ -139,7 +139,7 @@ struct varlena * lz4_compress_datum(const struct varlena *value) { #ifndef USE_LZ4 - NO_LZ4_SUPPORT(); + NO_COMPRESSION_SUPPORT("lz4"); return NULL; /* keep compiler quiet */ #else int32 valsize; @@ -182,7 +182,7 @@ struct varlena * lz4_decompress_datum(const struct varlena *value) { #ifndef USE_LZ4 - NO_LZ4_SUPPORT(); + NO_COMPRESSION_SUPPORT("lz4"); return NULL; /* keep compiler quiet */ #else int32 rawsize; @@ -215,7 +215,7 @@ struct varlena * lz4_decompress_datum_slice(const struct varlena *value, int32 slicelength) { #ifndef USE_LZ4 - NO_LZ4_SUPPORT(); + NO_COMPRESSION_SUPPORT("lz4"); return NULL; /* keep compiler quiet */ #else int32 rawsize; @@ -289,7 +289,7 @@ CompressionNameToMethod(const char *compression) else if (strcmp(compression, "lz4") == 0) { #ifndef USE_LZ4 - NO_LZ4_SUPPORT(); + NO_COMPRESSION_SUPPORT("lz4"); #endif return TOAST_LZ4_COMPRESSION; } diff --git a/src/backend/access/nbtree/nbtsearch.c b/src/backend/access/nbtree/nbtsearch.c index 4af1ff1e9e5..d69798795b4 100644 --- a/src/backend/access/nbtree/nbtsearch.c +++ b/src/backend/access/nbtree/nbtsearch.c @@ -892,9 +892,9 @@ _bt_first(IndexScanDesc scan, ScanDirection dir) OffsetNumber offnum; BTScanInsertData inskey; ScanKey startKeys[INDEX_MAX_KEYS]; - ScanKeyData notnullkeys[INDEX_MAX_KEYS]; + ScanKeyData notnullkey; int keysz = 0; - StrategyNumber strat_total; + StrategyNumber strat_total = InvalidStrategy; BlockNumber blkno = InvalidBlockNumber, lastcurrblkno; @@ -1034,7 +1034,6 @@ _bt_first(IndexScanDesc scan, ScanDirection dir) * need to be kept in sync. *---------- */ - strat_total = BTEqualStrategyNumber; if (so->numberOfKeys > 0) { AttrNumber curattr; @@ -1122,16 +1121,15 @@ _bt_first(IndexScanDesc scan, ScanDirection dir) ScanDirectionIsForward(dir) : ScanDirectionIsBackward(dir))) { - /* Yes, so build the key in notnullkeys[keysz] */ - bkey = ¬nullkeys[keysz]; + /* Final startKeys[] entry will be deduced NOT NULL key */ + bkey = ¬nullkey; ScanKeyEntryInitialize(bkey, (SK_SEARCHNOTNULL | SK_ISNULL | (impliesNN->sk_flags & (SK_BT_DESC | SK_BT_NULLS_FIRST))), curattr, - ((impliesNN->sk_flags & SK_BT_NULLS_FIRST) ? - BTGreaterStrategyNumber : - BTLessStrategyNumber), + ScanDirectionIsForward(dir) ? + BTGreaterStrategyNumber : BTLessStrategyNumber, InvalidOid, InvalidOid, InvalidOid, diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index 8a72b5e70a4..1a37737d4a2 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -5228,7 +5228,6 @@ ExecEvalJsonCoercionFinish(ExprState *state, ExprEvalStep *op) * JsonBehavior expression. */ jsestate->escontext.error_occurred = false; - jsestate->escontext.error_occurred = false; jsestate->escontext.details_wanted = true; } } diff --git a/src/backend/main/main.c b/src/backend/main/main.c index 7d63cf94a6b..bdcb5e4f261 100644 --- a/src/backend/main/main.c +++ b/src/backend/main/main.c @@ -125,13 +125,17 @@ main(int argc, char *argv[]) set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("postgres")); /* - * In the postmaster, absorb the environment values for LC_COLLATE and - * LC_CTYPE. Individual backends will change these later to settings - * taken from pg_database, but the postmaster cannot do that. If we leave - * these set to "C" then message localization might not work well in the - * postmaster. + * Collation is handled by pg_locale.c, and the behavior is dependent on + * the provider. strcoll(), etc., should not be called directly. + */ + init_locale("LC_COLLATE", LC_COLLATE, "C"); + + /* + * In the postmaster, absorb the environment value for LC_CTYPE. + * Individual backends will change it later to pg_database.datctype, but + * the postmaster cannot do that. If we leave it set to "C" then message + * localization might not work well in the postmaster. */ - init_locale("LC_COLLATE", LC_COLLATE, ""); init_locale("LC_CTYPE", LC_CTYPE, ""); /* diff --git a/src/backend/storage/lmgr/lwlock.c b/src/backend/storage/lmgr/lwlock.c index 46f44bc4511..2d43bf2cc13 100644 --- a/src/backend/storage/lmgr/lwlock.c +++ b/src/backend/storage/lmgr/lwlock.c @@ -170,8 +170,8 @@ static const char *const BuiltinTrancheNames[] = { [LWTRANCHE_DSM_REGISTRY_DSA] = "DSMRegistryDSA", [LWTRANCHE_DSM_REGISTRY_HASH] = "DSMRegistryHash", [LWTRANCHE_COMMITTS_SLRU] = "CommitTsSLRU", - [LWTRANCHE_MULTIXACTOFFSET_SLRU] = "MultixactOffsetSLRU", - [LWTRANCHE_MULTIXACTMEMBER_SLRU] = "MultixactMemberSLRU", + [LWTRANCHE_MULTIXACTOFFSET_SLRU] = "MultiXactOffsetSLRU", + [LWTRANCHE_MULTIXACTMEMBER_SLRU] = "MultiXactMemberSLRU", [LWTRANCHE_NOTIFY_SLRU] = "NotifySLRU", [LWTRANCHE_SERIAL_SLRU] = "SerialSLRU", [LWTRANCHE_SUBTRANS_SLRU] = "SubtransSLRU", diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c index c86ceefda94..641e535a73c 100644 --- a/src/backend/utils/init/postinit.c +++ b/src/backend/utils/init/postinit.c @@ -417,12 +417,11 @@ CheckMyDatabase(const char *name, bool am_superuser, bool override_allow_connect datum = SysCacheGetAttrNotNull(DATABASEOID, tup, Anum_pg_database_datctype); ctype = TextDatumGetCString(datum); - if (pg_perm_setlocale(LC_COLLATE, collate) == NULL) - ereport(FATAL, - (errmsg("database locale is incompatible with operating system"), - errdetail("The database was initialized with LC_COLLATE \"%s\", " - " which is not recognized by setlocale().", collate), - errhint("Recreate the database with another locale or install the missing locale."))); + /* + * Historcally, we set LC_COLLATE from datcollate, as well. That's no + * longer necessary because all collation behavior is handled through + * pg_locale_t. + */ if (pg_perm_setlocale(LC_CTYPE, ctype) == NULL) ereport(FATAL, diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 1937997ea67..c6226175528 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -12583,8 +12583,13 @@ dumpDomain(Archive *fout, const TypeInfo *tyinfo) for (i = 0; i < tyinfo->nDomChecks; i++) { ConstraintInfo *domcheck = &(tyinfo->domChecks[i]); - PQExpBuffer conprefix = createPQExpBuffer(); + PQExpBuffer conprefix; + /* but only if the constraint itself was dumped here */ + if (domcheck->separate) + continue; + + conprefix = createPQExpBuffer(); appendPQExpBuffer(conprefix, "CONSTRAINT %s ON DOMAIN", fmtId(domcheck->dobj.name)); @@ -18488,6 +18493,22 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo) .section = SECTION_POST_DATA, .createStmt = q->data, .dropStmt = delq->data)); + + if (coninfo->dobj.dump & DUMP_COMPONENT_COMMENT) + { + PQExpBuffer conprefix = createPQExpBuffer(); + char *qtypname = pg_strdup(fmtId(tyinfo->dobj.name)); + + appendPQExpBuffer(conprefix, "CONSTRAINT %s ON DOMAIN", + fmtId(coninfo->dobj.name)); + + dumpComment(fout, conprefix->data, qtypname, + tyinfo->dobj.namespace->dobj.name, + tyinfo->rolname, + coninfo->dobj.catId, 0, tyinfo->dobj.dumpId); + destroyPQExpBuffer(conprefix); + free(qtypname); + } } } else diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c index 3cbcad65c5f..100317b1aa9 100644 --- a/src/bin/pg_dump/pg_dumpall.c +++ b/src/bin/pg_dump/pg_dumpall.c @@ -632,7 +632,7 @@ main(int argc, char *argv[]) fprintf(OPF, "SET escape_string_warning = off;\n"); fprintf(OPF, "\n"); - if (!data_only) + if (!data_only && !statistics_only && !no_schema) { /* * If asked to --clean, do that first. We can avoid detailed diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index a2e009ab9be..8c62729a0d1 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -748,7 +748,7 @@ void print_copyright(void) { puts("PostgreSQL Database Management System\n" - "(formerly known as Postgres, then as Postgres95)\n\n" + "(also known as Postgres, formerly known as Postgres95)\n\n" "Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group\n\n" "Portions Copyright (c) 1994, The Regents of the University of California\n\n" "Permission to use, copy, modify, and distribute this software and its\n" diff --git a/src/test/regress/expected/compression.out b/src/test/regress/expected/compression.out index 4dd9ee7200d..09f198149aa 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 7bd7642b4b9..00000000000 --- 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 00000000000..068dd7c3674 --- /dev/null +++ b/src/test/regress/expected/compression_lz4.out @@ -0,0 +1,249 @@ +-- Tests for TOAST compression with lz4 +SELECT NOT(enumvals @> '{lz4}') AS skip_test FROM pg_settings WHERE + name = 'default_toast_compression' \gset +\if :skip_test + \echo '*** skipping TOAST tests with lz4 (not supported) ***' + \quit +\endif +CREATE SCHEMA lz4; +SET search_path TO lz4, public; +\set HIDE_TOAST_COMPRESSION false +-- Ensure we get stable results regardless of the 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_lz4() 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_lz4() || 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; +DROP FUNCTION large_val_lz4; +-- 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 00000000000..198056fa224 --- /dev/null +++ b/src/test/regress/expected/compression_lz4_1.out @@ -0,0 +1,7 @@ +-- Tests for TOAST compression with lz4 +SELECT NOT(enumvals @> '{lz4}') AS skip_test FROM pg_settings WHERE + name = 'default_toast_compression' \gset +\if :skip_test + \echo '*** skipping TOAST tests with lz4 (not supported) ***' +*** skipping TOAST tests with lz4 (not supported) *** + \quit diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index ccea883cffd..3590d3274f0 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -1701,3 +1701,7 @@ DROP TABLE constraint_comments_tbl; DROP DOMAIN constraint_comments_dom; DROP ROLE regress_constraint_comments; DROP ROLE regress_constraint_comments_noaccess; +-- Leave some constraints for the pg_upgrade test to pick up +CREATE DOMAIN constraint_comments_dom AS int; +ALTER DOMAIN constraint_comments_dom ADD CONSTRAINT inv_ck CHECK (value > 0) NOT VALID; +COMMENT ON CONSTRAINT inv_ck ON DOMAIN constraint_comments_dom IS 'comment on invalid constraint'; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index a424be2a6bf..fbffc67ae60 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 490595fcfb2..ce5ea37a660 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_lz4.sql b/src/test/regress/sql/compression_lz4.sql new file mode 100644 index 00000000000..3849f8618de --- /dev/null +++ b/src/test/regress/sql/compression_lz4.sql @@ -0,0 +1,129 @@ +-- Tests for TOAST compression with lz4 + +SELECT NOT(enumvals @> '{lz4}') AS skip_test FROM pg_settings WHERE + name = 'default_toast_compression' \gset +\if :skip_test + \echo '*** skipping TOAST tests with lz4 (not supported) ***' + \quit +\endif + +CREATE SCHEMA lz4; +SET search_path TO lz4, public; + +\set HIDE_TOAST_COMPRESSION false + +-- Ensure we get stable results regardless of the 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; + +-- decompress data slice +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_lz4; +\d+ cmmove1 +SELECT pg_column_compression(f1) FROM cmmove1; + +-- 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; + +-- 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 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 = cmdata_lz4.f1 FROM cmdata_lz4; +SELECT pg_column_compression(f1) FROM cmmove2; + +-- test externally stored compressed data +CREATE OR REPLACE FUNCTION large_val_lz4() 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_lz4() || repeat('a', 4000); +SELECT pg_column_compression(f1) FROM cmdata2; +SELECT SUBSTR(f1, 200, 5) FROM cmdata2; +DROP TABLE cmdata2; +DROP FUNCTION large_val_lz4; + +-- test compression with materialized view +CREATE MATERIALIZED VIEW compressmv(x) AS SELECT * FROM cmdata_lz4; +\d+ compressmv +SELECT pg_column_compression(f1) FROM cmdata_lz4; +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_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_pglz, cmdata3); + +-- 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; +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 + +-- 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; + +-- 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; +SELECT length(f1) FROM cmdata_lz4; +SELECT length(f1) FROM cmmove1; +SELECT length(f1) FROM cmmove2; +SELECT length(f1) FROM cmmove3; + +\set HIDE_TOAST_COMPRESSION true diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index 7487723ab84..1f6dc8fd69f 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -1043,3 +1043,9 @@ DROP DOMAIN constraint_comments_dom; DROP ROLE regress_constraint_comments; DROP ROLE regress_constraint_comments_noaccess; + +-- Leave some constraints for the pg_upgrade test to pick up +CREATE DOMAIN constraint_comments_dom AS int; + +ALTER DOMAIN constraint_comments_dom ADD CONSTRAINT inv_ck CHECK (value > 0) NOT VALID; +COMMENT ON CONSTRAINT inv_ck ON DOMAIN constraint_comments_dom IS 'comment on invalid constraint'; |