Re: Move pg_attribute.attcompression to earlier in struct for reduced size? - Mailing list pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, May 26, 2021 at 11:13 AM Tom Lane <tgl@sss.pgh.pa.us> 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> ] [
<replaceableclass="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> ] [
<replaceableclass="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;