This is an automated email from the ASF dual-hosted git repository.
lujingshang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 820dd5cc4a4 Support executing truncate in XA and postgreSQL (#19746)
820dd5cc4a4 is described below
commit 820dd5cc4a493d2545650ed9df68f9257ada884b
Author: ZhangCheng <[email protected]>
AuthorDate: Thu Aug 4 17:57:19 2022 +0800
Support executing truncate in XA and postgreSQL (#19746)
* Support executing truncate in XA and postgreSQL
* Add truncate it tests
* Add truncate it tests
* Add truncate it tests
* Add truncate it tests
* Simplified verification
---
.../backend/communication/ProxySQLExecutor.java | 24 +++++--
.../communication/ReactiveProxySQLExecutor.java | 17 ++++-
.../communication/ProxySQLExecutorTest.java | 39 +++++++++++
.../cases/truncate/MySQLLocalTruncateTestCase.java | 81 ++++++++++++++++++++++
.../cases/truncate/MySQLXATruncateTestCase.java | 77 ++++++++++++++++++++
.../PostgresSQLAndOpenGaussTruncateTestCase.java | 80 +++++++++++++++++++++
.../src/test/resources/env/common/command.xml | 2 +-
.../env/jdbc/mysql/config-sharding-local.yaml | 2 +-
.../jdbc/mysql/config-sharding-xa-atomikos.yaml | 2 +-
.../env/jdbc/opengauss/config-sharding-local.yaml | 2 +-
.../opengauss/config-sharding-xa-atomikos.yaml | 2 +-
.../env/jdbc/postgresql/config-sharding-local.yaml | 2 +-
.../postgresql/config-sharding-xa-atomikos.yaml | 2 +-
.../resources/env/transaction-it-env.properties | 2 +-
14 files changed, 320 insertions(+), 14 deletions(-)
diff --git
a/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/ProxySQLExecutor.java
b/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/ProxySQLExecutor.java
index 08f59af24bb..7a2acea1035 100644
---
a/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/ProxySQLExecutor.java
+++
b/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/ProxySQLExecutor.java
@@ -49,6 +49,7 @@ import
org.apache.shardingsphere.sql.parser.sql.common.statement.ddl.CloseStatem
import
org.apache.shardingsphere.sql.parser.sql.common.statement.ddl.DDLStatement;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.ddl.FetchStatement;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.ddl.MoveStatement;
+import
org.apache.shardingsphere.sql.parser.sql.common.statement.ddl.TruncateStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dml.MySQLInsertStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.opengauss.OpenGaussStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.opengauss.ddl.OpenGaussCursorStatement;
@@ -100,15 +101,30 @@ public final class ProxySQLExecutor {
private boolean isExecuteDDLInXATransaction(final SQLStatement
sqlStatement) {
TransactionStatus transactionStatus =
backendConnection.getConnectionSession().getTransactionStatus();
- return TransactionType.XA == transactionStatus.getTransactionType() &&
sqlStatement instanceof DDLStatement && transactionStatus.isInTransaction();
+ return TransactionType.XA == transactionStatus.getTransactionType() &&
isUnsupportedDDLStatement(sqlStatement) && transactionStatus.isInTransaction();
}
private boolean isExecuteDDLInPostgreSQLOpenGaussTransaction(final
SQLStatement sqlStatement) {
// TODO implement DDL statement commit/rollback in
PostgreSQL/openGauss transaction
- boolean isPostgreSQLOpenGaussStatement = sqlStatement instanceof
PostgreSQLStatement || sqlStatement instanceof OpenGaussStatement;
- boolean isCursorStatement = sqlStatement instanceof
OpenGaussCursorStatement
+ boolean isPostgreSQLOpenGaussStatement =
isPostgreSQLOrOpenGaussStatement(sqlStatement);
+ boolean isSupportedStatement = isCursorStatement(sqlStatement) ||
sqlStatement instanceof TruncateStatement;
+ return sqlStatement instanceof DDLStatement && !isSupportedStatement
&& isPostgreSQLOpenGaussStatement &&
backendConnection.getConnectionSession().getTransactionStatus().isInTransaction();
+ }
+
+ private boolean isCursorStatement(final SQLStatement sqlStatement) {
+ return sqlStatement instanceof OpenGaussCursorStatement
|| sqlStatement instanceof CloseStatement || sqlStatement
instanceof MoveStatement || sqlStatement instanceof FetchStatement;
- return sqlStatement instanceof DDLStatement && !isCursorStatement &&
isPostgreSQLOpenGaussStatement &&
backendConnection.getConnectionSession().getTransactionStatus().isInTransaction();
+ }
+
+ private boolean isUnsupportedDDLStatement(final SQLStatement sqlStatement)
{
+ if (isPostgreSQLOrOpenGaussStatement(sqlStatement) && sqlStatement
instanceof TruncateStatement) {
+ return false;
+ }
+ return sqlStatement instanceof DDLStatement;
+ }
+
+ private boolean isPostgreSQLOrOpenGaussStatement(final SQLStatement
sqlStatement) {
+ return sqlStatement instanceof PostgreSQLStatement || sqlStatement
instanceof OpenGaussStatement;
}
/**
diff --git
a/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/ReactiveProxySQLExecutor.java
b/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/ReactiveProxySQLExecutor.java
index 960acb4ff4d..99c3dca3783 100644
---
a/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/ReactiveProxySQLExecutor.java
+++
b/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/ReactiveProxySQLExecutor.java
@@ -39,6 +39,7 @@ import
org.apache.shardingsphere.proxy.backend.exception.TableModifyInTransactio
import
org.apache.shardingsphere.proxy.backend.session.transaction.TransactionStatus;
import org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.ddl.DDLStatement;
+import
org.apache.shardingsphere.sql.parser.sql.common.statement.ddl.TruncateStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.opengauss.OpenGaussStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.postgresql.PostgreSQLStatement;
import org.apache.shardingsphere.transaction.core.TransactionType;
@@ -79,13 +80,25 @@ public final class ReactiveProxySQLExecutor {
private boolean isExecuteDDLInXATransaction(final SQLStatement
sqlStatement) {
TransactionStatus transactionStatus =
backendConnection.getConnectionSession().getTransactionStatus();
- return TransactionType.XA == transactionStatus.getTransactionType() &&
sqlStatement instanceof DDLStatement && transactionStatus.isInTransaction();
+ return TransactionType.XA == transactionStatus.getTransactionType() &&
isUnsupportedDDLStatement(sqlStatement) && transactionStatus.isInTransaction();
}
private boolean isExecuteDDLInPostgreSQLOpenGaussTransaction(final
SQLStatement sqlStatement) {
// TODO implement DDL statement commit/rollback in
PostgreSQL/openGauss transaction
boolean isPostgreSQLOpenGaussStatement = sqlStatement instanceof
PostgreSQLStatement || sqlStatement instanceof OpenGaussStatement;
- return sqlStatement instanceof DDLStatement &&
isPostgreSQLOpenGaussStatement &&
backendConnection.getConnectionSession().getTransactionStatus().isInTransaction();
+ boolean isSupportedDDLStatement = sqlStatement instanceof
TruncateStatement;
+ return sqlStatement instanceof DDLStatement &&
!isSupportedDDLStatement && isPostgreSQLOpenGaussStatement &&
backendConnection.getConnectionSession().getTransactionStatus().isInTransaction();
+ }
+
+ private boolean isUnsupportedDDLStatement(final SQLStatement sqlStatement)
{
+ if (isPostgreSQLOrOpenGaussStatement(sqlStatement) && sqlStatement
instanceof TruncateStatement) {
+ return false;
+ }
+ return sqlStatement instanceof DDLStatement;
+ }
+
+ private boolean isPostgreSQLOrOpenGaussStatement(final SQLStatement
sqlStatement) {
+ return sqlStatement instanceof PostgreSQLStatement || sqlStatement
instanceof OpenGaussStatement;
}
/**
diff --git
a/shardingsphere-proxy/shardingsphere-proxy-backend/src/test/java/org/apache/shardingsphere/proxy/backend/communication/ProxySQLExecutorTest.java
b/shardingsphere-proxy/shardingsphere-proxy-backend/src/test/java/org/apache/shardingsphere/proxy/backend/communication/ProxySQLExecutorTest.java
index 35cc640ef5f..1477f12c9c5 100644
---
a/shardingsphere-proxy/shardingsphere-proxy-backend/src/test/java/org/apache/shardingsphere/proxy/backend/communication/ProxySQLExecutorTest.java
+++
b/shardingsphere-proxy/shardingsphere-proxy-backend/src/test/java/org/apache/shardingsphere/proxy/backend/communication/ProxySQLExecutorTest.java
@@ -18,7 +18,9 @@
package org.apache.shardingsphere.proxy.backend.communication;
import org.apache.shardingsphere.infra.binder.LogicSQL;
+import org.apache.shardingsphere.infra.binder.statement.SQLStatementContext;
import
org.apache.shardingsphere.infra.binder.statement.ddl.CreateTableStatementContext;
+import
org.apache.shardingsphere.infra.binder.statement.ddl.TruncateStatementContext;
import
org.apache.shardingsphere.infra.binder.statement.dml.InsertStatementContext;
import org.apache.shardingsphere.infra.database.DefaultDatabase;
import org.apache.shardingsphere.infra.executor.sql.context.ExecutionContext;
@@ -41,8 +43,10 @@ import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.Sim
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.TableNameSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.value.identifier.IdentifierValue;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.ddl.MySQLCreateTableStatement;
+import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.ddl.MySQLTruncateStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dml.MySQLInsertStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.postgresql.ddl.PostgreSQLCreateTableStatement;
+import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.postgresql.ddl.PostgreSQLTruncateStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.postgresql.dml.PostgreSQLInsertStatement;
import org.apache.shardingsphere.transaction.core.TransactionType;
import org.junit.Before;
@@ -84,6 +88,21 @@ public final class ProxySQLExecutorTest extends
ProxyContextRestorer {
new ProxySQLExecutor(JDBCDriverType.STATEMENT, backendConnection,
mock(JDBCDatabaseCommunicationEngine.class)).checkExecutePrerequisites(executionContext);
}
+ @Test(expected = TableModifyInTransactionException.class)
+ public void
assertCheckExecutePrerequisitesWhenExecuteTruncateInMySQLXATransaction() {
+ ExecutionContext executionContext = new ExecutionContext(
+ new LogicSQL(createMySQLTruncateStatementContext(), "",
Collections.emptyList()), Collections.emptyList(), mock(RouteContext.class));
+ new ProxySQLExecutor(JDBCDriverType.STATEMENT, backendConnection,
mock(JDBCDatabaseCommunicationEngine.class)).checkExecutePrerequisites(executionContext);
+ }
+
+ @Test
+ public void
assertCheckExecutePrerequisitesWhenExecuteTruncateInMySQLLocalTransaction() {
+
when(connectionSession.getTransactionStatus().getTransactionType()).thenReturn(TransactionType.LOCAL);
+ ExecutionContext executionContext = new ExecutionContext(
+ new LogicSQL(createMySQLTruncateStatementContext(), "",
Collections.emptyList()), Collections.emptyList(), mock(RouteContext.class));
+ new ProxySQLExecutor(JDBCDriverType.STATEMENT, backendConnection,
mock(JDBCDatabaseCommunicationEngine.class)).checkExecutePrerequisites(executionContext);
+ }
+
@Test
public void assertCheckExecutePrerequisitesWhenExecuteDMLInXATransaction()
{
ExecutionContext executionContext = new ExecutionContext(
@@ -115,6 +134,14 @@ public final class ProxySQLExecutorTest extends
ProxyContextRestorer {
new ProxySQLExecutor(JDBCDriverType.STATEMENT, backendConnection,
mock(JDBCDatabaseCommunicationEngine.class)).checkExecutePrerequisites(executionContext);
}
+ @Test
+ public void
assertCheckExecutePrerequisitesWhenExecuteTruncateInPostgreSQLTransaction() {
+
when(connectionSession.getTransactionStatus().getTransactionType()).thenReturn(TransactionType.LOCAL);
+ ExecutionContext executionContext = new ExecutionContext(
+ new LogicSQL(createPostgreSQLTruncateStatementContext(), "",
Collections.emptyList()), Collections.emptyList(), mock(RouteContext.class));
+ new ProxySQLExecutor(JDBCDriverType.STATEMENT, backendConnection,
mock(JDBCDatabaseCommunicationEngine.class)).checkExecutePrerequisites(executionContext);
+ }
+
@Test
public void
assertCheckExecutePrerequisitesWhenExecuteDMLInPostgreSQLTransaction() {
when(connectionSession.getTransactionStatus().getTransactionType()).thenReturn(TransactionType.LOCAL);
@@ -146,6 +173,18 @@ public final class ProxySQLExecutorTest extends
ProxyContextRestorer {
return new CreateTableStatementContext(sqlStatement);
}
+ private TruncateStatementContext createMySQLTruncateStatementContext() {
+ MySQLTruncateStatement sqlStatement = new MySQLTruncateStatement();
+ sqlStatement.getTables().add(new SimpleTableSegment(new
TableNameSegment(0, 0, new IdentifierValue("t_order"))));
+ return new TruncateStatementContext(sqlStatement);
+ }
+
+ private SQLStatementContext<?> createPostgreSQLTruncateStatementContext() {
+ PostgreSQLTruncateStatement sqlStatement = new
PostgreSQLTruncateStatement();
+ sqlStatement.getTables().add(new SimpleTableSegment(new
TableNameSegment(0, 0, new IdentifierValue("t_order"))));
+ return new TruncateStatementContext(sqlStatement);
+ }
+
private InsertStatementContext createMySQLInsertStatementContext() {
MySQLInsertStatement sqlStatement = new MySQLInsertStatement();
sqlStatement.setTable(new SimpleTableSegment(new TableNameSegment(0,
0, new IdentifierValue("t_order"))));
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/java/org/apache/shardingsphere/integration/transaction/cases/truncate/MySQLLocalTruncateTestCase.java
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/java/org/apache/shardingsphere/integration/transaction/cases/truncate/MySQLLocalTruncateTestCase.java
new file mode 100644
index 00000000000..7e6936c2a06
--- /dev/null
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/java/org/apache/shardingsphere/integration/transaction/cases/truncate/MySQLLocalTruncateTestCase.java
@@ -0,0 +1,81 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.integration.transaction.cases.truncate;
+
+import lombok.SneakyThrows;
+import lombok.extern.slf4j.Slf4j;
+import
org.apache.shardingsphere.integration.transaction.cases.base.BaseTransactionTestCase;
+import
org.apache.shardingsphere.integration.transaction.engine.base.BaseTransactionITCase;
+import
org.apache.shardingsphere.integration.transaction.engine.base.TransactionTestCase;
+import
org.apache.shardingsphere.integration.transaction.engine.constants.TransactionTestConstants;
+import org.apache.shardingsphere.transaction.core.TransactionType;
+
+import javax.sql.DataSource;
+import java.sql.Connection;
+import java.sql.SQLException;
+
+/**
+ * MySQL truncate local transaction integration test.
+ */
+@Slf4j
+@TransactionTestCase(dbTypes = {TransactionTestConstants.MYSQL},
transactionTypes = {TransactionType.LOCAL})
+public final class MySQLLocalTruncateTestCase extends BaseTransactionTestCase {
+
+ public MySQLLocalTruncateTestCase(final BaseTransactionITCase
baseTransactionITCase, final DataSource dataSource) {
+ super(baseTransactionITCase, dataSource);
+ }
+
+ @Override
+ @SneakyThrows(SQLException.class)
+ public void executeTest() {
+ assertTruncateRollback();
+ assertTruncateCommit();
+ }
+
+ private void assertTruncateRollback() throws SQLException {
+ prepare();
+ Connection conn = getDataSource().getConnection();
+ conn.setAutoCommit(false);
+ assertAccountRowCount(conn, 8);
+ executeWithLog(conn, "truncate account;");
+ assertAccountRowCount(conn, 0);
+ conn.rollback();
+ // Expected truncate operation cannot be rolled back in MySQL local
transaction
+ assertAccountRowCount(conn, 0);
+ conn.close();
+ }
+
+ public void assertTruncateCommit() throws SQLException {
+ prepare();
+ Connection conn = getDataSource().getConnection();
+ conn.setAutoCommit(false);
+ assertAccountRowCount(conn, 8);
+ executeWithLog(conn, "truncate account;");
+ assertAccountRowCount(conn, 0);
+ conn.commit();
+ assertAccountRowCount(conn, 0);
+ conn.close();
+ }
+
+ private void prepare() throws SQLException {
+ Connection conn = getDataSource().getConnection();
+ executeWithLog(conn, "delete from account;");
+ executeWithLog(conn, "insert into account(id, balance, transaction_id)
values(1, 1, 1),(2, 2, 2),(3, 3, 3),(4, 4, 4),(5, 5, 5),(6, 6, 6),(7, 7, 7),(8,
8, 8);");
+ conn.close();
+ }
+}
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/java/org/apache/shardingsphere/integration/transaction/cases/truncate/MySQLXATruncateTestCase.java
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/java/org/apache/shardingsphere/integration/transaction/cases/truncate/MySQLXATruncateTestCase.java
new file mode 100644
index 00000000000..a0a00dbe218
--- /dev/null
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/java/org/apache/shardingsphere/integration/transaction/cases/truncate/MySQLXATruncateTestCase.java
@@ -0,0 +1,77 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.integration.transaction.cases.truncate;
+
+import lombok.SneakyThrows;
+import lombok.extern.slf4j.Slf4j;
+import
org.apache.shardingsphere.integration.transaction.cases.base.BaseTransactionTestCase;
+import
org.apache.shardingsphere.integration.transaction.engine.base.BaseTransactionITCase;
+import
org.apache.shardingsphere.integration.transaction.engine.base.TransactionTestCase;
+import
org.apache.shardingsphere.integration.transaction.engine.constants.TransactionTestConstants;
+import
org.apache.shardingsphere.proxy.backend.exception.TableModifyInTransactionException;
+import org.apache.shardingsphere.transaction.core.TransactionType;
+
+import javax.sql.DataSource;
+import java.sql.Connection;
+import java.sql.SQLException;
+
+import static org.junit.Assert.fail;
+
+/**
+ * MySQL truncate XA transaction integration test.
+ */
+@Slf4j
+@TransactionTestCase(dbTypes = {TransactionTestConstants.MYSQL}, adapters =
{TransactionTestConstants.PROXY}, transactionTypes = {TransactionType.XA})
+public final class MySQLXATruncateTestCase extends BaseTransactionTestCase {
+
+ public MySQLXATruncateTestCase(final BaseTransactionITCase
baseTransactionITCase, final DataSource dataSource) {
+ super(baseTransactionITCase, dataSource);
+ }
+
+ @Override
+ @SneakyThrows(SQLException.class)
+ public void executeTest() {
+ assertTruncateInMySQLXATransaction();
+ }
+
+ private void assertTruncateInMySQLXATransaction() throws SQLException {
+ // TODO This test case may cause bad effects to other test cases in
JDBC adapter
+ prepare();
+ Connection conn = getDataSource().getConnection();
+ conn.setAutoCommit(false);
+ assertAccountRowCount(conn, 8);
+ try {
+ conn.createStatement().execute("truncate account;");
+ fail("Expect exception, but no exception report.");
+ } catch (TableModifyInTransactionException ex) {
+ log.info("Exception for expected in Proxy: {}", ex.getMessage());
+ } catch (SQLException ex) {
+ log.info("Exception for expected in JDBC: {}", ex.getMessage());
+ } finally {
+ conn.rollback();
+ conn.close();
+ }
+ }
+
+ private void prepare() throws SQLException {
+ Connection conn = getDataSource().getConnection();
+ executeWithLog(conn, "delete from account;");
+ executeWithLog(conn, "insert into account(id, balance, transaction_id)
values(1, 1, 1),(2, 2, 2),(3, 3, 3),(4, 4, 4),(5, 5, 5),(6, 6, 6),(7, 7, 7),(8,
8, 8);");
+ conn.close();
+ }
+}
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/java/org/apache/shardingsphere/integration/transaction/cases/truncate/PostgresSQLAndOpenGaussTruncateTestCase.java
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/java/org/apache/shardingsphere/integration/transaction/cases/truncate/PostgresSQLAndOpenGaussTruncateTestCase.java
new file mode 100644
index 00000000000..b5be524b68d
--- /dev/null
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/java/org/apache/shardingsphere/integration/transaction/cases/truncate/PostgresSQLAndOpenGaussTruncateTestCase.java
@@ -0,0 +1,80 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.integration.transaction.cases.truncate;
+
+import lombok.SneakyThrows;
+import lombok.extern.slf4j.Slf4j;
+import
org.apache.shardingsphere.integration.transaction.cases.base.BaseTransactionTestCase;
+import
org.apache.shardingsphere.integration.transaction.engine.base.BaseTransactionITCase;
+import
org.apache.shardingsphere.integration.transaction.engine.base.TransactionTestCase;
+import
org.apache.shardingsphere.integration.transaction.engine.constants.TransactionTestConstants;
+
+import javax.sql.DataSource;
+import java.sql.Connection;
+import java.sql.SQLException;
+
+/**
+ * PostgresSQL and OpenGauss truncate transaction integration test.
+ */
+@Slf4j
+@TransactionTestCase(dbTypes = {TransactionTestConstants.POSTGRESQL,
TransactionTestConstants.OPENGAUSS})
+public final class PostgresSQLAndOpenGaussTruncateTestCase extends
BaseTransactionTestCase {
+
+ public PostgresSQLAndOpenGaussTruncateTestCase(final BaseTransactionITCase
baseTransactionITCase, final DataSource dataSource) {
+ super(baseTransactionITCase, dataSource);
+ }
+
+ @Override
+ @SneakyThrows(SQLException.class)
+ public void executeTest() {
+ assertTruncateRollback();
+ assertTruncateCommit();
+ }
+
+ private void assertTruncateRollback() throws SQLException {
+ prepare();
+ Connection conn = getDataSource().getConnection();
+ conn.setAutoCommit(false);
+ assertAccountRowCount(conn, 8);
+ executeWithLog(conn, "truncate account;");
+ assertAccountRowCount(conn, 0);
+ conn.rollback();
+ // Expected truncate operation can be rolled back in PostgreSQL &
OpenGauss
+ assertAccountRowCount(conn, 8);
+ conn.close();
+ }
+
+ public void assertTruncateCommit() throws SQLException {
+ prepare();
+ Connection conn = getDataSource().getConnection();
+ conn.setAutoCommit(false);
+ assertAccountRowCount(conn, 8);
+ executeWithLog(conn, "truncate account;");
+ assertAccountRowCount(conn, 0);
+ conn.commit();
+ assertAccountRowCount(conn, 0);
+ conn.close();
+ }
+
+ private void prepare() throws SQLException {
+ Connection conn = getDataSource().getConnection();
+ executeWithLog(conn, "delete from account;");
+ executeWithLog(conn, "insert into account(id, balance, transaction_id)
values(1, 1, 1),(2, 2, 2),(3, 3, 3),(4, 4, 4),(5, 5, 5),(6, 6, 6),(7, 7, 7),(8,
8, 8);");
+ conn.close();
+ }
+}
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/common/command.xml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/common/command.xml
index 6d3e41c49c8..467e973ad7c 100644
---
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/common/command.xml
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/common/command.xml
@@ -42,7 +42,7 @@
<create-account-sharding-algorithm>
CREATE SHARDING ALGORITHM account_inline (
- TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="account_${id %
2}"))
+
TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="account_${Math.floorMod(Math.floorDiv(id.longValue(),
2L), 2L)}"))
)
</create-account-sharding-algorithm>
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/mysql/config-sharding-local.yaml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/mysql/config-sharding-local.yaml
index 51c01e70f9f..733ffe4fbb7 100644
---
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/mysql/config-sharding-local.yaml
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/mysql/config-sharding-local.yaml
@@ -87,7 +87,7 @@ rules:
account_inline:
type: INLINE
props:
- algorithm-expression: account_${id % 2}
+ algorithm-expression:
account_${Math.floorMod(Math.floorDiv(id.longValue(), 2L), 2L)}
keyGenerators:
snowflake:
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/mysql/config-sharding-xa-atomikos.yaml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/mysql/config-sharding-xa-atomikos.yaml
index d76760f284d..949ad464755 100644
---
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/mysql/config-sharding-xa-atomikos.yaml
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/mysql/config-sharding-xa-atomikos.yaml
@@ -88,7 +88,7 @@ rules:
account_inline:
type: INLINE
props:
- algorithm-expression: account_${id % 2}
+ algorithm-expression:
account_${Math.floorMod(Math.floorDiv(id.longValue(), 2L), 2L)}
keyGenerators:
snowflake:
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/opengauss/config-sharding-local.yaml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/opengauss/config-sharding-local.yaml
index 51c01e70f9f..733ffe4fbb7 100644
---
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/opengauss/config-sharding-local.yaml
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/opengauss/config-sharding-local.yaml
@@ -87,7 +87,7 @@ rules:
account_inline:
type: INLINE
props:
- algorithm-expression: account_${id % 2}
+ algorithm-expression:
account_${Math.floorMod(Math.floorDiv(id.longValue(), 2L), 2L)}
keyGenerators:
snowflake:
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/opengauss/config-sharding-xa-atomikos.yaml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/opengauss/config-sharding-xa-atomikos.yaml
index d76760f284d..949ad464755 100644
---
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/opengauss/config-sharding-xa-atomikos.yaml
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/opengauss/config-sharding-xa-atomikos.yaml
@@ -88,7 +88,7 @@ rules:
account_inline:
type: INLINE
props:
- algorithm-expression: account_${id % 2}
+ algorithm-expression:
account_${Math.floorMod(Math.floorDiv(id.longValue(), 2L), 2L)}
keyGenerators:
snowflake:
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/postgresql/config-sharding-local.yaml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/postgresql/config-sharding-local.yaml
index 51c01e70f9f..733ffe4fbb7 100644
---
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/postgresql/config-sharding-local.yaml
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/postgresql/config-sharding-local.yaml
@@ -87,7 +87,7 @@ rules:
account_inline:
type: INLINE
props:
- algorithm-expression: account_${id % 2}
+ algorithm-expression:
account_${Math.floorMod(Math.floorDiv(id.longValue(), 2L), 2L)}
keyGenerators:
snowflake:
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/postgresql/config-sharding-xa-atomikos.yaml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/postgresql/config-sharding-xa-atomikos.yaml
index d76760f284d..949ad464755 100644
---
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/postgresql/config-sharding-xa-atomikos.yaml
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/jdbc/postgresql/config-sharding-xa-atomikos.yaml
@@ -88,7 +88,7 @@ rules:
account_inline:
type: INLINE
props:
- algorithm-expression: account_${id % 2}
+ algorithm-expression:
account_${Math.floorMod(Math.floorDiv(id.longValue(), 2L), 2L)}
keyGenerators:
snowflake:
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/transaction-it-env.properties
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/transaction-it-env.properties
index df6fee6d852..76f5fd1150d 100644
---
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/transaction-it-env.properties
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-transaction/src/test/resources/env/transaction-it-env.properties
@@ -17,7 +17,7 @@
# transaction.it.type=DOCKER,NATIVE
transaction.it.env.type=
# transaction.it.env.cases= MySQLAutoCommitTestCase,
PostgresSQLAutoCommitTestCase, ClassicTransferTestCase,
MultiTableCommitAndRollbackTestCase, SingleTableCommitAndRollbackTestCase,
MySQLSetReadOnlyTestCase, MySQLSavePointTestCase, PostgreSQLSavePointTestCase
-transaction.it.env.cases=PostgresSQLAutoCommitTestCase,
MultiTableCommitAndRollbackTestCase, SingleTableCommitAndRollbackTestCase,
MySQLSetReadOnlyTestCase, MySQLSavePointTestCase, AddResourceTestCase,
CloseResourceTestCase
+transaction.it.env.cases=PostgresSQLAutoCommitTestCase,
MultiTableCommitAndRollbackTestCase, SingleTableCommitAndRollbackTestCase,
MySQLSetReadOnlyTestCase, MySQLSavePointTestCase, AddResourceTestCase,
CloseResourceTestCase, PostgresSQLAndOpenGaussTruncateTestCase,
MySQLLocalTruncateTestCase, MySQLXATruncateTestCase
# transaction.it.env.transtypes=LOCAL, XA, BASE
transaction.it.env.transtypes=LOCAL, XA
# transaction.it.env.xa.providers=Atomikos, Bitronix, Narayana
