You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by re...@apache.org on 2025/01/12 06:16:16 UTC
(cloudberry) 01/02: AT SET WITH() syntax to change table access method
This is an automated email from the ASF dual-hosted git repository.
reshke pushed a commit to branch at_set_am
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit df982a82baacddb5d6a882f82f3561e35a4c8ba0
Author: Soumyadeep Chakraborty <so...@gmail.com>
AuthorDate: Fri May 13 16:20:27 2022 -0700
AT SET WITH() syntax to change table access method
AT SET WITH syntax was used only for reorganize set dist
This commit adds support to change access method of the table using that
syntax.
ALTER TABLE mp_heap_table SET WITH (appendoptimized=true);
To support the AT SET WITH syntax variant, we use a thin layer over the
ATSETAM infrastructure, handling most of the massaging in the grammar.
We currently do not support both reorganize and changing access method
in the same AT command, since it currently requires two rewrites of the
table.
AT SET WITH() can now be used to either reorganize or for changing
access method.
Co-authored-by: Divyesh Vanjare <va...@vmware.com>
---
src/backend/parser/gram.y | 42 ++++++-
.../regress/expected/alter_distribution_policy.out | 31 +++--
src/test/regress/expected/alter_table_set_am.out | 131 +++++++++++++--------
src/test/regress/sql/alter_distribution_policy.sql | 5 -
src/test/regress/sql/alter_table_set_am.sql | 62 +++++-----
5 files changed, 169 insertions(+), 102 deletions(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 75c0d9b723..1b17adf4b4 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -215,6 +215,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
static Node *makeIsNotDistinctFromNode(Node *expr, int position);
+static bool isSetWithReorganize(List **options);
static char *greenplumLegacyAOoptions(const char *accessMethod, List **options);
static void check_expressions_in_partition_key(PartitionSpec *spec, core_yyscan_t yyscanner);
@@ -3733,12 +3734,25 @@ alter_table_cmd:
n->def = (Node *) list_make2($3, $4);
$$ = (Node *)n;
}
- /* storage only */
+ /* table storage type or reorganize only */
| SET WITH definition
{
AlterTableCmd *n = makeNode(AlterTableCmd);
- n->subtype = AT_SetDistributedBy;
- n->def = (Node *) list_make2($3, NULL);
+ if (isSetWithReorganize(&$3))
+ {
+ n->subtype = AT_SetDistributedBy;
+ n->def = (Node *) list_make2($3, NULL);
+ }
+ else
+ {
+ n->subtype = AT_SetAccessMethod;
+ n->name = greenplumLegacyAOoptions(n->name, &$3);
+ if (!n->name)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid storage type"),
+ parser_errposition(@3)));
+ }
$$ = (Node *)n;
}
| alter_table_partition_cmd
@@ -21779,6 +21793,28 @@ makeRecursiveViewSelect(char *relname, List *aliases, Node *query)
return (Node *) s;
}
+static bool
+isSetWithReorganize(List **options)
+{
+ ListCell *lc;
+ foreach (lc, *options)
+ {
+ DefElem *elem = lfirst(lc);
+
+ if (strcmp(elem->defname, "reorganize") == 0)
+ {
+ if (list_length(*options) == 1)
+ return true;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("reorganize isn't supported with other options in SET WITH")));
+ }
+ }
+ return false;
+}
+
+
/*
* Cloudberry: a thin wax off layer to keep compatibility with the legacy syntax
* for appendoptimized options. Before the introduction of the tableam in
diff --git a/src/test/regress/expected/alter_distribution_policy.out b/src/test/regress/expected/alter_distribution_policy.out
index 1d7e50df44..ec7789fe81 100644
--- a/src/test/regress/expected/alter_distribution_policy.out
+++ b/src/test/regress/expected/alter_distribution_policy.out
@@ -171,12 +171,12 @@ ERROR: permission denied: "pg_class" is a system catalog
create table atsdb (i int, j text) distributed by (j);
insert into atsdb select i, i::text from generate_series(1, 10) i;
alter table atsdb set with(appendonly = true);
-ERROR: option "appendonly" not supported
select relname, segrelid != 0, reloptions from pg_class, pg_appendonly where pg_class.oid =
'atsdb'::regclass and relid = pg_class.oid;
relname | ?column? | reloptions
---------+----------+------------
-(0 rows)
+ atsdb | t |
+(1 row)
select * from atsdb;
i | j
@@ -316,12 +316,12 @@ select * from distcheck where rel = 'atsdb';
alter table atsdb drop column n;
alter table atsdb set with(appendonly = true, compresslevel = 3);
-ERROR: cannot specify more than one option in WITH clause
select relname, segrelid != 0, reloptions from pg_class, pg_appendonly where pg_class.oid =
'atsdb'::regclass and relid = pg_class.oid;
relname | ?column? | reloptions
---------+----------+------------
-(0 rows)
+ atsdb | t |
+(1 row)
select * from distcheck where rel = 'atsdb';
rel | attname
@@ -445,7 +445,8 @@ select relname, segrelid != 0, reloptions from pg_class, pg_appendonly where pg_
'atsdb'::regclass and relid = pg_class.oid;
relname | ?column? | reloptions
---------+----------+------------
-(0 rows)
+ atsdb | t |
+(1 row)
select * from atsdb;
j | t
@@ -554,10 +555,12 @@ select * from atsdb;
-- validate parameters
alter table atsdb set with (appendonly = ff);
-ERROR: option "appendonly" not supported
+ERROR: appendonly requires a Boolean value
alter table atsdb set with (reorganize = true);
alter table atsdb set with (fgdfgef = asds);
-ERROR: option "fgdfgef" not supported
+ERROR: invalid storage type
+LINE 1: alter table atsdb set with (fgdfgef = asds);
+ ^
alter table atsdb set with(reorganize = true, reorganize = false) distributed
randomly;
ERROR: cannot specify more than one option in WITH clause
@@ -648,7 +651,7 @@ select * from atsdb order by 1, 2, 3;
(9 rows)
alter table atsdb set with(appendonly = true);
-ERROR: option "appendonly" not supported
+ERROR: cannot change access method of a partitioned table
select relname, a.blocksize, compresslevel, compresstype, checksum from pg_class c, pg_appendonly a where
relname like 'atsdb%' and c.oid = a.relid order by 1;
relname | blocksize | compresslevel | compresstype | checksum
@@ -762,7 +765,7 @@ NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c' as
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 mpp5746 select array[i], i from generate_series(1, 100) i;
alter table mpp5746 set with (reorganize=true, appendonly = true);
-ERROR: cannot specify more than one option in WITH clause
+ERROR: reorganize isn't supported with other options in SET WITH
select * from mpp5746 order by 1;
c | t
-------+-----
@@ -975,7 +978,7 @@ select * from mpp5746 order by 1;
(100 rows)
alter table mpp5746 set with (reorganize=true, appendonly = false);
-ERROR: cannot specify more than one option in WITH clause
+ERROR: reorganize isn't supported with other options in SET WITH
select * from mpp5746 order by 1;
c
-------
@@ -1104,7 +1107,7 @@ select * from mpp5738;
(10 rows)
alter table mpp5738 alter partition for (1) set with (appendonly=true);
-ERROR: option "appendonly" not supported
+ERROR: table "mpp5738_1_prt_1" is not partitioned
select * from mpp5738;
a | b | c | d
----+----+----+----
@@ -1233,15 +1236,11 @@ Alter table abc set with (reorganize=false) distributed randomly;
WARNING: distribution policy of relation "abc" already set to DISTRIBUTED RANDOMLY
HINT: Use ALTER TABLE "abc" SET WITH (REORGANIZE=TRUE) DISTRIBUTED RANDOMLY to force a random redistribution.
drop table abc;
--- disallow, so fails
-create table atsdb (i int, j text) distributed by (j);
-alter table atsdb set with(appendonly = true);
-ERROR: option "appendonly" not supported
-drop table atsdb;
-- MPP-18660: duplicate entry in gp_distribution_policy
set enable_indexscan=on;
set enable_seqscan=off;
drop table if exists distrib_index_test;
+NOTICE: table "distrib_index_test" does not exist, skipping
create table distrib_index_test (a int, b text) distributed by (a);
select count(*) from gp_distribution_policy
where localoid in (select oid from pg_class where relname='distrib_index_test');
diff --git a/src/test/regress/expected/alter_table_set_am.out b/src/test/regress/expected/alter_table_set_am.out
index c0eba8a685..43e8d80112 100644
--- a/src/test/regress/expected/alter_table_set_am.out
+++ b/src/test/regress/expected/alter_table_set_am.out
@@ -1,31 +1,34 @@
-- 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')
+ SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heap2heap', 'heap2heap2')
UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
- WHERE relname in ('heap2heap') ORDER BY segid;
+ WHERE relname in ('heap2heap', 'heap2heap2') 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')
+ SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heap2heap', 'heap2heap2')
UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
- WHERE relname in ('heap2heap') ORDER BY segid;
+ WHERE relname in ('heap2heap', 'heap2heap2') 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
-------
- 4
+ 8
(1 row)
-- Scenario 2: Heap to AO
-CREATE TABLE heap2ao(a int, b int) DISTRIBUTED BY (a);
+CREATE TABLE heap2ao(a int, b int) WITH (fillfactor=70) DISTRIBUTED BY (a);
CREATE TABLE heap2ao2(a int, b int) DISTRIBUTED BY (a);
CREATE INDEX heapi ON heap2ao(b);
ALTER TABLE heap2ao2 ADD CONSTRAINT unique_constraint UNIQUE (a);
@@ -47,53 +50,42 @@ ALTER TABLE heap2ao2 DROP CONSTRAINT unique_constraint;
SET gp_default_storage_options = 'blocksize=65536, compresstype=zlib, compresslevel=5, checksum=true';
-- Alter table heap to AO should work
ALTER TABLE heap2ao SET ACCESS METHOD ao_row;
-ALTER TABLE heap2ao2 SET ACCESS METHOD ao_row;
+ALTER TABLE heap2ao2 SET WITH (appendoptimized=true);
-- The altered table should inherit storage options from gp_default_storage_options
SELECT blocksize,compresslevel,checksum,compresstype,columnstore
-FROM pg_appendonly WHERE relid='heap2ao'::regclass::oid;
+FROM pg_appendonly WHERE relid in ('heap2ao'::regclass::oid, 'heap2ao2'::regclass::oid);
blocksize | compresslevel | checksum | compresstype | columnstore
-----------+---------------+----------+--------------+-------------
65536 | 5 | t | zlib | f
-(1 row)
-
-SELECT reloptions from pg_class where relname='heap2ao';
- reloptions
------------------------------------
- {blocksize=65536,compresslevel=5}
-(1 row)
-
-SELECT blocksize,compresslevel,checksum,compresstype,columnstore
-FROM pg_appendonly WHERE relid='heap2ao2'::regclass::oid;
- blocksize | compresslevel | checksum | compresstype | columnstore
------------+---------------+----------+--------------+-------------
65536 | 5 | t | zlib | f
-(1 row)
+(2 rows)
-SELECT reloptions from pg_class where relname='heap2ao2';
+SELECT reloptions from pg_class where relname in ('heap2ao', 'heap2ao2');
reloptions
-----------------------------------
{blocksize=65536,compresslevel=5}
-(1 row)
+ {blocksize=65536,compresslevel=5}
+(2 rows)
-- Check data is intact
SELECT * FROM heap2ao;
a | b
---+---
+ 1 | 1
2 | 2
3 | 3
4 | 4
- 1 | 1
5 | 5
(5 rows)
SELECT * FROM heap2ao2;
a | b
---+---
- 5 | 5
+ 1 | 1
2 | 2
3 | 3
4 | 4
- 1 | 1
+ 5 | 5
(5 rows)
-- The tables and indexes should have been rewritten (should have different relfilenodes)
@@ -113,9 +105,9 @@ SELECT * FROM relfilebeforeao INTERSECT SELECT * FROM relfileafterao;
SELECT * FROM gp_toolkit.__gp_aoseg('heap2ao');
segment_id | segno | eof | tupcount | varblockcount | eof_uncompressed | modcount | formatversion | state
------------+-------+-----+----------+---------------+------------------+----------+---------------+-------
+ 2 | 0 | 40 | 1 | 1 | 40 | 1 | 3 | 1
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_aovisimap('heap2ao');
@@ -136,29 +128,35 @@ SELECT * FROM gp_toolkit.__gp_aovisimap('heap2ao2');
-----+-------+---------
(0 rows)
-DROP TABLE heap2ao;
-DROP TABLE heap2ao2;
-- check inherited tables
CREATE TABLE heapbase (a int, b int);
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 child (c int) INHERITS (heapbase);
NOTICE: table has parent, setting distribution columns to match parent table
+CREATE TABLE heapbase2 (a int, b int);
+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 child2 (c int) INHERITS (heapbase2);
+NOTICE: table has parent, setting distribution columns to match parent table
INSERT INTO heapbase SELECT i,i FROM generate_series(1,5) i;
INSERT INTO child SELECT i,i,i FROM generate_series(1,5) i;
+INSERT INTO heapbase2 SELECT i,i FROM generate_series(1,5) i;
+INSERT INTO child2 SELECT i,i,i FROM generate_series(1,5) i;
CREATE TEMP TABLE inheritrelfilebefore AS
- SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heapbase')
+ SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heapbase', 'heapbase2')
UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
- WHERE relname in ('heapbase') ORDER BY segid;
+ WHERE relname in ('heapbase', 'heapbase2') 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.
CREATE TEMP TABLE inheritchildrelfilebefore AS
- SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('child')
+ SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('child', 'child2')
UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
- WHERE relname in ('child') ORDER BY segid;
+ WHERE relname in ('child', 'child2') 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.
ALTER TABLE heapbase SET ACCESS METHOD ao_row;
+ALTER TABLE heapbase2 SET WITH (appendoptimized=true);
-- The altered table should inherit storage options from gp_default_storage_options
show gp_default_storage_options;
gp_default_storage_options
@@ -167,38 +165,41 @@ show gp_default_storage_options;
(1 row)
SELECT blocksize,compresslevel,checksum,compresstype,columnstore
-FROM pg_appendonly WHERE relid='heapbase'::regclass::oid;
+FROM pg_appendonly WHERE relid in ('heapbase'::regclass::oid, 'heapbase2'::regclass::oid);
blocksize | compresslevel | checksum | compresstype | columnstore
-----------+---------------+----------+--------------+-------------
65536 | 5 | t | zlib | f
-(1 row)
+ 65536 | 5 | t | zlib | f
+(2 rows)
-SELECT reloptions from pg_class where relname='heapbase';
+SELECT reloptions from pg_class where relname in ('heapbase','heapbase2');
reloptions
-----------------------------------
{blocksize=65536,compresslevel=5}
-(1 row)
+ {blocksize=65536,compresslevel=5}
+(2 rows)
SELECT blocksize,compresslevel,checksum,compresstype,columnstore
-FROM pg_appendonly WHERE relid='child'::regclass::oid;
+FROM pg_appendonly WHERE relid in ('child'::regclass::oid, 'child2'::regclass::oid);
blocksize | compresslevel | checksum | compresstype | columnstore
-----------+---------------+----------+--------------+-------------
(0 rows)
-SELECT reloptions from pg_class where relname='child';
+SELECT reloptions from pg_class where relname in ('child','child2');
reloptions
------------
-(1 row)
+
+(2 rows)
-- Check data is intact
SELECT * FROM heapbase;
a | b
---+---
- 1 | 1
- 1 | 1
5 | 5
5 | 5
+ 1 | 1
+ 1 | 1
2 | 2
3 | 3
4 | 4
@@ -207,11 +208,26 @@ SELECT * FROM heapbase;
4 | 4
(10 rows)
+SELECT * FROM heapbase2;
+ a | b
+---+---
+ 2 | 2
+ 3 | 3
+ 4 | 4
+ 2 | 2
+ 3 | 3
+ 4 | 4
+ 5 | 5
+ 5 | 5
+ 1 | 1
+ 1 | 1
+(10 rows)
+
-- relfile node should change for base table set to AO
CREATE TEMP TABLE inheritrelfileafter AS
- SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heapbase')
+ SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heapbase', 'heapbase2')
UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
- WHERE relname in ('heapbase') ORDER BY segid;
+ WHERE relname in ('heapbase', 'heapbase2') 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.
SELECT * FROM inheritrelfilebefore INTERSECT SELECT * FROM inheritrelfileafter;
@@ -221,15 +237,15 @@ SELECT * FROM inheritrelfilebefore INTERSECT SELECT * FROM inheritrelfileafter;
-- relfile node should not change for child table
CREATE TEMP TABLE inheritchildrelfileafter AS
- SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('child')
+ SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('child', 'child2')
UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
- WHERE relname in ('child') ORDER BY segid;
+ WHERE relname in ('child', 'child2') 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.
SELECT count(*) FROM (SELECT * FROM inheritchildrelfilebefore UNION SELECT * FROM inheritchildrelfileafter)a;
count
-------
- 4
+ 8
(1 row)
-- aux tables are created, pg_appendonly row is created
@@ -246,10 +262,25 @@ SELECT * FROM gp_toolkit.__gp_aovisimap('heapbase');
-----+-------+---------
(0 rows)
+SELECT * FROM gp_toolkit.__gp_aoseg('heapbase2');
+ segment_id | segno | eof | tupcount | varblockcount | eof_uncompressed | modcount | formatversion | state
+------------+-------+-----+----------+---------------+------------------+----------+---------------+-------
+ 1 | 0 | 40 | 1 | 1 | 40 | 1 | 3 | 1
+ 2 | 0 | 40 | 1 | 1 | 40 | 1 | 3 | 1
+ 0 | 0 | 72 | 3 | 1 | 88 | 1 | 3 | 1
+(3 rows)
+
+SELECT * FROM gp_toolkit.__gp_aovisimap('heapbase2');
+ tid | segno | row_num
+-----+-------+---------
+(0 rows)
+
-- aux tables are not created for child table
SELECT * FROM gp_toolkit.__gp_aoseg('child');
-ERROR: 'child' is not an append-only row relation (seg0 slice1 127.0.0.1:7002 pid=19419)
+ERROR: 'child' is not an append-only row relation (seg0 slice1 127.0.0.1:7002 pid=29947)
SELECT * FROM gp_toolkit.__gp_aovisimap('child');
ERROR: function not supported on relation
-DROP TABLE heapbase CASCADE;
-NOTICE: drop cascades to table child
+SELECT * FROM gp_toolkit.__gp_aoseg('child2');
+ERROR: 'child2' is not an append-only row relation (seg0 slice1 127.0.0.1:7002 pid=29947)
+SELECT * FROM gp_toolkit.__gp_aovisimap('child2');
+ERROR: function not supported on relation
diff --git a/src/test/regress/sql/alter_distribution_policy.sql b/src/test/regress/sql/alter_distribution_policy.sql
index 71d9490872..ef6b5a2130 100644
--- a/src/test/regress/sql/alter_distribution_policy.sql
+++ b/src/test/regress/sql/alter_distribution_policy.sql
@@ -308,11 +308,6 @@ Alter table abc set distributed randomly;
Alter table abc set with (reorganize=false) distributed randomly;
drop table abc;
--- disallow, so fails
-create table atsdb (i int, j text) distributed by (j);
-alter table atsdb set with(appendonly = true);
-drop table atsdb;
-
-- MPP-18660: duplicate entry in gp_distribution_policy
set enable_indexscan=on;
set enable_seqscan=off;
diff --git a/src/test/regress/sql/alter_table_set_am.sql b/src/test/regress/sql/alter_table_set_am.sql
index ddf00653d1..f705d1b6a8 100644
--- a/src/test/regress/sql/alter_table_set_am.sql
+++ b/src/test/regress/sql/alter_table_set_am.sql
@@ -2,29 +2,32 @@
-- 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')
+ SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heap2heap', 'heap2heap2')
UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
- WHERE relname in ('heap2heap') ORDER BY segid;
+ WHERE relname in ('heap2heap', 'heap2heap2') 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);
CREATE TEMP TABLE relfileafterheap AS
- SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heap2heap')
+ SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heap2heap', 'heap2heap2')
UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
- WHERE relname in ('heap2heap') ORDER BY segid;
+ WHERE relname in ('heap2heap', 'heap2heap2') ORDER BY segid;
-- relfilenodes shouldn't change
SELECT count(*) FROM (SELECT * FROM relfilebeforeheap UNION SELECT * FROM relfileafterheap)a;
-- Scenario 2: Heap to AO
-CREATE TABLE heap2ao(a int, b int) DISTRIBUTED BY (a);
+CREATE TABLE heap2ao(a int, b int) WITH (fillfactor=70) DISTRIBUTED BY (a);
CREATE TABLE heap2ao2(a int, b int) DISTRIBUTED BY (a);
CREATE INDEX heapi ON heap2ao(b);
@@ -48,15 +51,12 @@ SET gp_default_storage_options = 'blocksize=65536, compresstype=zlib, compressle
-- Alter table heap to AO should work
ALTER TABLE heap2ao SET ACCESS METHOD ao_row;
-ALTER TABLE heap2ao2 SET ACCESS METHOD ao_row;
+ALTER TABLE heap2ao2 SET WITH (appendoptimized=true);
-- The altered table should inherit storage options from gp_default_storage_options
SELECT blocksize,compresslevel,checksum,compresstype,columnstore
-FROM pg_appendonly WHERE relid='heap2ao'::regclass::oid;
-SELECT reloptions from pg_class where relname='heap2ao';
-SELECT blocksize,compresslevel,checksum,compresstype,columnstore
-FROM pg_appendonly WHERE relid='heap2ao2'::regclass::oid;
-SELECT reloptions from pg_class where relname='heap2ao2';
+FROM pg_appendonly WHERE relid in ('heap2ao'::regclass::oid, 'heap2ao2'::regclass::oid);
+SELECT reloptions from pg_class where relname in ('heap2ao', 'heap2ao2');
-- Check data is intact
SELECT * FROM heap2ao;
@@ -77,63 +77,69 @@ SELECT * FROM gp_toolkit.__gp_aovisimap('heap2ao');
SELECT * FROM gp_toolkit.__gp_aoseg('heap2ao2');
SELECT * FROM gp_toolkit.__gp_aovisimap('heap2ao2');
-DROP TABLE heap2ao;
-DROP TABLE heap2ao2;
-
-- check inherited tables
CREATE TABLE heapbase (a int, b int);
CREATE TABLE child (c int) INHERITS (heapbase);
+CREATE TABLE heapbase2 (a int, b int);
+CREATE TABLE child2 (c int) INHERITS (heapbase2);
INSERT INTO heapbase SELECT i,i FROM generate_series(1,5) i;
INSERT INTO child SELECT i,i,i FROM generate_series(1,5) i;
+INSERT INTO heapbase2 SELECT i,i FROM generate_series(1,5) i;
+INSERT INTO child2 SELECT i,i,i FROM generate_series(1,5) i;
CREATE TEMP TABLE inheritrelfilebefore AS
- SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heapbase')
+ SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heapbase', 'heapbase2')
UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
- WHERE relname in ('heapbase') ORDER BY segid;
+ WHERE relname in ('heapbase', 'heapbase2') ORDER BY segid;
CREATE TEMP TABLE inheritchildrelfilebefore AS
- SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('child')
+ SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('child', 'child2')
UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
- WHERE relname in ('child') ORDER BY segid;
+ WHERE relname in ('child', 'child2') ORDER BY segid;
ALTER TABLE heapbase SET ACCESS METHOD ao_row;
+ALTER TABLE heapbase2 SET WITH (appendoptimized=true);
-- The altered table should inherit storage options from gp_default_storage_options
show gp_default_storage_options;
SELECT blocksize,compresslevel,checksum,compresstype,columnstore
-FROM pg_appendonly WHERE relid='heapbase'::regclass::oid;
-SELECT reloptions from pg_class where relname='heapbase';
+FROM pg_appendonly WHERE relid in ('heapbase'::regclass::oid, 'heapbase2'::regclass::oid);
+SELECT reloptions from pg_class where relname in ('heapbase','heapbase2');
+
SELECT blocksize,compresslevel,checksum,compresstype,columnstore
-FROM pg_appendonly WHERE relid='child'::regclass::oid;
-SELECT reloptions from pg_class where relname='child';
+FROM pg_appendonly WHERE relid in ('child'::regclass::oid, 'child2'::regclass::oid);
+SELECT reloptions from pg_class where relname in ('child','child2');
-- Check data is intact
SELECT * FROM heapbase;
+SELECT * FROM heapbase2;
-- relfile node should change for base table set to AO
CREATE TEMP TABLE inheritrelfileafter AS
- SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heapbase')
+ SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('heapbase', 'heapbase2')
UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
- WHERE relname in ('heapbase') ORDER BY segid;
+ WHERE relname in ('heapbase', 'heapbase2') ORDER BY segid;
SELECT * FROM inheritrelfilebefore INTERSECT SELECT * FROM inheritrelfileafter;
-- relfile node should not change for child table
CREATE TEMP TABLE inheritchildrelfileafter AS
- SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('child')
+ SELECT -1 segid, relfilenode FROM pg_class WHERE relname in ('child', 'child2')
UNION SELECT gp_segment_id segid, relfilenode FROM gp_dist_random('pg_class')
- WHERE relname in ('child') ORDER BY segid;
+ WHERE relname in ('child', 'child2') ORDER BY segid;
SELECT count(*) FROM (SELECT * FROM inheritchildrelfilebefore UNION SELECT * FROM inheritchildrelfileafter)a;
-- aux tables are created, pg_appendonly row is created
SELECT * FROM gp_toolkit.__gp_aoseg('heapbase');
SELECT * FROM gp_toolkit.__gp_aovisimap('heapbase');
+SELECT * FROM gp_toolkit.__gp_aoseg('heapbase2');
+SELECT * FROM gp_toolkit.__gp_aovisimap('heapbase2');
-- aux tables are not created for child table
SELECT * FROM gp_toolkit.__gp_aoseg('child');
SELECT * FROM gp_toolkit.__gp_aovisimap('child');
-
-DROP TABLE heapbase CASCADE;
\ No newline at end of file
+SELECT * FROM gp_toolkit.__gp_aoseg('child2');
+SELECT * FROM gp_toolkit.__gp_aovisimap('child2');
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org