shardingsphere Shardingsphere support foreign table syntax for openGauss

Feature Request

For English only, other languages will not be accepted.

Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot make decision by current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Is your feature request related to a problem?

#25103

Describe the feature you would like.

ShardingSphere doesn't support foreigin table syntax for openGauss now, syntax format is as fellow. https://docs.opengauss.org/zh/docs/5.0.0/docs/SQLReference/DROP-FOREIGN-TABLE.html https://docs.opengauss.org/zh/docs/5.0.0/docs/SQLReference/CREATE-FOREIGN-TABLE.html

Apr 11 '23 07:04 congzhou2603

This looks like a nice feature, let's see if any contributors want to support it.

Apr 11 '23 09:04 strongduanmu

Hi, @congzhou2603 , I'm a newcomer for shardingsphere, and I'm interested in this problem. But I am not sure whether can I solve this question or not. How can I start about this problem?

Apr 11 '23 11:04 Netter99

Hi @Netter99, welcome to develop this feature. First, you can complete the adaptation of CREATE FOREIGN TABLE syntax parsing, which has many cases in the PR list, implement syntax parsing based on ANTLR, and turn AST visit into SQLStatement.

In the second step, we need to consider supporting this SQL in the sharding scenario, which is not a simple task. I suggest that you can investigate other distributed databases, such as CockroachDB and Citus, and refer to their mature solutions.

Apr 12 '23 01:04 strongduanmu

Thanks for your advice, @strongduanmu , I want to have a try. Please assign to me.

Apr 12 '23 01:04 Netter99

@Netter99 Assigned, good luck for you.

Apr 12 '23 01:04 strongduanmu

Hi, @strongduanmu , sorry for bothering you. Counld you give me more detail advice?(Maybe the link to others PR about step1.) After several hours of finding information, I''m still confused about how to start and think maybe this issue can be divided into some small problems?

Apr 12 '23 08:04 Netter99

Hello, @strongduanmu , since I don't know how to start. I try to imitate other contributor from here https://github.com/apache/shardingsphere/pull/17861/files. I am not sure whether it is a right start or not, please have a check and give me some suggestions.

Apr 17 '23 08:04 Netter99

Here is an exmaple for creating and dropping foreigin table. “SERVER 'mot_server’” corresponeds to "SERVER gsmpp_server" in syntax, among which "gsmpp_server" indicates the server name of foreign table. "FOREIGN " and "SERVER gsmpp_server" both can be transparently transmitted to openGauss.

CREATE FOREIGN TABLE t_order (
user_id int,
order_name varchar(30),
order_value varchar(50),
order_id int default 0
)
SERVER mot_server;

DROP FOREIGN TABLE t_order;

Apr 17 '23 09:04 congzhou2603

imag

Hi @Netter99, can you try to test openGauss create foreign table sytax via IDEA antlr plugin? We need to first determine which part of the parse is reporting the error.

Apr 17 '23 09:04 strongduanmu

Hi, @strongduanmu , I add createForeignTable into schemaStmt (I am not sure if it is necessary). I have a check, and it seems work correctly.

Apr 17 '23 10:04 Netter99

Hi @Netter99, this change is not reasonable. Can you add createForeignTable to OpenGaussStatement.g4 file? And add some test case for createForeignTable statement.

Apr 17 '23 10:04 strongduanmu

Hi @strongduanmu , I have a review of the input caused error. Maybe it throw error because it miss SEVER server_name which should not be null?

Apr 17 '23 11:04 Netter99

Hi @Netter99, maybe we need to take a look at the openGauss source code, I guess server_name is an optional parameter. Following is openGauss gram.y source code:

/*****************************************************************************
 *
 *		QUERY:
 *             CREATE FOREIGN TABLE relname (...) SERVER name (...)
 *
 *****************************************************************************/

CreateForeignTableStmt:
		CREATE FOREIGN TABLE qualified_name
			OptForeignTableElementList
			SERVER name create_generic_options ForeignTblWritable
			OptForeignTableLogError OptForeignTableLogRemote OptPerNodeRejectLimit OptDistributeBy
/* PGXC_BEGIN */
			OptSubCluster
/* PGXC_END */
			OptForeignPartBy
				{
					CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
					$4->relpersistence = RELPERSISTENCE_PERMANENT;
					n->base.relation = $4;
					n->base.tableElts = $5;
					n->base.inhRelations = NIL;
					n->base.if_not_exists = false;
					/* FDW-specific data */
					n->servername = $7;
					n->options = $8;

					n->write_only = $9;
					n->error_relation = (Node*)$10;
					if ($11 != NULL)
						n->extOptions = lappend(n->extOptions, $11);
					if ($12 != NULL)
						n->extOptions = lappend(n->extOptions, $12);
					n->base.distributeby = $13;
/* PGXC_BEGIN */
					n->base.subcluster = $14;
/* PGXC_END */
					if ($15 != NULL)
						n->part_state = $15;

					$$ = (Node *) n;
				}
		| CREATE FOREIGN TABLE IF_P NOT EXISTS qualified_name
			OptForeignTableElementList
			SERVER name create_generic_options ForeignTblWritable
			OptForeignTableLogError OptForeignTableLogRemote OptPerNodeRejectLimit OptDistributeBy
/* PGXC_BEGIN */
			OptSubCluster
/* PGXC_END */
			OptForeignPartBy
				{
					CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
					$7->relpersistence = RELPERSISTENCE_PERMANENT;
					n->base.relation = $7;
					n->base.tableElts = $8;
					n->base.inhRelations = NIL;
					n->base.if_not_exists = true;
					/* FDW-specific data */
					n->servername = $10;
					n->options = $11;

					n->write_only = $12;
					n->error_relation = (Node*)$13;
					if ($14 != NULL)
						n->extOptions = lappend(n->extOptions, $14);
					if ($15 != NULL)
						n->extOptions = lappend(n->extOptions, $15);
					n->base.distributeby = $16;
/* PGXC_BEGIN */
					n->base.subcluster = $17;
/* PGXC_END */
					if ($18 != NULL)
						n->part_state = $18;

					$$ = (Node *) n;
				}
/* ENABLE_MOT BEGIN */
                | CREATE FOREIGN TABLE qualified_name
			OptForeignTableElementList
			create_generic_options ForeignTblWritable
			OptForeignTableLogError OptForeignTableLogRemote OptPerNodeRejectLimit OptDistributeBy
/* PGXC_BEGIN */
			OptSubCluster
/* PGXC_END */
			OptForeignPartBy
				{
					CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
					$4->relpersistence = RELPERSISTENCE_PERMANENT;
					n->base.relation = $4;
					n->base.tableElts = $5;
					n->base.inhRelations = NIL;
					n->base.if_not_exists = false;
					/* FDW-specific data */
#ifdef ENABLE_MOT
					n->servername = pstrdup("mot_server");
#else
        			const char* message = "Foreign server is not specified";
    				InsertErrorMessage(message, u_sess->plsql_cxt.plpgsql_yylloc);
                    ereport(errstate,
                        (errcode(ERRCODE_SYNTAX_ERROR),
                            errmsg("Foreign server is not specified")));
#endif
					n->options = $6;

					n->write_only = $7;
					n->error_relation = (Node*)$8;
					if ($9 != NULL)
						n->extOptions = lappend(n->extOptions, $9);
					if ($10 != NULL)
						n->extOptions = lappend(n->extOptions, $10);
					n->base.distributeby = $11;
/* PGXC_BEGIN */
					n->base.subcluster = $12;
/* PGXC_END */
					if ($13 != NULL)
						n->part_state = $13;

					$$ = (Node *) n;
				}
               | CREATE FOREIGN TABLE IF_P NOT EXISTS qualified_name
			OptForeignTableElementList
			create_generic_options ForeignTblWritable
			OptForeignTableLogError OptForeignTableLogRemote OptPerNodeRejectLimit OptDistributeBy
/* PGXC_BEGIN */
			OptSubCluster
/* PGXC_END */
			OptForeignPartBy
				{
					CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
					$7->relpersistence = RELPERSISTENCE_PERMANENT;
					n->base.relation = $7;
					n->base.tableElts = $8;
					n->base.inhRelations = NIL;
					n->base.if_not_exists = true;
					/* FDW-specific data */
#ifdef ENABLE_MOT
					n->servername = pstrdup("mot_server");
#else
        			const char* message = "Foreign server is not specified";
    				InsertErrorMessage(message, u_sess->plsql_cxt.plpgsql_yylloc);
                    ereport(errstate,
                        (errcode(ERRCODE_SYNTAX_ERROR),
                            errmsg("Foreign server is not specified")));
#endif
					n->options = $9;

					n->write_only = $10;
					n->error_relation = (Node*)$11;
					if ($12 != NULL)
						n->extOptions = lappend(n->extOptions, $12);
					if ($13 != NULL)
						n->extOptions = lappend(n->extOptions, $13);
					n->base.distributeby = $14;
/* PGXC_BEGIN */
					n->base.subcluster = $15;
/* PGXC_END */
					if ($16 != NULL)
						n->part_state = $16;

					$$ = (Node *) n;
				}
/* ENABLE_MOT END */
		;

Apr 17 '23 13:04 strongduanmu

