public inbox for [email protected]
help / color / mirror / Atom feedFrom: dilipkumar <[email protected]>
Subject: [PATCH v24 06/10] alter table set compression
Date: Fri, 18 Dec 2020 11:31:22 +0530
Add support for changing the compression method associated
with a column, forcing a table rewrite.
Dilip Kumar based on the patches from Ildus Kurbangaliev.
Design input from Robert Haas and Tomas Vondra.
Reviewed by Robert Haas, Tomas Vondra, Alexander Korotkov
Discussions:
https://www.postgresql.org/message-id/[email protected]
https://www.postgresql.org/message-id/CA%2BTgmoaKDW1Oi9V%3Djc9hOGyf77NbkNEABuqgHD1Cq%3D%3D1QsOcxg%40...
https://www.postgresql.org/message-id/CA%2BTgmobSDVgUage9qQ5P_%3DF_9jaMkCgyKxUQGtFQU7oN4kX-AA%40mail...
https://www.postgresql.org/message-id/20201005160355.byp74sh3ejsv7wrj%40development
https://www.postgresql.org/message-id/CAFiTN-tzTTT2oqWdRGLv1dvvS5MC1W%2BLE%2B3bqWPJUZj4GnHOJg%40mail...
---
doc/src/sgml/ref/alter_table.sgml | 16 ++
src/backend/commands/createas.c | 3 +-
src/backend/commands/matview.c | 3 +-
src/backend/commands/tablecmds.c | 226 +++++++++++++++-----
src/backend/executor/nodeModifyTable.c | 9 +-
src/backend/parser/gram.y | 9 +
src/bin/psql/tab-complete.c | 2 +-
src/include/commands/event_trigger.h | 1 +
src/include/executor/executor.h | 3 +-
src/include/nodes/parsenodes.h | 3 +-
src/test/regress/expected/compression.out | 93 ++++++--
src/test/regress/expected/compression_1.out | 56 ++++-
src/test/regress/sql/compression.sql | 21 ++
13 files changed, 370 insertions(+), 75 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index c25ef5abd6..0bd0c1a503 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -54,6 +54,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET ( <replaceable class="parameter">attribute_option</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> RESET ( <replaceable class="parameter">attribute_option</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
+ ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET COMPRESSION <replaceable class="parameter">compression_method</replaceable>
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
@@ -103,6 +104,7 @@ 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 ]
@@ -383,6 +385,20 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>
+ <literal>SET COMPRESSION <replaceable class="parameter">compression_method</replaceable></literal>
+ </term>
+ <listitem>
+ <para>
+ This sets the compression method for a 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>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]</literal></term>
<listitem>
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 1d17dc0d6b..748ec29570 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -589,7 +589,8 @@ intorel_receive(TupleTableSlot *slot, DestReceiver *self)
*/
slot = CompareCompressionMethodAndDecompress(slot,
&myState->decompress_tuple_slot,
- myState->rel->rd_att);
+ myState->rel->rd_att,
+ NULL);
/*
* Note that the input slot might not be of the type of the target
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 713fc3fceb..779b4e51cf 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -494,7 +494,8 @@ transientrel_receive(TupleTableSlot *slot, DestReceiver *self)
*/
slot = CompareCompressionMethodAndDecompress(slot,
&myState->decompress_tuple_slot,
- myState->transientrel->rd_att);
+ myState->transientrel->rd_att,
+ NULL);
/*
* Note that the input slot might not be of the type of the target
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 72ba017814..586a92f0c1 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -529,6 +529,8 @@ static void ATExecGenericOptions(Relation rel, List *options);
static void ATExecEnableRowSecurity(Relation rel);
static void ATExecDisableRowSecurity(Relation rel);
static void ATExecForceNoForceRowSecurity(Relation rel, bool force_rls);
+static ObjectAddress ATExecSetCompression(AlteredTableInfo *tab, Relation rel,
+ const char *column, Node *newValue, LOCKMODE lockmode);
static void index_copy_data(Relation rel, RelFileNode newrnode);
static const char *storage_name(char c);
@@ -3968,6 +3970,7 @@ AlterTableGetLockLevel(List *cmds)
*/
case AT_GenericOptions:
case AT_AlterColumnGenericOptions:
+ case AT_SetCompression:
cmd_lockmode = AccessExclusiveLock;
break;
@@ -4495,7 +4498,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
case AT_DisableRowSecurity:
case AT_ForceRowSecurity:
case AT_NoForceRowSecurity:
- ATSimplePermissions(rel, ATT_TABLE);
+ case AT_SetCompression:
+ ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW);
/* These commands never recurse */
/* No command-specific prep needed */
pass = AT_PASS_MISC;
@@ -4903,6 +4907,10 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
Assert(rel->rd_rel->relkind == RELKIND_INDEX);
ATExecAlterCollationRefreshVersion(rel, cmd->object);
break;
+ case AT_SetCompression:
+ address = ATExecSetCompression(tab, rel, cmd->name, cmd->def,
+ lockmode);
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
@@ -5519,6 +5527,7 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
while (table_scan_getnextslot(scan, ForwardScanDirection, oldslot))
{
+ bool decompressed = false;
TupleTableSlot *insertslot;
if (tab->rewrite > 0)
@@ -5527,11 +5536,25 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
slot_getallattrs(oldslot);
ExecClearTuple(newslot);
- /* copy attributes */
- memcpy(newslot->tts_values, oldslot->tts_values,
- sizeof(Datum) * oldslot->tts_nvalid);
- memcpy(newslot->tts_isnull, oldslot->tts_isnull,
- sizeof(bool) * oldslot->tts_nvalid);
+ if (tab->rewrite & AT_REWRITE_ALTER_COMPRESSION)
+ (void) CompareCompressionMethodAndDecompress(oldslot,
+ &newslot,
+ newTupDesc,
+ &decompressed);
+
+ /*
+ * copy attributes, if we have decompressed some attribute then
+ * the values and nulls array is already copied
+ */
+ if (!decompressed)
+ {
+ memcpy(newslot->tts_values, oldslot->tts_values,
+ sizeof(Datum) * oldslot->tts_nvalid);
+ memcpy(newslot->tts_isnull, oldslot->tts_isnull,
+ sizeof(bool) * oldslot->tts_nvalid);
+ }
+ else
+ ExecClearTuple(newslot);
/* Set dropped attributes to null in new tuple */
foreach(lc, dropped_attrs)
@@ -7767,6 +7790,67 @@ ATExecSetOptions(Relation rel, const char *colName, Node *options,
return address;
}
+/*
+ * Helper function for ATExecSetStorage and ATExecSetCompression
+ *
+ * Set the attcompression and attstorage for the respective index attribute if
+ * the respective input values are valid.
+ */
+static void
+ApplyChangesToIndexes(Relation rel, Relation attrelation, AttrNumber attnum,
+ Oid newcompression, char newstorage, LOCKMODE lockmode)
+{
+ HeapTuple tuple;
+ ListCell *lc;
+ Form_pg_attribute attrtuple;
+
+ foreach(lc, RelationGetIndexList(rel))
+ {
+ Oid indexoid = lfirst_oid(lc);
+ Relation indrel;
+ AttrNumber indattnum = 0;
+
+ indrel = index_open(indexoid, lockmode);
+
+ for (int i = 0; i < indrel->rd_index->indnatts; i++)
+ {
+ if (indrel->rd_index->indkey.values[i] == attnum)
+ {
+ indattnum = i + 1;
+ break;
+ }
+ }
+
+ if (indattnum == 0)
+ {
+ index_close(indrel, lockmode);
+ continue;
+ }
+
+ tuple = SearchSysCacheCopyAttNum(RelationGetRelid(indrel), indattnum);
+
+ if (HeapTupleIsValid(tuple))
+ {
+ attrtuple = (Form_pg_attribute) GETSTRUCT(tuple);
+
+ if (OidIsValid(newcompression))
+ attrtuple->attcompression = newcompression;
+
+ if (newstorage != '\0')
+ attrtuple->attstorage = newstorage;
+
+ CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel),
+ attrtuple->attnum);
+
+ heap_freetuple(tuple);
+ }
+
+ index_close(indrel, lockmode);
+ }
+}
/*
* ALTER TABLE ALTER COLUMN SET STORAGE
*
@@ -7782,7 +7866,6 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE loc
Form_pg_attribute attrtuple;
AttrNumber attnum;
ObjectAddress address;
- ListCell *lc;
Assert(IsA(newValue, String));
storagemode = strVal(newValue);
@@ -7846,47 +7929,8 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE loc
* Apply the change to indexes as well (only for simple index columns,
* matching behavior of index.c ConstructTupleDescriptor()).
*/
- foreach(lc, RelationGetIndexList(rel))
- {
- Oid indexoid = lfirst_oid(lc);
- Relation indrel;
- AttrNumber indattnum = 0;
-
- indrel = index_open(indexoid, lockmode);
-
- for (int i = 0; i < indrel->rd_index->indnatts; i++)
- {
- if (indrel->rd_index->indkey.values[i] == attnum)
- {
- indattnum = i + 1;
- break;
- }
- }
-
- if (indattnum == 0)
- {
- index_close(indrel, lockmode);
- continue;
- }
-
- tuple = SearchSysCacheCopyAttNum(RelationGetRelid(indrel), indattnum);
-
- if (HeapTupleIsValid(tuple))
- {
- attrtuple = (Form_pg_attribute) GETSTRUCT(tuple);
- attrtuple->attstorage = newstorage;
-
- CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);
-
- InvokeObjectPostAlterHook(RelationRelationId,
- RelationGetRelid(rel),
- attrtuple->attnum);
-
- heap_freetuple(tuple);
- }
-
- index_close(indrel, lockmode);
- }
+ ApplyChangesToIndexes(rel, attrelation, attnum, InvalidOid, newstorage,
+ lockmode);
table_close(attrelation, RowExclusiveLock);
@@ -15033,6 +15077,92 @@ ATExecGenericOptions(Relation rel, List *options)
heap_freetuple(tuple);
}
+/*
+ * ALTER TABLE ALTER COLUMN SET COMPRESSION
+ *
+ * Return value is the address of the modified column
+ */
+static ObjectAddress
+ATExecSetCompression(AlteredTableInfo *tab,
+ Relation rel,
+ const char *column,
+ Node *newValue,
+ LOCKMODE lockmode)
+{
+ Relation attrel;
+ HeapTuple tuple;
+ Form_pg_attribute atttableform;
+ AttrNumber attnum;
+ char *compression;
+ char typstorage;
+ Oid cmoid;
+ Datum values[Natts_pg_attribute];
+ bool nulls[Natts_pg_attribute];
+ bool replace[Natts_pg_attribute];
+ ObjectAddress address;
+
+ Assert(IsA(newValue, String));
+ compression = strVal(newValue);
+
+ attrel = table_open(AttributeRelationId, RowExclusiveLock);
+
+ tuple = SearchSysCacheAttName(RelationGetRelid(rel), column);
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ column, RelationGetRelationName(rel))));
+
+ /* Prevent them from altering a system attribute */
+ atttableform = (Form_pg_attribute) GETSTRUCT(tuple);
+ attnum = atttableform->attnum;
+ if (attnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter system column \"%s\"", column)));
+
+ typstorage = get_typstorage(atttableform->atttypid);
+
+ /* Prevent from setting compression methods for uncompressible type */
+ if (!IsStorageCompressible(typstorage))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("column data type %s does not support compression",
+ format_type_be(atttableform->atttypid))));
+
+ /* Initialize buffers for new tuple values */
+ memset(values, 0, sizeof(values));
+ memset(nulls, false, sizeof(nulls));
+ memset(replace, false, sizeof(replace));
+
+ /* Get the attribute compression method. */
+ cmoid = GetAttributeCompression(atttableform, compression);
+
+ if (atttableform->attcompression != cmoid)
+ tab->rewrite |= AT_REWRITE_ALTER_COMPRESSION;
+
+ atttableform->attcompression = cmoid;
+ CatalogTupleUpdate(attrel, &tuple->t_self, tuple);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel),
+ atttableform->attnum);
+
+ ReleaseSysCache(tuple);
+
+ /* apply changes to the index column as well */
+ ApplyChangesToIndexes(rel, attrel, attnum, cmoid, '\0', lockmode);
+ table_close(attrel, RowExclusiveLock);
+
+ /* make changes visible */
+ CommandCounterIncrement();
+
+ ObjectAddressSubSet(address, RelationRelationId,
+ RelationGetRelid(rel), atttableform->attnum);
+ return address;
+}
+
+
/*
* Preparation phase for SET LOGGED/UNLOGGED
*
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 920d9dd0d5..ea82a05591 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -2052,7 +2052,8 @@ ExecPrepareTupleRouting(ModifyTableState *mtstate,
TupleTableSlot *
CompareCompressionMethodAndDecompress(TupleTableSlot *slot,
TupleTableSlot **outslot,
- TupleDesc targetTupDesc)
+ TupleDesc targetTupDesc,
+ bool *decompressed)
{
int i;
int attnum;
@@ -2139,6 +2140,9 @@ CompareCompressionMethodAndDecompress(TupleTableSlot *slot,
*outslot = newslot;
+ if (decompressed != NULL)
+ *decompressed = true;
+
return newslot;
}
@@ -2360,7 +2364,8 @@ ExecModifyTable(PlanState *pstate)
*/
slot = CompareCompressionMethodAndDecompress(slot,
&node->mt_decompress_tuple_slot,
- resultRelInfo->ri_RelationDesc->rd_att);
+ resultRelInfo->ri_RelationDesc->rd_att,
+ NULL);
switch (operation)
{
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 52d92df25d..30acfe615d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2308,6 +2308,15 @@ 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
{
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 1e1c315bae..ffa8d05edf 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2098,7 +2098,7 @@ psql_completion(const char *text, int start, int end)
/* ALTER TABLE ALTER [COLUMN] <foo> SET */
else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET") ||
Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET"))
- COMPLETE_WITH("(", "DEFAULT", "NOT NULL", "STATISTICS", "STORAGE");
+ COMPLETE_WITH("(", "COMPRESSION", "DEFAULT", "NOT NULL", "STATISTICS", "STORAGE");
/* ALTER TABLE ALTER [COLUMN] <foo> SET ( */
else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET", "(") ||
Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET", "("))
diff --git a/src/include/commands/event_trigger.h b/src/include/commands/event_trigger.h
index c11bf2d781..5a314f4c1d 100644
--- a/src/include/commands/event_trigger.h
+++ b/src/include/commands/event_trigger.h
@@ -32,6 +32,7 @@ typedef struct EventTriggerData
#define AT_REWRITE_ALTER_PERSISTENCE 0x01
#define AT_REWRITE_DEFAULT_VAL 0x02
#define AT_REWRITE_COLUMN_REWRITE 0x04
+#define AT_REWRITE_ALTER_COMPRESSION 0x08
/*
* EventTriggerData is the node type that is passed as fmgr "context" info
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index 6495162a33..050ef2dcd0 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -623,5 +623,6 @@ extern void CheckSubscriptionRelkind(char relkind, const char *nspname,
const char *relname);
extern TupleTableSlot *CompareCompressionMethodAndDecompress(TupleTableSlot *slot,
TupleTableSlot **outslot,
- TupleDesc targetTupDesc);
+ TupleDesc targetTupDesc,
+ bool *decompressed);
#endif /* EXECUTOR_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 19d2ba26bf..f9a87dee02 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1901,7 +1901,8 @@ typedef enum AlterTableType
AT_AddIdentity, /* ADD IDENTITY */
AT_SetIdentity, /* SET identity column options */
AT_DropIdentity, /* DROP IDENTITY */
- AT_AlterCollationRefreshVersion /* ALTER COLLATION ... REFRESH VERSION */
+ AT_AlterCollationRefreshVersion, /* ALTER COLLATION ... REFRESH VERSION */
+ AT_SetCompression /* SET COMPRESSION */
} AlterTableType;
typedef struct ReplicaIdentityStmt
diff --git a/src/test/regress/expected/compression.out b/src/test/regress/expected/compression.out
index 167878e78b..21c1b451d2 100644
--- a/src/test/regress/expected/compression.out
+++ b/src/test/regress/expected/compression.out
@@ -4,20 +4,20 @@ 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 | Stats target | Description
---------+------+-----------+----------+---------+----------+--------------+-------------
- f1 | text | | | | extended | |
+ Table "public.cmdata"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ f1 | text | | | | extended | pglz | |
Indexes:
"idx" btree (f1)
CREATE TABLE cmdata1(f1 TEXT COMPRESSION lz4);
INSERT INTO cmdata1 VALUES(repeat('1234567890',1004));
\d+ cmdata1
- Table "public.cmdata1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+------+-----------+----------+---------+----------+--------------+-------------
- f1 | text | | | | extended | |
+ Table "public.cmdata1"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ f1 | text | | | | extended | lz4 | |
-- try setting compression for incompressible data type
CREATE TABLE cmdata2 (f1 int COMPRESSION pglz);
@@ -112,18 +112,18 @@ DROP TABLE cmdata2;
-- test LIKE INCLUDING COMPRESSION
CREATE TABLE cmdata2 (LIKE cmdata1 INCLUDING COMPRESSION);
\d+ cmdata2
- Table "public.cmdata2"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+------+-----------+----------+---------+----------+--------------+-------------
- f1 | text | | | | extended | |
+ Table "public.cmdata2"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ f1 | text | | | | extended | lz4 | |
-- test compression with materialized view
CREATE MATERIALIZED VIEW mv(x) AS SELECT * FROM cmdata1;
\d+ mv
- Materialized view "public.mv"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+------+-----------+----------+---------+----------+--------------+-------------
- x | text | | | | extended | |
+ Materialized view "public.mv"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ x | text | | | | extended | pglz | |
View definition:
SELECT cmdata1.f1 AS x
FROM cmdata1;
@@ -165,6 +165,67 @@ CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata);
NOTICE: merging column "f1" with inherited definition
ERROR: column "f1" has a compression method conflict
DETAIL: pglz versus lz4
+-- test alter compression method with rewrite
+ALTER TABLE cmdata ALTER COLUMN f1 SET COMPRESSION lz4;
+\d+ cmdata
+ Table "public.cmdata"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ f1 | text | | | | extended | lz4 | |
+Indexes:
+ "idx" btree (f1)
+
+SELECT pg_column_compression(f1) FROM cmdata;
+ pg_column_compression
+-----------------------
+ lz4
+(1 row)
+
+-- test alter compression method for the materialized view
+ALTER TABLE cmdata1 ALTER COLUMN f1 SET COMPRESSION pglz;
+ALTER MATERIALIZED VIEW mv ALTER COLUMN x SET COMPRESSION lz4;
+REFRESH MATERIALIZED VIEW mv;
+\d+ mv
+ Materialized view "public.mv"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ x | text | | | | extended | lz4 | |
+View definition:
+ SELECT cmdata1.f1 AS x
+ FROM cmdata1;
+
+SELECT pg_column_compression(f1) FROM cmdata1;
+ pg_column_compression
+-----------------------
+ pglz
+ pglz
+(2 rows)
+
+SELECT pg_column_compression(x) FROM mv;
+ pg_column_compression
+-----------------------
+ lz4
+ lz4
+(2 rows)
+
+-- test alter compression method for the partitioned table
+ALTER TABLE cmpart ALTER COLUMN f1 SET COMPRESSION pglz;
+SELECT pg_column_compression(f1) FROM cmpart;
+ pg_column_compression
+-----------------------
+ lz4
+ pglz
+(2 rows)
+
+ALTER TABLE cmpart1 ALTER COLUMN f1 SET COMPRESSION pglz;
+ALTER TABLE cmpart2 ALTER COLUMN f1 SET COMPRESSION lz4;
+SELECT pg_column_compression(f1) FROM cmpart;
+ pg_column_compression
+-----------------------
+ pglz
+ lz4
+(2 rows)
+
-- check data is ok
SELECT length(f1) FROM cmdata;
length
diff --git a/src/test/regress/expected/compression_1.out b/src/test/regress/expected/compression_1.out
index 329e7881b0..64c5855bf7 100644
--- a/src/test/regress/expected/compression_1.out
+++ b/src/test/regress/expected/compression_1.out
@@ -4,10 +4,10 @@ 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 | Stats target | Description
---------+------+-----------+----------+---------+----------+--------------+-------------
- f1 | text | | | | extended | |
+ Table "public.cmdata"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ f1 | text | | | | extended | pglz | |
Indexes:
"idx" btree (f1)
@@ -157,6 +157,54 @@ CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata);
NOTICE: merging column "f1" with inherited definition
ERROR: column "f1" has a compression method conflict
DETAIL: pglz versus lz4
+-- test alter compression method with rewrite
+ALTER TABLE cmdata ALTER COLUMN f1 SET COMPRESSION lz4;
+ERROR: not built with lz4 support
+\d+ cmdata
+ Table "public.cmdata"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ f1 | text | | | | extended | pglz | |
+Indexes:
+ "idx" btree (f1)
+
+SELECT pg_column_compression(f1) FROM cmdata;
+ pg_column_compression
+-----------------------
+ pglz
+(1 row)
+
+-- test alter compression method for the materialized view
+ALTER TABLE cmdata1 ALTER COLUMN f1 SET COMPRESSION pglz;
+ERROR: relation "cmdata1" does not exist
+ALTER MATERIALIZED VIEW mv ALTER COLUMN x SET COMPRESSION lz4;
+ERROR: relation "mv" does not exist
+REFRESH MATERIALIZED VIEW mv;
+ERROR: relation "mv" does not exist
+\d+ mv
+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;
+ ^
+-- test alter compression method for the partitioned table
+ALTER TABLE cmpart ALTER COLUMN f1 SET COMPRESSION pglz;
+ERROR: relation "cmpart" does not exist
+SELECT pg_column_compression(f1) FROM cmpart;
+ERROR: relation "cmpart" does not exist
+LINE 1: SELECT pg_column_compression(f1) FROM cmpart;
+ ^
+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: not built with lz4 support
+SELECT pg_column_compression(f1) FROM cmpart;
+ERROR: relation "cmpart" does not exist
+LINE 1: SELECT pg_column_compression(f1) FROM cmpart;
+ ^
-- check data is ok
SELECT length(f1) FROM cmdata;
length
diff --git a/src/test/regress/sql/compression.sql b/src/test/regress/sql/compression.sql
index 450416ecb4..b9daa33b74 100644
--- a/src/test/regress/sql/compression.sql
+++ b/src/test/regress/sql/compression.sql
@@ -75,6 +75,27 @@ SELECT pg_column_compression(f1) FROM cmpart;
CREATE TABLE cminh() INHERITS(cmdata, cmdata1);
CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata);
+-- test alter compression method with rewrite
+ALTER TABLE cmdata ALTER COLUMN f1 SET COMPRESSION lz4;
+\d+ cmdata
+SELECT pg_column_compression(f1) FROM cmdata;
+
+-- test alter compression method for the materialized view
+ALTER TABLE cmdata1 ALTER COLUMN f1 SET COMPRESSION pglz;
+ALTER MATERIALIZED VIEW mv ALTER COLUMN x SET COMPRESSION lz4;
+REFRESH MATERIALIZED VIEW mv;
+\d+ mv
+SELECT pg_column_compression(f1) FROM cmdata1;
+SELECT pg_column_compression(x) FROM mv;
+
+-- test alter compression method for the partitioned table
+ALTER TABLE cmpart ALTER COLUMN f1 SET COMPRESSION pglz;
+SELECT pg_column_compression(f1) FROM cmpart;
+
+ALTER TABLE cmpart1 ALTER COLUMN f1 SET COMPRESSION pglz;
+ALTER TABLE cmpart2 ALTER COLUMN f1 SET COMPRESSION lz4;
+SELECT pg_column_compression(f1) FROM cmpart;
+
-- check data is ok
SELECT length(f1) FROM cmdata;
SELECT length(f1) FROM cmdata1;
--
2.17.0
--m51xatjYGsM+13rf
Content-Type: text/x-diff; charset=iso-8859-1
Content-Disposition: attachment;
filename="v24-0007-Add-support-for-PRESERVE.patch"
Content-Transfer-Encoding: 8bit
view thread (118+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v24 06/10] alter table set compression
In-Reply-To: <no-message-id-1877317@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox