This is an automated email from the ASF dual-hosted git repository.
maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new f7a5b7aa230 Allow ON COORDINATOR for ext file:// protocol
f7a5b7aa230 is described below
commit f7a5b7aa230fce5852a190d9803fb063a1c7dd97
Author: Maxim Smyatkin <[email protected]>
AuthorDate: Sat Sep 6 14:38:52 2025 +0300
Allow ON COORDINATOR for ext file:// protocol
This commit allows `CREATE EXTERNAL TABLE ... LOCATION('file://...') ON
COORDINATOR`. Before this we could only access files on segments with
file-based exttables.
The syntax was already there but was not implemented.
Added new test case and modified the existing one for ON COORDINATOR
syntax.
---
.../src/test/regress/input/external_table.source | 14 +++++++++++-
.../src/test/regress/output/external_table.source | 26 ++++++++++++++++++++--
.../regress/output/external_table_optimizer.source | 26 ++++++++++++++++++++--
src/backend/access/external/external.c | 9 +++++++-
src/test/regress/input/external_table.source | 14 +++++++++++-
src/test/regress/output/external_table.source | 26 ++++++++++++++++++++--
.../regress/output/external_table_optimizer.source | 26 ++++++++++++++++++++--
7 files changed, 130 insertions(+), 11 deletions(-)
diff --git a/contrib/pax_storage/src/test/regress/input/external_table.source
b/contrib/pax_storage/src/test/regress/input/external_table.source
index ea018e581bf..e4d57c1ae83 100644
--- a/contrib/pax_storage/src/test/regress/input/external_table.source
+++ b/contrib/pax_storage/src/test/regress/input/external_table.source
@@ -1365,10 +1365,22 @@ SELECT COUNT(*) FROM
gp_read_error_log('exttab_heap_join_1');
\! rm @abs_srcdir@/data/tableless.csv
-- start_ignore
+DROP EXTERNAL TABLE IF EXISTS ext_nation_on_coordinator;
DROP EXTERNAL TABLE IF EXISTS exttab_with_on_coordinator;
-- end_ignore
--- Create external table with on clause
+-- Create external table on coordinator
+-- good, should fetch data from coordinator
+CREATE EXTERNAL TABLE ext_nation_on_coordinator ( N_NATIONKEY INTEGER ,
+ N_NAME CHAR(25) ,
+ N_REGIONKEY INTEGER ,
+ N_COMMENT VARCHAR(152))
+location ('file://@hostname@@abs_srcdir@/data/nation.tbl' ) ON COORDINATOR
+FORMAT 'text' (delimiter '|');
+SELECT gp_segment_id, * FROM ext_nation_on_coordinator ORDER BY N_NATIONKEY
DESC LIMIT 5;
+DROP EXTERNAL TABLE IF EXISTS ext_nation_on_coordinator;
+
+-- bad, should error
CREATE EXTERNAL TABLE exttab_with_on_coordinator( i int, j text )
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') ON
COORDINATOR FORMAT 'TEXT' (DELIMITER '|');
diff --git a/contrib/pax_storage/src/test/regress/output/external_table.source
b/contrib/pax_storage/src/test/regress/output/external_table.source
index 19791a992c3..53c72200f01 100644
--- a/contrib/pax_storage/src/test/regress/output/external_table.source
+++ b/contrib/pax_storage/src/test/regress/output/external_table.source
@@ -2601,14 +2601,36 @@ SELECT COUNT(*) FROM
gp_read_error_log('exttab_heap_join_1');
\! rm @abs_srcdir@/data/tableless.csv
-- start_ignore
+DROP EXTERNAL TABLE IF EXISTS ext_nation_on_coordinator;
+NOTICE: table "ext_nation_on_coordinator" does not exist, skipping
DROP EXTERNAL TABLE IF EXISTS exttab_with_on_coordinator;
NOTICE: table "exttab_with_on_coordinator" does not exist, skipping
-- end_ignore
--- Create external table with on clause
+-- Create external table on coordinator
+-- good, should fetch data from coordinator
+CREATE EXTERNAL TABLE ext_nation_on_coordinator ( N_NATIONKEY INTEGER ,
+ N_NAME CHAR(25) ,
+ N_REGIONKEY INTEGER ,
+ N_COMMENT VARCHAR(152))
+location ('file://@hostname@@abs_srcdir@/data/nation.tbl' ) ON COORDINATOR
+FORMAT 'text' (delimiter '|');
+SELECT gp_segment_id, * FROM ext_nation_on_coordinator ORDER BY N_NATIONKEY
DESC LIMIT 5;
+ gp_segment_id | n_nationkey | n_name | n_regionkey |
n_comment
+---------------+-------------+---------------------------+-------------+----------------------------------------------------------------------------------------------------------------
+ -1 | 24 | UNITED STATES | 1 | y
final packages. slow foxes cajole quickly. quickly silent platelets breach
ironic accounts. unusual pinto be
+ -1 | 23 | UNITED KINGDOM | 3 | eans
boost carefully special requests. accounts are. carefull
+ -1 | 22 | RUSSIA | 3 |
requests against the platelets use never according to the quickly regular pint
+ -1 | 21 | VIETNAM | 2 | hely
enticingly express accounts. even, final
+ -1 | 20 | SAUDI ARABIA | 4 | ts.
silent requests haggle. closely express packages sleep across the blithely
+(5 rows)
+
+DROP EXTERNAL TABLE IF EXISTS ext_nation_on_coordinator;
+-- bad, should error
CREATE EXTERNAL TABLE exttab_with_on_coordinator( i int, j text )
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') ON
COORDINATOR FORMAT 'TEXT' (DELIMITER '|');
SELECT * FROM exttab_with_on_coordinator;
-ERROR: 'ON COORDINATOR' is not supported by this protocol yet
+ERROR: invalid input syntax for type integer: "error_0"
+CONTEXT: External table exttab_with_on_coordinator, line 3 of
file://@hostname@@abs_srcdir@/data/exttab_few_errors.data, column i
DROP EXTERNAL TABLE IF EXISTS exttab_with_on_coordinator;
-- start_ignore
DROP EXTERNAL TABLE IF EXISTS exttab_with_option_empty;
diff --git
a/contrib/pax_storage/src/test/regress/output/external_table_optimizer.source
b/contrib/pax_storage/src/test/regress/output/external_table_optimizer.source
index 68523e63aae..6c4f884d292 100644
---
a/contrib/pax_storage/src/test/regress/output/external_table_optimizer.source
+++
b/contrib/pax_storage/src/test/regress/output/external_table_optimizer.source
@@ -2601,14 +2601,36 @@ SELECT COUNT(*) FROM
gp_read_error_log('exttab_heap_join_1');
\! rm @abs_srcdir@/data/tableless.csv
-- start_ignore
+DROP EXTERNAL TABLE IF EXISTS ext_nation_on_coordinator;
+NOTICE: table "ext_nation_on_coordinator" does not exist, skipping
DROP EXTERNAL TABLE IF EXISTS exttab_with_on_coordinator;
NOTICE: table "exttab_with_on_coordinator" does not exist, skipping
-- end_ignore
--- Create external table with on clause
+-- Create external table on coordinator
+-- good, should fetch data from coordinator
+CREATE EXTERNAL TABLE ext_nation_on_coordinator ( N_NATIONKEY INTEGER ,
+ N_NAME CHAR(25) ,
+ N_REGIONKEY INTEGER ,
+ N_COMMENT VARCHAR(152))
+location ('file://@hostname@@abs_srcdir@/data/nation.tbl' ) ON COORDINATOR
+FORMAT 'text' (delimiter '|');
+SELECT gp_segment_id, * FROM ext_nation_on_coordinator ORDER BY N_NATIONKEY
DESC LIMIT 5;
+ gp_segment_id | n_nationkey | n_name | n_regionkey |
n_comment
+---------------+-------------+---------------------------+-------------+----------------------------------------------------------------------------------------------------------------
+ -1 | 24 | UNITED STATES | 1 | y
final packages. slow foxes cajole quickly. quickly silent platelets breach
ironic accounts. unusual pinto be
+ -1 | 23 | UNITED KINGDOM | 3 | eans
boost carefully special requests. accounts are. carefull
+ -1 | 22 | RUSSIA | 3 |
requests against the platelets use never according to the quickly regular pint
+ -1 | 21 | VIETNAM | 2 | hely
enticingly express accounts. even, final
+ -1 | 20 | SAUDI ARABIA | 4 | ts.
silent requests haggle. closely express packages sleep across the blithely
+(5 rows)
+
+DROP EXTERNAL TABLE IF EXISTS ext_nation_on_coordinator;
+-- bad, should error
CREATE EXTERNAL TABLE exttab_with_on_coordinator( i int, j text )
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') ON
COORDINATOR FORMAT 'TEXT' (DELIMITER '|');
SELECT * FROM exttab_with_on_coordinator;
-ERROR: 'ON COORDINATOR' is not supported by this protocol yet
+ERROR: invalid input syntax for type integer: "error_0"
+CONTEXT: External table exttab_with_on_coordinator, line 3 of
file://@hostname@@abs_srcdir@/data/exttab_few_errors.data, column i
DROP EXTERNAL TABLE IF EXISTS exttab_with_on_coordinator;
-- start_ignore
DROP EXTERNAL TABLE IF EXISTS exttab_with_option_empty;
diff --git a/src/backend/access/external/external.c
b/src/backend/access/external/external.c
index 0905a576a6c..62cff75db53 100644
--- a/src/backend/access/external/external.c
+++ b/src/backend/access/external/external.c
@@ -443,7 +443,7 @@ create_external_scan_uri_list(ExtTableEntry *ext, bool
*ismasteronly)
* protocols only */
on_clause = (char *) strVal(linitial(ext->execlocations));
if ((strcmp(on_clause, "COORDINATOR_ONLY") == 0)
- && using_location && (uri->protocol != URI_CUSTOM)) {
+ && using_location && (uri->protocol != URI_CUSTOM &&
uri->protocol != URI_FILE)) {
ereport(ERROR, (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("\'ON COORDINATOR\' is not supported by
this protocol yet")));
}
@@ -524,6 +524,13 @@ create_external_scan_uri_list(ExtTableEntry *ext, bool
*ismasteronly)
found_candidate = false;
found_match = false;
+ if ((strcmp(on_clause, "COORDINATOR_ONLY") == 0) &&
uri->protocol == URI_FILE)
+ {
+ found_match = true;
+ segdb_file_map[0] = pstrdup(uri_str);
+ *ismasteronly = true;
+ }
+
/*
* look through our segment database list and try to
find a
* database that can handle this uri.
diff --git a/src/test/regress/input/external_table.source
b/src/test/regress/input/external_table.source
index 048da739097..a6c9871215e 100644
--- a/src/test/regress/input/external_table.source
+++ b/src/test/regress/input/external_table.source
@@ -1402,10 +1402,22 @@ SELECT COUNT(*) FROM
gp_read_error_log('exttab_heap_join_1');
\! rm @abs_srcdir@/data/tableless.csv
-- start_ignore
+DROP EXTERNAL TABLE IF EXISTS ext_nation_on_coordinator;
DROP EXTERNAL TABLE IF EXISTS exttab_with_on_coordinator;
-- end_ignore
--- Create external table with on clause
+-- Create external table on coordinator
+-- good, should fetch data from coordinator
+CREATE EXTERNAL TABLE ext_nation_on_coordinator ( N_NATIONKEY INTEGER ,
+ N_NAME CHAR(25) ,
+ N_REGIONKEY INTEGER ,
+ N_COMMENT VARCHAR(152))
+location ('file://@hostname@@abs_srcdir@/data/nation.tbl' ) ON COORDINATOR
+FORMAT 'text' (delimiter '|');
+SELECT gp_segment_id, * FROM ext_nation_on_coordinator ORDER BY N_NATIONKEY
DESC LIMIT 5;
+DROP EXTERNAL TABLE IF EXISTS ext_nation_on_coordinator;
+
+-- bad, should error
CREATE EXTERNAL TABLE exttab_with_on_coordinator( i int, j text )
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') ON
COORDINATOR FORMAT 'TEXT' (DELIMITER '|');
diff --git a/src/test/regress/output/external_table.source
b/src/test/regress/output/external_table.source
index c7c628e5c6b..014c50f55d3 100644
--- a/src/test/regress/output/external_table.source
+++ b/src/test/regress/output/external_table.source
@@ -2657,14 +2657,36 @@ SELECT COUNT(*) FROM
gp_read_error_log('exttab_heap_join_1');
\! rm @abs_srcdir@/data/tableless.csv
-- start_ignore
+DROP EXTERNAL TABLE IF EXISTS ext_nation_on_coordinator;
+NOTICE: table "ext_nation_on_coordinator" does not exist, skipping
DROP EXTERNAL TABLE IF EXISTS exttab_with_on_coordinator;
NOTICE: table "exttab_with_on_coordinator" does not exist, skipping
-- end_ignore
--- Create external table with on clause
+-- Create external table on coordinator
+-- good, should fetch data from coordinator
+CREATE EXTERNAL TABLE ext_nation_on_coordinator ( N_NATIONKEY INTEGER ,
+ N_NAME CHAR(25) ,
+ N_REGIONKEY INTEGER ,
+ N_COMMENT VARCHAR(152))
+location ('file://@hostname@@abs_srcdir@/data/nation.tbl' ) ON COORDINATOR
+FORMAT 'text' (delimiter '|');
+SELECT gp_segment_id, * FROM ext_nation_on_coordinator ORDER BY N_NATIONKEY
DESC LIMIT 5;
+ gp_segment_id | n_nationkey | n_name | n_regionkey |
n_comment
+---------------+-------------+---------------------------+-------------+----------------------------------------------------------------------------------------------------------------
+ -1 | 24 | UNITED STATES | 1 | y
final packages. slow foxes cajole quickly. quickly silent platelets breach
ironic accounts. unusual pinto be
+ -1 | 23 | UNITED KINGDOM | 3 | eans
boost carefully special requests. accounts are. carefull
+ -1 | 22 | RUSSIA | 3 |
requests against the platelets use never according to the quickly regular pint
+ -1 | 21 | VIETNAM | 2 | hely
enticingly express accounts. even, final
+ -1 | 20 | SAUDI ARABIA | 4 | ts.
silent requests haggle. closely express packages sleep across the blithely
+(5 rows)
+
+DROP EXTERNAL TABLE IF EXISTS ext_nation_on_coordinator;
+-- bad, should error
CREATE EXTERNAL TABLE exttab_with_on_coordinator( i int, j text )
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') ON
COORDINATOR FORMAT 'TEXT' (DELIMITER '|');
SELECT * FROM exttab_with_on_coordinator;
-ERROR: 'ON COORDINATOR' is not supported by this protocol yet
+ERROR: invalid input syntax for type integer: "error_0"
+CONTEXT: External table exttab_with_on_coordinator, line 3 of
file://@hostname@@abs_srcdir@/data/exttab_few_errors.data, column i
DROP EXTERNAL TABLE IF EXISTS exttab_with_on_coordinator;
-- start_ignore
DROP EXTERNAL TABLE IF EXISTS exttab_with_option_empty;
diff --git a/src/test/regress/output/external_table_optimizer.source
b/src/test/regress/output/external_table_optimizer.source
index b5af22445d1..4e853662fb3 100644
--- a/src/test/regress/output/external_table_optimizer.source
+++ b/src/test/regress/output/external_table_optimizer.source
@@ -2657,14 +2657,36 @@ SELECT COUNT(*) FROM
gp_read_error_log('exttab_heap_join_1');
\! rm @abs_srcdir@/data/tableless.csv
-- start_ignore
+DROP EXTERNAL TABLE IF EXISTS ext_nation_on_coordinator;
+NOTICE: table "ext_nation_on_coordinator" does not exist, skipping
DROP EXTERNAL TABLE IF EXISTS exttab_with_on_coordinator;
NOTICE: table "exttab_with_on_coordinator" does not exist, skipping
-- end_ignore
--- Create external table with on clause
+-- Create external table on coordinator
+-- good, should fetch data from coordinator
+CREATE EXTERNAL TABLE ext_nation_on_coordinator ( N_NATIONKEY INTEGER ,
+ N_NAME CHAR(25) ,
+ N_REGIONKEY INTEGER ,
+ N_COMMENT VARCHAR(152))
+location ('file://@hostname@@abs_srcdir@/data/nation.tbl' ) ON COORDINATOR
+FORMAT 'text' (delimiter '|');
+SELECT gp_segment_id, * FROM ext_nation_on_coordinator ORDER BY N_NATIONKEY
DESC LIMIT 5;
+ gp_segment_id | n_nationkey | n_name | n_regionkey |
n_comment
+---------------+-------------+---------------------------+-------------+----------------------------------------------------------------------------------------------------------------
+ -1 | 24 | UNITED STATES | 1 | y
final packages. slow foxes cajole quickly. quickly silent platelets breach
ironic accounts. unusual pinto be
+ -1 | 23 | UNITED KINGDOM | 3 | eans
boost carefully special requests. accounts are. carefull
+ -1 | 22 | RUSSIA | 3 |
requests against the platelets use never according to the quickly regular pint
+ -1 | 21 | VIETNAM | 2 | hely
enticingly express accounts. even, final
+ -1 | 20 | SAUDI ARABIA | 4 | ts.
silent requests haggle. closely express packages sleep across the blithely
+(5 rows)
+
+DROP EXTERNAL TABLE IF EXISTS ext_nation_on_coordinator;
+-- bad, should error
CREATE EXTERNAL TABLE exttab_with_on_coordinator( i int, j text )
LOCATION ('file://@hostname@@abs_srcdir@/data/exttab_few_errors.data') ON
COORDINATOR FORMAT 'TEXT' (DELIMITER '|');
SELECT * FROM exttab_with_on_coordinator;
-ERROR: 'ON COORDINATOR' is not supported by this protocol yet
+ERROR: invalid input syntax for type integer: "error_0"
+CONTEXT: External table exttab_with_on_coordinator, line 3 of
file://@hostname@@abs_srcdir@/data/exttab_few_errors.data, column i
DROP EXTERNAL TABLE IF EXISTS exttab_with_on_coordinator;
-- start_ignore
DROP EXTERNAL TABLE IF EXISTS exttab_with_option_empty;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