Hi, @strongduanmu , I check again about this question. This is the official description about the parameter in GaussDB. https://support.huaweicloud.com/intl/zh-cn/distributed-devg-v2-opengauss/devg_02_0507.html

Here are some test cases for createForeignTable statement from openGauss-server-master - source code.

If we input create foreign tablesql without the parameter, the parse tree is generated like this. So I don't think SERVER server_name is an optional parameter.

Apr 18 '23 06:04 Netter99

Can you test this sql without semicolon? It is dealt with uniformly in OpenGaussStatement.g4 file.

Apr 18 '23 07:04 strongduanmu

Is this what you mean?

Apr 18 '23 07:04 Netter99

It has the same createForeignTable code design in opengauss and postgresql's DDLStatement. If we test the same sql in postgresql\DDLStatement.g4, the parse tree also has the same error.

Apr 18 '23 07:04 Netter99

Hi, @strongduanmu , I check again about this question. This is the official description about the parameter in GaussDB. https://support.huaweicloud.com/intl/zh-cn/distributed-devg-v2-opengauss/devg_02_0507.html

Here are some test cases for createForeignTable statement from openGauss-server-master - source code.

If we input create foreign tablesql without the parameter, the parse tree is generated like this. So I don't think SERVER server_name is an optional parameter.

Hi @congzhou2603, can you help check whether server name is an optional segement or not?

Apr 18 '23 08:04 strongduanmu

Hi, @strongduanmu , I check again about this question. This is the official description about the parameter in GaussDB. https://support.huaweicloud.com/intl/zh-cn/distributed-devg-v2-opengauss/devg_02_0507.html Here are some test cases for createForeignTable statement from openGauss-server-master - source code. If we input create foreign tablesql without the parameter, the parse tree is generated like this. So I don't think SERVER server_name is an optional parameter.

Hi @congzhou2603, can you help check whether server name is an optional segement or not?

Hi @strongduanmu @Netter99 I test this syntax by openGauss 5.0.0, server name is optional segment，here is the test result. And Here is the developer guide of creating foreign table.

https://docs.opengauss.org/zh/docs/5.0.0/docs/SQLReference/CREATE-FOREIGN-TABLE.html

Apr 19 '23 09:04 congzhou2603

Hi @strongduanmu @Netter99 I test this syntax by openGauss 5.0.0, server name is optional segment，here is the test result. And Here is the developer guide of creating foreign table.

https://docs.opengauss.org/zh/docs/5.0.0/docs/SQLReference/CREATE-FOREIGN-TABLE.html

Hi @congzhou2603 @strongduanmu , I know it has a default value and I don't know about how openGauss deals with this situation. But to Shardingsphere currently, this kind of input is invalid (according to DDLStatement.g4 which make a check about the sql and generate the corresponding syntax tree). If we make a null value checks on the input parameter and add default value when the parameter is null, I think the syntax tree can be generated correctly.

Apr 19 '23 09:04 Netter99

Hi @Netter99, maybe we need to take a look at the openGauss source code, I guess server_name is an optional parameter. Following is openGauss gram.y source code:

/*****************************************************************************
 *
 *		QUERY:
 *             CREATE FOREIGN TABLE relname (...) SERVER name (...)
 *
 *****************************************************************************/

CreateForeignTableStmt:
		CREATE FOREIGN TABLE qualified_name
			OptForeignTableElementList
			SERVER name create_generic_options ForeignTblWritable
			OptForeignTableLogError OptForeignTableLogRemote OptPerNodeRejectLimit OptDistributeBy
/* PGXC_BEGIN */
			OptSubCluster
/* PGXC_END */
			OptForeignPartBy
				{
					CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
					$4->relpersistence = RELPERSISTENCE_PERMANENT;
					n->base.relation = $4;
					n->base.tableElts = $5;
					n->base.inhRelations = NIL;
					n->base.if_not_exists = false;
					/* FDW-specific data */
					n->servername = $7;
					n->options = $8;

					n->write_only = $9;
					n->error_relation = (Node*)$10;
					if ($11 != NULL)
						n->extOptions = lappend(n->extOptions, $11);
					if ($12 != NULL)
						n->extOptions = lappend(n->extOptions, $12);
					n->base.distributeby = $13;
/* PGXC_BEGIN */
					n->base.subcluster = $14;
/* PGXC_END */
					if ($15 != NULL)
						n->part_state = $15;

					$$ = (Node *) n;
				}
		| CREATE FOREIGN TABLE IF_P NOT EXISTS qualified_name
			OptForeignTableElementList
			SERVER name create_generic_options ForeignTblWritable
			OptForeignTableLogError OptForeignTableLogRemote OptPerNodeRejectLimit OptDistributeBy
/* PGXC_BEGIN */
			OptSubCluster
