This is an automated email from the ASF dual-hosted git repository.
terrymanu 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 9d7a10d933b Fix PostgreSQL transactional DDL error handling (#39288)
9d7a10d933b is described below
commit 9d7a10d933b16cf5b8569314d38961df62dbf11e
Author: Liang Zhang <[email protected]>
AuthorDate: Fri Jul 31 14:50:19 2026 +0800
Fix PostgreSQL transactional DDL error handling (#39288)
Map rejected metadata-changing DDL statements to SQLSTATE 0A000 for
PostgreSQL and openGauss, and document the transaction limitation.
---
.../option/transaction/DialectTransactionOption.java | 1 -
.../mapper/PostgreSQLDialectExceptionMapper.java | 4 ++++
.../exception/postgresql/vendor/PostgreSQLVendorError.java | 3 +++
.../mapper/PostgreSQLDialectExceptionMapperTest.java | 3 +++
docs/document/content/features/transaction/appendix.cn.md | 2 +-
docs/document/content/features/transaction/appendix.en.md | 2 +-
.../content/features/transaction/limitations.cn.md | 3 +++
.../content/features/transaction/limitations.en.md | 3 +++
.../opengauss/err/OpenGaussErrorPacketFactoryTest.java | 14 ++++++++++++++
.../postgresql/err/PostgreSQLErrorPacketFactoryTest.java | 10 ++++++++++
10 files changed, 42 insertions(+), 3 deletions(-)
diff --git
a/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/transaction/DialectTransactionOption.java
b/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/transaction/DialectTransactionOption.java
index c5ccf03df80..9994e3376b2 100644
---
a/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/transaction/DialectTransactionOption.java
+++
b/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/transaction/DialectTransactionOption.java
@@ -39,7 +39,6 @@ public final class DialectTransactionOption {
private final boolean isSupportDDLInXATransaction;
- // TODO Investigate the reason of some databases cannot support meta data
refreshed in transaction. The method should be removed finally after metadata
refresh supported for all database.
private final boolean isSupportMetaDataRefreshInTransaction;
private final boolean isReturnRollbackStatementWhenCommitFailed;
diff --git
a/database/exception/dialect/postgresql/src/main/java/org/apache/shardingsphere/database/exception/postgresql/mapper/PostgreSQLDialectExceptionMapper.java
b/database/exception/dialect/postgresql/src/main/java/org/apache/shardingsphere/database/exception/postgresql/mapper/PostgreSQLDialectExceptionMapper.java
index b274c0594c1..f409b915148 100644
---
a/database/exception/dialect/postgresql/src/main/java/org/apache/shardingsphere/database/exception/postgresql/mapper/PostgreSQLDialectExceptionMapper.java
+++
b/database/exception/dialect/postgresql/src/main/java/org/apache/shardingsphere/database/exception/postgresql/mapper/PostgreSQLDialectExceptionMapper.java
@@ -27,6 +27,7 @@ import
org.apache.shardingsphere.database.exception.core.exception.syntax.databa
import
org.apache.shardingsphere.database.exception.core.exception.syntax.table.NoSuchTableException;
import
org.apache.shardingsphere.database.exception.core.exception.syntax.table.TableExistsException;
import
org.apache.shardingsphere.database.exception.core.exception.transaction.InTransactionException;
+import
org.apache.shardingsphere.database.exception.core.exception.transaction.TableModifyInTransactionException;
import
org.apache.shardingsphere.database.exception.core.mapper.SQLDialectExceptionMapper;
import
org.apache.shardingsphere.database.exception.postgresql.exception.PostgreSQLException;
import
org.apache.shardingsphere.database.exception.postgresql.exception.PostgreSQLException.ServerErrorMessage;
@@ -63,6 +64,9 @@ public final class PostgreSQLDialectExceptionMapper
implements SQLDialectExcepti
if (sqlDialectException instanceof TableExistsException) {
return new PostgreSQLException(new
ServerErrorMessage(ERROR_SEVERITY, PostgreSQLVendorError.DUPLICATE_TABLE,
((TableExistsException) sqlDialectException).getTableName()));
}
+ if (sqlDialectException instanceof TableModifyInTransactionException) {
+ return new PostgreSQLException(new
ServerErrorMessage(ERROR_SEVERITY,
PostgreSQLVendorError.METADATA_CHANGING_DDL_IN_TRANSACTION_NOT_SUPPORTED));
+ }
if (sqlDialectException instanceof InTransactionException) {
return new PostgreSQLException(new
ServerErrorMessage(ERROR_SEVERITY,
PostgreSQLVendorError.TRANSACTION_STATE_INVALID));
}
diff --git
a/database/exception/dialect/postgresql/src/main/java/org/apache/shardingsphere/database/exception/postgresql/vendor/PostgreSQLVendorError.java
b/database/exception/dialect/postgresql/src/main/java/org/apache/shardingsphere/database/exception/postgresql/vendor/PostgreSQLVendorError.java
index 67ecfc08645..68873c2e68e 100644
---
a/database/exception/dialect/postgresql/src/main/java/org/apache/shardingsphere/database/exception/postgresql/vendor/PostgreSQLVendorError.java
+++
b/database/exception/dialect/postgresql/src/main/java/org/apache/shardingsphere/database/exception/postgresql/vendor/PostgreSQLVendorError.java
@@ -41,6 +41,9 @@ public enum PostgreSQLVendorError implements VendorError {
FEATURE_NOT_SUPPORTED(XOpenSQLState.FEATURE_NOT_SUPPORTED,
"feature_not_supported"),
+ METADATA_CHANGING_DDL_IN_TRANSACTION_NOT_SUPPORTED(
+ XOpenSQLState.FEATURE_NOT_SUPPORTED, "DDL statements that modify
metadata are not supported in transactions by ShardingSphere-Proxy."),
+
DUPLICATE_DATABASE(PostgreSQLState.DUPLICATE_DATABASE, "Database '%s'
already exists"),
DUPLICATE_TABLE(PostgreSQLState.DUPLICATE_TABLE, "Table '%s' already
exists"),
diff --git
a/database/exception/dialect/postgresql/src/test/java/org/apache/shardingsphere/database/exception/postgresql/mapper/PostgreSQLDialectExceptionMapperTest.java
b/database/exception/dialect/postgresql/src/test/java/org/apache/shardingsphere/database/exception/postgresql/mapper/PostgreSQLDialectExceptionMapperTest.java
index 36628af1214..c8afda8d893 100644
---
a/database/exception/dialect/postgresql/src/test/java/org/apache/shardingsphere/database/exception/postgresql/mapper/PostgreSQLDialectExceptionMapperTest.java
+++
b/database/exception/dialect/postgresql/src/test/java/org/apache/shardingsphere/database/exception/postgresql/mapper/PostgreSQLDialectExceptionMapperTest.java
@@ -29,6 +29,7 @@ import
org.apache.shardingsphere.database.exception.core.exception.syntax.databa
import
org.apache.shardingsphere.database.exception.core.exception.syntax.table.NoSuchTableException;
import
org.apache.shardingsphere.database.exception.core.exception.syntax.table.TableExistsException;
import
org.apache.shardingsphere.database.exception.core.exception.transaction.InTransactionException;
+import
org.apache.shardingsphere.database.exception.core.exception.transaction.TableModifyInTransactionException;
import
org.apache.shardingsphere.database.exception.core.mapper.SQLDialectExceptionMapper;
import
org.apache.shardingsphere.database.exception.postgresql.exception.PostgreSQLException;
import
org.apache.shardingsphere.database.exception.postgresql.exception.authority.EmptyUsernameException;
@@ -95,6 +96,8 @@ class PostgreSQLDialectExceptionMapperTest {
Arguments.of("database_create_exists",
DatabaseCreateExistsException.class, PostgreSQLVendorError.DUPLICATE_DATABASE,
"FATAL"),
Arguments.of("no_such_table", NoSuchTableException.class,
PostgreSQLVendorError.NO_SUCH_TABLE, "FATAL"),
Arguments.of("table_exists", TableExistsException.class,
PostgreSQLVendorError.DUPLICATE_TABLE, "ERROR"),
+ Arguments.of("table_modify_in_transaction",
TableModifyInTransactionException.class,
+
PostgreSQLVendorError.METADATA_CHANGING_DDL_IN_TRANSACTION_NOT_SUPPORTED,
"ERROR"),
Arguments.of("in_transaction", InTransactionException.class,
PostgreSQLVendorError.TRANSACTION_STATE_INVALID, "ERROR"),
Arguments.of("insert_columns_and_values_mismatched",
InsertColumnsAndValuesMismatchedException.class,
PostgreSQLVendorError.WRONG_VALUE_COUNT_ON_ROW, "ERROR"),
Arguments.of("invalid_parameter_value",
InvalidParameterValueException.class,
PostgreSQLVendorError.INVALID_PARAMETER_VALUE, "ERROR"),
diff --git a/docs/document/content/features/transaction/appendix.cn.md
b/docs/document/content/features/transaction/appendix.cn.md
index 3eba19e6f8c..f98200bc24b 100644
--- a/docs/document/content/features/transaction/appendix.cn.md
+++ b/docs/document/content/features/transaction/appendix.cn.md
@@ -6,7 +6,7 @@ weight = 3
不支持的 SQL：
- 事务中使用 DistSQL 里的 RAL、RDL 操作；
-- XA 事务中使用 DDL 语句。
+- XA 事务中的 DDL 支持取决于数据库方言；PostgreSQL 和 openGauss 仅支持不改变元数据的 DDL 语句。
XA 事务所需的权限：
diff --git a/docs/document/content/features/transaction/appendix.en.md
b/docs/document/content/features/transaction/appendix.en.md
index 0b1de2f797f..81521f7b1a2 100644
--- a/docs/document/content/features/transaction/appendix.en.md
+++ b/docs/document/content/features/transaction/appendix.en.md
@@ -6,7 +6,7 @@ weight = 3
Unsupported SQL：
- RAL and RDL operations of DistSQL that are used in transactions.
-- DDL statements that are used in XA transactions.
+- DDL support in XA transactions depends on the database dialect. PostgreSQL
and openGauss permit only DDL statements that do not change metadata.
Privileges required for XA transactions:
diff --git a/docs/document/content/features/transaction/limitations.cn.md
b/docs/document/content/features/transaction/limitations.cn.md
index ffdc64af2c0..4d23a897160 100644
--- a/docs/document/content/features/transaction/limitations.cn.md
+++ b/docs/document/content/features/transaction/limitations.cn.md
@@ -11,6 +11,8 @@ Apache ShardingSphere 希望能够将分布式事务的选择权交给使用者
### 不支持项
* 不支持因网络、硬件异常导致的跨库事务。例如：同一事务中，跨两个库更新，更新完毕后、未提交之前，第一个库宕机，则只有第二个库数据提交，且无法回滚。
+* ShardingSphere-Proxy 使用 PostgreSQL 或 openGauss 时，不支持在事务中执行会改变元数据的 DDL 语句（如
`CREATE`、`ALTER` 和 `DROP`）。
+ 此类语句会在执行前以 SQLSTATE `0A000` 拒绝，避免事务回滚后存储数据库与 ShardingSphere 元数据不一致。
## XA 事务
@@ -19,6 +21,7 @@ Apache ShardingSphere 希望能够将分布式事务的选择权交给使用者
* 服务宕机后，在其它机器上恢复提交/回滚中的数据；
* MySQL 事务块内，SQL 执行出现异常，执行 `Commit`，数据保持一致；
* 配置 XA 事务后，存储单元名称最大长度不超过45个字符。
+* PostgreSQL 和 openGauss 同样受 LOCAL 事务中所述的元数据变更 DDL 限制。
## BASE 事务
diff --git a/docs/document/content/features/transaction/limitations.en.md
b/docs/document/content/features/transaction/limitations.en.md
index 7d45b2f90ea..a7186f8c809 100644
--- a/docs/document/content/features/transaction/limitations.en.md
+++ b/docs/document/content/features/transaction/limitations.en.md
@@ -12,6 +12,8 @@ The Apache ShardingSphere community chose instead to give the
users the ability
### Unsupported
* Does not support the cross-database transactions caused by network or
hardware crash. For example, when updating two databases in transaction, if one
database crashes before commit, then only the data of the other database can
commit.
+* When ShardingSphere-Proxy uses PostgreSQL or openGauss, metadata-changing
DDL statements such as `CREATE`, `ALTER`, and `DROP` are not supported in
transactions.
+ Such statements are rejected before execution with SQLSTATE `0A000` to
prevent inconsistencies between storage and ShardingSphere metadata after a
transaction rollback.
## XA Transaction
@@ -20,6 +22,7 @@ The Apache ShardingSphere community chose instead to give the
users the ability
* Recover committing and rolling back in other machines after the service is
down.
* MySQL, in the transaction block, the SQL execution is abnormal, and run
`Commit`, and data remains consistent.
* After XA transactions are configured, the maximum length of the storage unit
name cannot exceed 45 characters.
+* PostgreSQL and openGauss have the same metadata-changing DDL restriction
described for LOCAL transactions.
## BASE Transaction
diff --git
a/proxy/frontend/dialect/opengauss/src/test/java/org/apache/shardingsphere/proxy/frontend/opengauss/err/OpenGaussErrorPacketFactoryTest.java
b/proxy/frontend/dialect/opengauss/src/test/java/org/apache/shardingsphere/proxy/frontend/opengauss/err/OpenGaussErrorPacketFactoryTest.java
index 6f4761eb05c..a29a93957da 100644
---
a/proxy/frontend/dialect/opengauss/src/test/java/org/apache/shardingsphere/proxy/frontend/opengauss/err/OpenGaussErrorPacketFactoryTest.java
+++
b/proxy/frontend/dialect/opengauss/src/test/java/org/apache/shardingsphere/proxy/frontend/opengauss/err/OpenGaussErrorPacketFactoryTest.java
@@ -18,6 +18,8 @@
package org.apache.shardingsphere.proxy.frontend.opengauss.err;
import lombok.SneakyThrows;
+import
org.apache.shardingsphere.database.exception.core.exception.transaction.TableModifyInTransactionException;
+import
org.apache.shardingsphere.database.exception.postgresql.vendor.PostgreSQLVendorError;
import
org.apache.shardingsphere.database.protocol.opengauss.packet.command.generic.OpenGaussErrorResponsePacket;
import org.junit.jupiter.api.Test;
import org.mockito.internal.configuration.plugins.Plugins;
@@ -78,6 +80,18 @@ class OpenGaussErrorPacketFactoryTest {
assertThat(actualFields.get(OpenGaussErrorResponsePacket.FIELD_TYPE_ERROR_CODE),
is("0"));
}
+ @Test
+ void assertNewInstanceWithTableModifyInTransactionException() {
+ OpenGaussErrorResponsePacket actual =
OpenGaussErrorPacketFactory.newInstance(new
TableModifyInTransactionException("foo_table"));
+ Map<Character, String> actualFields = getFieldsInPacket(actual);
+ assertThat(actualFields.size(), is(4));
+
assertThat(actualFields.get(OpenGaussErrorResponsePacket.FIELD_TYPE_SEVERITY),
is("ERROR"));
+
assertThat(actualFields.get(OpenGaussErrorResponsePacket.FIELD_TYPE_CODE),
is(PostgreSQLVendorError.METADATA_CHANGING_DDL_IN_TRANSACTION_NOT_SUPPORTED.getSqlState().getValue()));
+
assertThat(actualFields.get(OpenGaussErrorResponsePacket.FIELD_TYPE_MESSAGE),
+ is("ERROR: DDL statements that modify metadata are not
supported in transactions by ShardingSphere-Proxy.\n Server SQLState: 0A000"));
+
assertThat(actualFields.get(OpenGaussErrorResponsePacket.FIELD_TYPE_ERROR_CODE),
is("0"));
+ }
+
@SuppressWarnings("unchecked")
@SneakyThrows(ReflectiveOperationException.class)
private Map<Character, String> getFieldsInPacket(final
OpenGaussErrorResponsePacket packet) {
diff --git
a/proxy/frontend/dialect/postgresql/src/test/java/org/apache/shardingsphere/proxy/frontend/postgresql/err/PostgreSQLErrorPacketFactoryTest.java
b/proxy/frontend/dialect/postgresql/src/test/java/org/apache/shardingsphere/proxy/frontend/postgresql/err/PostgreSQLErrorPacketFactoryTest.java
index 074c11e490d..1d8c4f10510 100644
---
a/proxy/frontend/dialect/postgresql/src/test/java/org/apache/shardingsphere/proxy/frontend/postgresql/err/PostgreSQLErrorPacketFactoryTest.java
+++
b/proxy/frontend/dialect/postgresql/src/test/java/org/apache/shardingsphere/proxy/frontend/postgresql/err/PostgreSQLErrorPacketFactoryTest.java
@@ -17,6 +17,7 @@
package org.apache.shardingsphere.proxy.frontend.postgresql.err;
+import
org.apache.shardingsphere.database.exception.core.exception.transaction.TableModifyInTransactionException;
import
org.apache.shardingsphere.database.exception.postgresql.exception.PostgreSQLException;
import
org.apache.shardingsphere.database.exception.postgresql.vendor.PostgreSQLVendorError;
import
org.apache.shardingsphere.database.protocol.postgresql.constant.PostgreSQLMessageSeverityLevel;
@@ -66,6 +67,15 @@ class PostgreSQLErrorPacketFactoryTest {
assertThat(fields.get(PostgreSQLErrorResponsePacket.FIELD_TYPE_MESSAGE),
is("Unknown exception." + System.lineSeparator() + "More details:
java.lang.RuntimeException: No reason"));
}
+ @Test
+ void assertTableModifyInTransactionException() throws
ReflectiveOperationException {
+ PostgreSQLErrorResponsePacket actual =
PostgreSQLErrorPacketFactory.newInstance(new
TableModifyInTransactionException("foo_table"));
+ Map<Character, String> fields = getFields(actual);
+
assertThat(fields.get(PostgreSQLErrorResponsePacket.FIELD_TYPE_SEVERITY),
is(PostgreSQLMessageSeverityLevel.ERROR));
+ assertThat(fields.get(PostgreSQLErrorResponsePacket.FIELD_TYPE_CODE),
is(PostgreSQLVendorError.METADATA_CHANGING_DDL_IN_TRANSACTION_NOT_SUPPORTED.getSqlState().getValue()));
+
assertThat(fields.get(PostgreSQLErrorResponsePacket.FIELD_TYPE_MESSAGE),
is("DDL statements that modify metadata are not supported in transactions by
ShardingSphere-Proxy."));
+ }
+
@Test
void assertPostgreSQLExceptionWithServerErrorMessage() throws
ReflectiveOperationException {
PostgreSQLException.ServerErrorMessage serverErrorMessage = new
PostgreSQLException.ServerErrorMessage(
