Shardingsphere support foreign table syntax for openGauss

[congzhou2603]

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

[strongduanmu]

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

[Netter99]

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?

[strongduanmu]

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.

[Netter99]

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

[strongduanmu]

@Netter99 Assigned, good luck for you.

[Netter99]

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?

[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.

[congzhou2603]

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;

[strongduanmu]

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.

[Netter99]

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

[strongduanmu]

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

[Netter99]

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?

[strongduanmu]

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 */
        ;

[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.

[strongduanmu]

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

[Netter99]

Is this what you mean?

[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.

[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?

[congzhou2603]

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

[Netter99]

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.

[strongduanmu]

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.

[Netter99]

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.