/* PGXC_END */
			OptForeignPartBy
				{
					CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
					$7->relpersistence = RELPERSISTENCE_PERMANENT;
					n->base.relation = $7;
					n->base.tableElts = $8;
					n->base.inhRelations = NIL;
					n->base.if_not_exists = true;
					/* FDW-specific data */
					n->servername = $10;
					n->options = $11;

					n->write_only = $12;
					n->error_relation = (Node*)$13;
					if ($14 != NULL)
						n->extOptions = lappend(n->extOptions, $14);
					if ($15 != NULL)
						n->extOptions = lappend(n->extOptions, $15);
					n->base.distributeby = $16;
/* PGXC_BEGIN */
					n->base.subcluster = $17;
/* PGXC_END */
					if ($18 != NULL)
						n->part_state = $18;

					$$ = (Node *) n;
				}
/* ENABLE_MOT BEGIN */
                | CREATE FOREIGN TABLE qualified_name
			OptForeignTableElementList
			create_generic_options ForeignTblWritable
			OptForeignTableLogError OptForeignTableLogRemote OptPerNodeRejectLimit OptDistributeBy
/* PGXC_BEGIN */
			OptSubCluster
/* PGXC_END */
			OptForeignPartBy
				{
					CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
					$4->relpersistence = RELPERSISTENCE_PERMANENT;
					n->base.relation = $4;
					n->base.tableElts = $5;
					n->base.inhRelations = NIL;
					n->base.if_not_exists = false;
					/* FDW-specific data */
#ifdef ENABLE_MOT
					n->servername = pstrdup("mot_server");
#else
        			const char* message = "Foreign server is not specified";
    				InsertErrorMessage(message, u_sess->plsql_cxt.plpgsql_yylloc);
                    ereport(errstate,
                        (errcode(ERRCODE_SYNTAX_ERROR),
                            errmsg("Foreign server is not specified")));
#endif
					n->options = $6;

					n->write_only = $7;
					n->error_relation = (Node*)$8;
					if ($9 != NULL)
						n->extOptions = lappend(n->extOptions, $9);
					if ($10 != NULL)
						n->extOptions = lappend(n->extOptions, $10);
					n->base.distributeby = $11;
/* PGXC_BEGIN */
					n->base.subcluster = $12;
/* PGXC_END */
					if ($13 != NULL)
						n->part_state = $13;

					$$ = (Node *) n;
				}
               | CREATE FOREIGN TABLE IF_P NOT EXISTS qualified_name
			OptForeignTableElementList
			create_generic_options ForeignTblWritable
			OptForeignTableLogError OptForeignTableLogRemote OptPerNodeRejectLimit OptDistributeBy
/* PGXC_BEGIN */
			OptSubCluster
/* PGXC_END */
			OptForeignPartBy
				{
					CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
					$7->relpersistence = RELPERSISTENCE_PERMANENT;
					n->base.relation = $7;
					n->base.tableElts = $8;
					n->base.inhRelations = NIL;
					n->base.if_not_exists = true;
					/* FDW-specific data */
#ifdef ENABLE_MOT
					n->servername = pstrdup("mot_server");
#else
        			const char* message = "Foreign server is not specified";
    				InsertErrorMessage(message, u_sess->plsql_cxt.plpgsql_yylloc);
                    ereport(errstate,
                        (errcode(ERRCODE_SYNTAX_ERROR),
                            errmsg("Foreign server is not specified")));
#endif
					n->options = $9;

					n->write_only = $10;
					n->error_relation = (Node*)$11;
					if ($12 != NULL)
						n->extOptions = lappend(n->extOptions, $12);
					if ($13 != NULL)
						n->extOptions = lappend(n->extOptions, $13);
					n->base.distributeby = $14;
/* PGXC_BEGIN */
					n->base.subcluster = $15;
/* PGXC_END */
					if ($16 != NULL)
						n->part_state = $16;

					$$ = (Node *) n;
				}
/* ENABLE_MOT END */
		;

Hi @Netter99, can you take a look at this file, from @congzhou2603 tests and the openGauss source code, the server name is optional, but it is not show in the openGauss documentation.

Apr 19 '23 10:04 strongduanmu

Sorry @strongduanmu , I try but can't understand gram.y source code in openGauss. Since I have,'t use Shardingsphere before, I have a question about it. Does it support foreign syntax(sql without server name) for postgresql? Shardingsphere source codes have the same create Foreign Table code in opengauss and postgresql's DDLStatement.g4. If the sql doesn't work for postgresql, it should have the same result for openGauss. If it works, I think maybe this issue is too hard for me at now and sorry for wasting your time.

Apr 21 '23 02:04 Netter99