Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1llzFk-00024m-6d for pgsql-hackers@arkaria.postgresql.org; Wed, 26 May 2021 19:31:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1llzFi-0002SK-2F for pgsql-hackers@arkaria.postgresql.org; Wed, 26 May 2021 19:31:38 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1llzFh-0002SC-II for pgsql-hackers@lists.postgresql.org; Wed, 26 May 2021 19:31:37 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1llzFe-0002Dh-Dw for pgsql-hackers@postgresql.org; Wed, 26 May 2021 19:31:36 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 14QJVSxQ1556958; Wed, 26 May 2021 15:31:28 -0400 From: Tom Lane To: Robert Haas cc: Michael Paquier , Justin Pryzby , Andres Freund , pgsql-hackers@postgresql.org, Dilip Kumar Subject: Re: Move pg_attribute.attcompression to earlier in struct for reduced size? In-reply-to: References: <20210517204803.iyk5wwvwgtjcmc5w@alap3.anarazel.de> <2854987.1621285592@sss.pgh.pa.us> <20210517212857.qyk4fxykfbxhmxof@alap3.anarazel.de> <3962395.1621609263@sss.pgh.pa.us> <626613.1621787110@sss.pgh.pa.us> <20210526013347.GG3676@telsasoft.com> <1505124.1622042026@sss.pgh.pa.us> Comments: In-reply-to Robert Haas message dated "Wed, 26 May 2021 11:17:22 -0400" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <1556861.1622057367.0@sss.pgh.pa.us> Date: Wed, 26 May 2021 15:31:28 -0400 Message-ID: <1556957.1622057488@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1556861.1622057367.1@sss.pgh.pa.us> Robert Haas writes: > On Wed, May 26, 2021 at 11:13 AM Tom Lane 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 ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="0004-allow-set-compression-default.patch"; charset="us-ascii" Content-ID: <1556861.1622057367.2@sss.pgh.pa.us> Content-Description: 0004-allow-set-compression-default.patch Content-Transfer-Encoding: quoted-printable 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 COMPRESSION column option in - CREATE TABLE or = + CREATE TABLE or ALTER TABLE.) - The supported compression methods are pglz and= , - if PostgreSQL was compiled with - --with-lz4, lz4. + The supported compression methods are pglz and + (if PostgreSQL was compiled with + ) lz4. The default is pglz. 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=3D# SELECT * FROM pg_walfile_name_offse= t(pg_stop_backup()); pg_column_compression pg_column_compression ( "any" ) - integer + text - 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 NULL if the value is not compressed. diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_ta= ble.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 numeri= c_literal, REM GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence= _options ) ] | UNIQUE index_parameters = | PRIMARY KEY index_parameters | - COMPRESSION compression_method | REFERENCES reftable [ ( = refcolumn ) ] [ MATCH FULL = | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action= ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIAT= E ] @@ -391,24 +390,27 @@ WITH ( MODULUS nume= ric_literal, REM - 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 rewr= itten with VACUUM FULL or CLUSTER, or re= stored - with pg_restore, then all tuples are rew= ritten - with the configured compression methods. - - Also, note that when data is inserted from another relation (for ex= ample, - by INSERT ... SELECT), tuples from the source da= ta are - not necessarily detoasted, and any previously compressed data is re= tained - with its existing compression method, rather than recompressing wit= h the - compression methods of the target columns. - + with pg_restore, then all values are rew= ritten + with the configured compression method. + However, when data is inserted from another relation (for example, + by INSERT ... SELECT), values from the source ta= ble are + not necessarily detoasted, so any previously compressed data may re= tain + its existing compression method, rather than being recompressed wit= h the + compression method of the target column. The supported compression methods are pglz and lz4. - lz4 is available only if --with-lz4 - was used when building PostgreSQL. + (lz4 is available only if 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 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF = NOT EXISTS ] table_name ( [ - { column_name data_type [ COLLATE coll= ation ] [ COMPRESSION compression_method ] [ column_constraint [ ... ] ] + { column_name data_type [ COMPRESSION = compression_method ] [ COLLATE collation ] [ column_constraint [ ... ] ] | table_constraint | LIKE source_table [ like_op= tion ... ] } [, ... ] @@ -293,17 +293,22 @@ WITH ( MODULUS nume= ric_literal, REM The COMPRESSION clause sets the compression meth= od - for a column. Compression is supported only for variable-width dat= a - types, and is used only for columns whose storage type is main or - extended. (See for information o= n - column storage types.) Setting this property for a partitioned tabl= e + for the column. Compression is supported only for variable-width d= ata + types, and is used only when the column's storage mode + is main or extended. + (See for information on + column storage modes.) Setting this property for a partitioned tabl= e 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 partiti= ons. The supported compression methods are pglz and - lz4. lz4 is available only i= f - --with-lz4 was used when building - PostgreSQL. The default - is pglz. + lz4. (lz4 is available only = if + was used when building + PostgreSQL.) In addition, + compression_method + can be default to explicitly specify the default + behavior, which is to consult the + setting at the ti= me of + data insertion to determine the method to use. 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 Do not output commands to set TOAST compressio= n 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. diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpal= l.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 Do not output commands to set TOAST compressio= n 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. - = + 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 o= ccurred — the content of the TOAST pointer tells that, instead. = + +The compression technique used for either in-line or out-of-line compress= ed +data can be selected for each column by setting +the COMPRESSION column option in CREATE +TABLE or ALTER TABLE. The default for colum= ns +with no explicit setting is to consult the + parameter at the time d= ata is +inserted. + + As mentioned, there are multiple types of TOAST pointe= r datums. The oldest and most common type is a pointer to out-of-line data stored i= n @@ -392,13 +402,6 @@ useful for avoiding copying and redundant processing = of large data values. Further details appear in . = - -The compression technique used for either in-line or out-of-line compress= ed -data can be selected using the COMPRESSION option on a= per-column -basis when creating a table. The default for columns with no explicit set= ting -is taken from the value of . - - Out-of-Line, On-Disk TOAST Storage = diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/table= cmds.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 attr= ibute - * 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 =3D lfirst_oid(lc); Relation indrel; AttrNumber indattnum =3D 0; + HeapTuple tuple; = indrel =3D index_open(indexoid, lockmode); = @@ -8021,14 +8022,14 @@ SetIndexStorageProperties(Relation rel, Relation a= ttrelation, = if (HeapTupleIsValid(tuple)) { - attrtuple =3D (Form_pg_attribute) GETSTRUCT(tuple); + Form_pg_attribute attrtuple =3D (Form_pg_attribute) GETSTRUCT(tuple); = - if (CompressionMethodIsValid(newcompression)) - attrtuple->attcompression =3D newcompression; - - if (newstorage !=3D '\0') + if (setstorage) attrtuple->attstorage =3D newstorage; = + if (setcompression) + attrtuple->attcompression =3D 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 *compre= ssion) char typstorage; char cmethod; = - if (compression =3D=3D NULL) + if (compression =3D=3D NULL || strcmp(compression, "default") =3D=3D 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, Li= st *aliases, Node *query); = %type TableConstraint TableLikeClause %type TableLikeOptionList TableLikeOption +%type column_compression opt_column_compression %type ColQualList %type ColConstraint ColConstraintElem ConstraintAttr %type key_actions key_delete key_match key_update key_action @@ -609,7 +610,6 @@ static Node *makeRecursiveViewSelect(char *relname, Li= st *aliases, Node *query); %type hash_partbound %type hash_partbound_elem = -%type optColumnCompression = /* * Non-keyword token types. These are hard-wired into the "flex" lexer. @@ -2302,6 +2302,15 @@ alter_table_cmd: n->def =3D (Node *) makeString($6); $$ =3D (Node *)n; } + /* ALTER TABLE ALTER [COLUMN] SET COMPRESSION */ + | ALTER opt_column ColId SET column_compression + { + AlterTableCmd *n =3D makeNode(AlterTableCmd); + n->subtype =3D AT_SetCompression; + n->name =3D $3; + n->def =3D (Node *) makeString($5); + $$ =3D (Node *)n; + } /* ALTER TABLE ALTER [COLUMN] ADD GENERATED ... AS ID= ENTITY ... */ | ALTER opt_column ColId ADD_P GENERATED generated_when AS IDENTITY_P = OptParenthesizedSeqOptList { @@ -2346,15 +2355,6 @@ alter_table_cmd: n->missing_ok =3D true; $$ =3D (Node *)n; } - /* ALTER TABLE ALTER [COLUMN] SET (COMPRESSION ) = */ - | ALTER opt_column ColId SET optColumnCompression - { - AlterTableCmd *n =3D makeNode(AlterTableCmd); - n->subtype =3D AT_SetCompression; - n->name =3D $3; - n->def =3D (Node *) makeString($5); - $$ =3D (Node *)n; - } /* ALTER TABLE DROP [COLUMN] IF EXISTS [RESTRICT|CASC= ADE] */ | DROP opt_column IF_P EXISTS ColId opt_drop_behavior { @@ -3462,7 +3462,7 @@ TypedTableElement: | TableConstraint { $$ =3D $1; } ; = -columnDef: ColId Typename optColumnCompression create_generic_options Col= QualList +columnDef: ColId Typename opt_column_compression create_generic_options C= olQualList { ColumnDef *n =3D makeNode(ColumnDef); n->colname =3D $1; @@ -3522,13 +3522,15 @@ columnOptions: ColId ColQualList } ; = -optColumnCompression: - COMPRESSION name - { - $$ =3D $2; - } - | /*EMPTY*/ { $$ =3D NULL; } - ; +column_compression: + COMPRESSION ColId { $$ =3D $2; } + | COMPRESSION DEFAULT { $$ =3D pstrdup("default"); } + ; + +opt_column_compression: + column_compression { $$ =3D $1; } + | /*EMPTY*/ { $$ =3D NULL; } + ; = ColQualList: ColQualList ColConstraint { $$ =3D 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 f= 1::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 =3D 'I do not exist com= pression'; ERROR: invalid value for parameter "default_toast_compression": "I do no= t exist compression" HINT: Available values: pglz, lz4. SET default_toast_compression =3D '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 =3D '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/regres= s/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 f= 1::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 =3D '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 =3D '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/c= ompression.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::i= nteger; --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(c= mdata); SET default_toast_compression =3D ''; SET default_toast_compression =3D 'I do not exist compression'; SET default_toast_compression =3D 'lz4'; -DROP TABLE cmdata2; -CREATE TABLE cmdata2 (f1 text); -\d+ cmdata2 SET default_toast_compression =3D '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; = ------- =_aaaaaaaaaa0--