Metadata Not Refreshed After Executing DDL Within PostgreSQL Transaction

[FlyingZC]

Bug Report

After removing the restriction on executing DDL within PostgreSQL transactions in ShardingSphere(org.apache.shardingsphere.proxy.backend.connector.ProxySQLExecutor#checkExecutePrerequisites), the following issue is observed:

When a DDL statement that modifies metadata (e.g., ALTER TABLE t1 ADD COLUMN c1 INT) is executed within a transaction, the refresh metadata operation uses a new physical connection. Since the transaction is not yet committed, the metadata changes from the DDL are not visible, leading to failure in refreshing metadata (e.g., new columns are not detected).

Which version of ShardingSphere did you use?

Master.

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

After the DDL is executed in the postgresql transaction, the metadata information in ShardingSphere should be consistent with the table structure on PostgreSQL.

Actual behavior

The DDL executed successfully in the transaction, but the corresponding metadata was not refreshed successfully on ShardingSphere.

Reason analyze (If you can)

Track all table names involved in metadata-changing DDL operations within the PostgreSQL transaction. ​​Defer metadata refresh​​ until the transaction is committed, then perform a unified metadata refresh for the affected tables.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1. Start a PostgreSQL transaction.
2. Execute a DDL statement within the transaction (e.g., ALTER TABLE t1 ADD COLUMN c1 INT).
3. After committing the transaction, query metadata again and observe that the new column is not refreshed. ​​Expected Behavior​​ Metadata should be automatically refreshed after transaction commit to ensure changes (e.g., new columns) take effect.

-- PostgreSQL test scenario
sharding_db=> begin;
BEGIN

-- Executing DDL within transaction. 
-- ShardingSphere Metadata refresh uses a new connection, thus cannot see uncommitted schema changes
sharding_db=> alter table t_order add column t1 int;  
ALTER TABLE
sharding_db=> commit;
COMMIT

-- Column t1 not refreshed automatically
sharding_db=> select * from t_order;  
 order_id | user_id | status | creation_date |
----------+---------+--------+---------------+----+----+----+
(0 rows)

-- Manual metadata refresh required to load t1 column
sharding_db=> refresh table metadata t_order;  
SUCCESS
sharding_db=> select * from t_order;  
 order_id | user_id | status | creation_date | t1 |
----------+---------+--------+---------------+----+----+----+----+
(0 rows)

Example codes for reproduce this issue (such as a github link).