From 8e5276e8fd282078ca3e741b230fda897b9a6c7e Mon Sep 17 00:00:00 2001 From: dilipkumar Date: Fri, 18 Dec 2020 11:31:22 +0530 Subject: [PATCH v21 3/7] alter table set compression 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/20171213151818.75a20259@postgrespro.ru https://www.postgresql.org/message-id/CA%2BTgmoaKDW1Oi9V%3Djc9hOGyf77NbkNEABuqgHD1Cq%3D%3D1QsOcxg%40mail.gmail.com https://www.postgresql.org/message-id/CA%2BTgmobSDVgUage9qQ5P_%3DF_9jaMkCgyKxUQGtFQU7oN4kX-AA%40mail.gmail.com https://www.postgresql.org/message-id/20201005160355.byp74sh3ejsv7wrj%40development https://www.postgresql.org/message-id/CAFiTN-tzTTT2oqWdRGLv1dvvS5MC1W%2BLE%2B3bqWPJUZj4GnHOJg%40mail.gmail.com --- doc/src/sgml/ref/alter_table.sgml | 14 ++ src/backend/commands/tablecmds.c | 208 ++++++++++++++++---- src/backend/parser/gram.y | 9 + src/bin/psql/tab-complete.c | 2 +- src/include/commands/event_trigger.h | 1 + src/include/nodes/parsenodes.h | 3 +- src/test/regress/expected/compression.out | 59 ++++++ src/test/regress/expected/compression_1.out | 57 ++++++ src/test/regress/sql/compression.sql | 21 ++ 9 files changed, 329 insertions(+), 45 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index c25ef5abd6..c4b53ec1c5 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 ] name ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] ) ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] ) ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } + ALTER [ COLUMN ] column_name SET COMPRESSION compression_method ADD table_constraint [ NOT VALID ] ADD table_constraint_using_index ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] @@ -383,6 +384,19 @@ WITH ( MODULUS numeric_literal, REM + + + SET COMPRESSION compression_method + + + + This clause adds compression to a column. The Compression method can be + set to any available compression method. The built-in methods are + pglz and lz4. + + + + ADD table_constraint [ NOT VALID ] diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 082db26bfa..89cca760c5 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); @@ -5527,6 +5535,9 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode) slot_getallattrs(oldslot); ExecClearTuple(newslot); + if (tab->rewrite & AT_REWRITE_ALTER_COMPRESSION) + CompareCompressionMethodAndDecompress(oldslot, newTupDesc); + /* copy attributes */ memcpy(newslot->tts_values, oldslot->tts_values, sizeof(Datum) * oldslot->tts_nvalid); @@ -7767,6 +7778,71 @@ ATExecSetOptions(Relation rel, const char *colName, Node *options, return address; } +/* + * Helper function for ATExecSetStorage and ATExecSetCompression + * + * Set the attcompression or attstorage for the respective index attribute. If + * attcompression is a valid oid then it will update the attcompression for the + * index attribute otherwise it will update the attstorage. + */ +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 a valid compression method Oid is passed then update the + * attcompression, otherwise update the attstorage. + */ + if (OidIsValid(newcompression)) + attrtuple->attcompression = newcompression; + else + 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 +7858,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 +7921,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 +15069,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/parser/gram.y b/src/backend/parser/gram.y index 355e273192..f2e9fe4ff8 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -2307,6 +2307,15 @@ alter_table_cmd: n->missing_ok = true; $$ = (Node *)n; } + /* ALTER TABLE ALTER [COLUMN] SET (COMPRESSION ) */ + | 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 DROP [COLUMN] IF EXISTS [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 17f7265038..7aeabf9543 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] 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] 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/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 232c9f1248..ffb739f565 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1875,7 +1875,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 4dea269fc3..84d31c781b 100644 --- a/src/test/regress/expected/compression.out +++ b/src/test/regress/expected/compression.out @@ -142,6 +142,65 @@ 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 +(1 row) + +SELECT pg_column_compression(x) FROM mv; + pg_column_compression +----------------------- + lz4 +(1 row) + +-- test alter compression method for the partioned 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 a50c7679bb..3250c12859 100644 --- a/src/test/regress/expected/compression_1.out +++ b/src/test/regress/expected/compression_1.out @@ -139,6 +139,63 @@ 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; +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 +----------------------- +(0 rows) + +SELECT pg_column_compression(x) FROM mv; + pg_column_compression +----------------------- +(0 rows) + +-- test alter compression method for the partioned table +ALTER TABLE cmpart ALTER COLUMN f1 SET COMPRESSION pglz; +SELECT pg_column_compression(f1) FROM cmpart; + pg_column_compression +----------------------- + pglz +(1 row) + +ALTER TABLE cmpart1 ALTER COLUMN f1 SET COMPRESSION pglz; +ALTER TABLE cmpart2 ALTER COLUMN f1 SET COMPRESSION lz4; +ERROR: not built with lz4 support +SELECT pg_column_compression(f1) FROM cmpart; + pg_column_compression +----------------------- + pglz +(1 row) + -- 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 0750a6c80e..3b0da88c19 100644 --- a/src/test/regress/sql/compression.sql +++ b/src/test/regress/sql/compression.sql @@ -65,6 +65,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 partioned 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