You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by av...@apache.org on 2025/01/26 01:55:17 UTC
(cloudberry) branch main updated (f3752542bc -> ccac620b00)
This is an automated email from the ASF dual-hosted git repository.
avamingli pushed a change to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
from f3752542bc Fix cherry-pick issues.
new 76a08f8fca Remove a fixme of plpgsql tests
new dc73f6ece0 Consider MPP FDW LIMIT pushdown when both offset and limit clause are specified (#16919)
new a368828c5a Update comment
new 96ff7a7644 Fix relptr's encoding of the base address. (#17255)
new 24bc2526c6 Add a test case about special location URI for CREATE FOREIGN TABLE syntax (#17252)
new 6cc9a78a82 Fix pipeline diff caused by different length of ----- (#17263)
new dde8910fc1 Fix fallback in debug build due to scalar with invalid return type
new 120758cf1b Add missing volatile qualifier. (#17273)
new e3bbfda9a8 Default to log_checkpoints=on, log_autovacuum_min_duration=10m
new ccac620b00 Add GUC gp_enable_statement_trigger
The 10 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
Summary of changes:
contrib/postgres_fdw/deparse.c | 56 +-
.../expected/mpp_gp2pg_postgres_fdw.out | 1051 ++++++++++++++++++++
contrib/postgres_fdw/postgres_clean.bash | 9 +
contrib/postgres_fdw/postgres_fdw.c | 23 +
.../postgres_fdw/sql/mpp_gp2pg_postgres_fdw.sql | 248 +++++
"gpcontrib/gp_exttable_fdw/data/spe\\cial1|" | 2 +
"gpcontrib/gp_exttable_fdw/data/|special2\\" | 1 +
.../gp_exttable_fdw/input/gp_exttable_fdw.source | 12 +
.../gp_exttable_fdw/output/gp_exttable_fdw.source | 25 +
src/backend/access/transam/xlog.c | 2 +-
src/backend/executor/execMain.c | 2 +-
.../gporca/libgpopt/src/xforms/CXformUtils.cpp | 1 +
src/backend/parser/gram.y | 16 +-
src/backend/utils/misc/guc.c | 2 +-
src/backend/utils/misc/guc_gp.c | 13 +
src/backend/utils/misc/postgresql.conf.sample | 2 +-
src/backend/utils/mmgr/freepage.c | 6 +-
src/bin/initdb/initdb.c | 4 +-
.../pg_upgrade/greenplum/pg_upgrade_greenplum.h | 2 +-
src/include/utils/freepage.h | 4 +-
src/include/utils/guc.h | 1 +
src/include/utils/relptr.h | 15 +-
src/include/utils/unsync_guc_name.h | 1 +
src/test/regress/expected/plpgsql.out | 11 +-
src/test/regress/expected/plpgsql_optimizer.out | 11 +-
src/test/regress/expected/triggers_gp.out | 22 +
src/test/regress/sql/plpgsql.sql | 11 +-
src/test/regress/sql/triggers_gp.sql | 23 +
28 files changed, 1525 insertions(+), 51 deletions(-)
create mode 100644 contrib/postgres_fdw/expected/mpp_gp2pg_postgres_fdw.out
create mode 100755 contrib/postgres_fdw/postgres_clean.bash
create mode 100644 contrib/postgres_fdw/sql/mpp_gp2pg_postgres_fdw.sql
create mode 100644 "gpcontrib/gp_exttable_fdw/data/spe\\cial1|"
create mode 100644 "gpcontrib/gp_exttable_fdw/data/|special2\\"
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
(cloudberry) 07/10: Fix fallback in debug build due to scalar with invalid return type
Posted by av...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
avamingli pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit dde8910fc107e7078f72366d3e7521122de0b32a
Author: Chris Hajas <ch...@vmware.com>
AuthorDate: Thu Feb 1 17:15:41 2024 -0800
Fix fallback in debug build due to scalar with invalid return type
This is exposed by Orca's support for query parameters. In retail build
it would still run fine, but it's better to make this explicit.
---
src/backend/gporca/libgpopt/src/xforms/CXformUtils.cpp | 1 +
1 file changed, 1 insertion(+)
diff --git a/src/backend/gporca/libgpopt/src/xforms/CXformUtils.cpp b/src/backend/gporca/libgpopt/src/xforms/CXformUtils.cpp
index 299a579df5..5a47a7c13a 100644
--- a/src/backend/gporca/libgpopt/src/xforms/CXformUtils.cpp
+++ b/src/backend/gporca/libgpopt/src/xforms/CXformUtils.cpp
@@ -3263,6 +3263,7 @@ CXformUtils::FHasAmbiguousType(CExpression *pexpr, CMDAccessor *md_accessor)
case COperator::EopScalarProjectList:
case COperator::EopScalarProjectElement:
case COperator::EopScalarSwitchCase:
+ case COperator::EopScalarArrayRefIndexList:
break; // these operators do not have valid return type
default:
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
(cloudberry) 10/10: Add GUC gp_enable_statement_trigger
Posted by av...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
avamingli pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit ccac620b0018fe4b9548378574c0c9156cf9185f
Author: Marbin Tan <tm...@vmware.com>
AuthorDate: Mon Mar 18 14:24:56 2024 -0700
Add GUC gp_enable_statement_trigger
Even though we claim that triggers are not supported in Greenplum, users
were still allowed to create them in GP6. In GP7, we've disabled
creating triggers, as such, restoring from GP6 that has triggers will
cause issues. Add a new GUC `gp_enable_statement_trigger`
to let `pg_restore` and users by pass this issue and create the trigger
anyway when the GUC `gp_enable_statement_trigger` is on.
The default value of `gp_enable_statement_trigger` is off.
Creating Triggers in GP7 were disabled
in commit https://github.com/greenplum-db/gpdb/commit/2325cffa86fa663567693cf54d5a98e7d5ed665f.
---
src/backend/parser/gram.y | 16 +++++++++++++---
src/backend/utils/misc/guc_gp.c | 13 +++++++++++++
src/include/utils/guc.h | 1 +
src/include/utils/unsync_guc_name.h | 1 +
src/test/regress/expected/triggers_gp.out | 22 ++++++++++++++++++++++
src/test/regress/sql/triggers_gp.sql | 23 +++++++++++++++++++++++
6 files changed, 73 insertions(+), 3 deletions(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index f300cdb828..fdbe8f7f9e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -8966,9 +8966,14 @@ TriggerForSpec:
}
| /* EMPTY */
{
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("Triggers for statements are not yet supported")));
+ /* let creation of triggers go through for pg_restore when upgrading from GP6 to GP7 */
+ if (!gp_enable_statement_trigger)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Triggers for statements are not yet supported")));
+ }
+ $$ = false;
}
;
@@ -8981,9 +8986,14 @@ TriggerForType:
ROW { $$ = true; }
| STATEMENT
{
+ /* let creation of triggers go through for pg_restore when upgrading from GP6 to GP7 */
+ if (!gp_enable_statement_trigger)
+ {
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("Triggers for statements are not yet supported")));
+ }
+ $$ = false;
}
;
diff --git a/src/backend/utils/misc/guc_gp.c b/src/backend/utils/misc/guc_gp.c
index 34a84582b6..1995d91e75 100644
--- a/src/backend/utils/misc/guc_gp.c
+++ b/src/backend/utils/misc/guc_gp.c
@@ -249,6 +249,7 @@ int gp_max_partition_level;
bool gp_maintenance_mode;
bool gp_maintenance_conn;
bool allow_segment_DML;
+bool gp_enable_statement_trigger;
/* Time based authentication GUC */
char *gp_auth_time_override_str = NULL;
@@ -621,6 +622,18 @@ struct config_bool ConfigureNamesBool_gp[] =
false,
NULL, NULL, NULL
},
+
+ {
+ {"gp_enable_statement_trigger", PGC_USERSET, CUSTOM_OPTIONS,
+ gettext_noop("Enables statement triggers to be created instead of erroring out."),
+ NULL,
+ GUC_NOT_IN_SAMPLE
+ },
+ &gp_enable_statement_trigger,
+ false,
+ NULL, NULL, NULL
+ },
+
{
{"enable_groupagg", PGC_USERSET, QUERY_TUNING_METHOD,
gettext_noop("Enables the planner's use of grouping aggregation plans."),
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index b79498207c..f9045dc8a2 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -343,6 +343,7 @@ extern int rep_lag_avoidance_threshold;
extern bool gp_maintenance_mode;
extern bool gp_maintenance_conn;
extern bool allow_segment_DML;
+extern bool gp_enable_statement_trigger;
extern bool gp_ignore_error_table;
diff --git a/src/include/utils/unsync_guc_name.h b/src/include/utils/unsync_guc_name.h
index 4c7226e33c..f6c3f11c48 100644
--- a/src/include/utils/unsync_guc_name.h
+++ b/src/include/utils/unsync_guc_name.h
@@ -201,6 +201,7 @@
"gp_enable_relsize_collection",
"gp_enable_slow_writer_testmode",
"gp_enable_sort_limit",
+ "gp_enable_statement_trigger",
"gp_encoding_check_locale_compatibility",
"gp_external_enable_exec",
"gp_external_max_segs",
diff --git a/src/test/regress/expected/triggers_gp.out b/src/test/regress/expected/triggers_gp.out
index 493fe305f4..3191971aee 100644
--- a/src/test/regress/expected/triggers_gp.out
+++ b/src/test/regress/expected/triggers_gp.out
@@ -12,6 +12,11 @@
-- This file aims to cover the things that behave sanely, even though we don't
-- officially support anything to do with triggers.
--
+-- Even though we claim that triggers are not supported in Greenplum, users
+-- were still allowed to create them. As such, restoring from GP6 that has
+-- triggers will cause issues; we now have a new GUC gp_enable_statement_trigger
+-- to let pg_restore by pass this issue and create the trigger anyway.
+--
create or replace function insert_notice_trig() returns trigger as $$
begin
raise notice 'insert trigger fired on % for %', TG_TABLE_NAME, TG_OP;
@@ -102,3 +107,20 @@ ERROR: UPDATE on distributed key column not allowed on relation with update tri
-- Should fire the DELETE trigger.
delete from parted_trig where nonkey = 2;
NOTICE: delete trigger fired on parted_trig2 for DELETE (seg0 127.0.0.1:40000 pid=10559)
+--
+-- Add GUC test to enable statement trigger
+-- default GUC value is off
+--
+SET gp_enable_statement_trigger = on;
+CREATE TABLE main_table_gp (a int, b int);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
+CREATE FUNCTION trigger_func_gp() RETURNS trigger LANGUAGE plpgsql AS '
+BEGIN
+ RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
+ RETURN NULL;
+END;';
+-- We do not drop the trigger since this is used as part of the dump and restore testing of ICW
+CREATE TRIGGER before_ins_stmt_trig_gp BEFORE INSERT ON main_table_gp
+FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func_gp('before_ins_stmt');
+SET gp_enable_statement_trigger = off;
diff --git a/src/test/regress/sql/triggers_gp.sql b/src/test/regress/sql/triggers_gp.sql
index 46d3087239..72c50858ca 100644
--- a/src/test/regress/sql/triggers_gp.sql
+++ b/src/test/regress/sql/triggers_gp.sql
@@ -12,6 +12,11 @@
-- This file aims to cover the things that behave sanely, even though we don't
-- officially support anything to do with triggers.
--
+-- Even though we claim that triggers are not supported in Greenplum, users
+-- were still allowed to create them. As such, restoring from GP6 that has
+-- triggers will cause issues; we now have a new GUC gp_enable_statement_trigger
+-- to let pg_restore by pass this issue and create the trigger anyway.
+--
create or replace function insert_notice_trig() returns trigger as $$
begin
@@ -104,3 +109,21 @@ update parted_trig set partkey = partkey + 1, distkey = distkey + 1;
-- Should fire the DELETE trigger.
delete from parted_trig where nonkey = 2;
+
+--
+-- Add GUC test to enable statement trigger
+-- default GUC value is off
+--
+SET gp_enable_statement_trigger = on;
+
+CREATE TABLE main_table_gp (a int, b int);
+CREATE FUNCTION trigger_func_gp() RETURNS trigger LANGUAGE plpgsql AS '
+BEGIN
+ RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
+ RETURN NULL;
+END;';
+-- We do not drop the trigger since this is used as part of the dump and restore testing of ICW
+CREATE TRIGGER before_ins_stmt_trig_gp BEFORE INSERT ON main_table_gp
+FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func_gp('before_ins_stmt');
+
+SET gp_enable_statement_trigger = off;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
(cloudberry) 03/10: Update comment
Posted by av...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
avamingli pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit a368828c5ae03679f120b8c81ee2bf7e434d3323
Author: Marbin Tan <tm...@vmware.com>
AuthorDate: Mon Mar 25 14:14:17 2024 -0700
Update comment
`pg_upgrade_greenplum.c` no longer exists and has been renamed to
`controldata_gp.c`.
---
src/bin/pg_upgrade/greenplum/pg_upgrade_greenplum.h | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/src/bin/pg_upgrade/greenplum/pg_upgrade_greenplum.h b/src/bin/pg_upgrade/greenplum/pg_upgrade_greenplum.h
index 463a159ec9..7aeb7d8a7a 100644
--- a/src/bin/pg_upgrade/greenplum/pg_upgrade_greenplum.h
+++ b/src/bin/pg_upgrade/greenplum/pg_upgrade_greenplum.h
@@ -65,7 +65,7 @@ bool is_greenplum_dispatcher_mode(void);
bool is_checksum_mode(checksumMode mode);
bool is_show_progress_mode(void);
-/* pg_upgrade_greenplum.c */
+/* controldata_gp.c */
void freeze_master_data(void);
void reset_system_identifier(void);
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
(cloudberry) 05/10: Add a test case about special location URI for CREATE FOREIGN TABLE syntax (#17252)
Posted by av...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
avamingli pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 24bc2526c6d12c350e844db6886e4784bf8fb5d5
Author: Jingwen Yang <ji...@outlook.com>
AuthorDate: Wed Mar 27 15:49:13 2024 +0800
Add a test case about special location URI for CREATE FOREIGN TABLE syntax (#17252)
Add a test case that external table location URI contains special character '|' for
CREATE FOREIGN TABLE syntax.
---
"gpcontrib/gp_exttable_fdw/data/spe\\cial1|" | 2 ++
"gpcontrib/gp_exttable_fdw/data/|special2\\" | 1 +
.../gp_exttable_fdw/input/gp_exttable_fdw.source | 10 ++++++++
.../gp_exttable_fdw/output/gp_exttable_fdw.source | 27 ++++++++++++++++++++++
4 files changed, 40 insertions(+)
diff --git "a/gpcontrib/gp_exttable_fdw/data/spe\\cial1|" "b/gpcontrib/gp_exttable_fdw/data/spe\\cial1|"
new file mode 100644
index 0000000000..3bf58f2519
--- /dev/null
+++ "b/gpcontrib/gp_exttable_fdw/data/spe\\cial1|"
@@ -0,0 +1,2 @@
+1,1
+2,2
diff --git "a/gpcontrib/gp_exttable_fdw/data/|special2\\" "b/gpcontrib/gp_exttable_fdw/data/|special2\\"
new file mode 100644
index 0000000000..3176ff229c
--- /dev/null
+++ "b/gpcontrib/gp_exttable_fdw/data/|special2\\"
@@ -0,0 +1 @@
+3,3
diff --git a/gpcontrib/gp_exttable_fdw/input/gp_exttable_fdw.source b/gpcontrib/gp_exttable_fdw/input/gp_exttable_fdw.source
index a3704704a7..d2ffe47e4f 100644
--- a/gpcontrib/gp_exttable_fdw/input/gp_exttable_fdw.source
+++ b/gpcontrib/gp_exttable_fdw/input/gp_exttable_fdw.source
@@ -67,3 +67,13 @@ OPTIONS (format_type 'c', delimiter ',',
SELECT * FROM tableless_ext_fdw;
+-- OK, location URI contains special characters '|' and '\'
+-- Single character '|' is used as delimiter for multiple location URIs.
+-- When using CREATE FOREIGN TABLE syntax, '|' and '\' need to be escaped as '|' --> '\|' and '|' --> '\\'.
+CREATE FOREIGN TABLE ext_special_uri(a int, b int)
+SERVER gp_exttable_server
+OPTIONS (format 'csv', delimiter ',',
+ location_uris 'file://@hostname@@abs_srcdir@/data/spe\\cial1\||file://@hostname@@abs_srcdir@/data/\|special2\\');
+SELECT urilocation FROM pg_exttable WHERE reloid = 'public.ext_special_uri'::regclass;
+SELECT ftoptions FROM pg_foreign_table WHERE ftrelid='public.ext_special_uri'::regclass;
+SELECT * FROM ext_special_uri ORDER BY a;
diff --git a/gpcontrib/gp_exttable_fdw/output/gp_exttable_fdw.source b/gpcontrib/gp_exttable_fdw/output/gp_exttable_fdw.source
index a5952d197e..4efc7ef29e 100644
--- a/gpcontrib/gp_exttable_fdw/output/gp_exttable_fdw.source
+++ b/gpcontrib/gp_exttable_fdw/output/gp_exttable_fdw.source
@@ -71,3 +71,30 @@ SELECT * FROM tableless_ext_fdw;
3 | 32
(3 rows)
+-- OK, location URI contains special characters '|' and '\'
+-- Single character '|' is used as delimiter for multiple location URIs.
+-- When using CREATE FOREIGN TABLE syntax, '|' and '\' need to be escaped as '|' --> '\|' and '|' --> '\\'.
+CREATE FOREIGN TABLE ext_special_uri(a int, b int)
+SERVER gp_exttable_server
+OPTIONS (format 'csv', delimiter ',',
+ location_uris 'file://@hostname@@abs_srcdir@/data/spe\\cial1\||file://@hostname@@abs_srcdir@/data/\|special2\\');
+SELECT urilocation FROM pg_exttable WHERE reloid = 'public.ext_special_uri'::regclass;
+ urilocation
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {"file://@hostname@@abs_srcdir@/data/spe\\cial1|","file://@hostname@@abs_srcdir@/data/|special2\\"}
+(1 row)
+
+SELECT ftoptions FROM pg_foreign_table WHERE ftrelid='public.ext_special_uri'::regclass;
+ ftoptions
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {format=csv,"delimiter=,","location_uris=file://@hostname@@abs_srcdir@/data/spe\\\\cial1\\||file://@hostname@@abs_srcdir@/data/\\|special2\\\\"}
+(1 row)
+
+SELECT * FROM ext_special_uri ORDER BY a;
+ a | b
+---+---
+ 1 | 1
+ 2 | 2
+ 3 | 3
+(3 rows)
+
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
(cloudberry) 04/10: Fix relptr's encoding of the base address. (#17255)
Posted by av...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
avamingli pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 96ff7a764475935e821ca9c73fa4f7f497467a0e
Author: Zhang Hao <zh...@gmail.com>
AuthorDate: Wed Mar 27 15:05:48 2024 +0800
Fix relptr's encoding of the base address. (#17255)
Backport from upstream
https://github.com/postgres/postgres/commit/7201cd18627afc64850537806da7f22150d1a83b
Previously, we encoded both NULL and the first byte at the base address
as 0. That confusion led to the assertion in commit e07d4ddc, which
failed when min_dynamic_shared_memory was used. Give them distinct
encodings, by switching to 1-based offsets for non-NULL pointers. Also
improve macro hygiene in passing (missing/misplaced parentheses), and
remove open-coded access to the raw offset value from freepage.c/h.
Although e07d4ddc was back-patched to 10, the only code that actually
makes use of relptr at the base address arrived in 84b1c63a, so no need
to back-patch further than 14 for now.
Reported-by: Justin Pryzby <pr...@telsasoft.com>
Reviewed-by: Robert Haas <ro...@gmail.com>
Discussion: https://postgr.es/m/20220519193839.GT19626%40telsasoft.com
(cherry picked from commit 7201cd18627afc64850537806da7f22150d1a83b)
Co-authored-by: Thomas Munro <tm...@postgresql.org>
---
src/backend/utils/mmgr/freepage.c | 6 +++---
src/include/utils/freepage.h | 4 ++--
src/include/utils/relptr.h | 15 +++++++++------
3 files changed, 14 insertions(+), 11 deletions(-)
diff --git a/src/backend/utils/mmgr/freepage.c b/src/backend/utils/mmgr/freepage.c
index e4ee1aab97..4208317de8 100644
--- a/src/backend/utils/mmgr/freepage.c
+++ b/src/backend/utils/mmgr/freepage.c
@@ -434,7 +434,7 @@ FreePageManagerDump(FreePageManager *fpm)
/* Dump general stuff. */
appendStringInfo(&buf, "metadata: self %zu max contiguous pages = %zu\n",
- fpm->self.relptr_off, fpm->contiguous_pages);
+ relptr_offset(fpm->self), fpm->contiguous_pages);
/* Dump btree. */
if (fpm->btree_depth > 0)
@@ -1269,7 +1269,7 @@ FreePageManagerDumpBtree(FreePageManager *fpm, FreePageBtree *btp,
if (btp->hdr.magic == FREE_PAGE_INTERNAL_MAGIC)
appendStringInfo(buf, " %zu->%zu",
btp->u.internal_key[index].first_page,
- btp->u.internal_key[index].child.relptr_off / FPM_PAGE_SIZE);
+ relptr_offset(btp->u.internal_key[index].child) / FPM_PAGE_SIZE);
else
appendStringInfo(buf, " %zu(%zu)",
btp->u.leaf_key[index].first_page,
@@ -1859,7 +1859,7 @@ FreePagePopSpanLeader(FreePageManager *fpm, Size pageno)
{
Size f = Min(span->npages, FPM_NUM_FREELISTS) - 1;
- Assert(fpm->freelist[f].relptr_off == pageno * FPM_PAGE_SIZE);
+ Assert(relptr_offset(fpm->freelist[f]) == pageno * FPM_PAGE_SIZE);
relptr_copy(fpm->freelist[f], span->next);
}
}
diff --git a/src/include/utils/freepage.h b/src/include/utils/freepage.h
index 59f50ad30c..b02ca4529b 100644
--- a/src/include/utils/freepage.h
+++ b/src/include/utils/freepage.h
@@ -78,11 +78,11 @@ struct FreePageManager
#define fpm_pointer_is_page_aligned(base, ptr) \
(((Size) (((char *) (ptr)) - (base))) % FPM_PAGE_SIZE == 0)
#define fpm_relptr_is_page_aligned(base, relptr) \
- ((relptr).relptr_off % FPM_PAGE_SIZE == 0)
+ (relptr_offset(relptr) % FPM_PAGE_SIZE == 0)
/* Macro to find base address of the segment containing a FreePageManager. */
#define fpm_segment_base(fpm) \
- (((char *) fpm) - fpm->self.relptr_off)
+ (((char *) fpm) - relptr_offset(fpm->self))
/* Macro to access a FreePageManager's largest consecutive run of pages. */
#define fpm_largest(fpm) \
diff --git a/src/include/utils/relptr.h b/src/include/utils/relptr.h
index e71c7d0bdc..2ed4b5187d 100644
--- a/src/include/utils/relptr.h
+++ b/src/include/utils/relptr.h
@@ -42,7 +42,7 @@
#define relptr_access(base, rp) \
(AssertVariableIsOfTypeMacro(base, char *), \
(__typeof__((rp).relptr_type)) ((rp).relptr_off == 0 ? NULL : \
- (base + (rp).relptr_off)))
+ (base) + (rp).relptr_off - 1))
#else
/*
* If we don't have __builtin_types_compatible_p, assume we might not have
@@ -50,12 +50,15 @@
*/
#define relptr_access(base, rp) \
(AssertVariableIsOfTypeMacro(base, char *), \
- (void *) ((rp).relptr_off == 0 ? NULL : (base + (rp).relptr_off)))
+ (void *) ((rp).relptr_off == 0 ? NULL : (base) + (rp).relptr_off - 1))
#endif
#define relptr_is_null(rp) \
((rp).relptr_off == 0)
+#define relptr_offset(rp) \
+ ((rp).relptr_off - 1)
+
/* We use this inline to avoid double eval of "val" in relptr_store */
static inline Size
relptr_store_eval(char *base, char *val)
@@ -64,8 +67,8 @@ relptr_store_eval(char *base, char *val)
return 0;
else
{
- Assert(val > base);
- return val - base;
+ Assert(val >= base);
+ return val - base + 1;
}
}
@@ -73,7 +76,7 @@ relptr_store_eval(char *base, char *val)
#define relptr_store(base, rp, val) \
(AssertVariableIsOfTypeMacro(base, char *), \
AssertVariableIsOfTypeMacro(val, __typeof__((rp).relptr_type)), \
- (rp).relptr_off = relptr_store_eval(base, (char *) (val)))
+ (rp).relptr_off = relptr_store_eval((base), (char *) (val)))
#else
/*
* If we don't have __builtin_types_compatible_p, assume we might not have
@@ -81,7 +84,7 @@ relptr_store_eval(char *base, char *val)
*/
#define relptr_store(base, rp, val) \
(AssertVariableIsOfTypeMacro(base, char *), \
- (rp).relptr_off = relptr_store_eval(base, (char *) (val)))
+ (rp).relptr_off = relptr_store_eval((base), (char *) (val)))
#endif
#define relptr_copy(rp1, rp2) \
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
(cloudberry) 06/10: Fix pipeline diff caused by different length of ----- (#17263)
Posted by av...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
avamingli pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 6cc9a78a82e7d2d7a67105161b14606438adc49c
Author: Jingwen Yang <ji...@outlook.com>
AuthorDate: Thu Mar 28 11:27:50 2024 +0800
Fix pipeline diff caused by different length of ----- (#17263)
Fix pipeline diff caused by different length of `--------` in SELECT result.
---
gpcontrib/gp_exttable_fdw/input/gp_exttable_fdw.source | 2 ++
gpcontrib/gp_exttable_fdw/output/gp_exttable_fdw.source | 14 ++++++--------
2 files changed, 8 insertions(+), 8 deletions(-)
diff --git a/gpcontrib/gp_exttable_fdw/input/gp_exttable_fdw.source b/gpcontrib/gp_exttable_fdw/input/gp_exttable_fdw.source
index d2ffe47e4f..033dddd521 100644
--- a/gpcontrib/gp_exttable_fdw/input/gp_exttable_fdw.source
+++ b/gpcontrib/gp_exttable_fdw/input/gp_exttable_fdw.source
@@ -74,6 +74,8 @@ CREATE FOREIGN TABLE ext_special_uri(a int, b int)
SERVER gp_exttable_server
OPTIONS (format 'csv', delimiter ',',
location_uris 'file://@hostname@@abs_srcdir@/data/spe\\cial1\||file://@hostname@@abs_srcdir@/data/\|special2\\');
+\a
SELECT urilocation FROM pg_exttable WHERE reloid = 'public.ext_special_uri'::regclass;
SELECT ftoptions FROM pg_foreign_table WHERE ftrelid='public.ext_special_uri'::regclass;
+\a
SELECT * FROM ext_special_uri ORDER BY a;
diff --git a/gpcontrib/gp_exttable_fdw/output/gp_exttable_fdw.source b/gpcontrib/gp_exttable_fdw/output/gp_exttable_fdw.source
index 4efc7ef29e..337d21a99e 100644
--- a/gpcontrib/gp_exttable_fdw/output/gp_exttable_fdw.source
+++ b/gpcontrib/gp_exttable_fdw/output/gp_exttable_fdw.source
@@ -78,18 +78,16 @@ CREATE FOREIGN TABLE ext_special_uri(a int, b int)
SERVER gp_exttable_server
OPTIONS (format 'csv', delimiter ',',
location_uris 'file://@hostname@@abs_srcdir@/data/spe\\cial1\||file://@hostname@@abs_srcdir@/data/\|special2\\');
+\a
SELECT urilocation FROM pg_exttable WHERE reloid = 'public.ext_special_uri'::regclass;
- urilocation
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- {"file://@hostname@@abs_srcdir@/data/spe\\cial1|","file://@hostname@@abs_srcdir@/data/|special2\\"}
+urilocation
+{"file://@hostname@@abs_srcdir@/data/spe\\cial1|","file://@hostname@@abs_srcdir@/data/|special2\\"}
(1 row)
-
SELECT ftoptions FROM pg_foreign_table WHERE ftrelid='public.ext_special_uri'::regclass;
- ftoptions
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- {format=csv,"delimiter=,","location_uris=file://@hostname@@abs_srcdir@/data/spe\\\\cial1\\||file://@hostname@@abs_srcdir@/data/\\|special2\\\\"}
+ftoptions
+{format=csv,"delimiter=,","location_uris=file://@hostname@@abs_srcdir@/data/spe\\\\cial1\\||file://@hostname@@abs_srcdir@/data/\\|special2\\\\"}
(1 row)
-
+\a
SELECT * FROM ext_special_uri ORDER BY a;
a | b
---+---
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
(cloudberry) 01/10: Remove a fixme of plpgsql tests
Posted by av...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
avamingli pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 76a08f8fca07c625b869aa5870fb628156238d45
Author: Adam Lee <ad...@gmail.com>
AuthorDate: Mon Mar 18 15:21:10 2024 +0800
Remove a fixme of plpgsql tests
It acts as expected, remove the comment fixme.
---
src/test/regress/expected/plpgsql.out | 11 +++--------
src/test/regress/expected/plpgsql_optimizer.out | 11 +++--------
src/test/regress/sql/plpgsql.sql | 11 +++--------
3 files changed, 9 insertions(+), 24 deletions(-)
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index e4dbc4d3a5..1fde463c09 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -1736,14 +1736,9 @@ insert into PField values ('PF1_1', 'should fail due to unique index');
ERROR: duplicate key value violates unique constraint "pfield_name"
DETAIL: Key (name)=(PF1_1) already exists.
/*
- * GPDB_96_MERGE_FIXME : should these update statements trigger the error
- * ERRCODE_FEATURE_NOT_SUPPORTED: function cannot execute on a QE slice because
- * it accesses relation "public.wslot"?
- * In Postgres, the expected behavior of these tests is to error out because
- * 'WS.not.there' does not exist.
- * However, in GPDB, it is unclear what the intended behavior is.
- * Currently, it does not error out but has no effect, as the table is empty at
- * this point in the test in GPDB
+ * It does not error out but has no effect, as the table is empty at this point
+ * in the test in GPDB. If data exist it errors out "ERROR: function cannot
+ * execute on a QE slice because it accesses relation "public.pfield"
* Adding an ignore block for now
*/
--start_ignore
diff --git a/src/test/regress/expected/plpgsql_optimizer.out b/src/test/regress/expected/plpgsql_optimizer.out
index e72bd73c24..c04d62c1bf 100755
--- a/src/test/regress/expected/plpgsql_optimizer.out
+++ b/src/test/regress/expected/plpgsql_optimizer.out
@@ -1736,14 +1736,9 @@ insert into PField values ('PF1_1', 'should fail due to unique index');
ERROR: duplicate key value violates unique constraint "pfield_name"
DETAIL: Key (name)=(PF1_1) already exists.
/*
- * GPDB_96_MERGE_FIXME : should these update statements trigger the error
- * ERRCODE_FEATURE_NOT_SUPPORTED: function cannot execute on a QE slice because
- * it accesses relation "public.wslot"?
- * In Postgres, the expected behavior of these tests is to error out because
- * 'WS.not.there' does not exist.
- * However, in GPDB, it is unclear what the intended behavior is.
- * Currently, it does not error out but has no effect, as the table is empty at
- * this point in the test in GPDB
+ * It does not error out but has no effect, as the table is empty at this point
+ * in the test in GPDB. If data exist it errors out "ERROR: function cannot
+ * execute on a QE slice because it accesses relation "public.pfield"
* Adding an ignore block for now
*/
--start_ignore
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index e0316ff099..2083792466 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -1408,14 +1408,9 @@ select * from PField_v1 where pfname = 'PF0_2' order by slotname;
--
insert into PField values ('PF1_1', 'should fail due to unique index');
/*
- * GPDB_96_MERGE_FIXME : should these update statements trigger the error
- * ERRCODE_FEATURE_NOT_SUPPORTED: function cannot execute on a QE slice because
- * it accesses relation "public.wslot"?
- * In Postgres, the expected behavior of these tests is to error out because
- * 'WS.not.there' does not exist.
- * However, in GPDB, it is unclear what the intended behavior is.
- * Currently, it does not error out but has no effect, as the table is empty at
- * this point in the test in GPDB
+ * It does not error out but has no effect, as the table is empty at this point
+ * in the test in GPDB. If data exist it errors out "ERROR: function cannot
+ * execute on a QE slice because it accesses relation "public.pfield"
* Adding an ignore block for now
*/
--start_ignore
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
(cloudberry) 08/10: Add missing volatile qualifier. (#17273)
Posted by av...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
avamingli pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 120758cf1bdfbe3ad7af64e95d436d9e65b7fa0a
Author: Xing Guo <hi...@gmail.com>
AuthorDate: Mon Apr 1 19:27:08 2024 +0800
Add missing volatile qualifier. (#17273)
According to C99 7.13.2.1[^1],
> All accessible objects have values, and all other components of the
abstract machine have state, as of the time the longjmp function was
called, except that the values of objects of automatic storage duration
that are local to the function containing the invocation of the
corresponding setjmp macro that do not have volatile-qualified type and
have been changed between the setjmp invocation and longjmp call are
indeterminate.
The object `toplevelOidCache` is changed in line 657 (in the PG_TRY()
block) and read in line 719 (in the PG_CATCH() block). We should qualify
it with `volatile`.
[^1]: https://www.open-std.org/jtc1/sc22/wg14/www/docs/n1256.pdf
---
src/backend/executor/execMain.c | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 471c76bfb1..67e1274669 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -248,7 +248,7 @@ standard_ExecutorStart(QueryDesc *queryDesc, int eflags)
GpExecIdentity exec_identity;
bool shouldDispatch;
bool needDtx;
- List *toplevelOidCache = NIL;
+ List *volatile toplevelOidCache = NIL;
/* sanity checks: queryDesc must not be started already */
Assert(queryDesc != NULL);
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
(cloudberry) 02/10: Consider MPP FDW LIMIT pushdown when both offset and limit clause are specified (#16919)
Posted by av...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
avamingli pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit dc73f6ece049fd40d9af2618d19200eaf0347ced
Author: Jingwen Yang <ji...@outlook.com>
AuthorDate: Wed Mar 20 16:05:34 2024 +0800
Consider MPP FDW LIMIT pushdown when both offset and limit clause are specified (#16919)
Before this commit, GPDB won't push OFFSET/LIMIT in the remote servers for MPP FDW when both offset and limit clause are specified.
Because it's unsafe if we don't adjust Remote SQL.
In this commit, we consider MPP FDW OFFSET/LIMIT pushdown when both OFFSET and LIMIT clause are specified.
We will adjust Remote SQL when mpp_execute = 'all segments' for query including OFFSET/LIMIT clause.
---
contrib/postgres_fdw/deparse.c | 56 +-
.../expected/mpp_gp2pg_postgres_fdw.out | 1051 ++++++++++++++++++++
contrib/postgres_fdw/postgres_clean.bash | 9 +
contrib/postgres_fdw/postgres_fdw.c | 23 +
.../postgres_fdw/sql/mpp_gp2pg_postgres_fdw.sql | 248 +++++
5 files changed, 1381 insertions(+), 6 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index efaf387890..19d8e9cc83 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -58,6 +58,8 @@
#include "utils/syscache.h"
#include "utils/typcache.h"
#include "commands/tablecmds.h"
+#include "parser/parse_oper.h"
+
/*
* Global context for foreign_expr_walker's search of an expression tree.
@@ -3421,19 +3423,61 @@ appendLimitClause(deparse_expr_cxt *context)
PlannerInfo *root = context->root;
StringInfo buf = context->buf;
int nestlevel;
+ RelOptInfo *foreignrel = context->foreignrel;
/* Make sure any constants in the exprs are printed portably */
nestlevel = set_transmission_modes();
- if (root->parse->limitCount)
+ if (foreignrel->exec_location != FTEXECLOCATION_ALL_SEGMENTS)
{
- appendStringInfoString(buf, " LIMIT ");
- deparseExpr((Expr *) root->parse->limitCount, context);
+ if (root->parse->limitCount)
+ {
+ appendStringInfoString(buf, " LIMIT ");
+ deparseExpr((Expr *) root->parse->limitCount, context);
+ }
+ if (root->parse->limitOffset)
+ {
+ appendStringInfoString(buf, " OFFSET ");
+ deparseExpr((Expr *) root->parse->limitOffset, context);
+ }
}
- if (root->parse->limitOffset)
+ else
{
- appendStringInfoString(buf, " OFFSET ");
- deparseExpr((Expr *) root->parse->limitOffset, context);
+ /*
+ * If mpp_execute = 'all segments', we can add a preliminary LIMIT
+ * on the partitioned results.
+ * This may reduce the number of tuples that we need to fetch from remote servers.
+ */
+ Node *precount = copyObject(root->parse->limitCount);
+
+ /*
+ * If we've specified both OFFSET and LIMIT clause,
+ * it's enough to fetch tuples from 0 to limitCount + limitOffset from remote servers.
+ */
+ if (precount)
+ {
+ if (root->parse->limitOffset)
+ {
+ ParseState *pstate = make_parsestate(NULL);
+ /*
+ * we should explicitly specify the schema of operator "+",
+ * to avoid misuse user defined operator "+".
+ */
+ precount = (Node *) make_op(pstate,
+ list_make2(makeString("pg_catalog"), makeString(pstrdup("+"))),
+ copyObject(root->parse->limitOffset),
+ precount,
+ NULL,
+ -1);
+ }
+
+ appendStringInfoString(buf, " LIMIT ");
+ deparseExpr((Expr *) precount, context);
+ }
+ /*
+ * If LIMIT clause is NOT specified, we need to fetch all tuple of foreign table.
+ * So here we don't append LIMIT to Remote SQL.
+ */
}
reset_transmission_modes(nestlevel);
diff --git a/contrib/postgres_fdw/expected/mpp_gp2pg_postgres_fdw.out b/contrib/postgres_fdw/expected/mpp_gp2pg_postgres_fdw.out
new file mode 100644
index 0000000000..61496856b2
--- /dev/null
+++ b/contrib/postgres_fdw/expected/mpp_gp2pg_postgres_fdw.out
@@ -0,0 +1,1051 @@
+-- This file is used to test the feature that there are multiple remote postgres servers.
+-- ===================================================================
+-- create FDW objects
+-- ===================================================================
+SET timezone = 'PST8PDT';
+-- If gp_enable_minmax_optimization is on, it won't generate aggregate functions pushdown plan.
+SET gp_enable_minmax_optimization = off;
+-- Clean
+-- start_ignore
+DROP EXTENSION IF EXISTS postgres_fdw CASCADE;
+-- end_ignore
+CREATE EXTENSION postgres_fdw;
+CREATE SERVER pgserver FOREIGN DATA WRAPPER postgres_fdw
+ OPTIONS (host 'dummy', port '0',
+ dbname 'contrib_regression', multi_hosts 'localhost localhost',
+ multi_ports '5432 5555', num_segments '2', mpp_execute 'all segments');
+CREATE USER MAPPING FOR CURRENT_USER SERVER pgserver;
+-- ===================================================================
+-- create objects used through FDW pgserver server
+-- ===================================================================
+-- remote postgres server 1 -- listening port 5432
+\! env PGOPTIONS='' psql -p 5432 contrib_regression -f sql/postgres_sql/mpp_gp2pg_postgres_init_1.sql
+SET
+CREATE SCHEMA
+CREATE TABLE
+CREATE TABLE
+ALTER TABLE
+ALTER TABLE
+INSERT 0 5
+INSERT 0 500
+UPDATE 500
+ANALYZE
+ANALYZE
+-- remote postgres server 2 -- listening port 5555
+\! env PGOPTIONS='' psql -p 5555 contrib_regression -f sql/postgres_sql/mpp_gp2pg_postgres_init_2.sql
+SET
+CREATE SCHEMA
+CREATE TABLE
+CREATE TABLE
+ALTER TABLE
+ALTER TABLE
+INSERT 0 5
+INSERT 0 500
+UPDATE 500
+ANALYZE
+ANALYZE
+-- ===================================================================
+-- create foreign tables
+-- ===================================================================
+CREATE FOREIGN TABLE mpp_ft1 (
+ c1 int,
+ c2 int
+) SERVER pgserver OPTIONS (schema_name 'MPP_S 1', table_name 'T 1');
+CREATE FOREIGN TABLE mpp_ft2 (
+ c1 int,
+ c2 int,
+ c3 smallint,
+ c4 bigint,
+ c5 real,
+ c6 double precision,
+ c7 numeric
+) SERVER pgserver OPTIONS (schema_name 'MPP_S 1', table_name 'T 2');
+-- ===================================================================
+-- tests for validator
+-- ===================================================================
+-- Error when the length of option multi_hosts and multi_ports is NOT same.
+CREATE SERVER testserver FOREIGN DATA WRAPPER postgres_fdw
+ OPTIONS (dbname 'contrib_regression', multi_hosts 'localhost localhost',
+ multi_ports '5432', num_segments '2', mpp_execute 'all segments');
+ERROR: The number of hosts and ports don't match in option 'multi_hosts' and 'multi_ports'.
+-- Error when specifying option multi_hosts and multi_ports but option mpp_execute is NOT 'all segments'.
+CREATE FOREIGN TABLE mpp_test (
+ c1 int,
+ c2 int
+) SERVER pgserver OPTIONS (schema_name 'MPP_S 1', table_name 'T 1', mpp_execute 'coordinator');
+SELECT * FROM mpp_test;
+ERROR: Only option mpp_execute is set to 'all segments', option multi_hosts and multi_ports is valid.
+ALTER FOREIGN TABLE mpp_test OPTIONS (drop mpp_execute);
+-- Error when the value of option num_segments is NOT same as the length of option multi_hosts and multi_ports.
+ALTER SERVER pgserver OPTIONS (set num_segments '1');
+SELECT * FROM mpp_test;
+ERROR: server option num_segments, multi_hosts and multi_ports don't match. (postgres_fdw.c:1502) (seg0 slice1 127.0.0.1:7002 pid=24510)
+ALTER SERVER pgserver OPTIONS (set num_segments '2');
+-- ===================================================================
+-- Simple queries
+-- ===================================================================
+EXPLAIN VERBOSE SELECT * FROM mpp_ft1 ORDER BY c1;
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Gather Motion 2:1 (slice1; segments: 2) (cost=100.00..5558.20 rows=172200 width=8)
+ Output: c1, c2
+ Merge Key: c1
+ -> Foreign Scan on public.mpp_ft1 (cost=100.00..2975.20 rows=86100 width=8)
+ Output: c1, c2
+ Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 1" ORDER BY c1 ASC NULLS LAST
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(8 rows)
+
+SELECT * FROM mpp_ft1 ORDER BY c1;
+ c1 | c2
+----+----
+ 1 | 1
+ 2 | 0
+ 3 | 1
+ 4 | 0
+ 5 | 1
+ 6 | 0
+ 7 | 1
+ 8 | 0
+ 9 | 1
+ 10 | 0
+(10 rows)
+
+ANALYZE mpp_ft1;
+ALTER FOREIGN TABLE mpp_ft1 OPTIONS (add use_remote_estimate 'true');
+EXPLAIN VERBOSE SELECT * FROM mpp_ft1 ORDER BY c1;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Gather Motion 2:1 (slice1; segments: 2) (cost=101.11..101.37 rows=10 width=8)
+ Output: c1, c2
+ Merge Key: c1
+ -> Foreign Scan on public.mpp_ft1 (cost=101.11..101.22 rows=5 width=8)
+ Output: c1, c2
+ Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 1" ORDER BY c1 ASC NULLS LAST
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(8 rows)
+
+SELECT * FROM mpp_ft1 ORDER BY c1;
+ c1 | c2
+----+----
+ 1 | 1
+ 2 | 0
+ 3 | 1
+ 4 | 0
+ 5 | 1
+ 6 | 0
+ 7 | 1
+ 8 | 0
+ 9 | 1
+ 10 | 0
+(10 rows)
+
+ALTER FOREIGN TABLE mpp_ft1 OPTIONS (drop use_remote_estimate);
+-- ===================================================================
+-- When there are multiple remote servers, we don't support IMPORT FOREIGN SCHEMA
+-- ===================================================================
+CREATE SCHEMA mpp_import_dest;
+IMPORT FOREIGN SCHEMA import_source FROM SERVER pgserver INTO mpp_import_dest;
+ERROR: If there are multiple remote servers, gpdb doesn't support import foreign schema
+-- ===================================================================
+-- When there are multiple remote servers, we don't support INSERT/UPDATE/DELETE
+-- ===================================================================
+INSERT INTO mpp_ft1 VALUES (1, 1);
+ERROR: foreign table "mpp_ft1" does not allow inserts
+UPDATE mpp_ft1 SET c1 = c1 + 1;
+ERROR: foreign table "mpp_ft1" does not allow updates
+DELETE FROM mpp_ft1;
+ERROR: foreign table "mpp_ft1" does not allow deletes
+-- ===================================================================
+-- Aggregate and grouping queries
+-- ===================================================================
+-- Simple aggregates with different data types
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(c1), count(c3), count(c4), count(c5), count(c6), count(c7) FROM mpp_ft2;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: count(c1), count(c3), count(c4), count(c5), count(c6), count(c7)
+ -> Gather Motion 2:1 (slice1; segments: 2)
+ Output: (PARTIAL count(c1)), (PARTIAL count(c3)), (PARTIAL count(c4)), (PARTIAL count(c5)), (PARTIAL count(c6)), (PARTIAL count(c7))
+ -> Foreign Scan
+ Output: (PARTIAL count(c1)), (PARTIAL count(c3)), (PARTIAL count(c4)), (PARTIAL count(c5)), (PARTIAL count(c6)), (PARTIAL count(c7))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT count(c1), count(c3), count(c4), count(c5), count(c6), count(c7) FROM "MPP_S 1"."T 2"
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(10 rows)
+
+SELECT count(c1), count(c3), count(c4), count(c5), count(c6), count(c7) FROM mpp_ft2;
+ count | count | count | count | count | count
+-------+-------+-------+-------+-------+-------
+ 1000 | 1000 | 1000 | 1000 | 1000 | 1000
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(c1), sum(c3), sum(c4), sum(c5), sum(c6), sum(c7) FROM mpp_ft2;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: sum(c1), sum(c3), sum(c4), sum(c5), sum(c6), sum(c7)
+ -> Gather Motion 2:1 (slice1; segments: 2)
+ Output: (PARTIAL sum(c1)), (PARTIAL sum(c3)), (PARTIAL sum(c4)), (PARTIAL sum(c5)), (PARTIAL sum(c6)), (PARTIAL sum(c7))
+ -> Foreign Scan
+ Output: (PARTIAL sum(c1)), (PARTIAL sum(c3)), (PARTIAL sum(c4)), (PARTIAL sum(c5)), (PARTIAL sum(c6)), (PARTIAL sum(c7))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT sum(c1), sum(c3), sum(c4), sum(c5), sum(c6), sum(c7) FROM "MPP_S 1"."T 2"
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(10 rows)
+
+SELECT sum(c1), sum(c3), sum(c4), sum(c5), sum(c6), sum(c7) FROM mpp_ft2;
+ sum | sum | sum | sum | sum | sum
+--------+--------+--------+-------+-------------------+---------
+ 500500 | 500500 | 500500 | 500.5 | 500.5000000000001 | 500.500
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(c1), avg(c3), avg(c4), avg(c5), avg(c6), avg(c7) FROM mpp_ft2;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: avg(c1), avg(c3), avg(c4), avg(c5), avg(c6), avg(c7)
+ -> Gather Motion 2:1 (slice1; segments: 2)
+ Output: (PARTIAL avg(c1)), (PARTIAL avg(c3)), (PARTIAL avg(c4)), (PARTIAL avg(c5)), (PARTIAL avg(c6)), (PARTIAL avg(c7))
+ -> Foreign Scan
+ Output: (PARTIAL avg(c1)), (PARTIAL avg(c3)), (PARTIAL avg(c4)), (PARTIAL avg(c5)), (PARTIAL avg(c6)), (PARTIAL avg(c7))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT array[count(c1), sum(c1)], array[count(c3), sum(c3)], array[count(c4), sum(c4)], array[count(c5), sum(c5), count(c5)*var_pop(c5)], array[count(c6), sum(c6), count(c6)*var_pop(c6)], array[count(c7), sum(c7)] FROM "MPP_S 1"."T 2"
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(10 rows)
+
+SELECT avg(c1), avg(c3), avg(c4), avg(c5), avg(c6), avg(c7) FROM mpp_ft2;
+ avg | avg | avg | avg | avg | avg
+----------------------+----------------------+----------------------+--------+--------------------+------------------------
+ 500.5000000000000000 | 500.5000000000000000 | 500.5000000000000000 | 0.5005 | 0.5005000000000002 | 0.50050000000000000000
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT min(c1), min(c3), min(c4), min(c5), min(c6), min(c7) FROM mpp_ft2;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: min(c1), min(c3), min(c4), min(c5), min(c6), min(c7)
+ -> Gather Motion 2:1 (slice1; segments: 2)
+ Output: (PARTIAL min(c1)), (PARTIAL min(c3)), (PARTIAL min(c4)), (PARTIAL min(c5)), (PARTIAL min(c6)), (PARTIAL min(c7))
+ -> Foreign Scan
+ Output: (PARTIAL min(c1)), (PARTIAL min(c3)), (PARTIAL min(c4)), (PARTIAL min(c5)), (PARTIAL min(c6)), (PARTIAL min(c7))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT min(c1), min(c3), min(c4), min(c5), min(c6), min(c7) FROM "MPP_S 1"."T 2"
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(10 rows)
+
+SELECT min(c1), min(c3), min(c4), min(c5), min(c6), min(c7) FROM mpp_ft2;
+ min | min | min | min | min | min
+-----+-----+-----+-------+-------+-------
+ 1 | 1 | 1 | 0.001 | 0.001 | 0.001
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT max(c1), max(c3), max(c4), max(c5), max(c6), max(c7) FROM mpp_ft2;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: max(c1), max(c3), max(c4), max(c5), max(c6), max(c7)
+ -> Gather Motion 2:1 (slice1; segments: 2)
+ Output: (PARTIAL max(c1)), (PARTIAL max(c3)), (PARTIAL max(c4)), (PARTIAL max(c5)), (PARTIAL max(c6)), (PARTIAL max(c7))
+ -> Foreign Scan
+ Output: (PARTIAL max(c1)), (PARTIAL max(c3)), (PARTIAL max(c4)), (PARTIAL max(c5)), (PARTIAL max(c6)), (PARTIAL max(c7))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT max(c1), max(c3), max(c4), max(c5), max(c6), max(c7) FROM "MPP_S 1"."T 2"
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(10 rows)
+
+SELECT max(c1), max(c3), max(c4), max(c5), max(c6), max(c7) FROM mpp_ft2;
+ max | max | max | max | max | max
+------+------+------+-----+-----+-------
+ 1000 | 1000 | 1000 | 1 | 1 | 1.000
+(1 row)
+
+-- Simple Aggregates with GROUP BY clause
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(c1), sum(c3), avg(c4), min(c5), max(c6), count(c1) * (random() <= 1)::int as count2 FROM mpp_ft2 GROUP BY c2 ORDER BY c2;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (count(c1)), (sum(c3)), (avg(c4)), (min(c5)), (max(c6)), (((count(c1)) * ((random() <= '1'::double precision))::integer)), c2
+ Merge Key: c2
+ -> Result
+ Output: (count(c1)), (sum(c3)), (avg(c4)), (min(c5)), (max(c6)), ((count(c1)) * ((random() <= '1'::double precision))::integer), c2
+ -> Sort
+ Output: (count(c1)), (sum(c3)), (avg(c4)), (min(c5)), (max(c6)), c2
+ Sort Key: mpp_ft2.c2
+ -> Finalize HashAggregate
+ Output: count(c1), sum(c3), avg(c4), min(c5), max(c6), c2
+ Group Key: mpp_ft2.c2
+ -> Redistribute Motion 2:3 (slice2; segments: 2)
+ Output: c2, (PARTIAL count(c1)), (PARTIAL sum(c3)), (PARTIAL avg(c4)), (PARTIAL min(c5)), (PARTIAL max(c6))
+ Hash Key: c2
+ -> Foreign Scan
+ Output: c2, (PARTIAL count(c1)), (PARTIAL sum(c3)), (PARTIAL avg(c4)), (PARTIAL min(c5)), (PARTIAL max(c6))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT c2, count(c1), sum(c3), array[count(c4), sum(c4)], min(c5), max(c6) FROM "MPP_S 1"."T 2" GROUP BY 1
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(20 rows)
+
+SELECT count(c1), sum(c3), avg(c4), min(c5), max(c6), count(c1) * (random() <= 1)::int as count2 FROM mpp_ft2 GROUP BY c2 ORDER BY c2;
+ count | sum | avg | min | max | count2
+-------+-------+----------------------+-------+-------+--------
+ 100 | 50500 | 505.0000000000000000 | 0.01 | 1 | 100
+ 100 | 49600 | 496.0000000000000000 | 0.001 | 0.991 | 100
+ 100 | 49700 | 497.0000000000000000 | 0.002 | 0.992 | 100
+ 100 | 49800 | 498.0000000000000000 | 0.003 | 0.993 | 100
+ 100 | 49900 | 499.0000000000000000 | 0.004 | 0.994 | 100
+ 100 | 50000 | 500.0000000000000000 | 0.005 | 0.995 | 100
+ 100 | 50100 | 501.0000000000000000 | 0.006 | 0.996 | 100
+ 100 | 50200 | 502.0000000000000000 | 0.007 | 0.997 | 100
+ 100 | 50300 | 503.0000000000000000 | 0.008 | 0.998 | 100
+ 100 | 50400 | 504.0000000000000000 | 0.009 | 0.999 | 100
+(10 rows)
+
+-- Aggregate is not pushed down as aggregation contains random()
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(c1 * (random() <= 1)::int) as sum, avg(c1) FROM mpp_ft2;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: sum((c1 * ((random() <= '1'::double precision))::integer)), avg(c1)
+ -> Gather Motion 2:1 (slice1; segments: 2)
+ Output: (PARTIAL sum((c1 * ((random() <= '1'::double precision))::integer))), (PARTIAL avg(c1))
+ -> Partial Aggregate
+ Output: PARTIAL sum((c1 * ((random() <= '1'::double precision))::integer)), PARTIAL avg(c1)
+ -> Foreign Scan on public.mpp_ft2
+ Output: c1, c2, c3, c4, c5, c6, c7
+ Remote SQL: SELECT c1 FROM "MPP_S 1"."T 2"
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(11 rows)
+
+SELECT sum(c1 * (random() <= 1)::int) as sum, avg(c1) FROM mpp_ft2;
+ sum | avg
+--------+----------------------
+ 500500 | 500.5000000000000000
+(1 row)
+
+-- GROUP BY clause having expressions
+/* FIXME: Aggregates are not pushed down.
+ Because for Remote SQL of partial agg, non-grouping columns
+ might neither appear in the GROUP BY clause nor be used in
+ an aggregate function.
+ This is unsafe to make foreign grouping.
+*/
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c2/2, sum(c2) * (c2/2) FROM mpp_ft2 GROUP BY c2/2 ORDER BY c2/2;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: ((c2 / 2)), ((sum(c2) * ((c2 / 2))))
+ Merge Key: ((c2 / 2))
+ -> Sort
+ Output: ((c2 / 2)), ((sum(c2) * ((c2 / 2))))
+ Sort Key: ((mpp_ft2.c2 / 2))
+ -> Finalize HashAggregate
+ Output: ((c2 / 2)), (sum(c2) * ((c2 / 2)))
+ Group Key: ((mpp_ft2.c2 / 2))
+ -> Redistribute Motion 2:3 (slice2; segments: 2)
+ Output: ((c2 / 2)), (PARTIAL sum(c2))
+ Hash Key: ((c2 / 2))
+ -> Foreign Scan
+ Output: ((c2 / 2)), (PARTIAL sum(c2))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT (c2 / 2), sum(c2) FROM "MPP_S 1"."T 2" GROUP BY 1
+ Optimizer: Postgres-based planner
+ Settings: gp_enable_minmax_optimization = 'off'
+(18 rows)
+
+SELECT c2/2, sum(c2) * (c2/2) FROM mpp_ft2 GROUP BY c2/2 ORDER BY c2/2;
+ ?column? | ?column?
+----------+----------
+ 0 | 0
+ 1 | 500
+ 2 | 1800
+ 3 | 3900
+ 4 | 6800
+(5 rows)
+
+-- Aggregates in subquery are pushed down.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(x.a), sum(x.a) FROM (SELECT c2 a, sum(c1) b FROM mpp_ft2 GROUP BY c2, sqrt(c1) ORDER BY 1, 2) x;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: count(mpp_ft2.c2), sum(mpp_ft2.c2)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: (PARTIAL count(mpp_ft2.c2)), (PARTIAL sum(mpp_ft2.c2))
+ -> Partial Aggregate
+ Output: PARTIAL count(mpp_ft2.c2), PARTIAL sum(mpp_ft2.c2)
+ -> Sort
+ Output: mpp_ft2.c2, (sum(mpp_ft2.c1)), (sqrt((mpp_ft2.c1)::double precision))
+ Sort Key: mpp_ft2.c2, (sum(mpp_ft2.c1))
+ -> Finalize HashAggregate
+ Output: mpp_ft2.c2, sum(mpp_ft2.c1), (sqrt((mpp_ft2.c1)::double precision))
+ Group Key: mpp_ft2.c2, (sqrt((mpp_ft2.c1)::double precision))
+ -> Redistribute Motion 2:3 (slice2; segments: 2)
+ Output: mpp_ft2.c2, (sqrt((mpp_ft2.c1)::double precision)), (PARTIAL sum(mpp_ft2.c1))
+ Hash Key: mpp_ft2.c2, (sqrt((mpp_ft2.c1)::double precision))
+ -> Foreign Scan
+ Output: mpp_ft2.c2, (sqrt((mpp_ft2.c1)::double precision)), (PARTIAL sum(mpp_ft2.c1))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT c2, sqrt(c1), sum(c1) FROM "MPP_S 1"."T 2" GROUP BY 1, 2
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(21 rows)
+
+SELECT count(x.a), sum(x.a) FROM (SELECT c2 a, sum(c1) b FROM mpp_ft2 GROUP BY c2, sqrt(c1) ORDER BY 1, 2) x;
+ count | sum
+-------+------
+ 1000 | 4500
+(1 row)
+
+-- Aggregate is still pushed down by taking unshippable expression out
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c2 * (random() <= 1)::int as sum1, sum(c1) * c2 as sum2 FROM mpp_ft2 GROUP BY c2 ORDER BY 1, 2;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: ((c2 * ((random() <= '1'::double precision))::integer)), ((sum(c1) * c2)), c2
+ Merge Key: ((c2 * ((random() <= '1'::double precision))::integer)), ((sum(c1) * c2))
+ -> Sort
+ Output: ((c2 * ((random() <= '1'::double precision))::integer)), ((sum(c1) * c2)), c2
+ Sort Key: ((mpp_ft2.c2 * ((random() <= '1'::double precision))::integer)), ((sum(mpp_ft2.c1) * mpp_ft2.c2))
+ -> Finalize HashAggregate
+ Output: (c2 * ((random() <= '1'::double precision))::integer), (sum(c1) * c2), c2
+ Group Key: mpp_ft2.c2
+ -> Redistribute Motion 2:3 (slice2; segments: 2)
+ Output: c2, (PARTIAL sum(c1))
+ Hash Key: c2
+ -> Foreign Scan
+ Output: c2, (PARTIAL sum(c1))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT c2, sum(c1) FROM "MPP_S 1"."T 2" GROUP BY 1
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(18 rows)
+
+SELECT c2 * (random() <= 1)::int as sum1, sum(c1) * c2 as sum2 FROM mpp_ft2 GROUP BY c2 ORDER BY 1, 2;
+ sum1 | sum2
+------+--------
+ 0 | 0
+ 1 | 49600
+ 2 | 99400
+ 3 | 149400
+ 4 | 199600
+ 5 | 250000
+ 6 | 300600
+ 7 | 351400
+ 8 | 402400
+ 9 | 453600
+(10 rows)
+
+-- Aggregate with unshippable GROUP BY clause are not pushed
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c2 * (random() <= 1)::int as c2 FROM mpp_ft2 GROUP BY c2 * (random() <= 1)::int ORDER BY 1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: ((c2 * ((random() <= '1'::double precision))::integer))
+ Merge Key: ((c2 * ((random() <= '1'::double precision))::integer))
+ -> Sort
+ Output: ((c2 * ((random() <= '1'::double precision))::integer))
+ Sort Key: ((mpp_ft2.c2 * ((random() <= '1'::double precision))::integer))
+ -> HashAggregate
+ Output: ((c2 * ((random() <= '1'::double precision))::integer))
+ Group Key: ((mpp_ft2.c2 * ((random() <= '1'::double precision))::integer))
+ -> Redistribute Motion 2:3 (slice2; segments: 2)
+ Output: ((c2 * ((random() <= '1'::double precision))::integer))
+ Hash Key: ((c2 * ((random() <= '1'::double precision))::integer))
+ -> HashAggregate
+ Output: ((c2 * ((random() <= '1'::double precision))::integer))
+ Group Key: (mpp_ft2.c2 * ((random() <= '1'::double precision))::integer)
+ -> Foreign Scan on public.mpp_ft2
+ Output: (c2 * ((random() <= '1'::double precision))::integer)
+ Remote SQL: SELECT c2 FROM "MPP_S 1"."T 2"
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(20 rows)
+
+SELECT c2 * (random() <= 1)::int as c2 FROM mpp_ft2 GROUP BY c2 * (random() <= 1)::int ORDER BY 1;
+ c2
+----
+ 0
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+(10 rows)
+
+-- GROUP BY clause in various forms, cardinal, alias and constant expression
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(c2) w, c2 x, 5 y, 7.0 z FROM mpp_ft2 GROUP BY 2, y, 9.0::int ORDER BY 2;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (count(c2)), c2, 5, 7.0, 9
+ Merge Key: c2
+ -> Sort
+ Output: (count(c2)), c2, 5, 7.0, 9
+ Sort Key: mpp_ft2.c2
+ -> Finalize HashAggregate
+ Output: count(c2), c2, (5), 7.0, (9)
+ Group Key: mpp_ft2.c2, 5, 9
+ -> Redistribute Motion 2:3 (slice2; segments: 2)
+ Output: c2, 5, 9, (PARTIAL count(c2))
+ Hash Key: c2, 5, 9
+ -> Foreign Scan
+ Output: c2, 5, 9, (PARTIAL count(c2))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT c2, 5, 9, count(c2) FROM "MPP_S 1"."T 2" GROUP BY 1, 2, 3
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(18 rows)
+
+SELECT count(c2) w, c2 x, 5 y, 7.0 z FROM mpp_ft2 GROUP BY 2, y, 9.0::int ORDER BY 2;
+ w | x | y | z
+-----+---+---+-----
+ 100 | 0 | 5 | 7.0
+ 100 | 1 | 5 | 7.0
+ 100 | 2 | 5 | 7.0
+ 100 | 3 | 5 | 7.0
+ 100 | 4 | 5 | 7.0
+ 100 | 5 | 5 | 7.0
+ 100 | 6 | 5 | 7.0
+ 100 | 7 | 5 | 7.0
+ 100 | 8 | 5 | 7.0
+ 100 | 9 | 5 | 7.0
+(10 rows)
+
+-- GROUP BY clause referring to same column multiple times
+-- Also, ORDER BY contains an aggregate function
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c2, c2 FROM mpp_ft2 WHERE c2 > 6 GROUP BY 1, 2 ORDER BY sum(c1);
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: c2, c2, (sum(c1))
+ Merge Key: (sum(c1))
+ -> Sort
+ Output: c2, c2, (sum(c1))
+ Sort Key: (sum(mpp_ft2.c1))
+ -> Finalize HashAggregate
+ Output: c2, c2, sum(c1)
+ Group Key: mpp_ft2.c2, mpp_ft2.c2
+ -> Redistribute Motion 2:3 (slice2; segments: 2)
+ Output: c2, c2, (PARTIAL sum(c1))
+ Hash Key: c2, c2
+ -> Foreign Scan
+ Output: c2, c2, (PARTIAL sum(c1))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT c2, c2, sum(c1) FROM "MPP_S 1"."T 2" WHERE ((c2 > 6)) GROUP BY 1, 2
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(18 rows)
+
+SELECT c2, c2 FROM mpp_ft2 WHERE c2 > 6 GROUP BY 1, 2 ORDER BY sum(c1);
+ c2 | c2
+----+----
+ 7 | 7
+ 8 | 8
+ 9 | 9
+(3 rows)
+
+-- Testing HAVING clause
+-- It's unsafe for partial agg to push down HAVING clause.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c2, sum(c1) FROM mpp_ft2 GROUP BY c2 HAVING avg(c1) < 500 AND sum(c1) < 49800 ORDER BY c2;
+ QUERY PLAN
+------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: c2, (sum(c1))
+ Merge Key: c2
+ -> Sort
+ Output: c2, (sum(c1))
+ Sort Key: mpp_ft2.c2
+ -> Finalize HashAggregate
+ Output: c2, sum(c1)
+ Group Key: mpp_ft2.c2
+ Filter: ((avg(mpp_ft2.c1) < '500'::numeric) AND (sum(mpp_ft2.c1) < 49800))
+ -> Redistribute Motion 2:3 (slice2; segments: 2)
+ Output: c2, (PARTIAL sum(c1)), (PARTIAL avg(c1))
+ Hash Key: c2
+ -> Partial HashAggregate
+ Output: c2, PARTIAL sum(c1), PARTIAL avg(c1)
+ Group Key: mpp_ft2.c2
+ -> Foreign Scan on public.mpp_ft2
+ Output: c1, c2, c3, c4, c5, c6, c7
+ Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2"
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(21 rows)
+
+SELECT c2, sum(c1) FROM mpp_ft2 GROUP BY c2 HAVING avg(c1) < 500 AND sum(c1) < 49800 ORDER BY c2;
+ c2 | sum
+----+-------
+ 1 | 49600
+ 2 | 49700
+(2 rows)
+
+-- Remote aggregate in combination with a local Param (for the output
+-- of an initplan) can be trouble, per bug #15781
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT exists(SELECT 1 FROM pg_aggregate), sum(c1) FROM mpp_ft2;
+ QUERY PLAN
+--------------------------------------------------------------------
+ Finalize Aggregate
+ Output: $0, sum(mpp_ft2.c1)
+ InitPlan 1 (returns $0) (slice2)
+ -> Seq Scan on pg_catalog.pg_aggregate
+ Output: 1
+ -> Gather Motion 2:1 (slice1; segments: 2)
+ Output: (PARTIAL sum(mpp_ft2.c1))
+ -> Foreign Scan
+ Output: (PARTIAL sum(mpp_ft2.c1))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT sum(c1) FROM "MPP_S 1"."T 2"
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(13 rows)
+
+SELECT exists(SELECT 1 FROM pg_aggregate), sum(c1) FROM mpp_ft2;
+ exists | sum
+--------+--------
+ t | 500500
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT exists(SELECT 1 FROM pg_aggregate), sum(c1) FROM mpp_ft2 group by 1;
+ QUERY PLAN
+--------------------------------------------------------------------
+ Finalize GroupAggregate
+ Output: ($0), sum(mpp_ft2.c1)
+ Group Key: ($0)
+ InitPlan 1 (returns $0) (slice2)
+ -> Seq Scan on pg_catalog.pg_aggregate
+ Output: 1
+ -> Gather Motion 2:1 (slice1; segments: 2)
+ Output: ($0), (PARTIAL sum(mpp_ft2.c1))
+ -> Partial GroupAggregate
+ Output: ($0), PARTIAL sum(mpp_ft2.c1)
+ Group Key: $0
+ -> Foreign Scan on public.mpp_ft2
+ Output: $0, mpp_ft2.c1
+ Remote SQL: SELECT c1 FROM "MPP_S 1"."T 2"
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(16 rows)
+
+SELECT exists(SELECT 1 FROM pg_aggregate), sum(c1) FROM mpp_ft2 group by 1;
+ exists | sum
+--------+--------
+ t | 500500
+(1 row)
+
+-- Testing ORDER BY, DISTINCT, FILTER within aggregates
+-- ORDER BY within aggregate, same column used to order
+-- TODO: Now we don't support array_agg mpp pushdown.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT array_agg(c1 ORDER BY c1) FROM mpp_ft2 WHERE c1 < 100 GROUP BY c2 ORDER BY 1;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------
+ Sort
+ Output: (array_agg(c1 ORDER BY c1)), c2
+ Sort Key: (array_agg(mpp_ft2.c1 ORDER BY mpp_ft2.c1))
+ -> GroupAggregate
+ Output: array_agg(c1 ORDER BY c1), c2
+ Group Key: mpp_ft2.c2
+ -> Gather Motion 2:1 (slice1; segments: 2)
+ Output: c2, c1
+ Merge Key: c2
+ -> Foreign Scan on public.mpp_ft2
+ Output: c2, c1
+ Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2" WHERE ((c1 < 100)) ORDER BY c2 ASC NULLS LAST
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(14 rows)
+
+SELECT array_agg(c1 ORDER BY c1) FROM mpp_ft2 WHERE c1 < 100 GROUP BY c2 ORDER BY 1;
+ array_agg
+--------------------------------
+ {1,11,21,31,41,51,61,71,81,91}
+ {2,12,22,32,42,52,62,72,82,92}
+ {3,13,23,33,43,53,63,73,83,93}
+ {4,14,24,34,44,54,64,74,84,94}
+ {5,15,25,35,45,55,65,75,85,95}
+ {6,16,26,36,46,56,66,76,86,96}
+ {7,17,27,37,47,57,67,77,87,97}
+ {8,18,28,38,48,58,68,78,88,98}
+ {9,19,29,39,49,59,69,79,89,99}
+ {10,20,30,40,50,60,70,80,90}
+(10 rows)
+
+-- FILTER within aggregate
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(c1) FILTER (WHERE c1 < 100 AND c2 > 5) FROM mpp_ft2 GROUP BY c2 ORDER BY 1 nulls last;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(c1) FILTER (WHERE ((c1 < 100) AND (c2 > 5)))), c2
+ Merge Key: (sum(c1) FILTER (WHERE ((c1 < 100) AND (c2 > 5))))
+ -> Sort
+ Output: (sum(c1) FILTER (WHERE ((c1 < 100) AND (c2 > 5)))), c2
+ Sort Key: (sum(mpp_ft2.c1) FILTER (WHERE ((mpp_ft2.c1 < 100) AND (mpp_ft2.c2 > 5))))
+ -> Finalize HashAggregate
+ Output: sum(c1) FILTER (WHERE ((c1 < 100) AND (c2 > 5))), c2
+ Group Key: mpp_ft2.c2
+ -> Redistribute Motion 2:3 (slice2; segments: 2)
+ Output: c2, (PARTIAL sum(c1) FILTER (WHERE ((c1 < 100) AND (c2 > 5))))
+ Hash Key: c2
+ -> Foreign Scan
+ Output: c2, (PARTIAL sum(c1) FILTER (WHERE ((c1 < 100) AND (c2 > 5))))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT c2, sum(c1) FILTER (WHERE ((c1 < 100) AND (c2 > 5))) FROM "MPP_S 1"."T 2" GROUP BY 1
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(18 rows)
+
+SELECT sum(c1) FILTER (WHERE c1 < 100 AND c2 > 5) FROM mpp_ft2 GROUP BY c2 ORDER BY 1 nulls last;
+ sum
+-----
+ 510
+ 520
+ 530
+ 540
+
+
+
+
+
+
+(10 rows)
+
+-- DISTINCT, ORDER BY and FILTER within aggregate
+-- It's unsafe to push down DISTINCT within aggregates when there are multiple remote servers.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(c1%3), sum(DISTINCT c1%3 ORDER BY c1%3) FILTER (WHERE c1%3 < 2), c2 FROM mpp_ft2 WHERE c2 = 6 GROUP BY c2;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum((c1 % 3))), (sum(DISTINCT (c1 % 3) ORDER BY (c1 % 3)) FILTER (WHERE ((c1 % 3) < 2))), c2
+ -> GroupAggregate
+ Output: sum((c1 % 3)), sum(DISTINCT (c1 % 3) ORDER BY (c1 % 3)) FILTER (WHERE ((c1 % 3) < 2)), c2
+ Group Key: mpp_ft2.c2
+ -> Redistribute Motion 2:3 (slice2; segments: 2)
+ Output: c2, c1
+ Hash Key: c2
+ -> Foreign Scan on public.mpp_ft2
+ Output: c2, c1
+ Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2" WHERE ((c2 = 6))
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(13 rows)
+
+SELECT sum(c1%3), sum(DISTINCT c1%3 ORDER BY c1%3) FILTER (WHERE c1%3 < 2), c2 FROM mpp_ft2 WHERE c2 = 6 GROUP BY c2;
+ sum | sum | c2
+-----+-----+----
+ 99 | 1 | 6
+(1 row)
+
+-- Aggregate not pushed down as FILTER condition is not pushable
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(c1) FILTER (WHERE (c1 / c1) * random() <= 1) FROM mpp_ft2 GROUP BY c2 ORDER BY 1;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(c1) FILTER (WHERE ((((c1 / c1))::double precision * random()) <= '1'::double precision))), c2
+ Merge Key: (sum(c1) FILTER (WHERE ((((c1 / c1))::double precision * random()) <= '1'::double precision)))
+ -> Sort
+ Output: (sum(c1) FILTER (WHERE ((((c1 / c1))::double precision * random()) <= '1'::double precision))), c2
+ Sort Key: (sum(mpp_ft2.c1) FILTER (WHERE ((((mpp_ft2.c1 / mpp_ft2.c1))::double precision * random()) <= '1'::double precision)))
+ -> Finalize HashAggregate
+ Output: sum(c1) FILTER (WHERE ((((c1 / c1))::double precision * random()) <= '1'::double precision)), c2
+ Group Key: mpp_ft2.c2
+ -> Redistribute Motion 2:3 (slice2; segments: 2)
+ Output: c2, (PARTIAL sum(c1) FILTER (WHERE ((((c1 / c1))::double precision * random()) <= '1'::double precision)))
+ Hash Key: c2
+ -> Partial HashAggregate
+ Output: c2, PARTIAL sum(c1) FILTER (WHERE ((((c1 / c1))::double precision * random()) <= '1'::double precision))
+ Group Key: mpp_ft2.c2
+ -> Foreign Scan on public.mpp_ft2
+ Output: c1, c2, c3, c4, c5, c6, c7
+ Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2"
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(20 rows)
+
+SELECT sum(c1) FILTER (WHERE (c1 / c1) * random() <= 1) FROM mpp_ft2 GROUP BY c2 ORDER BY 1;
+ sum
+-------
+ 49600
+ 49700
+ 49800
+ 49900
+ 50000
+ 50100
+ 50200
+ 50300
+ 50400
+ 50500
+(10 rows)
+
+-- Set use_remote_estimate to true
+ALTER FOREIGN TABLE mpp_ft2 OPTIONS(add use_remote_estimate 'true');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT min(c5) FROM mpp_ft2;
+ QUERY PLAN
+--------------------------------------------------------------------
+ Finalize Aggregate
+ Output: min(c5)
+ -> Gather Motion 2:1 (slice1; segments: 2)
+ Output: (PARTIAL min(c5))
+ -> Foreign Scan
+ Output: (PARTIAL min(c5))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT min(c5) FROM "MPP_S 1"."T 2"
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(10 rows)
+
+SELECT min(c5) FROM mpp_ft2;
+ min
+-------
+ 0.001
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(c1), max(c6) FROM mpp_ft2 GROUP BY c2;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Finalize GroupAggregate
+ Output: count(c1), max(c6), c2
+ Group Key: mpp_ft2.c2
+ -> Gather Motion 2:1 (slice1; segments: 2)
+ Output: c2, (PARTIAL count(c1)), (PARTIAL max(c6))
+ Merge Key: c2
+ -> Sort
+ Output: c2, (PARTIAL count(c1)), (PARTIAL max(c6))
+ Sort Key: mpp_ft2.c2
+ -> Foreign Scan
+ Output: c2, (PARTIAL count(c1)), (PARTIAL max(c6))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT c2, count(c1), max(c6) FROM "MPP_S 1"."T 2" GROUP BY 1
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(15 rows)
+
+SELECT count(c1), max(c6) FROM mpp_ft2 GROUP BY c2;
+ count | max
+-------+-------
+ 100 | 1
+ 100 | 0.991
+ 100 | 0.992
+ 100 | 0.993
+ 100 | 0.994
+ 100 | 0.995
+ 100 | 0.996
+ 100 | 0.997
+ 100 | 0.998
+ 100 | 0.999
+(10 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(c1), sum(c3), avg(c4), min(c5), max(c6), count(c1) * (random() <= 1)::int as count2 FROM mpp_ft2 GROUP BY c2 ORDER BY c2;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------
+ Finalize GroupAggregate
+ Output: count(c1), sum(c3), avg(c4), min(c5), max(c6), (count(c1) * ((random() <= '1'::double precision))::integer), c2
+ Group Key: mpp_ft2.c2
+ -> Gather Motion 2:1 (slice1; segments: 2)
+ Output: c2, (PARTIAL count(c1)), (PARTIAL sum(c3)), (PARTIAL avg(c4)), (PARTIAL min(c5)), (PARTIAL max(c6))
+ Merge Key: c2
+ -> Sort
+ Output: c2, (PARTIAL count(c1)), (PARTIAL sum(c3)), (PARTIAL avg(c4)), (PARTIAL min(c5)), (PARTIAL max(c6))
+ Sort Key: mpp_ft2.c2
+ -> Foreign Scan
+ Output: c2, (PARTIAL count(c1)), (PARTIAL sum(c3)), (PARTIAL avg(c4)), (PARTIAL min(c5)), (PARTIAL max(c6))
+ Relations: Aggregate on (public.mpp_ft2)
+ Remote SQL: SELECT c2, count(c1), sum(c3), array[count(c4), sum(c4)], min(c5), max(c6) FROM "MPP_S 1"."T 2" GROUP BY 1
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(15 rows)
+
+SELECT count(c1), sum(c3), avg(c4), min(c5), max(c6), count(c1) * (random() <= 1)::int as count2 FROM mpp_ft2 GROUP BY c2 ORDER BY c2;
+ count | sum | avg | min | max | count2
+-------+-------+----------------------+-------+-------+--------
+ 100 | 50500 | 505.0000000000000000 | 0.01 | 1 | 100
+ 100 | 49600 | 496.0000000000000000 | 0.001 | 0.991 | 100
+ 100 | 49700 | 497.0000000000000000 | 0.002 | 0.992 | 100
+ 100 | 49800 | 498.0000000000000000 | 0.003 | 0.993 | 100
+ 100 | 49900 | 499.0000000000000000 | 0.004 | 0.994 | 100
+ 100 | 50000 | 500.0000000000000000 | 0.005 | 0.995 | 100
+ 100 | 50100 | 501.0000000000000000 | 0.006 | 0.996 | 100
+ 100 | 50200 | 502.0000000000000000 | 0.007 | 0.997 | 100
+ 100 | 50300 | 503.0000000000000000 | 0.008 | 0.998 | 100
+ 100 | 50400 | 504.0000000000000000 | 0.009 | 0.999 | 100
+(10 rows)
+
+ALTER FOREIGN TABLE mpp_ft2 OPTIONS(set use_remote_estimate 'false');
+-- ===================================================================
+-- Queries with LIMIT/OFFSET clauses
+-- ===================================================================
+-- Simple query with LIMIT clause is pushed down.
+EXPLAIN VERBOSE
+SELECT c1, c2 FROM mpp_ft2 order by c1 limit 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------
+ Limit (cost=100.00..100.10 rows=3 width=8)
+ Output: c1, c2
+ -> Gather Motion 2:1 (slice1; segments: 2) (cost=100.00..100.19 rows=6 width=8)
+ Output: c1, c2
+ Merge Key: c1
+ -> Foreign Scan on public.mpp_ft2 (cost=100.00..100.10 rows=3 width=8)
+ Output: c1, c2
+ Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2" ORDER BY c1 ASC NULLS LAST LIMIT 3::bigint
+ Optimizer: Postgres-based planner
+ Settings: gp_enable_minmax_optimization = 'off'
+(10 rows)
+
+SELECT c1, c2 FROM mpp_ft2 order by c1 limit 3;
+ c1 | c2
+----+----
+ 1 | 1
+ 2 | 2
+ 3 | 3
+(3 rows)
+
+-- Simple query with OFFSET and LIMIT clause together is pushed down.
+EXPLAIN VERBOSE
+SELECT c1, c2 FROM mpp_ft2 order by c1 offset 2 limit 3;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
+ Limit (cost=100.06..100.16 rows=3 width=8)
+ Output: c1, c2
+ -> Gather Motion 2:1 (slice1; segments: 2) (cost=100.00..100.32 rows=10 width=8)
+ Output: c1, c2
+ Merge Key: c1
+ -> Foreign Scan on public.mpp_ft2 (cost=100.00..100.17 rows=5 width=8)
+ Output: c1, c2
+ Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2" ORDER BY c1 ASC NULLS LAST LIMIT (2::bigint + 3::bigint)
+ Optimizer: Postgres-based planner
+ Settings: gp_enable_minmax_optimization = 'off'
+(10 rows)
+
+SELECT c1, c2 FROM mpp_ft2 order by c1 offset 2 limit 3;
+ c1 | c2
+----+----
+ 3 | 3
+ 4 | 4
+ 5 | 5
+(3 rows)
+
+-- Simple query with only OFFSET clause is NOT pushed down.
+EXPLAIN VERBOSE
+SELECT c1, c2 FROM mpp_ft2 order by c1 offset 998;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
+ Limit (cost=132.62..2421.00 rows=70002 width=8)
+ Output: c1, c2
+ -> Gather Motion 2:1 (slice1; segments: 2) (cost=100.00..2421.00 rows=71000 width=8)
+ Output: c1, c2
+ Merge Key: c1
+ -> Foreign Scan on public.mpp_ft2 (cost=100.00..1356.00 rows=35500 width=8)
+ Output: c1, c2
+ Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2" ORDER BY c1 ASC NULLS LAST
+ Optimizer: Postgres-based planner
+ Settings: gp_enable_minmax_optimization = 'off'
+(10 rows)
+
+SELECT c1, c2 FROM mpp_ft2 order by c1 offset 998;
+ c1 | c2
+------+----
+ 999 | 9
+ 1000 | 0
+(2 rows)
+
+-- Query with aggregates and limit clause together is NOT pushed down.
+-- Because it's unsafe to do partial aggregate and limit in multiple remote servers.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(c1), max(c6) FROM mpp_ft2 GROUP BY c2 order by c2 limit 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------
+ Limit
+ Output: (count(c1)), (max(c6)), c2
+ -> Finalize GroupAggregate
+ Output: count(c1), max(c6), c2
+ Group Key: mpp_ft2.c2
+ -> Gather Motion 2:1 (slice1; segments: 2)
+ Output: c2, (PARTIAL count(c1)), (PARTIAL max(c6))
+ Merge Key: c2
+ -> Partial GroupAggregate
+ Output: c2, PARTIAL count(c1), PARTIAL max(c6)
+ Group Key: mpp_ft2.c2
+ -> Foreign Scan on public.mpp_ft2
+ Output: c1, c2, c3, c4, c5, c6, c7
+ Remote SQL: SELECT c1, c2, c6 FROM "MPP_S 1"."T 2" ORDER BY c2 ASC NULLS LAST
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off'
+(16 rows)
+
+SELECT count(c1), max(c6) FROM mpp_ft2 GROUP BY c2 order by c2 limit 3;
+ count | max
+-------+-------
+ 100 | 1
+ 100 | 0.991
+ 100 | 0.992
+(3 rows)
+
+-- ===================================================================
+-- Queries with JOIN
+-- ===================================================================
+-- join is not safe to pushed down when there are multiple remote servers
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(*), sum(t1.c1), avg(t2.c2) FROM mpp_ft2 t1 inner join mpp_ft2 t2 on (t1.c1 = t2.c1) where t1.c1 = 2;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: count(*), sum(t1.c1), avg(t2.c2)
+ -> Gather Motion 2:1 (slice1; segments: 2)
+ Output: (PARTIAL count(*)), (PARTIAL sum(t1.c1)), (PARTIAL avg(t2.c2))
+ -> Partial Aggregate
+ Output: PARTIAL count(*), PARTIAL sum(t1.c1), PARTIAL avg(t2.c2)
+ -> Nested Loop
+ Output: t1.c1, t2.c2
+ -> Broadcast Motion 2:2 (slice2; segments: 2)
+ Output: t1.c1
+ -> Foreign Scan on public.mpp_ft2 t1
+ Output: t1.c1
+ Remote SQL: SELECT c1 FROM "MPP_S 1"."T 2" WHERE ((c1 = 2))
+ -> Materialize
+ Output: t2.c2, t2.c1
+ -> Foreign Scan on public.mpp_ft2 t2
+ Output: t2.c2, t2.c1
+ Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2" WHERE ((c1 = 2))
+ Optimizer: Postgres query optimizer
+ Settings: gp_enable_minmax_optimization = 'off', optimizer = 'off'
+(20 rows)
+
+SELECT count(*), sum(t1.c1), avg(t2.c2) FROM mpp_ft2 t1 inner join mpp_ft2 t2 on (t1.c1 = t2.c1) where t1.c1 = 2;
+ count | sum | avg
+-------+-----+--------------------
+ 1 | 2 | 2.0000000000000000
+(1 row)
+
diff --git a/contrib/postgres_fdw/postgres_clean.bash b/contrib/postgres_fdw/postgres_clean.bash
new file mode 100755
index 0000000000..511ffcfc98
--- /dev/null
+++ b/contrib/postgres_fdw/postgres_clean.bash
@@ -0,0 +1,9 @@
+#!/usr/bin/env bash
+if [ -d "testdata/pgdata" ] && [ -d "testdata/pgsql" ] ; then
+ pgbin="testdata/pgsql"
+ ${pgbin}/bin/pg_ctl -D testdata/pgdata stop || true
+ ${pgbin}/bin/pg_ctl -D testdata/pgdata2 -o "-p 5555" stop || true
+ rm -rf testdata/pgdata
+ rm -rf testdata/pgdata2
+fi
+rm -rf testdata/pglog
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 0debe55b17..f0411a78c2 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -6872,6 +6872,29 @@ add_foreign_final_paths(PlannerInfo *root, RelOptInfo *input_rel,
fpextra->count_est = extra->count_est;
fpextra->offset_est = extra->offset_est;
+ /* If mpp_execute = 'all segments', we need to adjust origin count_est and offset_est. */
+ if (final_rel->exec_location == FTEXECLOCATION_ALL_SEGMENTS && fpextra->offset_est > 0)
+ {
+ if (fpextra->count_est > 0)
+ {
+ /*
+ * When both OFFSET and LIMIT clause are specified,
+ * we need to fetch tuples from 0 to limitOffset + limitCount from remote servers.
+ */
+ fpextra->count_est += fpextra->offset_est;
+ }
+ else
+ {
+ /*
+ * When only OFFSET clasue is specified,
+ * we need to fetch all tuples from remote servers.
+ */
+ fpextra->count_est = 0;
+ }
+
+ fpextra->offset_est = 0;
+ }
+
/*
* Estimate the costs of performing the final sort and the LIMIT
* restriction remotely. If has_final_sort is false, we wouldn't need to
diff --git a/contrib/postgres_fdw/sql/mpp_gp2pg_postgres_fdw.sql b/contrib/postgres_fdw/sql/mpp_gp2pg_postgres_fdw.sql
new file mode 100644
index 0000000000..2ebb297c7d
--- /dev/null
+++ b/contrib/postgres_fdw/sql/mpp_gp2pg_postgres_fdw.sql
@@ -0,0 +1,248 @@
+-- This file is used to test the feature that there are multiple remote postgres servers.
+-- ===================================================================
+-- create FDW objects
+-- ===================================================================
+SET timezone = 'PST8PDT';
+-- If gp_enable_minmax_optimization is on, it won't generate aggregate functions pushdown plan.
+SET gp_enable_minmax_optimization = off;
+
+-- Clean
+-- start_ignore
+DROP EXTENSION IF EXISTS postgres_fdw CASCADE;
+-- end_ignore
+
+CREATE EXTENSION postgres_fdw;
+
+CREATE SERVER pgserver FOREIGN DATA WRAPPER postgres_fdw
+ OPTIONS (host 'dummy', port '0',
+ dbname 'contrib_regression', multi_hosts 'localhost localhost',
+ multi_ports '5432 5555', num_segments '2', mpp_execute 'all segments');
+
+CREATE USER MAPPING FOR CURRENT_USER SERVER pgserver;
+
+-- ===================================================================
+-- create objects used through FDW pgserver server
+-- ===================================================================
+-- remote postgres server 1 -- listening port 5432
+\! env PGOPTIONS='' psql -p 5432 contrib_regression -f sql/postgres_sql/mpp_gp2pg_postgres_init_1.sql
+-- remote postgres server 2 -- listening port 5555
+\! env PGOPTIONS='' psql -p 5555 contrib_regression -f sql/postgres_sql/mpp_gp2pg_postgres_init_2.sql
+
+-- ===================================================================
+-- create foreign tables
+-- ===================================================================
+CREATE FOREIGN TABLE mpp_ft1 (
+ c1 int,
+ c2 int
+) SERVER pgserver OPTIONS (schema_name 'MPP_S 1', table_name 'T 1');
+
+CREATE FOREIGN TABLE mpp_ft2 (
+ c1 int,
+ c2 int,
+ c3 smallint,
+ c4 bigint,
+ c5 real,
+ c6 double precision,
+ c7 numeric
+) SERVER pgserver OPTIONS (schema_name 'MPP_S 1', table_name 'T 2');
+
+-- ===================================================================
+-- tests for validator
+-- ===================================================================
+-- Error when the length of option multi_hosts and multi_ports is NOT same.
+CREATE SERVER testserver FOREIGN DATA WRAPPER postgres_fdw
+ OPTIONS (dbname 'contrib_regression', multi_hosts 'localhost localhost',
+ multi_ports '5432', num_segments '2', mpp_execute 'all segments');
+-- Error when specifying option multi_hosts and multi_ports but option mpp_execute is NOT 'all segments'.
+CREATE FOREIGN TABLE mpp_test (
+ c1 int,
+ c2 int
+) SERVER pgserver OPTIONS (schema_name 'MPP_S 1', table_name 'T 1', mpp_execute 'coordinator');
+SELECT * FROM mpp_test;
+ALTER FOREIGN TABLE mpp_test OPTIONS (drop mpp_execute);
+-- Error when the value of option num_segments is NOT same as the length of option multi_hosts and multi_ports.
+ALTER SERVER pgserver OPTIONS (set num_segments '1');
+SELECT * FROM mpp_test;
+ALTER SERVER pgserver OPTIONS (set num_segments '2');
+-- ===================================================================
+-- Simple queries
+-- ===================================================================
+EXPLAIN VERBOSE SELECT * FROM mpp_ft1 ORDER BY c1;
+SELECT * FROM mpp_ft1 ORDER BY c1;
+ANALYZE mpp_ft1;
+
+ALTER FOREIGN TABLE mpp_ft1 OPTIONS (add use_remote_estimate 'true');
+EXPLAIN VERBOSE SELECT * FROM mpp_ft1 ORDER BY c1;
+SELECT * FROM mpp_ft1 ORDER BY c1;
+ALTER FOREIGN TABLE mpp_ft1 OPTIONS (drop use_remote_estimate);
+
+-- ===================================================================
+-- When there are multiple remote servers, we don't support IMPORT FOREIGN SCHEMA
+-- ===================================================================
+CREATE SCHEMA mpp_import_dest;
+IMPORT FOREIGN SCHEMA import_source FROM SERVER pgserver INTO mpp_import_dest;
+
+-- ===================================================================
+-- When there are multiple remote servers, we don't support INSERT/UPDATE/DELETE
+-- ===================================================================
+INSERT INTO mpp_ft1 VALUES (1, 1);
+
+UPDATE mpp_ft1 SET c1 = c1 + 1;
+
+DELETE FROM mpp_ft1;
+
+-- ===================================================================
+-- Aggregate and grouping queries
+-- ===================================================================
+-- Simple aggregates with different data types
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(c1), count(c3), count(c4), count(c5), count(c6), count(c7) FROM mpp_ft2;
+SELECT count(c1), count(c3), count(c4), count(c5), count(c6), count(c7) FROM mpp_ft2;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(c1), sum(c3), sum(c4), sum(c5), sum(c6), sum(c7) FROM mpp_ft2;
+SELECT sum(c1), sum(c3), sum(c4), sum(c5), sum(c6), sum(c7) FROM mpp_ft2;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(c1), avg(c3), avg(c4), avg(c5), avg(c6), avg(c7) FROM mpp_ft2;
+SELECT avg(c1), avg(c3), avg(c4), avg(c5), avg(c6), avg(c7) FROM mpp_ft2;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT min(c1), min(c3), min(c4), min(c5), min(c6), min(c7) FROM mpp_ft2;
+SELECT min(c1), min(c3), min(c4), min(c5), min(c6), min(c7) FROM mpp_ft2;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT max(c1), max(c3), max(c4), max(c5), max(c6), max(c7) FROM mpp_ft2;
+SELECT max(c1), max(c3), max(c4), max(c5), max(c6), max(c7) FROM mpp_ft2;
+
+-- Simple Aggregates with GROUP BY clause
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(c1), sum(c3), avg(c4), min(c5), max(c6), count(c1) * (random() <= 1)::int as count2 FROM mpp_ft2 GROUP BY c2 ORDER BY c2;
+SELECT count(c1), sum(c3), avg(c4), min(c5), max(c6), count(c1) * (random() <= 1)::int as count2 FROM mpp_ft2 GROUP BY c2 ORDER BY c2;
+
+-- Aggregate is not pushed down as aggregation contains random()
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(c1 * (random() <= 1)::int) as sum, avg(c1) FROM mpp_ft2;
+SELECT sum(c1 * (random() <= 1)::int) as sum, avg(c1) FROM mpp_ft2;
+
+-- GROUP BY clause having expressions
+/* FIXME: Aggregates are not pushed down.
+ Because for Remote SQL of partial agg, non-grouping columns
+ might neither appear in the GROUP BY clause nor be used in
+ an aggregate function.
+ This is unsafe to make foreign grouping.
+*/
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c2/2, sum(c2) * (c2/2) FROM mpp_ft2 GROUP BY c2/2 ORDER BY c2/2;
+SELECT c2/2, sum(c2) * (c2/2) FROM mpp_ft2 GROUP BY c2/2 ORDER BY c2/2;
+
+-- Aggregates in subquery are pushed down.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(x.a), sum(x.a) FROM (SELECT c2 a, sum(c1) b FROM mpp_ft2 GROUP BY c2, sqrt(c1) ORDER BY 1, 2) x;
+SELECT count(x.a), sum(x.a) FROM (SELECT c2 a, sum(c1) b FROM mpp_ft2 GROUP BY c2, sqrt(c1) ORDER BY 1, 2) x;
+
+-- Aggregate is still pushed down by taking unshippable expression out
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c2 * (random() <= 1)::int as sum1, sum(c1) * c2 as sum2 FROM mpp_ft2 GROUP BY c2 ORDER BY 1, 2;
+SELECT c2 * (random() <= 1)::int as sum1, sum(c1) * c2 as sum2 FROM mpp_ft2 GROUP BY c2 ORDER BY 1, 2;
+
+-- Aggregate with unshippable GROUP BY clause are not pushed
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c2 * (random() <= 1)::int as c2 FROM mpp_ft2 GROUP BY c2 * (random() <= 1)::int ORDER BY 1;
+SELECT c2 * (random() <= 1)::int as c2 FROM mpp_ft2 GROUP BY c2 * (random() <= 1)::int ORDER BY 1;
+
+-- GROUP BY clause in various forms, cardinal, alias and constant expression
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(c2) w, c2 x, 5 y, 7.0 z FROM mpp_ft2 GROUP BY 2, y, 9.0::int ORDER BY 2;
+SELECT count(c2) w, c2 x, 5 y, 7.0 z FROM mpp_ft2 GROUP BY 2, y, 9.0::int ORDER BY 2;
+
+-- GROUP BY clause referring to same column multiple times
+-- Also, ORDER BY contains an aggregate function
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c2, c2 FROM mpp_ft2 WHERE c2 > 6 GROUP BY 1, 2 ORDER BY sum(c1);
+SELECT c2, c2 FROM mpp_ft2 WHERE c2 > 6 GROUP BY 1, 2 ORDER BY sum(c1);
+
+-- Testing HAVING clause
+-- It's unsafe for partial agg to push down HAVING clause.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c2, sum(c1) FROM mpp_ft2 GROUP BY c2 HAVING avg(c1) < 500 AND sum(c1) < 49800 ORDER BY c2;
+SELECT c2, sum(c1) FROM mpp_ft2 GROUP BY c2 HAVING avg(c1) < 500 AND sum(c1) < 49800 ORDER BY c2;
+
+-- Remote aggregate in combination with a local Param (for the output
+-- of an initplan) can be trouble, per bug #15781
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT exists(SELECT 1 FROM pg_aggregate), sum(c1) FROM mpp_ft2;
+SELECT exists(SELECT 1 FROM pg_aggregate), sum(c1) FROM mpp_ft2;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT exists(SELECT 1 FROM pg_aggregate), sum(c1) FROM mpp_ft2 group by 1;
+SELECT exists(SELECT 1 FROM pg_aggregate), sum(c1) FROM mpp_ft2 group by 1;
+
+-- Testing ORDER BY, DISTINCT, FILTER within aggregates
+-- ORDER BY within aggregate, same column used to order
+-- TODO: Now we don't support array_agg mpp pushdown.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT array_agg(c1 ORDER BY c1) FROM mpp_ft2 WHERE c1 < 100 GROUP BY c2 ORDER BY 1;
+SELECT array_agg(c1 ORDER BY c1) FROM mpp_ft2 WHERE c1 < 100 GROUP BY c2 ORDER BY 1;
+
+-- FILTER within aggregate
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(c1) FILTER (WHERE c1 < 100 AND c2 > 5) FROM mpp_ft2 GROUP BY c2 ORDER BY 1 nulls last;
+SELECT sum(c1) FILTER (WHERE c1 < 100 AND c2 > 5) FROM mpp_ft2 GROUP BY c2 ORDER BY 1 nulls last;
+
+-- DISTINCT, ORDER BY and FILTER within aggregate
+-- It's unsafe to push down DISTINCT within aggregates when there are multiple remote servers.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(c1%3), sum(DISTINCT c1%3 ORDER BY c1%3) FILTER (WHERE c1%3 < 2), c2 FROM mpp_ft2 WHERE c2 = 6 GROUP BY c2;
+SELECT sum(c1%3), sum(DISTINCT c1%3 ORDER BY c1%3) FILTER (WHERE c1%3 < 2), c2 FROM mpp_ft2 WHERE c2 = 6 GROUP BY c2;
+
+-- Aggregate not pushed down as FILTER condition is not pushable
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(c1) FILTER (WHERE (c1 / c1) * random() <= 1) FROM mpp_ft2 GROUP BY c2 ORDER BY 1;
+SELECT sum(c1) FILTER (WHERE (c1 / c1) * random() <= 1) FROM mpp_ft2 GROUP BY c2 ORDER BY 1;
+
+-- Set use_remote_estimate to true
+ALTER FOREIGN TABLE mpp_ft2 OPTIONS(add use_remote_estimate 'true');
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT min(c5) FROM mpp_ft2;
+SELECT min(c5) FROM mpp_ft2;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(c1), max(c6) FROM mpp_ft2 GROUP BY c2;
+SELECT count(c1), max(c6) FROM mpp_ft2 GROUP BY c2;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(c1), sum(c3), avg(c4), min(c5), max(c6), count(c1) * (random() <= 1)::int as count2 FROM mpp_ft2 GROUP BY c2 ORDER BY c2;
+SELECT count(c1), sum(c3), avg(c4), min(c5), max(c6), count(c1) * (random() <= 1)::int as count2 FROM mpp_ft2 GROUP BY c2 ORDER BY c2;
+
+ALTER FOREIGN TABLE mpp_ft2 OPTIONS(set use_remote_estimate 'false');
+
+-- ===================================================================
+-- Queries with LIMIT/OFFSET clauses
+-- ===================================================================
+-- Simple query with LIMIT clause is pushed down.
+EXPLAIN VERBOSE
+SELECT c1, c2 FROM mpp_ft2 order by c1 limit 3;
+SELECT c1, c2 FROM mpp_ft2 order by c1 limit 3;
+-- Simple query with OFFSET and LIMIT clause together is pushed down.
+EXPLAIN VERBOSE
+SELECT c1, c2 FROM mpp_ft2 order by c1 offset 2 limit 3;
+SELECT c1, c2 FROM mpp_ft2 order by c1 offset 2 limit 3;
+-- Simple query with only OFFSET clause is NOT pushed down.
+EXPLAIN VERBOSE
+SELECT c1, c2 FROM mpp_ft2 order by c1 offset 998;
+SELECT c1, c2 FROM mpp_ft2 order by c1 offset 998;
+-- Query with aggregates and limit clause together is NOT pushed down.
+-- Because it's unsafe to do partial aggregate and limit in multiple remote servers.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(c1), max(c6) FROM mpp_ft2 GROUP BY c2 order by c2 limit 3;
+SELECT count(c1), max(c6) FROM mpp_ft2 GROUP BY c2 order by c2 limit 3;
+
+-- ===================================================================
+-- Queries with JOIN
+-- ===================================================================
+-- join is not safe to pushed down when there are multiple remote servers
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(*), sum(t1.c1), avg(t2.c2) FROM mpp_ft2 t1 inner join mpp_ft2 t2 on (t1.c1 = t2.c1) where t1.c1 = 2;
+SELECT count(*), sum(t1.c1), avg(t2.c2) FROM mpp_ft2 t1 inner join mpp_ft2 t2 on (t1.c1 = t2.c1) where t1.c1 = 2;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
(cloudberry) 09/10: Default to log_checkpoints=on, log_autovacuum_min_duration=10m
Posted by av...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
avamingli pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit e3bbfda9a88113581e464e4611e354d4ef80015f
Author: Soumyadeep Chakraborty <so...@gmail.com>
AuthorDate: Wed Apr 3 10:28:40 2024 -0700
Default to log_checkpoints=on, log_autovacuum_min_duration=10m
Backported from upstream commit:
64da07c41a8c0a680460cdafc79093736332b6cf
with one important difference: we didn't absorb the
log_autovacuum_min_duration change. This is because in GPDB, autovacuum
driven autoanalyze can take longer due to the size of GPDB tables (and
also analyze is dispatched from the QD). So, we decide to be
conservative about that change.
Original commit message follows:
The idea here is that when a performance problem is known to have
occurred at a certain point in time, it's a good thing if there is
some information available from the logs to help figure out what
might have happened around that time.
This change attracted an above-average amount of dissent, because
it means that a server with default settings will produce some amount
of log output even if nothing has gone wrong. However, by my count,
the mailing list discussion had about twice as many people in favor
of the change as opposed. The reasons for believing that the extra
log output is not an issue in practice are: (1) the rate at which
messages can be generated by this setting is bounded to one every
few minutes on a properly-configured system and (2) production
systems tend to have a lot more junk in the log from that due to
failed connection attempts, ERROR messages generated by application
activity, and the like.
Bharath Rupireddy, reviewed by Fujii Masao and by me. Many other
people commented on the thread, but as far as I can see that was
discussion of the merits of the change rather than review of the
patch.
Discussion: https://postgr.es/m/CALj2ACX-rW_OeDcp4gqrFUAkf1f50Fnh138dmkd0JkvCNQRKGA@mail.gmail.com
Reviewed-by: Marbin Tan <tm...@vmware.com>
---
src/backend/access/transam/xlog.c | 2 +-
src/backend/utils/misc/guc.c | 2 +-
src/backend/utils/misc/postgresql.conf.sample | 2 +-
src/bin/initdb/initdb.c | 4 ++--
4 files changed, 5 insertions(+), 5 deletions(-)
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 87853dcafe..3a850556ef 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -122,7 +122,7 @@ char *wal_consistency_checking_string = NULL;
bool *wal_consistency_checking = NULL;
bool wal_init_zero = true;
bool wal_recycle = true;
-bool log_checkpoints = false;
+bool log_checkpoints = true;
int sync_method = DEFAULT_SYNC_METHOD;
int wal_level = WAL_LEVEL_MINIMAL;
int CommitDelay = 0; /* precommit delay in microseconds */
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 5329f55e3c..1f8c6019b6 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -1417,7 +1417,7 @@ static struct config_bool ConfigureNamesBool[] =
NULL
},
&log_checkpoints,
- false,
+ true,
NULL, NULL, NULL
},
{
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 31ac67628a..98db5c5635 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -547,7 +547,7 @@ optimizer_analyze_root_partition = on # stats collection on root partitions
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
-#log_checkpoints = off
+#log_checkpoints = on
#log_connections = off
#log_disconnections = off
#log_duration = off
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index dd35309652..4b997df45f 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -212,8 +212,8 @@ static bool authwarning = false;
* but here it is more convenient to pass it as an environment variable
* (no quoting to worry about).
*/
-static const char *boot_options = "-F";
-static const char *backend_options = "--single -F -O -j -c gp_role=utility -c search_path=pg_catalog -c exit_on_error=true";
+static const char *boot_options = "-F -c log_checkpoints=false";
+static const char *backend_options = "--single -F -O -j -c gp_role=utility -c search_path=pg_catalog -c exit_on_error=true -c log_checkpoints=false";
/* Additional switches to pass to backend (either boot or standalone) */
static char *extra_options = "";
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org