This is an automated email from the ASF dual-hosted git repository. reshke pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit dd4d96d3d4a54e85aa53a595679cb9df873f5d2e Author: Huansong Fu <[email protected]> AuthorDate: Tue Jul 19 07:13:55 2022 -0700 ALTER TABLE SET ACCESS METHOD: AO->AOCO support Currently adding support for the following cases: ``` CREATE TABLE foo (appendonly=true); ALTER TABLE foo SET ACCESS METHOD ao_column; -- Or: ALTER TABLE foo SET WITH (appendonly=true, orientation=column); ``` Similar to other variations of ATSETAM commands, user can also specify reloptions in a WITH clause, such as: ``` ALTER TABLE foo SET ACCESS METHOD ao_column WITH (blocksize=65536); ``` If no reloptions are given, the new AOCO table will use the existing table-level options for its column encoding options. If any reloption is given in the WITH clause, it will be recorded in the catalog and then used for the column encoding option too. Note that there was once a thought to support specifying column-level encoding in the ATSETAM command, but was abandoned because there exists better alternatives. Discussions see https://groups.google.com/a/greenplum.org/g/gpdb-dev/c/NaNH6TssgA8 --- src/backend/catalog/pg_appendonly.c | 4 +- src/backend/commands/tablecmds.c | 35 +++++ src/backend/nodes/makefuncs.c | 1 + src/backend/parser/gram.y | 38 +++--- src/test/regress/expected/alter_table_set_am.out | 160 ++++++++++++++++++++++- src/test/regress/sql/alter_table_set_am.sql | 82 +++++++++++- 6 files changed, 293 insertions(+), 27 deletions(-) diff --git a/src/backend/catalog/pg_appendonly.c b/src/backend/catalog/pg_appendonly.c index 1f51d8a625..c1c364cdbd 100644 --- a/src/backend/catalog/pg_appendonly.c +++ b/src/backend/catalog/pg_appendonly.c @@ -578,9 +578,7 @@ ATAOEntries(Form_pg_class relform1, Form_pg_class relform2, swapAppendonlyEntriesUsingTAM(relform1, relform2, frozenXid, cutoffMulti); break; case AO_COLUMN_TABLE_AM_OID: - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("alter table does not support switch from AO to AOCO"))); + SwapAppendonlyEntries(relform1->oid, relform2->oid); break; default: ereport(ERROR, diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 7a419c5c33..437b2d31fe 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -584,6 +584,7 @@ static bool prebuild_temp_table(Relation rel, RangeVar *tmpname, DistributedBy * static void checkATSetDistributedByStandalone(AlteredTableInfo *tab, Relation rel); +static void populate_rel_col_encodings(Relation rel, List *stenc, List *withOptions); /* ---------------------------------------------------------------- @@ -4818,6 +4819,35 @@ AlterTable(AlterTableStmt *stmt, LOCKMODE lockmode, } } +/* + * Populate the column encoding option for each column in the relation. + */ +static void populate_rel_col_encodings(Relation rel, List *stenc, List *withOptions) +{ + int attno; + List *colDefs = NIL; + TupleDesc tupdesc = RelationGetDescr(rel); + + /* Figure out the column definition list. */ + for (attno = 0; attno < tupdesc->natts; attno++) + { + Form_pg_attribute att = TupleDescAttr(tupdesc, attno); + ColumnDef *cd = makeColumnDef(NameStr(att->attname), + att->atttypid, + att->atttypmod, + 0); + colDefs = lappend(colDefs, cd); + } + + List *attr_encodings = transformColumnEncoding(rel, + colDefs /*column clauses*/, + stenc /*encoding clauses*/, + withOptions /*withOptions*/, + rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE /*rootpartition*/, + false /*errorOnEncodingClause*/); + AddRelationAttributeEncodings(rel, attr_encodings); +} + /* * AlterTableInternal * @@ -6179,6 +6209,11 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, if (aoopt_changed) tab->rewrite |= AT_REWRITE_ALTER_RELOPTS; } + + /* If we are changing AM to AOCO, add pg_attribute_encoding entries for each column. */ + if (tab->newAccessMethod == AO_COLUMN_TABLE_AM_OID) + populate_rel_col_encodings(rel, NULL, (List*)cmd->def); + break; case AT_SetTableSpace: /* SET TABLESPACE */ diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index 6ac95ee2b4..cbf64e2ad0 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -506,6 +506,7 @@ makeColumnDef(const char *colname, Oid typeOid, int32 typmod, Oid collOid) n->collClause = NULL; n->collOid = collOid; n->constraints = NIL; + n->encoding = NIL; n->fdwoptions = NIL; n->location = -1; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 8b8a3c28bd..f300cdb828 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -3809,19 +3809,11 @@ alter_table_cmd: n->newowner = $3; $$ = (Node *)n; } - /* ALTER TABLE <name> SET ACCESS METHOD <amname> */ - | SET ACCESS METHOD name - { - AlterTableCmd *n = makeNode(AlterTableCmd); - n->subtype = AT_SetAccessMethod; - n->name = $4; - $$ = (Node *)n; - } /* ALTER TABLE <name> SET ACCESS METHOD <amname> WITH (<reloptions>) */ - | SET ACCESS METHOD name WITH definition + | SET ACCESS METHOD name OptWith { AlterTableCmd *n = makeNode(AlterTableCmd); - char *witham = greenplumLegacyAOoptions(n->name, &$6); + char *witham = greenplumLegacyAOoptions(n->name, &$5); n->subtype = AT_SetAccessMethod; n->name = $4; /* @@ -3829,18 +3821,24 @@ alter_table_cmd: * clause such as 'appendonly' or 'appendoptimized', it has * to match with the AM name. */ - if (witham && - (strlen(witham) != strlen(n->name) || - strncmp(n->name, witham, strlen(n->name) != 0))) + if (witham) { - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("ACCESS METHOD is specified as \"%s\" but " - "the WITH option indicates it to be \"%s\"", - n->name, witham), - parser_errposition(@5))); + if (strlen(witham) != strlen(n->name) || + strncmp(n->name, witham, strlen(n->name) != 0)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("ACCESS METHOD is specified as \"%s\" but " + "the WITH option indicates it to be \"%s\"", + n->name, witham), + parser_errposition(@5))); + else + ereport(NOTICE, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("Redundant clauses are used to indicate the access method."), + errhint("Only one of these is needed to indicate access method: the " + "SET ACCESS METHOD clause or the options in the WITH clause."))); } - n->def = (Node *) $6; + n->def = (Node *) $5; $$ = (Node *)n; } /* ALTER TABLE <name> SET TABLESPACE <tablespacename> */ diff --git a/src/test/regress/expected/alter_table_set_am.out b/src/test/regress/expected/alter_table_set_am.out index 9fe0e3d968..58e0f934d4 100644 --- a/src/test/regress/expected/alter_table_set_am.out +++ b/src/test/regress/expected/alter_table_set_am.out @@ -601,12 +601,168 @@ SELECT c.relname, a.amname, c.reloptions FROM pg_class c JOIN pg_am a ON c.relam DROP TABLE ataoset; DROP TABLE ataoset2; --- Final scenario: run the iterations of AT from "A" to "B" and back to "A", that includes: +-- Scenario 5: AO to AOCO +SET gp_default_storage_options = 'blocksize=65536, compresstype=zlib, compresslevel=5, checksum=true'; +CREATE TABLE ao2co(a int, b int) WITH (appendonly=true); +CREATE TABLE ao2co2(a int, b int) WITH (appendonly=true); +CREATE TABLE ao2co3(a int, b int) WITH (appendonly=true); +CREATE TABLE ao2co4(a int, b int) WITH (appendonly=true); +CREATE INDEX index_ao2co ON ao2co(b); +CREATE INDEX index_ao2co3 ON ao2co3(b); +INSERT INTO ao2co SELECT i,i FROM generate_series(1,5) i; +INSERT INTO ao2co2 SELECT i,i FROM generate_series(1,5) i; +INSERT INTO ao2co3 SELECT i,i FROM generate_series(1,5) i; +INSERT INTO ao2co4 SELECT i,i FROM generate_series(1,5) i; +-- ERROR: conflicting storage option specified. +ALTER TABLE ao2co SET ACCESS METHOD ao_column WITH (appendoptimized=true, orientation=row); +ERROR: ACCESS METHOD is specified as "ao_column" but the WITH option indicates it to be "ao_row" +LINE 1: ALTER TABLE ao2co SET ACCESS METHOD ao_column WITH (appendop... + ^ +-- Use of *both* ACCESS METHOD and WITH clauses is allowed, but we'll print a hint to indicate the redundancy. +ALTER TABLE ao2co SET ACCESS METHOD ao_row WITH (appendoptimized=true, orientation=row); +HINT: Only one of these is needed to indicate access method: the SET ACCESS METHOD clause or the options in the WITH clause. +NOTICE: Redundant clauses are used to indicate the access method. +CREATE TEMP TABLE relfilebeforeao AS + SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'ao2co%' + UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class') + WHERE relname LIKE 'ao2co%' ORDER BY segid; +-- Check once the reloptions +SELECT c.relname, a.amname, c.reloptions FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'ao2co%'; + relname | amname | reloptions +---------+--------+----------------------------------- + ao2co | ao_row | {blocksize=65536,compresslevel=5} + ao2co2 | ao_row | {blocksize=65536,compresslevel=5} + ao2co3 | ao_row | {blocksize=65536,compresslevel=5} + ao2co4 | ao_row | {blocksize=65536,compresslevel=5} +(4 rows) + +-- Altering AO to AOCO with various syntaxes, reloptions: +ALTER TABLE ao2co SET ACCESS METHOD ao_column; +ALTER TABLE ao2co2 SET WITH (appendoptimized=true, orientation=column); +ALTER TABLE ao2co3 SET ACCESS METHOD ao_column WITH (blocksize=32768, compresslevel=3); +ALTER TABLE ao2co4 SET WITH (appendoptimized=true, orientation=column, blocksize=32768, compresslevel=3); +-- The tables are rewritten +CREATE TEMP TABLE relfileafterao AS + SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'ao2co%' + UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class') + WHERE relname LIKE 'ao2co%' ORDER BY segid; +SELECT * FROM relfilebeforeao INTERSECT SELECT * FROM relfileafterao; + segid | relname | relfilenode +-------+---------+------------- +(0 rows) + +DROP TABLE relfilebeforeao; +DROP TABLE relfileafterao; +-- Check data is intact +SELECT count(*) FROM ao2co; + count +------- + 5 +(1 row) + +SELECT count(*) FROM ao2co2; + count +------- + 5 +(1 row) + +SELECT count(*) FROM ao2co3; + count +------- + 5 +(1 row) + +SELECT count(*) FROM ao2co4; + count +------- + 5 +(1 row) + +-- Aux tables should have been deleted for the old AO table and recreated for the new AOCO table +-- Only tested for 2 out of the 4 tables being created, where the tables were altered w/wo reloptions. +-- No need to test the other ones created by the alternative syntax SET WITH(). +SELECT * FROM gp_toolkit.__gp_aoseg('ao2co'); +ERROR: 'ao2co' is not an append-only row relation +SELECT * FROM gp_toolkit.__gp_aovisimap('ao2co'); + tid | segno | row_num +-----+-------+--------- +(0 rows) + +SELECT count(*) FROM gp_toolkit.__gp_aocsseg('ao2co'); + count +------- + 6 +(1 row) + +SELECT * FROM gp_toolkit.__gp_aoblkdir('ao2co'); + tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count +---------+-------+----------------+----------+--------------+-------------+----------- +(0 rows) + +SELECT * FROM gp_toolkit.__gp_aoseg('ao2co3'); +ERROR: 'ao2co3' is not an append-only row relation +SELECT * FROM gp_toolkit.__gp_aovisimap('ao2co3'); + tid | segno | row_num +-----+-------+--------- +(0 rows) + +SELECT count(*) FROM gp_toolkit.__gp_aocsseg('ao2co3'); + count +------- + 6 +(1 row) + +SELECT * FROM gp_toolkit.__gp_aoblkdir('ao2co3'); + tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count +---------+-------+----------------+----------+--------------+-------------+----------- +(0 rows) + +-- pg_attribute_encoding should have columns for the AOCO table +SELECT c.relname, a.attnum, a.attoptions FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid = c.oid AND c.relname LIKE 'ao2co%'; + relname | attnum | attoptions +---------+--------+----------------------------------------------------- + ao2co | 1 | {compresstype=zlib,blocksize=65536,compresslevel=5} + ao2co | 2 | {compresstype=zlib,blocksize=65536,compresslevel=5} + ao2co2 | 1 | {compresstype=zlib,blocksize=65536,compresslevel=5} + ao2co2 | 2 | {compresstype=zlib,blocksize=65536,compresslevel=5} + ao2co3 | 1 | {blocksize=32768,compresslevel=3,compresstype=zlib} + ao2co3 | 2 | {blocksize=32768,compresslevel=3,compresstype=zlib} + ao2co4 | 1 | {blocksize=32768,compresslevel=3,compresstype=zlib} + ao2co4 | 2 | {blocksize=32768,compresslevel=3,compresstype=zlib} +(8 rows) + +-- AM and reloptions changed accordingly +SELECT c.relname, a.amname, c.reloptions FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'ao2co%'; + relname | amname | reloptions +---------+-----------+----------------------------------- + ao2co | ao_column | {blocksize=65536,compresslevel=5} + ao2co2 | ao_column | {blocksize=65536,compresslevel=5} + ao2co3 | ao_column | {blocksize=32768,compresslevel=3} + ao2co4 | ao_column | {blocksize=32768,compresslevel=3} +(4 rows) + +-- pg_appendonly should reflect the changes in reloptions +SELECT c.relname,a.blocksize,a.compresslevel,a.checksum,a.compresstype,a.columnstore +FROM pg_appendonly a, pg_class c WHERE a.relid = c.oid AND relname like ('ao2co%'); + relname | blocksize | compresslevel | checksum | compresstype | columnstore +---------+-----------+---------------+----------+--------------+------------- + ao2co | 65536 | 5 | t | zlib | t + ao2co2 | 65536 | 5 | t | zlib | t + ao2co3 | 32768 | 3 | t | zlib | t + ao2co4 | 32768 | 3 | t | zlib | t +(4 rows) + +DROP TABLE ao2co; +DROP TABLE ao2co2; +DROP TABLE ao2co3; +DROP TABLE ao2co4; +-- Final scenario: the iterations of altering table from storage type "A" to "B" and back to "A". +-- The following cases will cover all variations of such iterations: -- 1. Heap->AO->Heap->AO -- (TODO) 2. AO->AOCO->AO->AOCO -- (TODO) 3. Heap->AOCO->Heap->AOCO -- 1. Heap->AO->Heap->AO -CREATE TABLE heapao(a int, b int) WITH (appendonly=true); +CREATE TABLE heapao(a int, b int); CREATE INDEX heapaoindex ON heapao(b); INSERT INTO heapao SELECT i,i FROM generate_series(1,5) i; ALTER TABLE heapao SET ACCESS METHOD ao_row; diff --git a/src/test/regress/sql/alter_table_set_am.sql b/src/test/regress/sql/alter_table_set_am.sql index aebf63f9a0..d2d65f65d3 100644 --- a/src/test/regress/sql/alter_table_set_am.sql +++ b/src/test/regress/sql/alter_table_set_am.sql @@ -341,13 +341,90 @@ SELECT c.relname, a.amname, c.reloptions FROM pg_class c JOIN pg_am a ON c.relam DROP TABLE ataoset; DROP TABLE ataoset2; --- Final scenario: run the iterations of AT from "A" to "B" and back to "A", that includes: +-- Scenario 5: AO to AOCO +SET gp_default_storage_options = 'blocksize=65536, compresstype=zlib, compresslevel=5, checksum=true'; +CREATE TABLE ao2co(a int, b int) WITH (appendonly=true); +CREATE TABLE ao2co2(a int, b int) WITH (appendonly=true); +CREATE TABLE ao2co3(a int, b int) WITH (appendonly=true); +CREATE TABLE ao2co4(a int, b int) WITH (appendonly=true); +CREATE INDEX index_ao2co ON ao2co(b); +CREATE INDEX index_ao2co3 ON ao2co3(b); + +INSERT INTO ao2co SELECT i,i FROM generate_series(1,5) i; +INSERT INTO ao2co2 SELECT i,i FROM generate_series(1,5) i; +INSERT INTO ao2co3 SELECT i,i FROM generate_series(1,5) i; +INSERT INTO ao2co4 SELECT i,i FROM generate_series(1,5) i; + +-- ERROR: conflicting storage option specified. +ALTER TABLE ao2co SET ACCESS METHOD ao_column WITH (appendoptimized=true, orientation=row); +-- Use of *both* ACCESS METHOD and WITH clauses is allowed, but we'll print a hint to indicate the redundancy. +ALTER TABLE ao2co SET ACCESS METHOD ao_row WITH (appendoptimized=true, orientation=row); + +CREATE TEMP TABLE relfilebeforeao AS + SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'ao2co%' + UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class') + WHERE relname LIKE 'ao2co%' ORDER BY segid; + +-- Check once the reloptions +SELECT c.relname, a.amname, c.reloptions FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'ao2co%'; + +-- Altering AO to AOCO with various syntaxes, reloptions: +ALTER TABLE ao2co SET ACCESS METHOD ao_column; +ALTER TABLE ao2co2 SET WITH (appendoptimized=true, orientation=column); +ALTER TABLE ao2co3 SET ACCESS METHOD ao_column WITH (blocksize=32768, compresslevel=3); +ALTER TABLE ao2co4 SET WITH (appendoptimized=true, orientation=column, blocksize=32768, compresslevel=3); + +-- The tables are rewritten +CREATE TEMP TABLE relfileafterao AS + SELECT -1 segid, relname, relfilenode FROM pg_class WHERE relname LIKE 'ao2co%' + UNION SELECT gp_segment_id segid, relname, relfilenode FROM gp_dist_random('pg_class') + WHERE relname LIKE 'ao2co%' ORDER BY segid; + +SELECT * FROM relfilebeforeao INTERSECT SELECT * FROM relfileafterao; +DROP TABLE relfilebeforeao; +DROP TABLE relfileafterao; + +-- Check data is intact +SELECT count(*) FROM ao2co; +SELECT count(*) FROM ao2co2; +SELECT count(*) FROM ao2co3; +SELECT count(*) FROM ao2co4; + +-- Aux tables should have been deleted for the old AO table and recreated for the new AOCO table +-- Only tested for 2 out of the 4 tables being created, where the tables were altered w/wo reloptions. +-- No need to test the other ones created by the alternative syntax SET WITH(). +SELECT * FROM gp_toolkit.__gp_aoseg('ao2co'); +SELECT * FROM gp_toolkit.__gp_aovisimap('ao2co'); +SELECT count(*) FROM gp_toolkit.__gp_aocsseg('ao2co'); +SELECT * FROM gp_toolkit.__gp_aoblkdir('ao2co'); +SELECT * FROM gp_toolkit.__gp_aoseg('ao2co3'); +SELECT * FROM gp_toolkit.__gp_aovisimap('ao2co3'); +SELECT count(*) FROM gp_toolkit.__gp_aocsseg('ao2co3'); +SELECT * FROM gp_toolkit.__gp_aoblkdir('ao2co3'); + +-- pg_attribute_encoding should have columns for the AOCO table +SELECT c.relname, a.attnum, a.attoptions FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid = c.oid AND c.relname LIKE 'ao2co%'; + +-- AM and reloptions changed accordingly +SELECT c.relname, a.amname, c.reloptions FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'ao2co%'; + +-- pg_appendonly should reflect the changes in reloptions +SELECT c.relname,a.blocksize,a.compresslevel,a.checksum,a.compresstype,a.columnstore +FROM pg_appendonly a, pg_class c WHERE a.relid = c.oid AND relname like ('ao2co%'); + +DROP TABLE ao2co; +DROP TABLE ao2co2; +DROP TABLE ao2co3; +DROP TABLE ao2co4; + +-- Final scenario: the iterations of altering table from storage type "A" to "B" and back to "A". +-- The following cases will cover all variations of such iterations: -- 1. Heap->AO->Heap->AO -- (TODO) 2. AO->AOCO->AO->AOCO -- (TODO) 3. Heap->AOCO->Heap->AOCO -- 1. Heap->AO->Heap->AO -CREATE TABLE heapao(a int, b int) WITH (appendonly=true); +CREATE TABLE heapao(a int, b int); CREATE INDEX heapaoindex ON heapao(b); INSERT INTO heapao SELECT i,i FROM generate_series(1,5) i; @@ -358,3 +435,4 @@ ALTER TABLE heapao SET ACCESS METHOD ao_row; -- Just checking data is intact. SELECT count(*) FROM heapao; DROP TABLE heapao; + --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
