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 182af31fc62a8d9bd219e2136f5b13c11684e982 Author: Huansong Fu <[email protected]> AuthorDate: Thu Aug 11 14:14:42 2022 -0700 ALTER TABLE SET ACCESS METHOD: AOCO->AO support As part of the ATSETAM support, this commit adds support for changing AM of a table from AOCO to AO. E.g.: ``` CREATE TABLE foo (appendonly=true, orientation=column); ALTER TABLE foo SET ACCESS METHOD ao_row; -- Or: ALTER TABLE foo SET WITH (appendoptimized=true); ``` Optionally, user can specify reloptions in a WITH clause too, e.g.: ``` ALTER TABLE foo SET ACCESS METHOD ao_row WITH (compresslevel=7); ``` Additionally for the regress test: 1. Add more cases for altering with the same existing AM; 2. Add more cases for the "Final scenario" in the test. --- src/backend/catalog/pg_appendonly.c | 7 +- src/backend/commands/tablecmds.c | 10 +- src/test/regress/expected/alter_table_set_am.out | 438 +++++++++++++++++++++-- src/test/regress/sql/alter_table_set_am.sql | 82 +++-- 4 files changed, 480 insertions(+), 57 deletions(-) diff --git a/src/backend/catalog/pg_appendonly.c b/src/backend/catalog/pg_appendonly.c index 8ec58f74f3..7dd08ae5cf 100644 --- a/src/backend/catalog/pg_appendonly.c +++ b/src/backend/catalog/pg_appendonly.c @@ -596,9 +596,10 @@ ATAOEntries(Form_pg_class relform1, Form_pg_class relform2, errmsg("alter table does not support switch from AOCO to Heap"))); break; case AO_ROW_TABLE_AM_OID: - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("alter table does not support switch from AOCO to AO"))); + /* For pg_appendonly entries, it's same as AO->AO/CO. */ + SwapAppendonlyEntries(relform1->oid, relform2->oid); + /* For pg_attribute_encoding entries, it's same as AOCO->heap.*/ + RemoveAttributeEncodingsByRelid(relform1->oid); break; case AO_COLUMN_TABLE_AM_OID: swapAppendonlyEntriesUsingTAM(relform1, relform2, frozenXid, cutoffMulti); diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 704ee492d7..55f6604264 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -585,7 +585,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); -static void remove_rel_opts(Relation rel); +static void clear_rel_opts(Relation rel); /* ---------------------------------------------------------------- @@ -16104,7 +16104,7 @@ ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel, const char *tablespacen * * GPDB specific arguments: * aoopt_changed: whether any AO storage options have been changed in this function. - * am_change_heap_ao: whether we are changing the AM from heap->AO/CO or vice-versa. + * valid_as_ao: whether we validate teh reloptions as AO tables. */ static void ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation, @@ -16131,7 +16131,7 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation, if (OidIsValid(newAccessMethod)) { newAM = GetTableAmRoutineByAmId(newAccessMethod); - remove_rel_opts(rel); + clear_rel_opts(rel); } else newAM = NULL; @@ -18003,10 +18003,10 @@ get_rel_opts(Relation rel) } /* - * GPDB: Convenience function to remove the pg_class.reloptions field for a given relation. + * GPDB: Convenience function to clear the pg_class.reloptions field for a given relation. */ static void -remove_rel_opts(Relation rel) +clear_rel_opts(Relation rel) { Datum val[Natts_pg_class] = {0}; bool null[Natts_pg_class] = {0}; diff --git a/src/test/regress/expected/alter_table_set_am.out b/src/test/regress/expected/alter_table_set_am.out index 701496a0f3..a8da1c39f7 100644 --- a/src/test/regress/expected/alter_table_set_am.out +++ b/src/test/regress/expected/alter_table_set_am.out @@ -1,31 +1,51 @@ -- Check changing table access method --- Scenario 1: Heap to Heap -CREATE TABLE heap2heap(a int, b int) DISTRIBUTED BY (a); -CREATE TABLE heap2heap2(a int, b int) DISTRIBUTED BY (a); -INSERT INTO heap2heap SELECT i,i FROM generate_series(1,5) i; -INSERT INTO heap2heap2 SELECT i,i FROM generate_series(1,5) i; -CREATE TEMP TABLE relfilebeforeheap AS - SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heap2heap', 'heap2heap2') +-- Scenario 1: Changing to the same AM: it should have no effect but +-- make sure it doesn't rewrite table or blow up existing reloptions: +CREATE TABLE sameam_heap(a int, b int) WITH (fillfactor=70) DISTRIBUTED BY (a); +CREATE TABLE sameam_heap2(a int, b int) WITH (fillfactor=70) DISTRIBUTED BY (a); +CREATE TABLE sameam_ao(a int, b int) WITH (appendoptimized=true, orientation=row, compresstype=zlib, compresslevel=3); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +CREATE TABLE sameam_co(a int, b int) WITH (appendoptimized=true, orientation=column, compresstype=rle_type, compresslevel=3); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +INSERT INTO sameam_heap SELECT i,i FROM generate_series(1,5) i; +INSERT INTO sameam_heap2 SELECT i,i FROM generate_series(1,5) i; +INSERT INTO sameam_ao SELECT i,i FROM generate_series(1,5) i; +INSERT INTO sameam_co SELECT i,i FROM generate_series(1,5) i; +CREATE TEMP TABLE relfilebeforesameam AS + SELECT -1 segid, relfilenode FROM pg_class WHERE relname LIKE 'sameam_%' UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class') - WHERE relname in ('heap2heap', 'heap2heap2') ORDER BY segid; + WHERE relname LIKE 'sameam_%' ORDER BY segid; NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segid' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. -- changing to the same access method shouldn't rewrite the table -- (i.e. the relfilenodes shouldn't change) -ALTER TABLE heap2heap SET ACCESS METHOD heap; -ALTER TABLE heap2heap2 SET WITH (appendoptimized=false); -CREATE TEMP TABLE relfileafterheap AS - SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heap2heap', 'heap2heap2') +ALTER TABLE sameam_heap SET ACCESS METHOD heap; +ALTER TABLE sameam_heap2 SET WITH (appendoptimized=false); -- Alternative syntax of ATSETAM +ALTER TABLE sameam_ao SET ACCESS METHOD ao_row; +ALTER TABLE sameam_co SET ACCESS METHOD ao_column; +CREATE TEMP TABLE relfileaftersameam AS + SELECT -1 segid, relfilenode FROM pg_class WHERE relname LIKE 'sameam_%' UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class') - WHERE relname in ('heap2heap', 'heap2heap2') ORDER BY segid; + WHERE relname LIKE 'sameam_%' ORDER BY segid; NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'segid' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. -- relfilenodes shouldn't change -SELECT count(*) FROM (SELECT * FROM relfilebeforeheap UNION SELECT * FROM relfileafterheap)a; - count -------- - 8 -(1 row) +SELECT * FROM relfilebeforesameam EXCEPT SELECT * FROM relfileaftersameam; + segid | relfilenode +-------+------------- +(0 rows) + +-- reloptions should remain the same +SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'sameam_%'; + relname | reloptions +--------------+----------------------------------------- + sameam_ao | {compresstype=zlib,compresslevel=3} + sameam_co | {compresstype=rle_type,compresslevel=3} + sameam_heap | {fillfactor=70} + sameam_heap2 | {fillfactor=70} +(4 rows) -- Scenario 2: Heap to AO CREATE TABLE heap2ao(a int, b int) WITH (fillfactor=70) DISTRIBUTED BY (a); @@ -714,7 +734,7 @@ SELECT count(*) FROM gp_toolkit.__gp_aocsseg('ao2co3'); 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 @@ -756,10 +776,373 @@ DROP TABLE ao2co; DROP TABLE ao2co2; DROP TABLE ao2co3; DROP TABLE ao2co4; +-- Scenario 6: AOCO to Heap +SET gp_default_storage_options = 'blocksize=65536, compresstype=zlib, compresslevel=5, checksum=true'; +CREATE TABLE co2heap(a int, b int) WITH (appendonly=true, orientation=column); +CREATE TABLE co2heap2(a int, b int) WITH (appendonly=true, orientation=column); +CREATE TABLE co2heap3(a int, b int) WITH (appendonly=true, orientation=column); +CREATE TABLE co2heap4(a int, b int) WITH (appendonly=true, orientation=column); +CREATE INDEX aoi ON co2heap(b); +INSERT INTO co2heap SELECT i,i FROM generate_series(1,5) i; +INSERT INTO co2heap2 SELECT i,i FROM generate_series(1,5) i; +INSERT INTO co2heap3 SELECT i,i FROM generate_series(1,5) i; +INSERT INTO co2heap4 SELECT i,i FROM generate_series(1,5) i; +-- Prior-ATSETAM checks: +-- Check once that the AO tables have the custom reloptions +SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'co2heap%'; + relname | reloptions +----------+----------------------------------- + co2heap | {blocksize=65536,compresslevel=5} + co2heap2 | {blocksize=65536,compresslevel=5} + co2heap3 | {blocksize=65536,compresslevel=5} + co2heap4 | {blocksize=65536,compresslevel=5} +(4 rows) + +-- Check once that the AO tables have relfrozenxid = 0 +SELECT relname, relfrozenxid FROM pg_class WHERE relname LIKE 'co2heap%'; + relname | relfrozenxid +----------+-------------- + co2heap | 0 + co2heap2 | 0 + co2heap3 | 0 + co2heap4 | 0 +(4 rows) + +-- Check once that the pg_attribute_encoding has entries for the AOCO tables. +SELECT c.relname, a.attnum, attoptions FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid=c.oid AND c.relname LIKE 'co2heap%'; + relname | attnum | attoptions +----------+--------+----------------------------------------------------- + co2heap | 1 | {compresstype=zlib,blocksize=65536,compresslevel=5} + co2heap | 2 | {compresstype=zlib,blocksize=65536,compresslevel=5} + co2heap2 | 1 | {compresstype=zlib,blocksize=65536,compresslevel=5} + co2heap2 | 2 | {compresstype=zlib,blocksize=65536,compresslevel=5} + co2heap3 | 1 | {compresstype=zlib,blocksize=65536,compresslevel=5} + co2heap3 | 2 | {compresstype=zlib,blocksize=65536,compresslevel=5} + co2heap4 | 1 | {compresstype=zlib,blocksize=65536,compresslevel=5} + co2heap4 | 2 | {compresstype=zlib,blocksize=65536,compresslevel=5} +(8 rows) + +CREATE TEMP TABLE relfilebeforeco2heap AS + SELECT -1 segid, relfilenode FROM pg_class WHERE relname LIKE 'co2heap%' + UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class') + WHERE relname LIKE 'co2heap%' ORDER BY segid; +-- Various cases of altering AOCO to AO: +-- 1. Basic ATSETAMs: +ALTER TABLE co2heap SET ACCESS METHOD heap; +ALTER TABLE co2heap2 SET WITH (appendoptimized=false); +-- 2. ATSETAM with reloptions: +ALTER TABLE co2heap3 SET ACCESS METHOD heap WITH (fillfactor=70); +ALTER TABLE co2heap4 SET WITH (appendoptimized=false, fillfactor=70); +-- The tables and indexes should have been rewritten (should have different relfilenodes) +CREATE TEMP TABLE relfileafterco2heap AS + SELECT -1 segid, relfilenode FROM pg_class WHERE relname LIKE 'co2heap%' + UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class') + WHERE relname LIKE 'co2heap%' ORDER BY segid; +SELECT * FROM relfilebeforeco2heap INTERSECT SELECT * FROM relfileafterco2heap; + segid | relfilenode +-------+------------- +(0 rows) + +-- Check data is intact +SELECT count(*) FROM co2heap; + count +------- + 5 +(1 row) + +SELECT count(*) FROM co2heap2; + count +------- + 5 +(1 row) + +SELECT count(*) FROM co2heap3; + count +------- + 5 +(1 row) + +SELECT count(*) FROM co2heap4; + count +------- + 5 +(1 row) + +-- No AO aux tables should be left. +-- Only testing 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('co2heap'); +ERROR: 'co2heap' is not an append-only row relation +SELECT * FROM gp_toolkit.__gp_aovisimap('co2heap'); +ERROR: function not supported on relation +SELECT count(*) FROM gp_toolkit.__gp_aocsseg('co2heap'); +ERROR: 'co2heap' is not an append-only columnar relation +SELECT * FROM gp_toolkit.__gp_aoblkdir('co2heap'); +ERROR: function not supported on non append-optimized relation +SELECT * FROM gp_toolkit.__gp_aoseg('co2heap3'); +ERROR: 'co2heap3' is not an append-only row relation +SELECT * FROM gp_toolkit.__gp_aovisimap('co2heap3'); +ERROR: function not supported on relation +SELECT count(*) FROM gp_toolkit.__gp_aocsseg('co2heap3'); +ERROR: 'co2heap3' is not an append-only columnar relation +SELECT * FROM gp_toolkit.__gp_aoblkdir('co2heap3'); +ERROR: function not supported on non append-optimized relation +-- No pg_appendonly entries should be left too +SELECT c.relname FROM pg_class c, pg_appendonly p WHERE c.relname LIKE 'co2heap%' AND c.oid = p.relid; + relname +--------- +(0 rows) + +-- The altered tables should have heap AM. +SELECT c.relname, a.amname FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'co2heap%'; + relname | amname +----------+-------- + co2heap | heap + co2heap2 | heap + co2heap3 | heap + co2heap4 | heap +(4 rows) + +-- The new heap tables shouldn't have the old AO table's reloptions +SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'co2heap%'; + relname | reloptions +----------+----------------- + co2heap | + co2heap2 | + co2heap3 | {fillfactor=70} + co2heap4 | {fillfactor=70} +(4 rows) + +-- The new heap tables should have a valid relfrozenxid +SELECT relname, relfrozenxid <> '0' FROM pg_class WHERE relname LIKE 'co2heap%'; + relname | ?column? +----------+---------- + co2heap | t + co2heap2 | t + co2heap3 | t + co2heap4 | t +(4 rows) + +-- The pg_attribute_encoding entries for the altered tables should have all gone. +SELECT c.relname, a.attnum, attoptions FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid=c.oid AND c.relname LIKE 'co2heap%'; + relname | attnum | attoptions +---------+--------+------------ +(0 rows) + +DROP TABLE co2heap; +DROP TABLE co2heap2; +DROP TABLE co2heap3; +DROP TABLE co2heap4; +-- Scenario 7: AOCO to AO +CREATE TABLE co2ao(a int, b int) WITH (appendonly=true, orientation=column, compresstype=rle_type, compresslevel=3); +CREATE TABLE co2ao2(a int, b int) WITH (appendonly=true, orientation=column, compresstype=rle_type, compresslevel=3); +CREATE TABLE co2ao3(a int, b int) WITH (appendonly=true, orientation=column, compresstype=rle_type, compresslevel=3); +CREATE TABLE co2ao4(a int, b int) WITH (appendonly=true, orientation=column, compresstype=rle_type, compresslevel=3); +CREATE INDEX aoi ON co2ao(b); +CREATE INDEX aoi2 ON co2ao3(b); +INSERT INTO co2ao SELECT i,i FROM generate_series(1,5) i; +INSERT INTO co2ao2 SELECT i,i FROM generate_series(1,5) i; +INSERT INTO co2ao3 SELECT i,i FROM generate_series(1,5) i; +INSERT INTO co2ao4 SELECT i,i FROM generate_series(1,5) i; +-- Prior-ATSETAM checks: +-- Check once that the AOCO tables have the custom reloptions +SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'co2ao%'; + relname | reloptions +---------+--------------------------------------------------------- + co2ao | {compresstype=rle_type,compresslevel=3,blocksize=65536} + co2ao2 | {compresstype=rle_type,compresslevel=3,blocksize=65536} + co2ao3 | {compresstype=rle_type,compresslevel=3,blocksize=65536} + co2ao4 | {compresstype=rle_type,compresslevel=3,blocksize=65536} +(4 rows) + +-- Check once that pg_appendonly has expected entries. +SELECT c.relname, p.compresstype, p.compresslevel, p.blocksize FROM pg_class c, pg_appendonly p WHERE c.relname LIKE 'co2ao%' AND c.oid = p.relid; + relname | compresstype | compresslevel | blocksize +---------+--------------+---------------+----------- + co2ao | rle_type | 3 | 65536 + co2ao2 | rle_type | 3 | 65536 + co2ao3 | rle_type | 3 | 65536 + co2ao4 | rle_type | 3 | 65536 +(4 rows) + +-- Check once that the pg_attribute_encoding has entries for the AOCO tables. +SELECT c.relname, a.attnum, attoptions FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid=c.oid AND c.relname LIKE 'co2ao%'; + relname | attnum | attoptions +---------+--------+--------------------------------------------------------- + co2ao | 1 | {compresstype=rle_type,compresslevel=3,blocksize=65536} + co2ao | 2 | {compresstype=rle_type,compresslevel=3,blocksize=65536} + co2ao2 | 1 | {compresstype=rle_type,compresslevel=3,blocksize=65536} + co2ao2 | 2 | {compresstype=rle_type,compresslevel=3,blocksize=65536} + co2ao3 | 1 | {compresstype=rle_type,compresslevel=3,blocksize=65536} + co2ao3 | 2 | {compresstype=rle_type,compresslevel=3,blocksize=65536} + co2ao4 | 1 | {compresstype=rle_type,compresslevel=3,blocksize=65536} + co2ao4 | 2 | {compresstype=rle_type,compresslevel=3,blocksize=65536} +(8 rows) + +-- Check once on the aoblkdirs +SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('co2ao')).* FROM gp_dist_random('gp_id'); + gp_segment_id | tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count +---------------+---------+-------+----------------+----------+--------------+-------------+----------- + 0 | (0,1) | 1 | 0 | 0 | 1 | 0 | 3 + 0 | (0,2) | 1 | 1 | 0 | 1 | 0 | 3 + 1 | (0,1) | 1 | 0 | 0 | 1 | 0 | 1 + 1 | (0,2) | 1 | 1 | 0 | 1 | 0 | 1 + 2 | (0,1) | 1 | 0 | 0 | 1 | 0 | 1 + 2 | (0,2) | 1 | 1 | 0 | 1 | 0 | 1 +(6 rows) + +SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('co2ao3')).* FROM gp_dist_random('gp_id'); + gp_segment_id | tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count +---------------+---------+-------+----------------+----------+--------------+-------------+----------- + 0 | (0,1) | 1 | 0 | 0 | 1 | 0 | 3 + 0 | (0,2) | 1 | 1 | 0 | 1 | 0 | 3 + 1 | (0,1) | 1 | 0 | 0 | 1 | 0 | 1 + 1 | (0,2) | 1 | 1 | 0 | 1 | 0 | 1 + 2 | (0,1) | 1 | 0 | 0 | 1 | 0 | 1 + 2 | (0,2) | 1 | 1 | 0 | 1 | 0 | 1 +(6 rows) + +CREATE TEMP TABLE relfilebeforeco2ao AS + SELECT -1 segid, relfilenode FROM pg_class WHERE relname LIKE 'co2ao%' + UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class') + WHERE relname LIKE 'co2ao%' ORDER BY segid; +-- Various cases of altering AOCO to AO: +-- 1. Basic ATSETAMs: +ALTER TABLE co2ao SET ACCESS METHOD ao_row; +ALTER TABLE co2ao2 SET WITH (appendoptimized=true); +-- 2. ATSETAM with reloptions: +ALTER TABLE co2ao3 SET ACCESS METHOD ao_row WITH (compresstype=zlib, compresslevel=7); +ALTER TABLE co2ao4 SET WITH (appendoptimized=true, compresstype=zlib, compresslevel=7); +-- The tables and indexes should have been rewritten (should have different relfilenodes) +CREATE TEMP TABLE relfileafterco2ao AS + SELECT -1 segid, relfilenode FROM pg_class WHERE relname LIKE 'co2ao%' + UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class') + WHERE relname LIKE 'co2ao%' ORDER BY segid; +SELECT * FROM relfilebeforeco2ao INTERSECT SELECT * FROM relfileafterco2ao; + segid | relfilenode +-------+------------- +(0 rows) + +DROP TABLE relfilebeforeco2ao; +DROP TABLE relfileafterco2ao; +-- Check data is intact +SELECT count(*) FROM co2ao; + count +------- + 5 +(1 row) + +SELECT count(*) FROM co2ao2; + count +------- + 5 +(1 row) + +SELECT count(*) FROM co2ao3; + count +------- + 5 +(1 row) + +SELECT count(*) FROM co2ao4; + count +------- + 5 +(1 row) + +-- AO aux tables should still be there, but AOCO seg tables are not. +-- Only testing 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('co2ao'); + segment_id | segno | eof | tupcount | varblockcount | eof_uncompressed | modcount | formatversion | state +------------+-------+-----+----------+---------------+------------------+----------+---------------+------- + 0 | 0 | 88 | 3 | 1 | 88 | 1 | 3 | 1 + 1 | 0 | 40 | 1 | 1 | 40 | 1 | 3 | 1 + 2 | 0 | 40 | 1 | 1 | 40 | 1 | 3 | 1 +(3 rows) + +SELECT * FROM gp_toolkit.__gp_aocsseg('co2ao'); +ERROR: 'co2ao' is not an append-only columnar relation +SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('co2ao')).* FROM gp_dist_random('gp_id'); + gp_segment_id | tid | segno | row_num +---------------+-----+-------+--------- +(0 rows) + +SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('co2ao')).* FROM gp_dist_random('gp_id'); + gp_segment_id | tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count +---------------+---------+-------+----------------+----------+--------------+-------------+----------- + 0 | (0,1) | 0 | 0 | 0 | 1 | 0 | 3 + 1 | (0,1) | 0 | 0 | 0 | 1 | 0 | 1 + 2 | (0,1) | 0 | 0 | 0 | 1 | 0 | 1 +(3 rows) + +SELECT * FROM gp_toolkit.__gp_aoseg('co2ao3'); + segment_id | segno | eof | tupcount | varblockcount | eof_uncompressed | modcount | formatversion | state +------------+-------+-----+----------+---------------+------------------+----------+---------------+------- + 0 | 0 | 72 | 3 | 1 | 88 | 1 | 3 | 1 + 1 | 0 | 40 | 1 | 1 | 40 | 1 | 3 | 1 + 2 | 0 | 40 | 1 | 1 | 40 | 1 | 3 | 1 +(3 rows) + +SELECT * FROM gp_toolkit.__gp_aocsseg('co2ao3'); +ERROR: 'co2ao3' is not an append-only columnar relation +SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('co2ao3')).* FROM gp_dist_random('gp_id'); + gp_segment_id | tid | segno | row_num +---------------+-----+-------+--------- +(0 rows) + +SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('co2ao3')).* FROM gp_dist_random('gp_id'); + gp_segment_id | tupleid | segno | columngroup_no | entry_no | first_row_no | file_offset | row_count +---------------+---------+-------+----------------+----------+--------------+-------------+----------- + 0 | (0,1) | 0 | 0 | 0 | 1 | 0 | 3 + 1 | (0,1) | 0 | 0 | 0 | 1 | 0 | 1 + 2 | (0,1) | 0 | 0 | 0 | 1 | 0 | 1 +(3 rows) + +-- pg_appendonly entries should be still be there, but options has changed accordingly. +SELECT c.relname, p.compresstype, p.compresslevel, p.blocksize FROM pg_class c, pg_appendonly p WHERE c.relname LIKE 'co2ao%' AND c.oid = p.relid; + relname | compresstype | compresslevel | blocksize +---------+--------------+---------------+----------- + co2ao | | 0 | 32768 + co2ao2 | | 0 | 32768 + co2ao3 | zlib | 7 | 32768 + co2ao4 | zlib | 7 | 32768 +(4 rows) + +-- The altered tables should show AO AM. +SELECT c.relname, a.amname FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'co2ao%'; + relname | amname +---------+-------- + co2ao | ao_row + co2ao2 | ao_row + co2ao3 | ao_row + co2ao4 | ao_row +(4 rows) + +-- Only the new tables altered w/ reloptions supplies should have reloptions. +SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'co2ao%'; + relname | reloptions +---------+------------------------------------- + co2ao | + co2ao2 | + co2ao3 | {compresstype=zlib,compresslevel=7} + co2ao4 | {compresstype=zlib,compresslevel=7} +(4 rows) + +-- The pg_attribute_encoding entries for the altered tables should have all gone. +SELECT c.relname, a.attnum, attoptions FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid=c.oid AND c.relname LIKE 'co2ao%'; + relname | attnum | attoptions +---------+--------+------------ +(0 rows) + +DROP TABLE co2ao; +DROP TABLE co2ao2; +DROP TABLE co2ao3; +DROP TABLE co2ao4; -- 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 +-- 2. AO->AOCO->AO->AOCO -- (TODO) 3. Heap->AOCO->Heap->AOCO -- 1. Heap->AO->Heap->AO CREATE TABLE heapao(a int, b int); @@ -776,3 +1159,18 @@ SELECT count(*) FROM heapao; (1 row) DROP TABLE heapao; +-- 2. AO->AOCO->AO->AOCO +CREATE TABLE aoco(a int, b int) with (appendoptimized=true); +CREATE INDEX aocoindex ON aoco(b); +INSERT INTO aoco SELECT i,i FROM generate_series(1,5) i; +ALTER TABLE aoco SET ACCESS METHOD ao_column; +ALTER TABLE aoco SET ACCESS METHOD ao_row; +ALTER TABLE aoco SET ACCESS METHOD ao_column; +-- Just checking data is intact. +SELECT count(*) FROM aoco; + count +------- + 5 +(1 row) + +DROP TABLE aoco; diff --git a/src/test/regress/sql/alter_table_set_am.sql b/src/test/regress/sql/alter_table_set_am.sql index 11a0b90d1c..e97903b2ed 100644 --- a/src/test/regress/sql/alter_table_set_am.sql +++ b/src/test/regress/sql/alter_table_set_am.sql @@ -1,30 +1,38 @@ -- Check changing table access method --- Scenario 1: Heap to Heap -CREATE TABLE heap2heap(a int, b int) DISTRIBUTED BY (a); -CREATE TABLE heap2heap2(a int, b int) DISTRIBUTED BY (a); - -INSERT INTO heap2heap SELECT i,i FROM generate_series(1,5) i; -INSERT INTO heap2heap2 SELECT i,i FROM generate_series(1,5) i; - -CREATE TEMP TABLE relfilebeforeheap AS - SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heap2heap', 'heap2heap2') +-- Scenario 1: Changing to the same AM: it should have no effect but +-- make sure it doesn't rewrite table or blow up existing reloptions: +CREATE TABLE sameam_heap(a int, b int) WITH (fillfactor=70) DISTRIBUTED BY (a); +CREATE TABLE sameam_heap2(a int, b int) WITH (fillfactor=70) DISTRIBUTED BY (a); +CREATE TABLE sameam_ao(a int, b int) WITH (appendoptimized=true, orientation=row, compresstype=zlib, compresslevel=3); +CREATE TABLE sameam_co(a int, b int) WITH (appendoptimized=true, orientation=column, compresstype=rle_type, compresslevel=3); + +INSERT INTO sameam_heap SELECT i,i FROM generate_series(1,5) i; +INSERT INTO sameam_heap2 SELECT i,i FROM generate_series(1,5) i; +INSERT INTO sameam_ao SELECT i,i FROM generate_series(1,5) i; +INSERT INTO sameam_co SELECT i,i FROM generate_series(1,5) i; + +CREATE TEMP TABLE relfilebeforesameam AS + SELECT -1 segid, relfilenode FROM pg_class WHERE relname LIKE 'sameam_%' UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class') - WHERE relname in ('heap2heap', 'heap2heap2') ORDER BY segid; + WHERE relname LIKE 'sameam_%' ORDER BY segid; -- changing to the same access method shouldn't rewrite the table -- (i.e. the relfilenodes shouldn't change) -ALTER TABLE heap2heap SET ACCESS METHOD heap; -ALTER TABLE heap2heap2 SET WITH (appendoptimized=false); +ALTER TABLE sameam_heap SET ACCESS METHOD heap; +ALTER TABLE sameam_heap2 SET WITH (appendoptimized=false); -- Alternative syntax of ATSETAM +ALTER TABLE sameam_ao SET ACCESS METHOD ao_row; +ALTER TABLE sameam_co SET ACCESS METHOD ao_column; -CREATE TEMP TABLE relfileafterheap AS - SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heap2heap', 'heap2heap2') +CREATE TEMP TABLE relfileaftersameam AS + SELECT -1 segid, relfilenode FROM pg_class WHERE relname LIKE 'sameam_%' UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class') - WHERE relname in ('heap2heap', 'heap2heap2') ORDER BY segid; + WHERE relname LIKE 'sameam_%' ORDER BY segid; -- relfilenodes shouldn't change -SELECT count(*) FROM (SELECT * FROM relfilebeforeheap UNION SELECT * FROM relfileafterheap)a; - +SELECT * FROM relfilebeforesameam EXCEPT SELECT * FROM relfileaftersameam; +-- reloptions should remain the same +SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'sameam_%'; -- Scenario 2: Heap to AO CREATE TABLE heap2ao(a int, b int) WITH (fillfactor=70) DISTRIBUTED BY (a); @@ -517,7 +525,10 @@ SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'co2ao%'; -- Check once that pg_appendonly has expected entries. SELECT c.relname, p.compresstype, p.compresslevel, p.blocksize FROM pg_class c, pg_appendonly p WHERE c.relname LIKE 'co2ao%' AND c.oid = p.relid; -- Check once that the pg_attribute_encoding has entries for the AOCO tables. -SELECT c.relname, a.* FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid=c.oid AND c.relname LIKE 'co2ao%'; +SELECT c.relname, a.attnum, attoptions FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid=c.oid AND c.relname LIKE 'co2ao%'; +-- Check once on the aoblkdirs +SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('co2ao')).* FROM gp_dist_random('gp_id'); +SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('co2ao3')).* FROM gp_dist_random('gp_id'); CREATE TEMP TABLE relfilebeforeco2ao AS SELECT -1 segid, relfilenode FROM pg_class WHERE relname LIKE 'co2ao%' @@ -549,29 +560,29 @@ SELECT count(*) FROM co2ao2; SELECT count(*) FROM co2ao3; SELECT count(*) FROM co2ao4; --- AO aux tables should still be there. +-- AO aux tables should still be there, but AOCO seg tables are not. -- Only testing 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('co2ao'); -SELECT * FROM gp_toolkit.__gp_aovisimap('co2ao'); -SELECT count(*) FROM gp_toolkit.__gp_aocsseg('co2ao'); -SELECT * FROM gp_toolkit.__gp_aoblkdir('co2ao'); +SELECT * FROM gp_toolkit.__gp_aocsseg('co2ao'); +SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('co2ao')).* FROM gp_dist_random('gp_id'); +SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('co2ao')).* FROM gp_dist_random('gp_id'); SELECT * FROM gp_toolkit.__gp_aoseg('co2ao3'); -SELECT * FROM gp_toolkit.__gp_aovisimap('co2ao3'); -SELECT count(*) FROM gp_toolkit.__gp_aocsseg('co2ao3'); -SELECT * FROM gp_toolkit.__gp_aoblkdir('co2ao3'); +SELECT * FROM gp_toolkit.__gp_aocsseg('co2ao3'); +SELECT gp_segment_id, (gp_toolkit.__gp_aovisimap('co2ao3')).* FROM gp_dist_random('gp_id'); +SELECT gp_segment_id, (gp_toolkit.__gp_aoblkdir('co2ao3')).* FROM gp_dist_random('gp_id'); -- pg_appendonly entries should be still be there, but options has changed accordingly. SELECT c.relname, p.compresstype, p.compresslevel, p.blocksize FROM pg_class c, pg_appendonly p WHERE c.relname LIKE 'co2ao%' AND c.oid = p.relid; --- The altered tables should show AOCO AM. +-- The altered tables should show AO AM. SELECT c.relname, a.amname FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname LIKE 'co2ao%'; --- The new tables should have new reloptions. +-- Only the new tables altered w/ reloptions supplies should have reloptions. SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'co2ao%'; -- The pg_attribute_encoding entries for the altered tables should have all gone. -SELECT c.relname, a.* FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid=c.oid AND c.relname LIKE 'co2ao%'; +SELECT c.relname, a.attnum, attoptions FROM pg_attribute_encoding a, pg_class c WHERE a.attrelid=c.oid AND c.relname LIKE 'co2ao%'; DROP TABLE co2ao; DROP TABLE co2ao2; @@ -581,7 +592,7 @@ DROP TABLE co2ao4; -- 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 +-- 2. AO->AOCO->AO->AOCO -- (TODO) 3. Heap->AOCO->Heap->AOCO -- 1. Heap->AO->Heap->AO @@ -597,3 +608,16 @@ ALTER TABLE heapao SET ACCESS METHOD ao_row; SELECT count(*) FROM heapao; DROP TABLE heapao; +-- 2. AO->AOCO->AO->AOCO +CREATE TABLE aoco(a int, b int) with (appendoptimized=true); +CREATE INDEX aocoindex ON aoco(b); +INSERT INTO aoco SELECT i,i FROM generate_series(1,5) i; + +ALTER TABLE aoco SET ACCESS METHOD ao_column; +ALTER TABLE aoco SET ACCESS METHOD ao_row; +ALTER TABLE aoco SET ACCESS METHOD ao_column; + +-- Just checking data is intact. +SELECT count(*) FROM aoco; +DROP TABLE aoco; + --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
