[GitHub] [shardingsphere] MrCong233 opened a new issue, #20490: Consult the logic about shardingsphere updating metadata

[GitBox <gi...@apache.org>]

MrCong233 opened a new issue, #20490:
URL: https://github.com/apache/shardingsphere/issues/20490

   ## Question
   
   ## Precondition
   
   I am developing global logical clock(GLT) feature of openGauss distributed transaction  based on **shardingsphere + openGauss + Redis**.
   
   Its basic principle is to use Redis to save the latest global CSN of distributed transactions, and send the global CSN to openGauss through shardingsphere when a transaction is committed or needs to judge the visibility of distributed transactions.
   
   Now I have completed the global CSN distribution process within the explicit transaction, like "begin; -> select; -> insert; ->commit;", the transaction will uses the same global CSN to determine visibility and commit.
   
   ## Question
   
   Now the problem I have is that the existing logic cannot override the implicit transaction, , such as executing "create table",  the following problems will occur：
   
   1. like openGauss will automatically commit when  executing DDL outside the transaction,  at this time openGauss cannot use the global CSN to submit, because he is not in an explicit transaction.
   2. I found that after executing DDL, shardingsphere will automatically execute "select XXX from information_schema.columns" to update metadata, and may not find the table just inserted.
   
   I have two solutions and would like to ask if it is useful or if there is a better solution.
   
   ## Solution 1
   
   Can I put both DDL and update metadata inside the transaction?
   
   Considering that pgsql allows the execution of DDL within a transaction, I have tried to cancel the restriction that shardingsphere prohibits the execution of DDL within a transaction, but found that "create table" is within the transaction, and "select XXX from information_schema.columns" is not within the transaction, thus causing the metadata to not be updated.
   
   **Can I reuse transactional connections when "select XXX from information_schema.columns" while inside a transaction？**
   
   opengauss的pg_log
   
   
   ![image-20220824163727231](https://img-blog.csdnimg.cn/6570f446f4ac4f5fa4c181bcb98a9fc2.png)
   
   ![image-20220824101519981](https://img-blog.csdnimg.cn/62470b47dd734755ae45607bc43f42a2.png)
   
   查询元数据的代码块
   
   ![image-20220824163656353](https://img-blog.csdnimg.cn/ea75a1c91a474a2c8d233ddda84f0650.png)
   
   ## Solution 2 
   
   Whether the sql statement can be judged as DDL (for example: startWith(“create table”) before executing sql, and the multiplexed connection issues the global CSN to each shard for implicit transaction submission. If openGauss can receive the global CSN and DDL successively in the same, it can use the global CSN submission.
   
   ![image-20220824164653935](https://img-blog.csdnimg.cn/7d630bc78efc4220b7a6930fb0505d7a.png)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] MrCong233 closed issue #20490: Consult the logic about shardingsphere updating metadata

[GitBox <gi...@apache.org>]

MrCong233 closed issue #20490: Consult the logic about shardingsphere updating metadata
URL: https://github.com/apache/shardingsphere/issues/20490


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org