This is an automated email from the ASF dual-hosted git repository.
maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new 9193e63ff9a Implement pg_get_expr() for subpartition template (#14288)
9193e63ff9a is described below
commit 9193e63ff9abf6ea6aeb372abed04da1ee23499a
Author: Lei (Alexandra) Wang <alexandra.wang...@gmail.com>
AuthorDate: Tue Nov 8 14:50:51 2022 -0800
Implement pg_get_expr() for subpartition template (#14288)
Implement pg_get_expr() for subpartition template
Co-authored-by: Ashwin Agrawal <aash...@vmware.com>
---
src/backend/utils/adt/ruleutils.c | 154 +++++++++++++++
src/test/regress/expected/AOCO_Compression.out | 48 +++++
src/test/regress/expected/bfv_partition.out | 222 +++++++++++-----------
src/test/regress/expected/column_compression.out | 30 ++-
src/test/regress/expected/partition.out | 62 +++---
src/test/regress/expected/partition_optimizer.out | 62 +++---
src/test/regress/sql/AOCO_Compression.sql | 9 +-
src/test/regress/sql/bfv_partition.sql | 60 +++---
src/test/regress/sql/column_compression.sql | 10 +-
src/test/regress/sql/partition.sql | 14 +-
10 files changed, 456 insertions(+), 215 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index cbfc3d184d2..cdbd27d4d95 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -53,6 +53,7 @@
#include "nodes/pathnodes.h"
#include "optimizer/optimizer.h"
#include "parser/parse_agg.h"
+#include "parser/parse_expr.h"
#include "parser/parse_func.h"
#include "parser/parse_node.h"
#include "parser/parse_oper.h"
@@ -9696,6 +9697,159 @@ get_rule_expr(Node *node, deparse_context *context,
}
break;
+ case T_GpPartitionDefinition:
+ {
+ GpPartitionDefinition *def =
(GpPartitionDefinition*)node;
+ ListCell *lc;
+ char *sep = "";
+ ParseState *pstate = make_parsestate(NULL);
+
+ if (def->isTemplate)
+ appendStringInfo(buf, "SUBPARTITION
TEMPLATE(");
+
+ foreach(lc, def->partDefElems)
+ {
+ if (IsA(lfirst(lc), GpPartDefElem))
+ {
+ GpPartDefElem *elem =
lfirst_node(GpPartDefElem, lc);
+
+ if (elem->colencs)
+ elog(ERROR, "Partition
specific ENCODING clause not supported yet");
+
+ appendStringInfoString(buf,
sep);
+ sep = ", ";
+ if (elem->partName)
+ {
+ if (elem->isDefault)
+
appendStringInfo(buf, "DEFAULT SUBPARTITION %s", elem->partName);
+ else
+
appendStringInfo(buf, "SUBPARTITION %s", elem->partName);
+ }
+
+ if (elem->boundSpec)
+ {
+ switch
(nodeTag(elem->boundSpec))
+ {
+ case
T_GpPartitionRangeSpec:
+ {
+
GpPartitionRangeSpec *rspec =
+
(GpPartitionRangeSpec *)
elem->boundSpec;
+ if
(rspec->partStart)
+ {
+
Node *val = transformExpr(pstate,
+
(Node *)linitial(rspec->partStart->val), EXPR_KIND_VALUES);
+
Assert(rspec->partStart->edge ==
+
PART_EDGE_INCLUSIVE);
+
appendStringInfo(buf, " START (");
+
get_rule_expr(val, context, true);
+
appendStringInfo(buf, ")");
+ }
+ if
(rspec->partEnd)
+ {
+
Node *val = transformExpr(pstate,
+
(Node *)linitial(rspec->partEnd->val), EXPR_KIND_VALUES);
+
Assert(rspec->partEnd->edge ==
+
PART_EDGE_EXCLUSIVE);
+
appendStringInfo(buf, " END (");
+
get_rule_expr(val, context, true);
+
appendStringInfo(buf, ")");
+ }
+ if
(rspec->partEvery)
+ {
+
Node *val = transformExpr(pstate,
+
(Node *)linitial(rspec->partEvery), EXPR_KIND_VALUES);
+
+
appendStringInfo(buf, " Every (");
+
get_rule_expr(val, context, true);
+
appendStringInfo(buf, ")");
+ }
+ }
+ break;
+ case
T_GpPartitionListSpec:
+ {
+
GpPartitionListSpec *lspec = (GpPartitionListSpec *) elem->boundSpec;
+
ListCell *cell;
+
+
appendStringInfoString(buf, " VALUES (");
+ sep =
"";
+
foreach(cell, lspec->partValues)
+ {
+
Node *val = transformExpr(pstate, (Node *)linitial(lfirst(cell)),
EXPR_KIND_VALUES);
+
+
Assert(list_length(lfirst(cell)) == 1);
+
appendStringInfoString(buf, sep);
+
get_rule_expr(val, context, -1);
+
sep = ", ";
+ }
+
appendStringInfoChar(buf, ')');
+ }
+ break;
+ default:
+
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
+ }
+ }
+ if (elem->options)
+ {
+ ListCell *cell;
+
+
appendStringInfoString(buf, sep);
+
appendStringInfoString(buf, " WITH (");
+ if (elem->accessMethod)
+ {
+ if
(pg_strcasecmp(elem->accessMethod, "ao_row") != 0)
+ {
+
appendStringInfoString(buf, "appendonly=true, orientation=row");
+ sep =
", ";
+ }
+ else if
(pg_strcasecmp(elem->accessMethod, "ao_column") != 0)
+ {
+
appendStringInfoString(buf, "appendonly=true, orientation=column");
+ sep =
", ";
+ }
+ }
+ foreach (cell,
elem->options)
+ {
+ DefElem *el =
lfirst_node(DefElem, cell);
+ char *arg;
+
+
appendStringInfoString(buf, sep);
+
appendStringInfo(buf, "%s=", el->defname);
+
appendStringInfo(buf, "%s", arg = defGetString(el));
+ sep = ", ";
+ }
+
appendStringInfoChar(buf, ')');
+ }
+ }
+ else if (IsA(lfirst(lc),
ColumnReferenceStorageDirective))
+ {
+ ListCell *cell;
+ ColumnReferenceStorageDirective
*crsd = lfirst_node(ColumnReferenceStorageDirective, lc);
+
+ appendStringInfoString(buf,
sep);
+ if (crsd->deflt)
+ appendStringInfo(buf,
"DEFAULT COLUMN ENCODING (");
+ else
+ appendStringInfo(buf,
"COLUMN %s ENCODING (", crsd->column);
+
+ sep = "";
+ foreach(cell, crsd->encoding)
+ {
+ DefElem *el =
lfirst_node(DefElem, cell);
+ char *arg;
+
+
appendStringInfoString(buf, sep);
+ appendStringInfo(buf,
"%s=", el->defname);
+ appendStringInfo(buf,
"%s", arg = defGetString(el));
+ sep = ", ";
+ }
+ appendStringInfo(buf, ")");
+ }
+ }
+ if (def->isTemplate)
+ appendStringInfo(buf, ")");
+ }
+ break;
+
default:
elog(ERROR, "unrecognized node type: %d", (int)
nodeTag(node));
break;
diff --git a/src/test/regress/expected/AOCO_Compression.out
b/src/test/regress/expected/AOCO_Compression.out
index f8e1c6164a9..adfcafab56f 100644
--- a/src/test/regress/expected/AOCO_Compression.out
+++ b/src/test/regress/expected/AOCO_Compression.out
@@ -459,6 +459,12 @@ CREATE TABLE co_cr_sub_partzlib8192_1
COLUMN a1 encoding (compresstype = zlib),
COLUMN a5 ENCODING (compresstype=zlib,compresslevel=1, blocksize=8192),
DEFAULT COLUMN ENCODING (compresstype=zlib,compresslevel=1,blocksize=8192))
(start(1) end(5000) every(1000));
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'co_cr_sub_partzlib8192_1'::regclass;
+ level |
pg_get_expr
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 1 | SUBPARTITION TEMPLATE(DEFAULT SUBPARTITION df_sp, SUBPARTITION sp1
VALUES ('M'), SUBPARTITION sp2 VALUES ('F'), COLUMN a2 ENCODING
(compresstype=zlib, compresslevel=1, blocksize=8192), COLUMN a1 ENCODING
(compresstype=zlib), COLUMN a5 ENCODING (compresstype=zlib, compresslevel=1,
blocksize=8192), DEFAULT COLUMN ENCODING (compresstype=zlib, compresslevel=1,
blocksize=8192))
+(1 row)
+
--
-- Create Indexes
--
@@ -472,6 +478,12 @@ INSERT INTO
co_cr_sub_partzlib8192_1(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,
ANALYZE co_cr_sub_partzlib8192_1;
--Create Uncompressed table of same schema definition
CREATE TABLE co_cr_sub_partzlib8192_1_uncompr(id SERIAL,a1 int,a2 char(5),a3
numeric,a4 boolean DEFAULT false ,a5 char DEFAULT 'd',a6 text,a7 timestamp,a8
character varying(705),a9 bigint,a10 date,a11 varchar(600),a12 text,a13
decimal,a14 real,a15 bigint,a16 int4 ,a17 bytea,a18 timestamp with time
zone,a19 timetz,a20 path,a21 box,a22 macaddr,a23 interval,a24 character
varying(800),a25 lseg,a26 point,a27 double precision,a28 circle,a29 int4,a30
numeric(8),a31 polygon,a32 date,a33 real,a34 [...]
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'co_cr_sub_partzlib8192_1_uncompr'::regclass;
+ level | pg_get_expr
+-------+-------------------------------------------------------------------------------------
+ 1 | SUBPARTITION TEMPLATE(SUBPARTITION sp1 VALUES ('M'), SUBPARTITION sp2
VALUES ('F'))
+(1 row)
+
--
-- Insert to uncompressed table
--
@@ -844,6 +856,12 @@ CREATE TABLE co_cr_sub_partzlib8192_1_2
COLUMN a1 encoding (compresstype = zlib),
COLUMN a5 ENCODING (compresstype=zlib,compresslevel=1, blocksize=8192),
DEFAULT COLUMN ENCODING (compresstype=zlib,compresslevel=1,blocksize=8192))
(partition p1 values('F'), partition p2 values ('M'));
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'co_cr_sub_partzlib8192_1_2'::regclass;
+ level |
pg_get_expr
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 1 | SUBPARTITION TEMPLATE(DEFAULT SUBPARTITION df_sp, START (1) END
(5000) Every (1000), COLUMN a2 ENCODING (compresstype=zlib, compresslevel=1,
blocksize=8192), COLUMN a1 ENCODING (compresstype=zlib), COLUMN a5 ENCODING
(compresstype=zlib, compresslevel=1, blocksize=8192), DEFAULT COLUMN ENCODING
(compresstype=zlib, compresslevel=1, blocksize=8192))
+(1 row)
+
--
-- Create Indexes
--
@@ -1283,6 +1301,12 @@ CREATE TABLE co_wt_sub_partrle_type8192_1
(id SERIAL,a1 int,a2 char(5),a3 numeric,a4 boolean DEFAULT false ,a5
char DEFAULT 'd',a6 text,a7 timestamp,a8 character varying(705),a9 bigint,a10
date,a11 varchar(600),a12 text,a13 decimal,a14 real,a15 bigint,a16 int4 ,a17
bytea,a18 timestamp with time zone,a19 timetz,a20 path,a21 box,a22 macaddr,a23
interval,a24 character varying(800),a25 lseg,a26 point,a27 double precision,a28
circle,a29 int4,a30 numeric(8),a31 polygon,a32 date,a33 real,a34 money,a35
cidr,a36 inet,a37 time,a38 text,a [...]
WITH (appendonly=true, orientation=column) distributed randomly Partition by
range(a1) Subpartition by list(a2) subpartition template ( default subpartition
df_sp, subpartition sp1 values('M') , subpartition sp2 values('F')
WITH (appendonly=true,
orientation=column,compresstype=rle_type,compresslevel=1,blocksize=8192))
(start(1) end(5000) every(1000) );
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'co_wt_sub_partrle_type8192_1'::regclass;
+ level |
pg_get_expr
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 1 | SUBPARTITION TEMPLATE(DEFAULT SUBPARTITION df_sp, SUBPARTITION sp1
VALUES ('M'), SUBPARTITION sp2 VALUES ('F'), WITH (appendonly=true,
orientation=row, compresstype=rle_type, compresslevel=1, blocksize=8192))
+(1 row)
+
--
-- Create Indexes
--
@@ -1296,6 +1320,12 @@ INSERT INTO
co_wt_sub_partrle_type8192_1(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,
ANALYZE co_wt_sub_partrle_type8192_1;
--Create Uncompressed table of same schema definition
CREATE TABLE co_wt_sub_partrle_type8192_1_uncompr(id SERIAL,a1 int,a2
char(5),a3 numeric,a4 boolean DEFAULT false ,a5 char DEFAULT 'd',a6 text,a7
timestamp,a8 character varying(705),a9 bigint,a10 date,a11 varchar(600),a12
text,a13 decimal,a14 real,a15 bigint,a16 int4 ,a17 bytea,a18 timestamp with
time zone,a19 timetz,a20 path,a21 box,a22 macaddr,a23 interval,a24 character
varying(800),a25 lseg,a26 point,a27 double precision,a28 circle,a29 int4,a30
numeric(8),a31 polygon,a32 date,a33 real [...]
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'co_wt_sub_partrle_type8192_1_uncompr'::regclass;
+ level | pg_get_expr
+-------+-------------------------------------------------------------------------------------
+ 1 | SUBPARTITION TEMPLATE(SUBPARTITION sp1 VALUES ('M'), SUBPARTITION sp2
VALUES ('F'))
+(1 row)
+
--
-- Insert to uncompressed table
--
@@ -1668,6 +1698,12 @@ CREATE TABLE co_wt_sub_partrle_type8192_1_2
(id SERIAL,a1 int,a2 char(5),a3 numeric,a4 boolean DEFAULT false ,a5
char DEFAULT 'd',a6 text,a7 timestamp,a8 character varying(705),a9 bigint,a10
date,a11 varchar(600),a12 text,a13 decimal,a14 real,a15 bigint,a16 int4 ,a17
bytea,a18 timestamp with time zone,a19 timetz,a20 path,a21 box,a22 macaddr,a23
interval,a24 character varying(800),a25 lseg,a26 point,a27 double precision,a28
circle,a29 int4,a30 numeric(8),a31 polygon,a32 date,a33 real,a34 money,a35
cidr,a36 inet,a37 time,a38 text,a [...]
WITH (appendonly=true, orientation=column) distributed randomly Partition by
list(a2) Subpartition by range(a1) subpartition template (default subpartition
df_sp, start(1) end(5000) every(1000)
WITH (appendonly=true,
orientation=column,compresstype=rle_type,compresslevel=1,blocksize=8192))
(partition p1 values ('M'), partition p2 values ('F'));
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'co_wt_sub_partrle_type8192_1_2'::regclass;
+ level |
pg_get_expr
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 1 | SUBPARTITION TEMPLATE(DEFAULT SUBPARTITION df_sp, START (1) END
(5000) Every (1000), WITH (appendonly=true, orientation=row,
compresstype=rle_type, compresslevel=1, blocksize=8192))
+(1 row)
+
--
-- Create Indexes
--
@@ -2111,6 +2147,12 @@ CREATE TABLE ao_wt_sub_partzlib8192_5
(id SERIAL,a1 int,a2 char(5),a3 numeric,a4 boolean DEFAULT false ,a5
char DEFAULT 'd',a6 text,a7 timestamp,a8 character varying(705),a9 bigint,a10
date,a11 varchar(600),a12 text,a13 decimal,a14 real,a15 bigint,a16 int4 ,a17
bytea,a18 timestamp with time zone,a19 timetz,a20 path,a21 box,a22 macaddr,a23
interval,a24 character varying(800),a25 lseg,a26 point,a27 double precision,a28
circle,a29 int4,a30 numeric(8),a31 polygon,a32 date,a33 real,a34 money,a35
cidr,a36 inet,a37 time,a38 text,a [...]
WITH (appendonly=true, orientation=row) distributed randomly Partition by
range(a1) Subpartition by list(a2) subpartition template ( default subpartition
df_sp, subpartition sp1 values('M') , subpartition sp2 values('F')
WITH (appendonly=true,
orientation=row,compresstype=zlib,compresslevel=5,blocksize=8192)) (start(1)
end(5000) every(1000) );
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'ao_wt_sub_partzlib8192_5'::regclass;
+ level |
pg_get_expr
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 1 | SUBPARTITION TEMPLATE(DEFAULT SUBPARTITION df_sp, SUBPARTITION sp1
VALUES ('M'), SUBPARTITION sp2 VALUES ('F'), WITH (appendonly=true,
orientation=column, compresstype=zlib, compresslevel=5, blocksize=8192))
+(1 row)
+
--
-- Create Indexes
--
@@ -2505,6 +2547,12 @@ CREATE TABLE ao_wt_sub_partzlib8192_5_2
(id SERIAL,a1 int,a2 char(5),a3 numeric,a4 boolean DEFAULT false ,a5
char DEFAULT 'd',a6 text,a7 timestamp,a8 character varying(705),a9 bigint,a10
date,a11 varchar(600),a12 text,a13 decimal,a14 real,a15 bigint,a16 int4 ,a17
bytea,a18 timestamp with time zone,a19 timetz,a20 path,a21 box,a22 macaddr,a23
interval,a24 character varying(800),a25 lseg,a26 point,a27 double precision,a28
circle,a29 int4,a30 numeric(8),a31 polygon,a32 date,a33 real,a34 money,a35
cidr,a36 inet,a37 time,a38 text,a [...]
WITH (appendonly=true, orientation=row) distributed randomly Partition by
list(a2) Subpartition by range(a1) subpartition template (default subpartition
df_sp, start(1) end(5000) every(1000)
WITH (appendonly=true,
orientation=row,compresstype=zlib,compresslevel=5,blocksize=8192)) (partition
p1 values ('M'), partition p2 values ('F'));
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'ao_wt_sub_partzlib8192_5_2'::regclass;
+ level |
pg_get_expr
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 1 | SUBPARTITION TEMPLATE(DEFAULT SUBPARTITION df_sp, START (1) END
(5000) Every (1000), WITH (appendonly=true, orientation=column,
compresstype=zlib, compresslevel=5, blocksize=8192))
+(1 row)
+
--
-- Create Indexes
--
diff --git a/src/test/regress/expected/bfv_partition.out
b/src/test/regress/expected/bfv_partition.out
index ec180b9855f..c159a5c28df 100644
--- a/src/test/regress/expected/bfv_partition.out
+++ b/src/test/regress/expected/bfv_partition.out
@@ -2239,18 +2239,18 @@ ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (`);
ERROR: syntax error at or near "`"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (`);
^
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
- relid | level |
template
[...]
--------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa"
:location 216}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 252}))}
:subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <>
:colencs <>} {GPPARTDEFELEM :partNam [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
+ relid | level |
pg_get_expr
+-------------+-------+----------------------------------------------------------------------------------------------------------------------------------
+ qa147.sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usa VALUES ('usa'),
SUBPARTITION asia VALUES ('asia'), SUBPARTITION europe VALUES ('europe'))
(1 row)
set client_min_messages='warning';
drop schema qa147 cascade;
reset client_min_messages;
-select relid, level, template from gp_partition_template where not exists
(select oid from pg_class where oid = relid);
- relid | level | template
--------+-------+----------
+select relid, level, pg_get_expr(template, relid) from gp_partition_template
where not exists (select oid from pg_class where oid = relid);
+ relid | level | pg_get_expr
+-------+-------+-------------
(0 rows)
-- Mix-Match for Alter subpartition template
@@ -2268,18 +2268,18 @@ SUBPARTITION TEMPLATE
EVERY (INTERVAL '1 month') );
-- Clear TEMPLATE
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE ();
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
- relid | level | template
--------+-------+----------
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
+ relid | level | pg_get_expr
+-------+-------+-------------
(0 rows)
-- This will overwrite previous subpartition template
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia') );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
- relid | level |
template
[...]
-------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa"
:location 76}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 110}))}
:subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <>
:colencs <>}) :encClauses <> :isTempla [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
+ relid | level | pg_get_expr
+------------+-------+-------------------------------------------------------------------------------------------
+ qa147sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usa VALUES ('usa'),
SUBPARTITION asia VALUES ('asia'))
(1 row)
-- Invalid subpartition
@@ -2293,10 +2293,10 @@ ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
ERROR: invalid boundary specification for LIST partition
LINE 2: ( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END...
^
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
- relid | level |
template
[...]
-------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa"
:location 76}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 110}))}
:subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <>
:colencs <>}) :encClauses <> :isTempla [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
+ relid | level | pg_get_expr
+------------+-------+-------------------------------------------------------------------------------------------
+ qa147sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usa VALUES ('usa'),
SUBPARTITION asia VALUES ('asia'))
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
@@ -2307,10 +2307,10 @@ SUBPARTITION usadate start (date '2008-01-01')
INCLUSIVE END(date '2009-01-01')
ERROR: invalid boundary specification for LIST partition
LINE 4: SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END...
^
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
- relid | level |
template
[...]
-------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa"
:location 76}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 110}))}
:subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <>
:colencs <>}) :encClauses <> :isTempla [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
+ relid | level | pg_get_expr
+------------+-------+-------------------------------------------------------------------------------------------
+ qa147sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usa VALUES ('usa'),
SUBPARTITION asia VALUES ('asia'))
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
@@ -2321,10 +2321,10 @@ SUBPARTITION usa1 VALUES('usa'));
ERROR: invalid boundary specification for LIST partition
LINE 3: SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END...
^
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
- relid | level |
template
[...]
-------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa"
:location 76}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 110}))}
:subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <>
:colencs <>}) :encClauses <> :isTempla [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
+ relid | level | pg_get_expr
+------------+-------+-------------------------------------------------------------------------------------------
+ qa147sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usa VALUES ('usa'),
SUBPARTITION asia VALUES ('asia'))
(1 row)
drop table qa147sales;
@@ -2343,26 +2343,26 @@ SUBPARTITION TEMPLATE
PARTITION europe VALUES ('europe') );
-- Clear TEMPLATE
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE ();
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
- relid | level | template
--------+-------+----------
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
+ relid | level | pg_get_expr
+-------+-------+-------------
(0 rows)
-- This will overwrite previous subpartition template
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date
'2008-02-01') EXCLUSIVE );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
- relid | level |
template
[...]
-------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart
{GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01"
:location 82} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false
:pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 77} :location
-1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val
"\2008-02-01" :location 116} :typeName {TYPENAME :na [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
+ relid | level | pg_get_expr
+------------+-------+-----------------------------------------------------------------------------------------------
+ qa147sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usam1 START
('01-01-2008'::date) END ('02-01-2008'::date))
(1 row)
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date
'2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
- relid | level |
[...]
-------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart
{GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01"
:location 82} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false
:pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 77} :location
-1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val
"\2009-01-01" :location 116} :typeName {TYPENAME :na [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
+ relid | level |
pg_get_expr
+------------+-------+---------------------------------------------------------------------------------------------------------------------------
+ qa147sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usam1 START
('01-01-2008'::date) END ('01-01-2009'::date) Every ('@ 1 mon'::interval))
(1 row)
-- Invalid subpartition template
@@ -2371,10 +2371,10 @@ ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
ERROR: invalid boundary specification for RANGE partition
LINE 2: ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ...
^
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
- relid | level |
[...]
-------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart
{GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01"
:location 82} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false
:pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 77} :location
-1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val
"\2009-01-01" :location 116} :typeName {TYPENAME :na [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
+ relid | level |
pg_get_expr
+------------+-------+---------------------------------------------------------------------------------------------------------------------------
+ qa147sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usam1 START
('01-01-2008'::date) END ('01-01-2009'::date) Every ('@ 1 mon'::interval))
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
@@ -2385,10 +2385,10 @@ SUBPARTITION usadate start (date '2008-01-01')
INCLUSIVE END(date '2009-01-01')
ERROR: invalid boundary specification for RANGE partition
LINE 3: SUBPARTITION usa1 VALUES('usa'),
^
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
- relid | level |
[...]
-------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart
{GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01"
:location 82} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false
:pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 77} :location
-1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val
"\2009-01-01" :location 116} :typeName {TYPENAME :na [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
+ relid | level |
pg_get_expr
+------------+-------+---------------------------------------------------------------------------------------------------------------------------
+ qa147sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usam1 START
('01-01-2008'::date) END ('01-01-2009'::date) Every ('@ 1 mon'::interval))
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
@@ -2399,16 +2399,16 @@ SUBPARTITION usa1 VALUES('usa'));
ERROR: invalid boundary specification for RANGE partition
LINE 4: SUBPARTITION usa1 VALUES('usa'));
^
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
- relid | level |
[...]
-------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart
{GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01"
:location 82} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false
:pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 77} :location
-1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val
"\2009-01-01" :location 116} :typeName {TYPENAME :na [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
+ relid | level |
pg_get_expr
+------------+-------+---------------------------------------------------------------------------------------------------------------------------
+ qa147sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usam1 START
('01-01-2008'::date) END ('01-01-2009'::date) Every ('@ 1 mon'::interval))
(1 row)
drop table qa147sales;
-select relid, level, template from gp_partition_template where not exists
(select oid from pg_class where oid = relid);
- relid | level | template
--------+-------+----------
+select relid, level, pg_get_expr(template, relid) from gp_partition_template
where not exists (select oid from pg_class where oid = relid);
+ relid | level | pg_get_expr
+-------+-------+-------------
(0 rows)
-- Now with Schema
@@ -2428,18 +2428,18 @@ SUBPARTITION TEMPLATE
EVERY (INTERVAL '1 month') );
-- Clear TEMPLATE
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE ();
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
- relid | level | template
--------+-------+----------
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
+ relid | level | pg_get_expr
+-------+-------+-------------
(0 rows)
-- This will overwrite previous subpartition template
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia') );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
- relid | level |
template
[...]
--------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa"
:location 77}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 111}))}
:subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <>
:colencs <>}) :encClauses <> :isTempl [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
+ relid | level | pg_get_expr
+-------------+-------+-------------------------------------------------------------------------------------------
+ qa147.sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usa VALUES ('usa'),
SUBPARTITION asia VALUES ('asia'))
(1 row)
-- Invalid subpartition
@@ -2453,10 +2453,10 @@ ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
ERROR: invalid boundary specification for LIST partition
LINE 2: ( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END...
^
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
- relid | level |
template
[...]
--------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa"
:location 77}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 111}))}
:subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <>
:colencs <>}) :encClauses <> :isTempl [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
+ relid | level | pg_get_expr
+-------------+-------+-------------------------------------------------------------------------------------------
+ qa147.sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usa VALUES ('usa'),
SUBPARTITION asia VALUES ('asia'))
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
@@ -2467,10 +2467,10 @@ SUBPARTITION usadate start (date '2008-01-01')
INCLUSIVE END(date '2009-01-01')
ERROR: invalid boundary specification for LIST partition
LINE 4: SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END...
^
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
- relid | level |
template
[...]
--------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa"
:location 77}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 111}))}
:subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <>
:colencs <>}) :encClauses <> :isTempl [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
+ relid | level | pg_get_expr
+-------------+-------+-------------------------------------------------------------------------------------------
+ qa147.sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usa VALUES ('usa'),
SUBPARTITION asia VALUES ('asia'))
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
@@ -2481,17 +2481,17 @@ SUBPARTITION usa1 VALUES('usa'));
ERROR: invalid boundary specification for LIST partition
LINE 3: SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END...
^
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
- relid | level |
template
[...]
--------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa"
:location 77}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 111}))}
:subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <>
:colencs <>}) :encClauses <> :isTempl [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
+ relid | level | pg_get_expr
+-------------+-------+-------------------------------------------------------------------------------------------
+ qa147.sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usa VALUES ('usa'),
SUBPARTITION asia VALUES ('asia'))
(1 row)
DROP SCHEMA qa147 cascade;
NOTICE: drop cascades to table qa147.sales
-select relid, level, template from gp_partition_template where not exists
(select oid from pg_class where oid = relid);
- relid | level | template
--------+-------+----------
+select relid, level, pg_get_expr(template, relid) from gp_partition_template
where not exists (select oid from pg_class where oid = relid);
+ relid | level | pg_get_expr
+-------+-------+-------------
(0 rows)
CREATE SCHEMA qa147;
@@ -2510,26 +2510,26 @@ SUBPARTITION TEMPLATE
PARTITION europe VALUES ('europe') );
-- Clear TEMPLATE
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE ();
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
- relid | level | template
--------+-------+----------
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
+ relid | level | pg_get_expr
+-------+-------+-------------
(0 rows)
-- This will overwrite previous subpartition template
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date
'2008-02-01') EXCLUSIVE );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
- relid | level |
template
[...]
--------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart
{GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01"
:location 83} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false
:pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 78} :location
-1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val
"\2008-02-01" :location 117} :typeName {TYPENAME :n [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
+ relid | level | pg_get_expr
+-------------+-------+-----------------------------------------------------------------------------------------------
+ qa147.sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usam1 START
('01-01-2008'::date) END ('02-01-2008'::date))
(1 row)
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date
'2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
- relid | level |
[...]
--------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart
{GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01"
:location 83} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false
:pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 78} :location
-1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val
"\2009-01-01" :location 117} :typeName {TYPENAME :n [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
+ relid | level |
pg_get_expr
+-------------+-------+---------------------------------------------------------------------------------------------------------------------------
+ qa147.sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usam1 START
('01-01-2008'::date) END ('01-01-2009'::date) Every ('@ 1 mon'::interval))
(1 row)
-- Invalid subpartition template
@@ -2538,10 +2538,10 @@ ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
ERROR: invalid boundary specification for RANGE partition
LINE 2: ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ...
^
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
- relid | level |
[...]
--------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart
{GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01"
:location 83} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false
:pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 78} :location
-1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val
"\2009-01-01" :location 117} :typeName {TYPENAME :n [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
+ relid | level |
pg_get_expr
+-------------+-------+---------------------------------------------------------------------------------------------------------------------------
+ qa147.sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usam1 START
('01-01-2008'::date) END ('01-01-2009'::date) Every ('@ 1 mon'::interval))
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
@@ -2552,10 +2552,10 @@ SUBPARTITION usadate start (date '2008-01-01')
INCLUSIVE END(date '2009-01-01')
ERROR: invalid boundary specification for RANGE partition
LINE 3: SUBPARTITION usa1 VALUES('usa'),
^
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
- relid | level |
[...]
--------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart
{GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01"
:location 83} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false
:pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 78} :location
-1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val
"\2009-01-01" :location 117} :typeName {TYPENAME :n [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
+ relid | level |
pg_get_expr
+-------------+-------+---------------------------------------------------------------------------------------------------------------------------
+ qa147.sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usam1 START
('01-01-2008'::date) END ('01-01-2009'::date) Every ('@ 1 mon'::interval))
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
@@ -2566,17 +2566,17 @@ SUBPARTITION usa1 VALUES('usa'));
ERROR: invalid boundary specification for RANGE partition
LINE 4: SUBPARTITION usa1 VALUES('usa'));
^
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
- relid | level |
[...]
--------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart
{GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01"
:location 83} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false
:pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 78} :location
-1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val
"\2009-01-01" :location 117} :typeName {TYPENAME :n [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
+ relid | level |
pg_get_expr
+-------------+-------+---------------------------------------------------------------------------------------------------------------------------
+ qa147.sales | 1 | SUBPARTITION TEMPLATE(SUBPARTITION usam1 START
('01-01-2008'::date) END ('01-01-2009'::date) Every ('@ 1 mon'::interval))
(1 row)
drop schema qa147 cascade;
NOTICE: drop cascades to table qa147.sales
-select relid, level, template from gp_partition_template where not exists
(select oid from pg_class where oid = relid);
- relid | level | template
--------+-------+----------
+select relid, level, pg_get_expr(template, relid) from gp_partition_template
where not exists (select oid from pg_class where oid = relid);
+ relid | level | pg_get_expr
+-------+-------+-------------
(0 rows)
set gp_autostats_mode=on_change;
@@ -2701,10 +2701,10 @@ alter table partsupp_def set subpartition template(
subpartition aaa start(400)
-- If we want to use the new subpartition template, we have to drop the
default partition first, and then readd the default partition
-- Note 2: We do not support this function yet, but if we are able to split
default partition with default subpartition, would we
-- be using the subpartition template to definte the "new" partition or the
existing one.
-select relid::regclass, level, template from gp_partition_template where relid
= 'partsupp_def'::regclass;
- relid | level |
template
---------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- partsupp_def | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName aaa :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM
:val ({A_CONST :val 400 :location 75}) :edge 1} :partEnd {GPPARTITIONRANGEITEM
:val ({A_CONST :val 600 :location 85}) :edge 2} :partEvery ({A_CONST :val 100
:location 96})} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'partsupp_def'::regclass;
+ relid | level | pg_get_expr
+--------------+-------+---------------------------------------------------------------------------
+ partsupp_def | 1 | SUBPARTITION TEMPLATE(SUBPARTITION aaa START (400) END
(600) Every (100))
(1 row)
alter table partsup_def add partition f1 start(0) end (300) every(100);
@@ -2727,10 +2727,10 @@ NOTICE: Table doesn't have 'DISTRIBUTED BY' clause --
Using column named 'ps_pa
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 partsupp_def2 set subpartition template();
alter table partsupp_def2 set subpartition template( subpartition aaa
start(400) end (600) every(100) );
-select relid::regclass, level, template from gp_partition_template where relid
= 'partsupp_def2'::regclass;
- relid | level |
template
----------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- partsupp_def2 | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName aaa :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM
:val ({A_CONST :val 400 :location 76}) :edge 1} :partEnd {GPPARTITIONRANGEITEM
:val ({A_CONST :val 600 :location 86}) :edge 2} :partEvery ({A_CONST :val 100
:location 97})} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'partsupp_def2'::regclass;
+ relid | level | pg_get_expr
+---------------+-------+---------------------------------------------------------------------------
+ partsupp_def2 | 1 | SUBPARTITION TEMPLATE(SUBPARTITION aaa START (400)
END (600) Every (100))
(1 row)
drop table partsupp_def;
@@ -3011,9 +3011,9 @@ drop table partition_cleanup1;
drop schema partition_999 cascade;
NOTICE: drop cascades to table partition_cleanup2
-- These should be empty
-select relid, level, template from gp_partition_template where not exists
(select oid from pg_class where oid = relid);
- relid | level | template
--------+-------+----------
+select relid, level, pg_get_expr(template, relid) from gp_partition_template
where not exists (select oid from pg_class where oid = relid);
+ relid | level | pg_get_expr
+-------+-------+-------------
(0 rows)
--
diff --git a/src/test/regress/expected/column_compression.out
b/src/test/regress/expected/column_compression.out
index 148f73ddb91..b2edc54ddc3 100644
--- a/src/test/regress/expected/column_compression.out
+++ b/src/test/regress/expected/column_compression.out
@@ -765,6 +765,12 @@ execute ccddlcheck;
ccddl_1_prt_p2_2_prt_sp1 | l | 4 |
{compresstype=zlib,compresslevel=1,blocksize=32768}
(20 rows)
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'ccddl'::regclass;
+ level |
pg_get_expr
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 1 | SUBPARTITION TEMPLATE(SUBPARTITION sp1 VALUES (1, 2, 3, 4, 5), COLUMN
i ENCODING (compresstype=zlib), COLUMN j ENCODING (compresstype=rle_type),
COLUMN k ENCODING (compresstype=zlib), COLUMN l ENCODING (compresstype=zlib))
+(1 row)
+
insert into ccddl select 1, (i % 19) + 1, ((i+3) % 5) + 1, i+3 from
generate_series(1, 100) i;
select * from ccddl;
i | j | k | l
@@ -1227,6 +1233,12 @@ execute ccddlcheck;
ccddl_1_prt_p1_2_prt_sp1 | j | 2 |
{compresstype=rle_type,compresslevel=1,blocksize=32768}
(6 rows)
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'ccddl'::regclass;
+ level |
pg_get_expr
+-------+----------------------------------------------------------------------------------------------------------------------------------------------
+ 1 | SUBPARTITION TEMPLATE(SUBPARTITION sp1 START (1) END (20), COLUMN i
ENCODING (compresstype=zlib), COLUMN j ENCODING (compresstype=rle_type))
+(1 row)
+
alter table ccddl alter partition p1 split partition sp1 at (10) into
(partition sp2, partition sp3);
execute ccddlcheck;
relname | attname | filenum |
attoptions
@@ -1304,6 +1316,13 @@ execute ccddlcheck;
ccddl_1_prt_1_2_prt_1_3_prt_usa | region | 5 |
{compresstype=rle_type,compresslevel=1,blocksize=32768}
(20 rows)
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'ccddl'::regclass;
+ level | pg_get_expr
+-------+--------------------------------------------------------------------------------------------------------
+ 1 | SUBPARTITION TEMPLATE( START (1) END (13), COLUMN month ENCODING
(compresstype=rle_type))
+ 2 | SUBPARTITION TEMPLATE(SUBPARTITION usa VALUES ('usa'), COLUMN region
ENCODING (compresstype=rle_type))
+(2 rows)
+
-- Ensure we can read and write
insert into ccddl select 1, 2008, 1, 2, 'usa' from generate_series(1, 100);
select * from ccddl;
@@ -1624,6 +1643,12 @@ execute ccddlcheck;
ccddl_1_prt_newp_2_prt_sp1 | j | 3 |
{compresstype=zlib,compresslevel=1,blocksize=32768}
(33 rows)
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'ccddl'::regclass;
+ level | pg_get_expr
+-------+-------------------------------------------------------------------------------------------------
+ 1 | SUBPARTITION TEMPLATE(SUBPARTITION sp1 VALUES (1), DEFAULT COLUMN
ENCODING (compresstype=zlib))
+(1 row)
+
drop table ccddl;
-----------------------------------------------------------------------
-- Partitioning support
@@ -1641,8 +1666,9 @@ encoding(compresstype=sdf2sdf));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i'
as the Apache Cloudberry 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.
ERROR: unknown compresstype "sdf2sdf"
--- We don't support partition element specific encoding clauses in subpartition
--- templates as we have no place to store them.
+-- Historically we don't support partition element specific encoding clauses in
+-- subpartition templates as we didn't have place to store them. We now have
+-- place to store them if we want to, but for now we keep this door closed
create table a (i int, j int) with (appendonly=true, orientation=column)
partition by range(i) subpartition by range(j)
subpartition template(start(1) end(10) default column encoding
(compresstype=zlib),
diff --git a/src/test/regress/expected/partition.out
b/src/test/regress/expected/partition.out
index ae7dc6b2b2a..1e70e47b872 100755
--- a/src/test/regress/expected/partition.out
+++ b/src/test/regress/expected/partition.out
@@ -2314,9 +2314,9 @@ DETAIL: Partition key of the failing row contains (i) =
(5).
drop table bar_p;
-- Drop should not leave anything lingering for bar_p or its
-- subpartitions in pg_partition* catalog tables.
-select relid, level, template from gp_partition_template where not exists
(select oid from pg_class where oid = relid);
- relid | level | template
--------+-------+----------
+select relid, level, pg_get_expr(template, relid) from gp_partition_template
where not exists (select oid from pg_class where oid = relid);
+ relid | level | pg_get_expr
+-------+-------+-------------
(0 rows)
-- MPP-4172
@@ -2437,28 +2437,28 @@ select relid::regclass, level from
gp_partition_template where relid = 'rank_set
alter table rank_settemp set subpartition template (default subpartition def2);
-- def2 is there
-select relid::regclass, level, template from gp_partition_template where relid
= 'rank_settemp'::regclass;
- relid | level |
template
---------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- rank_settemp | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName def2 :boundSpec <> :subSpec <> :isDefault true :options <>
:accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate
true}
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'rank_settemp'::regclass;
+ relid | level | pg_get_expr
+--------------+-------+--------------------------------------------------
+ rank_settemp | 1 | SUBPARTITION TEMPLATE(DEFAULT SUBPARTITION def2)
(1 row)
alter table rank_settemp set subpartition template (default subpartition def2);
-- Should still be there
-select relid::regclass, level, template from gp_partition_template where relid
= 'rank_settemp'::regclass;
- relid | level |
template
---------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- rank_settemp | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName def2 :boundSpec <> :subSpec <> :isDefault true :options <>
:accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate
true}
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'rank_settemp'::regclass;
+ relid | level | pg_get_expr
+--------------+-------+--------------------------------------------------
+ rank_settemp | 1 | SUBPARTITION TEMPLATE(DEFAULT SUBPARTITION def2)
(1 row)
alter table rank_settemp set subpartition template (start (date '2006-01-01')
with (appendonly=true));
alter table rank_settemp add partition f1 values ('N');
alter table rank_settemp set subpartition template (start (date '2007-01-01')
with (appendonly=true, compresslevel=5));
alter table rank_settemp add partition f2 values ('C');
-select relid::regclass, level, template from gp_partition_template where relid
= 'rank_settemp'::regclass;
- relid | level |
template
[...]
---------------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- rank_settemp | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName <> :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM
:val ({TYPECAST :arg {A_CONST :val "\2007-01-01" :location 64} :typeName
{TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <>
:typemod -1 :arrayBounds <> :location 59} :location -1}) :edge 1} :partEnd <>
:partEvery <>} :subSpec <> :isDefault false :options ({DEFELEM :defnamespace <>
:defname compresslevel :arg [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'rank_settemp'::regclass;
+ relid | level |
pg_get_expr
+--------------+-------+------------------------------------------------------------------------------------------------------------------
+ rank_settemp | 1 | SUBPARTITION TEMPLATE( START ('01-01-2007'::date),
WITH (appendonly=true, orientation=column, compresslevel=5))
(1 row)
drop table rank_settemp;
@@ -3593,12 +3593,12 @@ subpartition l2 values (6,7,8,9,10) );
alter table mpp5992
set subpartition template (subpartition l1 values (1,2,3),
subpartition l2 values (4,5,6), subpartition l3 values (7,8,9,10));
-select relid::regclass, level, template from gp_partition_template where relid
= 'mpp5992'::regclass;
- relid | level |
template
[...]
----------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- mpp5992 | 3 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName lll1 :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "M"
:location 133}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName lll2 :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val "F" :location 165}))} :subSpec
<> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs
<>}) :encClauses <> :isTemplate true}
- mpp5992 | 2 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName ll1 :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val
"Engineering" :location 108}))} :subSpec <> :isDefault false :options <>
:accessMethod <> :tablespacename <> :colencs <>} {GPPARTDEFELEM :partName ll2
:boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "QA" :location
149}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>}) :encClauses <> :isTem [...]
- mpp5992 | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName l1 :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val 1
:location 72}) ({A_CONST :val 2 :location 74}) ({A_CONST :val 3 :location
76}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName l2 :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val 4 :location 105}) ({A_CONST
:val 5 :location 107}) ({A_CONST :val 6 :location 109}))} [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'mpp5992'::regclass;
+ relid | level |
pg_get_expr
+---------+-------+---------------------------------------------------------------------------------------------------------------------------------
+ mpp5992 | 1 | SUBPARTITION TEMPLATE(SUBPARTITION l1 VALUES (1, 2, 3),
SUBPARTITION l2 VALUES (4, 5, 6), SUBPARTITION l3 VALUES (7, 8, 9, 10))
+ mpp5992 | 3 | SUBPARTITION TEMPLATE(SUBPARTITION lll1 VALUES ('M'),
SUBPARTITION lll2 VALUES ('F'))
+ mpp5992 | 2 | SUBPARTITION TEMPLATE(SUBPARTITION ll1 VALUES
('Engineering'), SUBPARTITION ll2 VALUES ('QA'))
(3 rows)
-- Now we can add a new partition
@@ -3847,12 +3847,12 @@ select relname, relam, pg_get_expr(relpartbound, oid)
from pg_class where relnam
mpp5992_1_prt_foo3_2_prt_l3_3_prt_ll2_4_prt_lll2 | 7024 | FOR VALUES IN ('F')
(112 rows)
-select relid::regclass, level, template from gp_partition_template where relid
= 'mpp5992'::regclass;
- relid | level |
template
[...]
----------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- mpp5992 | 3 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName lll1 :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "M"
:location 133}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName lll2 :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val "F" :location 165}))} :subSpec
<> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs
<>}) :encClauses <> :isTemplate true}
- mpp5992 | 2 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName ll1 :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val
"Engineering" :location 108}))} :subSpec <> :isDefault false :options <>
:accessMethod <> :tablespacename <> :colencs <>} {GPPARTDEFELEM :partName ll2
:boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "QA" :location
149}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>}) :encClauses <> :isTem [...]
- mpp5992 | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName l1 :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val 1
:location 72}) ({A_CONST :val 2 :location 74}) ({A_CONST :val 3 :location
76}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName l2 :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val 4 :location 105}) ({A_CONST
:val 5 :location 107}) ({A_CONST :val 6 :location 109}))} [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'mpp5992'::regclass;
+ relid | level |
pg_get_expr
+---------+-------+---------------------------------------------------------------------------------------------------------------------------------
+ mpp5992 | 1 | SUBPARTITION TEMPLATE(SUBPARTITION l1 VALUES (1, 2, 3),
SUBPARTITION l2 VALUES (4, 5, 6), SUBPARTITION l3 VALUES (7, 8, 9, 10))
+ mpp5992 | 3 | SUBPARTITION TEMPLATE(SUBPARTITION lll1 VALUES ('M'),
SUBPARTITION lll2 VALUES ('F'))
+ mpp5992 | 2 | SUBPARTITION TEMPLATE(SUBPARTITION ll1 VALUES
('Engineering'), SUBPARTITION ll2 VALUES ('QA'))
(3 rows)
-- MPP-10223: split subpartitions
@@ -4051,10 +4051,10 @@ subpartition template (start (1) end (10) every (1))
(start (20) end (30) every (1));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a'
as the Apache Cloudberry 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 relid::regclass, level, template from gp_partition_template where relid
= 'MPP10480'::regclass;
- relid | level |
template
-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- mpp10480 | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName <> :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM
:val ({A_CONST :val 1 :location 122}) :edge 1} :partEnd {GPPARTITIONRANGEITEM
:val ({A_CONST :val 10 :location 130}) :edge 2} :partEvery ({A_CONST :val 1
:location 141})} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'MPP10480'::regclass;
+ relid | level | pg_get_expr
+----------+-------+------------------------------------------------------
+ mpp10480 | 1 | SUBPARTITION TEMPLATE( START (1) END (10) Every (1))
(1 row)
-- MPP-10421: fix SPLIT of partitions with PRIMARY KEY constraint/indexes
diff --git a/src/test/regress/expected/partition_optimizer.out
b/src/test/regress/expected/partition_optimizer.out
index 8e738bb508b..c237e41bcad 100755
--- a/src/test/regress/expected/partition_optimizer.out
+++ b/src/test/regress/expected/partition_optimizer.out
@@ -2315,9 +2315,9 @@ DETAIL: Partition key of the failing row contains (i) =
(5).
drop table bar_p;
-- Drop should not leave anything lingering for bar_p or its
-- subpartitions in pg_partition* catalog tables.
-select relid, level, template from gp_partition_template where not exists
(select oid from pg_class where oid = relid);
- relid | level | template
--------+-------+----------
+select relid, level, pg_get_expr(template, relid) from gp_partition_template
where not exists (select oid from pg_class where oid = relid);
+ relid | level | pg_get_expr
+-------+-------+-------------
(0 rows)
-- MPP-4172
@@ -2438,28 +2438,28 @@ select relid::regclass, level from
gp_partition_template where relid = 'rank_set
alter table rank_settemp set subpartition template (default subpartition def2);
-- def2 is there
-select relid::regclass, level, template from gp_partition_template where relid
= 'rank_settemp'::regclass;
- relid | level |
template
---------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- rank_settemp | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName def2 :boundSpec <> :subSpec <> :isDefault true :options <>
:accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate
true}
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'rank_settemp'::regclass;
+ relid | level | pg_get_expr
+--------------+-------+--------------------------------------------------
+ rank_settemp | 1 | SUBPARTITION TEMPLATE(DEFAULT SUBPARTITION def2)
(1 row)
alter table rank_settemp set subpartition template (default subpartition def2);
-- Should still be there
-select relid::regclass, level, template from gp_partition_template where relid
= 'rank_settemp'::regclass;
- relid | level |
template
---------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- rank_settemp | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName def2 :boundSpec <> :subSpec <> :isDefault true :options <>
:accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate
true}
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'rank_settemp'::regclass;
+ relid | level | pg_get_expr
+--------------+-------+--------------------------------------------------
+ rank_settemp | 1 | SUBPARTITION TEMPLATE(DEFAULT SUBPARTITION def2)
(1 row)
alter table rank_settemp set subpartition template (start (date '2006-01-01')
with (appendonly=true));
alter table rank_settemp add partition f1 values ('N');
alter table rank_settemp set subpartition template (start (date '2007-01-01')
with (appendonly=true, compresslevel=5));
alter table rank_settemp add partition f2 values ('C');
-select relid::regclass, level, template from gp_partition_template where relid
= 'rank_settemp'::regclass;
- relid | level |
template
[...]
---------------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- rank_settemp | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName <> :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM
:val ({TYPECAST :arg {A_CONST :val "\2007-01-01" :location 64} :typeName
{TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <>
:typemod -1 :arrayBounds <> :location 59} :location -1}) :edge 1} :partEnd <>
:partEvery <>} :subSpec <> :isDefault false :options ({DEFELEM :defnamespace <>
:defname compresslevel :arg [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'rank_settemp'::regclass;
+ relid | level |
pg_get_expr
+--------------+-------+------------------------------------------------------------------------------------------------------------------
+ rank_settemp | 1 | SUBPARTITION TEMPLATE( START ('01-01-2007'::date),
WITH (appendonly=true, orientation=column, compresslevel=5))
(1 row)
drop table rank_settemp;
@@ -3594,12 +3594,12 @@ subpartition l2 values (6,7,8,9,10) );
alter table mpp5992
set subpartition template (subpartition l1 values (1,2,3),
subpartition l2 values (4,5,6), subpartition l3 values (7,8,9,10));
-select relid::regclass, level, template from gp_partition_template where relid
= 'mpp5992'::regclass;
- relid | level |
template
[...]
----------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- mpp5992 | 3 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName lll1 :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "M"
:location 133}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName lll2 :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val "F" :location 165}))} :subSpec
<> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs
<>}) :encClauses <> :isTemplate true}
- mpp5992 | 2 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName ll1 :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val
"Engineering" :location 108}))} :subSpec <> :isDefault false :options <>
:accessMethod <> :tablespacename <> :colencs <>} {GPPARTDEFELEM :partName ll2
:boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "QA" :location
149}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>}) :encClauses <> :isTem [...]
- mpp5992 | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName l1 :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val 1
:location 72}) ({A_CONST :val 2 :location 74}) ({A_CONST :val 3 :location
76}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName l2 :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val 4 :location 105}) ({A_CONST
:val 5 :location 107}) ({A_CONST :val 6 :location 109}))} [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'mpp5992'::regclass;
+ relid | level |
pg_get_expr
+---------+-------+---------------------------------------------------------------------------------------------------------------------------------
+ mpp5992 | 1 | SUBPARTITION TEMPLATE(SUBPARTITION l1 VALUES (1, 2, 3),
SUBPARTITION l2 VALUES (4, 5, 6), SUBPARTITION l3 VALUES (7, 8, 9, 10))
+ mpp5992 | 3 | SUBPARTITION TEMPLATE(SUBPARTITION lll1 VALUES ('M'),
SUBPARTITION lll2 VALUES ('F'))
+ mpp5992 | 2 | SUBPARTITION TEMPLATE(SUBPARTITION ll1 VALUES
('Engineering'), SUBPARTITION ll2 VALUES ('QA'))
(3 rows)
-- Now we can add a new partition
@@ -3848,12 +3848,12 @@ select relname, relam, pg_get_expr(relpartbound, oid)
from pg_class where relnam
mpp5992_1_prt_foo3_2_prt_l3_3_prt_ll2_4_prt_lll2 | 7024 | FOR VALUES IN ('F')
(112 rows)
-select relid::regclass, level, template from gp_partition_template where relid
= 'mpp5992'::regclass;
- relid | level |
template
[...]
----------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- mpp5992 | 3 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName lll1 :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "M"
:location 133}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName lll2 :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val "F" :location 165}))} :subSpec
<> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs
<>}) :encClauses <> :isTemplate true}
- mpp5992 | 2 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName ll1 :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val
"Engineering" :location 108}))} :subSpec <> :isDefault false :options <>
:accessMethod <> :tablespacename <> :colencs <>} {GPPARTDEFELEM :partName ll2
:boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "QA" :location
149}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>}) :encClauses <> :isTem [...]
- mpp5992 | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName l1 :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val 1
:location 72}) ({A_CONST :val 2 :location 74}) ({A_CONST :val 3 :location
76}))} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>} {GPPARTDEFELEM :partName l2 :boundSpec
{GPPARTITIONLISTSPEC :partValues (({A_CONST :val 4 :location 105}) ({A_CONST
:val 5 :location 107}) ({A_CONST :val 6 :location 109}))} [...]
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'mpp5992'::regclass;
+ relid | level |
pg_get_expr
+---------+-------+---------------------------------------------------------------------------------------------------------------------------------
+ mpp5992 | 1 | SUBPARTITION TEMPLATE(SUBPARTITION l1 VALUES (1, 2, 3),
SUBPARTITION l2 VALUES (4, 5, 6), SUBPARTITION l3 VALUES (7, 8, 9, 10))
+ mpp5992 | 3 | SUBPARTITION TEMPLATE(SUBPARTITION lll1 VALUES ('M'),
SUBPARTITION lll2 VALUES ('F'))
+ mpp5992 | 2 | SUBPARTITION TEMPLATE(SUBPARTITION ll1 VALUES
('Engineering'), SUBPARTITION ll2 VALUES ('QA'))
(3 rows)
-- MPP-10223: split subpartitions
@@ -4052,10 +4052,10 @@ subpartition template (start (1) end (10) every (1))
(start (20) end (30) every (1));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a'
as the Apache Cloudberry 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 relid::regclass, level, template from gp_partition_template where relid
= 'MPP10480'::regclass;
- relid | level |
template
-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- mpp10480 | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM
:partName <> :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM
:val ({A_CONST :val 1 :location 122}) :edge 1} :partEnd {GPPARTITIONRANGEITEM
:val ({A_CONST :val 10 :location 130}) :edge 2} :partEvery ({A_CONST :val 1
:location 141})} :subSpec <> :isDefault false :options <> :accessMethod <>
:tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'MPP10480'::regclass;
+ relid | level | pg_get_expr
+----------+-------+------------------------------------------------------
+ mpp10480 | 1 | SUBPARTITION TEMPLATE( START (1) END (10) Every (1))
(1 row)
-- MPP-10421: fix SPLIT of partitions with PRIMARY KEY constraint/indexes
diff --git a/src/test/regress/sql/AOCO_Compression.sql
b/src/test/regress/sql/AOCO_Compression.sql
index fe7ac125103..9bc25350de0 100644
--- a/src/test/regress/sql/AOCO_Compression.sql
+++ b/src/test/regress/sql/AOCO_Compression.sql
@@ -381,7 +381,7 @@ CREATE TABLE co_cr_sub_partzlib8192_1
COLUMN a1 encoding (compresstype = zlib),
COLUMN a5 ENCODING (compresstype=zlib,compresslevel=1, blocksize=8192),
DEFAULT COLUMN ENCODING (compresstype=zlib,compresslevel=1,blocksize=8192))
(start(1) end(5000) every(1000));
-
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'co_cr_sub_partzlib8192_1'::regclass;
--
-- Create Indexes
--
@@ -400,6 +400,7 @@ ANALYZE co_cr_sub_partzlib8192_1;
--Create Uncompressed table of same schema definition
CREATE TABLE co_cr_sub_partzlib8192_1_uncompr(id SERIAL,a1 int,a2 char(5),a3
numeric,a4 boolean DEFAULT false ,a5 char DEFAULT 'd',a6 text,a7 timestamp,a8
character varying(705),a9 bigint,a10 date,a11 varchar(600),a12 text,a13
decimal,a14 real,a15 bigint,a16 int4 ,a17 bytea,a18 timestamp with time
zone,a19 timetz,a20 path,a21 box,a22 macaddr,a23 interval,a24 character
varying(800),a25 lseg,a26 point,a27 double precision,a28 circle,a29 int4,a30
numeric(8),a31 polygon,a32 date,a33 real,a34 [...]
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'co_cr_sub_partzlib8192_1_uncompr'::regclass;
--
-- Insert to uncompressed table
@@ -531,6 +532,7 @@ CREATE TABLE co_cr_sub_partzlib8192_1_2
COLUMN a1 encoding (compresstype = zlib),
COLUMN a5 ENCODING (compresstype=zlib,compresslevel=1, blocksize=8192),
DEFAULT COLUMN ENCODING (compresstype=zlib,compresslevel=1,blocksize=8192))
(partition p1 values('F'), partition p2 values ('M'));
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'co_cr_sub_partzlib8192_1_2'::regclass;
--
-- Create Indexes
@@ -681,6 +683,7 @@ CREATE TABLE co_wt_sub_partrle_type8192_1
(id SERIAL,a1 int,a2 char(5),a3 numeric,a4 boolean DEFAULT false ,a5
char DEFAULT 'd',a6 text,a7 timestamp,a8 character varying(705),a9 bigint,a10
date,a11 varchar(600),a12 text,a13 decimal,a14 real,a15 bigint,a16 int4 ,a17
bytea,a18 timestamp with time zone,a19 timetz,a20 path,a21 box,a22 macaddr,a23
interval,a24 character varying(800),a25 lseg,a26 point,a27 double precision,a28
circle,a29 int4,a30 numeric(8),a31 polygon,a32 date,a33 real,a34 money,a35
cidr,a36 inet,a37 time,a38 text,a [...]
WITH (appendonly=true, orientation=column) distributed randomly Partition by
range(a1) Subpartition by list(a2) subpartition template ( default subpartition
df_sp, subpartition sp1 values('M') , subpartition sp2 values('F')
WITH (appendonly=true,
orientation=column,compresstype=rle_type,compresslevel=1,blocksize=8192))
(start(1) end(5000) every(1000) );
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'co_wt_sub_partrle_type8192_1'::regclass;
--
-- Create Indexes
@@ -702,6 +705,7 @@ ANALYZE co_wt_sub_partrle_type8192_1;
--Create Uncompressed table of same schema definition
CREATE TABLE co_wt_sub_partrle_type8192_1_uncompr(id SERIAL,a1 int,a2
char(5),a3 numeric,a4 boolean DEFAULT false ,a5 char DEFAULT 'd',a6 text,a7
timestamp,a8 character varying(705),a9 bigint,a10 date,a11 varchar(600),a12
text,a13 decimal,a14 real,a15 bigint,a16 int4 ,a17 bytea,a18 timestamp with
time zone,a19 timetz,a20 path,a21 box,a22 macaddr,a23 interval,a24 character
varying(800),a25 lseg,a26 point,a27 double precision,a28 circle,a29 int4,a30
numeric(8),a31 polygon,a32 date,a33 real [...]
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'co_wt_sub_partrle_type8192_1_uncompr'::regclass;
--
-- Insert to uncompressed table
@@ -829,6 +833,7 @@ CREATE TABLE co_wt_sub_partrle_type8192_1_2
(id SERIAL,a1 int,a2 char(5),a3 numeric,a4 boolean DEFAULT false ,a5
char DEFAULT 'd',a6 text,a7 timestamp,a8 character varying(705),a9 bigint,a10
date,a11 varchar(600),a12 text,a13 decimal,a14 real,a15 bigint,a16 int4 ,a17
bytea,a18 timestamp with time zone,a19 timetz,a20 path,a21 box,a22 macaddr,a23
interval,a24 character varying(800),a25 lseg,a26 point,a27 double precision,a28
circle,a29 int4,a30 numeric(8),a31 polygon,a32 date,a33 real,a34 money,a35
cidr,a36 inet,a37 time,a38 text,a [...]
WITH (appendonly=true, orientation=column) distributed randomly Partition by
list(a2) Subpartition by range(a1) subpartition template (default subpartition
df_sp, start(1) end(5000) every(1000)
WITH (appendonly=true,
orientation=column,compresstype=rle_type,compresslevel=1,blocksize=8192))
(partition p1 values ('M'), partition p2 values ('F'));
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'co_wt_sub_partrle_type8192_1_2'::regclass;
--
-- Create Indexes
@@ -981,6 +986,7 @@ CREATE TABLE ao_wt_sub_partzlib8192_5
(id SERIAL,a1 int,a2 char(5),a3 numeric,a4 boolean DEFAULT false ,a5
char DEFAULT 'd',a6 text,a7 timestamp,a8 character varying(705),a9 bigint,a10
date,a11 varchar(600),a12 text,a13 decimal,a14 real,a15 bigint,a16 int4 ,a17
bytea,a18 timestamp with time zone,a19 timetz,a20 path,a21 box,a22 macaddr,a23
interval,a24 character varying(800),a25 lseg,a26 point,a27 double precision,a28
circle,a29 int4,a30 numeric(8),a31 polygon,a32 date,a33 real,a34 money,a35
cidr,a36 inet,a37 time,a38 text,a [...]
WITH (appendonly=true, orientation=row) distributed randomly Partition by
range(a1) Subpartition by list(a2) subpartition template ( default subpartition
df_sp, subpartition sp1 values('M') , subpartition sp2 values('F')
WITH (appendonly=true,
orientation=row,compresstype=zlib,compresslevel=5,blocksize=8192)) (start(1)
end(5000) every(1000) );
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'ao_wt_sub_partzlib8192_5'::regclass;
--
-- Create Indexes
@@ -1128,6 +1134,7 @@ CREATE TABLE ao_wt_sub_partzlib8192_5_2
(id SERIAL,a1 int,a2 char(5),a3 numeric,a4 boolean DEFAULT false ,a5
char DEFAULT 'd',a6 text,a7 timestamp,a8 character varying(705),a9 bigint,a10
date,a11 varchar(600),a12 text,a13 decimal,a14 real,a15 bigint,a16 int4 ,a17
bytea,a18 timestamp with time zone,a19 timetz,a20 path,a21 box,a22 macaddr,a23
interval,a24 character varying(800),a25 lseg,a26 point,a27 double precision,a28
circle,a29 int4,a30 numeric(8),a31 polygon,a32 date,a33 real,a34 money,a35
cidr,a36 inet,a37 time,a38 text,a [...]
WITH (appendonly=true, orientation=row) distributed randomly Partition by
list(a2) Subpartition by range(a1) subpartition template (default subpartition
df_sp, start(1) end(5000) every(1000)
WITH (appendonly=true,
orientation=row,compresstype=zlib,compresslevel=5,blocksize=8192)) (partition
p1 values ('M'), partition p2 values ('F'));
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'ao_wt_sub_partzlib8192_5_2'::regclass;
--
-- Create Indexes
diff --git a/src/test/regress/sql/bfv_partition.sql
b/src/test/regress/sql/bfv_partition.sql
index bf705af1285..cc2b8989f43 100644
--- a/src/test/regress/sql/bfv_partition.sql
+++ b/src/test/regress/sql/bfv_partition.sql
@@ -1207,13 +1207,13 @@ ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (|);
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (~);
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (`);
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
set client_min_messages='warning';
drop schema qa147 cascade;
reset client_min_messages;
-select relid, level, template from gp_partition_template where not exists
(select oid from pg_class where oid = relid);
+select relid, level, pg_get_expr(template, relid) from gp_partition_template
where not exists (select oid from pg_class where oid = relid);
-- Mix-Match for Alter subpartition template
CREATE TABLE qa147sales (trans_id int, date date, amount
@@ -1231,29 +1231,29 @@ SUBPARTITION TEMPLATE
-- Clear TEMPLATE
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE ();
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
-- This will overwrite previous subpartition template
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia') );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
-- Invalid subpartition
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date
'2008-02-01') EXCLUSIVE );
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date
'2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usa1 VALUES('usa'),
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date
'2009-01-01') EXCLUSIVE);
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date
'2009-01-01') EXCLUSIVE,
SUBPARTITION usa1 VALUES('usa'));
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
drop table qa147sales;
@@ -1273,33 +1273,33 @@ SUBPARTITION TEMPLATE
-- Clear TEMPLATE
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE ();
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
-- This will overwrite previous subpartition template
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date
'2008-02-01') EXCLUSIVE );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date
'2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
-- Invalid subpartition template
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia') );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usa1 VALUES('usa'),
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date
'2009-01-01') EXCLUSIVE);
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date
'2009-01-01') EXCLUSIVE,
SUBPARTITION usa1 VALUES('usa'));
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147sales'::regclass;
drop table qa147sales;
-select relid, level, template from gp_partition_template where not exists
(select oid from pg_class where oid = relid);
+select relid, level, pg_get_expr(template, relid) from gp_partition_template
where not exists (select oid from pg_class where oid = relid);
-- Now with Schema
-- Mix-Match for Alter subpartition template in a schema
@@ -1319,32 +1319,32 @@ SUBPARTITION TEMPLATE
-- Clear TEMPLATE
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE ();
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
-- This will overwrite previous subpartition template
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia') );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
-- Invalid subpartition
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date
'2008-02-01') EXCLUSIVE );
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date
'2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usa1 VALUES('usa'),
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date
'2009-01-01') EXCLUSIVE);
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date
'2009-01-01') EXCLUSIVE,
SUBPARTITION usa1 VALUES('usa'));
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
DROP SCHEMA qa147 cascade;
-select relid, level, template from gp_partition_template where not exists
(select oid from pg_class where oid = relid);
+select relid, level, pg_get_expr(template, relid) from gp_partition_template
where not exists (select oid from pg_class where oid = relid);
CREATE SCHEMA qa147;
CREATE TABLE qa147.sales (trans_id int, date date, amount
@@ -1363,33 +1363,33 @@ SUBPARTITION TEMPLATE
-- Clear TEMPLATE
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE ();
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
-- This will overwrite previous subpartition template
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date
'2008-02-01') EXCLUSIVE );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date
'2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
-- Invalid subpartition template
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia') );
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usa1 VALUES('usa'),
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date
'2009-01-01') EXCLUSIVE);
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date
'2009-01-01') EXCLUSIVE,
SUBPARTITION usa1 VALUES('usa'));
-select relid::regclass, level, template from gp_partition_template where relid
= 'qa147.sales'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'qa147.sales'::regclass;
drop schema qa147 cascade;
-select relid, level, template from gp_partition_template where not exists
(select oid from pg_class where oid = relid);
+select relid, level, pg_get_expr(template, relid) from gp_partition_template
where not exists (select oid from pg_class where oid = relid);
set gp_autostats_mode=on_change;
set gp_autostats_on_change_threshold=100;
@@ -1474,7 +1474,7 @@ alter table partsupp_def set subpartition template(
subpartition aaa start(400)
-- Note 2: We do not support this function yet, but if we are able to split
default partition with default subpartition, would we
-- be using the subpartition template to definte the "new" partition or the
existing one.
-select relid::regclass, level, template from gp_partition_template where relid
= 'partsupp_def'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'partsupp_def'::regclass;
alter table partsup_def add partition f1 start(0) end (300) every(100);
@@ -1494,7 +1494,7 @@ subpartition template
alter table partsupp_def2 set subpartition template();
alter table partsupp_def2 set subpartition template( subpartition aaa
start(400) end (600) every(100) );
-select relid::regclass, level, template from gp_partition_template where relid
= 'partsupp_def2'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'partsupp_def2'::regclass;
drop table partsupp_def;
drop table partsupp_def2;
@@ -1618,7 +1618,7 @@ drop table partition_cleanup1;
drop schema partition_999 cascade;
-- These should be empty
-select relid, level, template from gp_partition_template where not exists
(select oid from pg_class where oid = relid);
+select relid, level, pg_get_expr(template, relid) from gp_partition_template
where not exists (select oid from pg_class where oid = relid);
--
diff --git a/src/test/regress/sql/column_compression.sql
b/src/test/regress/sql/column_compression.sql
index 5849a935d3a..4f70667fedd 100644
--- a/src/test/regress/sql/column_compression.sql
+++ b/src/test/regress/sql/column_compression.sql
@@ -287,6 +287,7 @@ partition by range(j)
);
execute ccddlcheck;
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'ccddl'::regclass;
insert into ccddl select 1, (i % 19) + 1, ((i+3) % 5) + 1, i+3 from
generate_series(1, 100) i;
@@ -367,6 +368,7 @@ subpartition template (subpartition sp1 start(1) end(20),
(partition p1 start(1) end(20));
execute ccddlcheck;
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'ccddl'::regclass;
alter table ccddl alter partition p1 split partition sp1 at (10) into
(partition sp2, partition sp3);
execute ccddlcheck;
@@ -400,6 +402,7 @@ CREATE TABLE ccddl (id int, year int, month int, day int,
region text)
)
( START (2008) END (2010) );
execute ccddlcheck;
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'ccddl'::regclass;
-- Ensure we can read and write
insert into ccddl select 1, 2008, 1, 2, 'usa' from generate_series(1, 100);
@@ -512,6 +515,8 @@ alter table ccddl add partition newp
with (appendonly=true, orientation=column);
execute ccddlcheck;
+SELECT level, pg_get_expr(template, relid) from gp_partition_template t WHERE
t.relid = 'ccddl'::regclass;
+
drop table ccddl;
-----------------------------------------------------------------------
@@ -527,8 +532,9 @@ create table gg (i int, k int) with (appendonly=true,
orientation=column)
partition by range(k) (partition p1 start(1) end(2), column i
encoding(compresstype=sdf2sdf));
--- We don't support partition element specific encoding clauses in subpartition
--- templates as we have no place to store them.
+-- Historically we don't support partition element specific encoding clauses in
+-- subpartition templates as we didn't have place to store them. We now have
+-- place to store them if we want to, but for now we keep this door closed
create table a (i int, j int) with (appendonly=true, orientation=column)
partition by range(i) subpartition by range(j)
subpartition template(start(1) end(10) default column encoding
(compresstype=zlib),
diff --git a/src/test/regress/sql/partition.sql
b/src/test/regress/sql/partition.sql
index f3d9c8f0acf..445b1cda74b 100644
--- a/src/test/regress/sql/partition.sql
+++ b/src/test/regress/sql/partition.sql
@@ -1549,7 +1549,7 @@ insert into bar_p values(5, 5);
drop table bar_p;
-- Drop should not leave anything lingering for bar_p or its
-- subpartitions in pg_partition* catalog tables.
-select relid, level, template from gp_partition_template where not exists
(select oid from pg_class where oid = relid);
+select relid, level, pg_get_expr(template, relid) from gp_partition_template
where not exists (select oid from pg_class where oid = relid);
-- MPP-4172
-- should fail
@@ -1664,11 +1664,11 @@ select relid::regclass, level from
gp_partition_template where relid = 'rank_set
alter table rank_settemp set subpartition template (default subpartition def2);
-- def2 is there
-select relid::regclass, level, template from gp_partition_template where relid
= 'rank_settemp'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'rank_settemp'::regclass;
alter table rank_settemp set subpartition template (default subpartition def2);
-- Should still be there
-select relid::regclass, level, template from gp_partition_template where relid
= 'rank_settemp'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'rank_settemp'::regclass;
alter table rank_settemp set subpartition template (start (date '2006-01-01')
with (appendonly=true));
@@ -1676,7 +1676,7 @@ alter table rank_settemp add partition f1 values ('N');
alter table rank_settemp set subpartition template (start (date '2007-01-01')
with (appendonly=true, compresslevel=5));
alter table rank_settemp add partition f2 values ('C');
-select relid::regclass, level, template from gp_partition_template where relid
= 'rank_settemp'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'rank_settemp'::regclass;
drop table rank_settemp;
@@ -2381,7 +2381,7 @@ subpartition l2 values (6,7,8,9,10) );
alter table mpp5992
set subpartition template (subpartition l1 values (1,2,3),
subpartition l2 values (4,5,6), subpartition l3 values (7,8,9,10));
-select relid::regclass, level, template from gp_partition_template where relid
= 'mpp5992'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'mpp5992'::regclass;
-- Now we can add a new partition
alter table mpp5992
@@ -2400,7 +2400,7 @@ start (date '2013-01-01') end (date '2014-01-01') WITH
(appendonly=true);
select * from pg_partition_tree('mpp5992');
select relname, relam, pg_get_expr(relpartbound, oid) from pg_class where
relname like 'mpp5992%';
-select relid::regclass, level, template from gp_partition_template where relid
= 'mpp5992'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'mpp5992'::regclass;
-- MPP-10223: split subpartitions
CREATE TABLE MPP10223pk
@@ -2544,7 +2544,7 @@ subpartition by range(d)
subpartition template (start (1) end (10) every (1))
(start (20) end (30) every (1));
-select relid::regclass, level, template from gp_partition_template where relid
= 'MPP10480'::regclass;
+select relid::regclass, level, pg_get_expr(template, relid) from
gp_partition_template where relid = 'MPP10480'::regclass;
-- MPP-10421: fix SPLIT of partitions with PRIMARY KEY constraint/indexes
CREATE TABLE mpp10321a
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@cloudberry.apache.org
For additional commands, e-mail: commits-h...@cloudberry.apache.org
