Robert Haas <[email protected]> writes:
> On Wed, May 26, 2021 at 11:13 AM Tom Lane <[email protected]> wrote:
>> * As things stand here, once you've applied ALTER ... SET COMPRESSION
>> to select a specific method, there is no way to undo that and go
>> back to the use-the-default setting. All you can do is change to
>> explicitly select the other method. Should we invent "ALTER ...
>> SET COMPRESSION default" or the like to cover that?
> Yes. Irreversible catalog changes are bad.
Here's an add-on 0004 that does that, and takes care of assorted
silliness in the grammar and docs --- did you know that this patch
caused
alter table foo alter column bar set ;
to be allowed?
I think this is about ready to commit now (though I didn't yet nuke
GetDefaultToastCompression).
regards, tom lane
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 95a302ffee..9f7f42c4aa 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8242,11 +8242,11 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
compression method for values of compressible columns.
(This can be overridden for individual columns by setting
the <literal>COMPRESSION</literal> column option in
- <command>CREATE TABLE</command> or
+ <command>CREATE TABLE</command> or
<command>ALTER TABLE</command>.)
- The supported compression methods are <literal>pglz</literal> and,
- if <productname>PostgreSQL</productname> was compiled with
- <literal>--with-lz4</literal>, <literal>lz4</literal>.
+ The supported compression methods are <literal>pglz</literal> and
+ (if <productname>PostgreSQL</productname> was compiled with
+ <option>--with-lz4</option>) <literal>lz4</literal>.
The default is <literal>pglz</literal>.
</para>
</listitem>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 3a21129021..08b07f561e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26253,10 +26253,10 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
<primary>pg_column_compression</primary>
</indexterm>
<function>pg_column_compression</function> ( <type>"any"</type> )
- <returnvalue>integer</returnvalue>
+ <returnvalue>text</returnvalue>
</para>
<para>
- Shows the compression algorithm that was used to compress a
+ Shows the compression algorithm that was used to compress
an individual variable-length value. Returns <literal>NULL</literal>
if the value is not compressed.
</para></entry>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1431d2649b..939d3fe273 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -104,7 +104,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
UNIQUE <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
- COMPRESSION <replaceable class="parameter">compression_method</replaceable> |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
@@ -391,24 +390,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</term>
<listitem>
<para>
- This sets the compression method to be used for data inserted into a column.
-
+ This form sets the compression method for a column, determining how
+ values inserted in future will be compressed (if the storage mode
+ permits compression at all).
This does not cause the table to be rewritten, so existing data may still
be compressed with other compression methods. If the table is rewritten with
<command>VACUUM FULL</command> or <command>CLUSTER</command>, or restored
- with <application>pg_restore</application>, then all tuples are rewritten
- with the configured compression methods.
-
- Also, note that when data is inserted from another relation (for example,
- by <command>INSERT ... SELECT</command>), tuples from the source data are
- not necessarily detoasted, and any previously compressed data is retained
- with its existing compression method, rather than recompressing with the
- compression methods of the target columns.
-
+ with <application>pg_restore</application>, then all values are rewritten
+ with the configured compression method.
+ However, when data is inserted from another relation (for example,
+ by <command>INSERT ... SELECT</command>), values from the source table are
+ not necessarily detoasted, so any previously compressed data may retain
+ its existing compression method, rather than being recompressed with the
+ compression method of the target column.
The supported compression
methods are <literal>pglz</literal> and <literal>lz4</literal>.
- <literal>lz4</literal> is available only if <literal>--with-lz4</literal>
- was used when building <productname>PostgreSQL</productname>.
+ (<literal>lz4</literal> is available only if <option>--with-lz4</option>
+ was used when building <productname>PostgreSQL</productname>.) In
+ addition, <replaceable class="parameter">compression_method</replaceable>
+ can be <literal>default</literal>, which selects the default behavior of
+ consulting the <xref linkend="guc-default-toast-compression"/> setting
+ at the time of data insertion to determine the method to use.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index a8c5e4028a..c6d0a35e50 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -22,7 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
- { <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable>collation</replaceable> ] [ COMPRESSION <replaceable>compression_method</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
+ { <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ COMPRESSION <replaceable>compression_method</replaceable> ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
| <replaceable>table_constraint</replaceable>
| LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
[, ... ]
@@ -293,17 +293,22 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
The <literal>COMPRESSION</literal> clause sets the compression method
- for a column. Compression is supported only for variable-width data
- types, and is used only for columns whose storage type is main or
- extended. (See <xref linkend="sql-altertable"/> for information on
- column storage types.) Setting this property for a partitioned table
+ for the column. Compression is supported only for variable-width data
+ types, and is used only when the column's storage mode
+ is <literal>main</literal> or <literal>extended</literal>.
+ (See <xref linkend="sql-altertable"/> for information on
+ column storage modes.) Setting this property for a partitioned table
has no direct effect, because such tables have no storage of their own,
- but the configured value is inherited by newly-created partitions.
+ but the configured value will be inherited by newly-created partitions.
The supported compression methods are <literal>pglz</literal> and
- <literal>lz4</literal>. <literal>lz4</literal> is available only if
- <literal>--with-lz4</literal> was used when building
- <productname>PostgreSQL</productname>. The default
- is <literal>pglz</literal>.
+ <literal>lz4</literal>. (<literal>lz4</literal> is available only if
+ <option>--with-lz4</option> was used when building
+ <productname>PostgreSQL</productname>.) In addition,
+ <replaceable class="parameter">compression_method</replaceable>
+ can be <literal>default</literal> to explicitly specify the default
+ behavior, which is to consult the
+ <xref linkend="guc-default-toast-compression"/> setting at the time of
+ data insertion to determine the method to use.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 67c2cbbec6..5ddadc11f2 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -975,8 +975,8 @@ PostgreSQL documentation
<para>
Do not output commands to set <acronym>TOAST</acronym> compression
methods.
- With this option, all objects will be created using whichever
- compression method is the default during restore.
+ With this option, all columns will be restored with the default
+ compression setting.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index 805c47d5c1..ddffbf85ed 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -464,12 +464,12 @@ PostgreSQL documentation
<para>
Do not output commands to set <acronym>TOAST</acronym> compression
methods.
- With this option, all objects will be created using whichever
- compression method is the default during restore.
+ With this option, all columns will be restored with the default
+ compression setting.
</para>
</listitem>
</varlistentry>
-
+
<varlistentry>
<term><option>--no-unlogged-table-data</option></term>
<listitem>
diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml
index bfccda77af..7136bbe7a3 100644
--- a/doc/src/sgml/storage.sgml
+++ b/doc/src/sgml/storage.sgml
@@ -376,6 +376,16 @@ but the varlena header does not tell whether it has occurred —
the content of the <acronym>TOAST</acronym> pointer tells that, instead.
</para>
+<para>
+The compression technique used for either in-line or out-of-line compressed
+data can be selected for each column by setting
+the <literal>COMPRESSION</literal> column option in <command>CREATE
+TABLE</command> or <command>ALTER TABLE</command>. The default for columns
+with no explicit setting is to consult the
+<xref linkend="guc-default-toast-compression"/> parameter at the time data is
+inserted.
+</para>
+
<para>
As mentioned, there are multiple types of <acronym>TOAST</acronym> pointer datums.
The oldest and most common type is a pointer to out-of-line data stored in
@@ -392,13 +402,6 @@ useful for avoiding copying and redundant processing of large data values.
Further details appear in <xref linkend="storage-toast-inmemory"/>.
</para>
-<para>
-The compression technique used for either in-line or out-of-line compressed
-data can be selected using the <literal>COMPRESSION</literal> option on a per-column
-basis when creating a table. The default for columns with no explicit setting
-is taken from the value of <xref linkend="guc-default-toast-compression" />.
-</para>
-
<sect2 id="storage-toast-ondisk">
<title>Out-of-Line, On-Disk TOAST Storage</title>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index e87d9cda93..e14039e971 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7982,23 +7982,24 @@ ATExecSetOptions(Relation rel, const char *colName, Node *options,
/*
* Helper function for ATExecSetStorage and ATExecSetCompression
*
- * Set the attcompression and/or attstorage for the respective index attribute
- * if the respective input values are valid.
+ * Set the attstorage and/or attcompression fields for index columns
+ * associated with the specified table column.
*/
static void
SetIndexStorageProperties(Relation rel, Relation attrelation,
- AttrNumber attnum, char newcompression,
- char newstorage, LOCKMODE lockmode)
+ AttrNumber attnum,
+ bool setstorage, char newstorage,
+ bool setcompression, char newcompression,
+ LOCKMODE lockmode)
{
- HeapTuple tuple;
ListCell *lc;
- Form_pg_attribute attrtuple;
foreach(lc, RelationGetIndexList(rel))
{
Oid indexoid = lfirst_oid(lc);
Relation indrel;
AttrNumber indattnum = 0;
+ HeapTuple tuple;
indrel = index_open(indexoid, lockmode);
@@ -8021,14 +8022,14 @@ SetIndexStorageProperties(Relation rel, Relation attrelation,
if (HeapTupleIsValid(tuple))
{
- attrtuple = (Form_pg_attribute) GETSTRUCT(tuple);
+ Form_pg_attribute attrtuple = (Form_pg_attribute) GETSTRUCT(tuple);
- if (CompressionMethodIsValid(newcompression))
- attrtuple->attcompression = newcompression;
-
- if (newstorage != '\0')
+ if (setstorage)
attrtuple->attstorage = newstorage;
+ if (setcompression)
+ attrtuple->attcompression = newcompression;
+
CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);
InvokeObjectPostAlterHook(RelationRelationId,
@@ -8121,8 +8122,9 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE loc
* matching behavior of index.c ConstructTupleDescriptor()).
*/
SetIndexStorageProperties(rel, attrelation, attnum,
- InvalidCompressionMethod,
- newstorage, lockmode);
+ true, newstorage,
+ false, 0,
+ lockmode);
table_close(attrelation, RowExclusiveLock);
@@ -15626,7 +15628,10 @@ ATExecSetCompression(AlteredTableInfo *tab,
* Apply the change to indexes as well (only for simple index columns,
* matching behavior of index.c ConstructTupleDescriptor()).
*/
- SetIndexStorageProperties(rel, attrel, attnum, cmethod, '\0', lockmode);
+ SetIndexStorageProperties(rel, attrel, attnum,
+ false, 0,
+ true, cmethod,
+ lockmode);
heap_freetuple(tuple);
@@ -18581,7 +18586,7 @@ GetAttributeCompression(Oid atttypid, char *compression)
char typstorage;
char cmethod;
- if (compression == NULL)
+ if (compression == NULL || strcmp(compression, "default") == 0)
return InvalidCompressionMethod;
/*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index aaf1a51f68..9ee90e3f13 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -561,6 +561,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> TableConstraint TableLikeClause
%type <ival> TableLikeOptionList TableLikeOption
+%type <str> column_compression opt_column_compression
%type <list> ColQualList
%type <node> ColConstraint ColConstraintElem ConstraintAttr
%type <ival> key_actions key_delete key_match key_update key_action
@@ -609,7 +610,6 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> hash_partbound
%type <defelt> hash_partbound_elem
-%type <str> optColumnCompression
/*
* Non-keyword token types. These are hard-wired into the "flex" lexer.
@@ -2302,6 +2302,15 @@ alter_table_cmd:
n->def = (Node *) makeString($6);
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> ALTER [COLUMN] <colname> SET COMPRESSION <cm> */
+ | ALTER opt_column ColId SET column_compression
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetCompression;
+ n->name = $3;
+ n->def = (Node *) makeString($5);
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> ALTER [COLUMN] <colname> ADD GENERATED ... AS IDENTITY ... */
| ALTER opt_column ColId ADD_P GENERATED generated_when AS IDENTITY_P OptParenthesizedSeqOptList
{
@@ -2346,15 +2355,6 @@ alter_table_cmd:
n->missing_ok = true;
$$ = (Node *)n;
}
- /* ALTER TABLE <name> ALTER [COLUMN] <colname> SET (COMPRESSION <cm>) */
- | ALTER opt_column ColId SET optColumnCompression
- {
- AlterTableCmd *n = makeNode(AlterTableCmd);
- n->subtype = AT_SetCompression;
- n->name = $3;
- n->def = (Node *) makeString($5);
- $$ = (Node *)n;
- }
/* ALTER TABLE <name> DROP [COLUMN] IF EXISTS <colname> [RESTRICT|CASCADE] */
| DROP opt_column IF_P EXISTS ColId opt_drop_behavior
{
@@ -3462,7 +3462,7 @@ TypedTableElement:
| TableConstraint { $$ = $1; }
;
-columnDef: ColId Typename optColumnCompression create_generic_options ColQualList
+columnDef: ColId Typename opt_column_compression create_generic_options ColQualList
{
ColumnDef *n = makeNode(ColumnDef);
n->colname = $1;
@@ -3522,13 +3522,15 @@ columnOptions: ColId ColQualList
}
;
-optColumnCompression:
- COMPRESSION name
- {
- $$ = $2;
- }
- | /*EMPTY*/ { $$ = NULL; }
- ;
+column_compression:
+ COMPRESSION ColId { $$ = $2; }
+ | COMPRESSION DEFAULT { $$ = pstrdup("default"); }
+ ;
+
+opt_column_compression:
+ column_compression { $$ = $1; }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
ColQualList:
ColQualList ColConstraint { $$ = lappend($1, $2); }
diff --git a/src/test/regress/expected/compression.out b/src/test/regress/expected/compression.out
index 79e929673a..5c645e4650 100644
--- a/src/test/regress/expected/compression.out
+++ b/src/test/regress/expected/compression.out
@@ -158,18 +158,19 @@ ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE int USING f1::integer;
--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 | | |
+ 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 | | |
+ f1 | character varying | | | | plain | pglz | |
INSERT INTO cmdata2 VALUES (repeat('123456789', 800));
SELECT pg_column_compression(f1) FROM cmdata2;
@@ -179,9 +180,9 @@ SELECT pg_column_compression(f1) FROM cmdata2;
(1 row)
-- test compression with materialized view
-CREATE MATERIALIZED VIEW mv(x) AS SELECT * FROM cmdata1;
-\d+ mv
- Materialized view "public.mv"
+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 | | |
@@ -196,7 +197,7 @@ SELECT pg_column_compression(f1) FROM cmdata1;
lz4
(2 rows)
-SELECT pg_column_compression(x) FROM mv;
+SELECT pg_column_compression(x) FROM compressmv;
pg_column_compression
-----------------------
lz4
@@ -222,7 +223,7 @@ SELECT pg_column_compression(f1) FROM cmpart2;
pglz
(1 row)
--- test compression with inheritence, error
+-- 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
@@ -239,14 +240,6 @@ 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';
-DROP TABLE cmdata2;
-CREATE TABLE cmdata2 (f1 text);
-\d+ cmdata2
- Table "public.cmdata2"
- Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+------+-----------+----------+---------+----------+-------------+--------------+-------------
- f1 | text | | | | extended | | |
-
SET default_toast_compression = 'pglz';
-- test alter compression method
ALTER TABLE cmdata ALTER COLUMN f1 SET COMPRESSION lz4;
@@ -266,10 +259,17 @@ SELECT pg_column_compression(f1) FROM cmdata;
lz4
(2 rows)
--- test alter compression method for the materialized view
-ALTER MATERIALIZED VIEW mv ALTER COLUMN x SET COMPRESSION lz4;
-\d+ mv
- Materialized view "public.mv"
+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 | |
@@ -277,7 +277,7 @@ View definition:
SELECT cmdata1.f1 AS x
FROM cmdata1;
--- test alter compression method for the partitioned table
+-- 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
diff --git a/src/test/regress/expected/compression_1.out b/src/test/regress/expected/compression_1.out
index 2a9fc81b51..aac96037fc 100644
--- a/src/test/regress/expected/compression_1.out
+++ b/src/test/regress/expected/compression_1.out
@@ -156,18 +156,19 @@ ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE int USING f1::integer;
--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 | | |
+ 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 | | |
+ f1 | character varying | | | | plain | pglz | |
INSERT INTO cmdata2 VALUES (repeat('123456789', 800));
SELECT pg_column_compression(f1) FROM cmdata2;
@@ -177,18 +178,18 @@ SELECT pg_column_compression(f1) FROM cmdata2;
(1 row)
-- test compression with materialized view
-CREATE MATERIALIZED VIEW mv(x) AS SELECT * FROM cmdata1;
+CREATE MATERIALIZED VIEW compressmv(x) AS SELECT * FROM cmdata1;
ERROR: relation "cmdata1" does not exist
-LINE 1: CREATE MATERIALIZED VIEW mv(x) AS SELECT * FROM cmdata1;
- ^
-\d+ mv
+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 mv;
-ERROR: relation "mv" does not exist
-LINE 1: SELECT pg_column_compression(x) FROM mv;
+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);
@@ -217,7 +218,7 @@ SELECT pg_column_compression(f1) FROM cmpart2;
-----------------------
(0 rows)
--- test compression with inheritence, error
+-- test compression with inheritance, error
CREATE TABLE cminh() INHERITS(cmdata, cmdata1);
ERROR: relation "cmdata1" does not exist
CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata);
@@ -234,14 +235,6 @@ HINT: Available values: pglz.
SET default_toast_compression = 'lz4';
ERROR: invalid value for parameter "default_toast_compression": "lz4"
HINT: Available values: pglz.
-DROP TABLE cmdata2;
-CREATE TABLE cmdata2 (f1 text);
-\d+ cmdata2
- Table "public.cmdata2"
- Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+------+-----------+----------+---------+----------+-------------+--------------+-------------
- f1 | text | | | | extended | | |
-
SET default_toast_compression = 'pglz';
-- test alter compression method
ALTER TABLE cmdata ALTER COLUMN f1 SET COMPRESSION lz4;
@@ -264,11 +257,18 @@ SELECT pg_column_compression(f1) FROM cmdata;
pglz
(2 rows)
--- test alter compression method for the materialized view
-ALTER MATERIALIZED VIEW mv ALTER COLUMN x SET COMPRESSION lz4;
-ERROR: relation "mv" does not exist
-\d+ mv
--- test alter compression method for the partitioned table
+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;
diff --git a/src/test/regress/sql/compression.sql b/src/test/regress/sql/compression.sql
index 76d1776d83..35557c1f7d 100644
--- a/src/test/regress/sql/compression.sql
+++ b/src/test/regress/sql/compression.sql
@@ -69,6 +69,7 @@ ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE int USING f1::integer;
--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
@@ -76,10 +77,10 @@ INSERT INTO cmdata2 VALUES (repeat('123456789', 800));
SELECT pg_column_compression(f1) FROM cmdata2;
-- test compression with materialized view
-CREATE MATERIALIZED VIEW mv(x) AS SELECT * FROM cmdata1;
-\d+ mv
+CREATE MATERIALIZED VIEW compressmv(x) AS SELECT * FROM cmdata1;
+\d+ compressmv
SELECT pg_column_compression(f1) FROM cmdata1;
-SELECT pg_column_compression(x) FROM mv;
+SELECT pg_column_compression(x) FROM compressmv;
-- test compression with partition
CREATE TABLE cmpart(f1 text COMPRESSION lz4) PARTITION BY HASH(f1);
@@ -92,7 +93,7 @@ INSERT INTO cmpart VALUES (repeat('123456789', 4004));
SELECT pg_column_compression(f1) FROM cmpart1;
SELECT pg_column_compression(f1) FROM cmpart2;
--- test compression with inheritence, error
+-- test compression with inheritance, error
CREATE TABLE cminh() INHERITS(cmdata, cmdata1);
CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata);
@@ -100,9 +101,6 @@ CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata);
SET default_toast_compression = '';
SET default_toast_compression = 'I do not exist compression';
SET default_toast_compression = 'lz4';
-DROP TABLE cmdata2;
-CREATE TABLE cmdata2 (f1 text);
-\d+ cmdata2
SET default_toast_compression = 'pglz';
-- test alter compression method
@@ -111,11 +109,14 @@ INSERT INTO cmdata VALUES (repeat('123456789', 4004));
\d+ cmdata
SELECT pg_column_compression(f1) FROM cmdata;
--- test alter compression method for the materialized view
-ALTER MATERIALIZED VIEW mv ALTER COLUMN x SET COMPRESSION lz4;
-\d+ mv
+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 the partitioned table
+-- 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;
