Can't exeute DDL statement with python module that interfaces to a PostgreSQL database

[carlors]

Bug Report

Which version of ShardingSphere did you use?

5.1.2

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

ShardingSphere-Proxy

Expected behavior

DDL statement was executed successfully with python module that interfaces to a PostgreSQL database.

Actual behavior

Got an error saying: OperationalError: ERROR: org.apache.shardingsphere.proxy.backend.exception.TableModifyInTransactionException

Reason analyze (If you can)

ShardingSphere-Proxy is not compatible with python module that interfaces to a PostgreSQL database, since I executed the same DDL statement successfully according the ShardingSphere-Proxy.

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

First. start ShardingSphere-Proxy successfully with two data source configured. Second, install the python PostgreSQL database interfaces modules

yum install python-devel
yum install postgresql
yum install postgresql-devel
pip3 install PyGreSQL

Third, use the python PostgreSQL database interfaces connect the ShardingSphere-Proxy and execute the DDL statement

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

#!/usr/bin/python3
import pgdb
conn=pgdb.connect( host='localhost', port=3307, user='your_user_name', password='your_password', dbname='your_db_name')
cur=conn.cursor()
cur.execute("create table test(id serial);")
print (cur)
conn.commit()
cur.close()
conn.close()

[TeslaCN]

Could you try the latest version?

[carlors]

Could you try the latest version?

I think I find the reason, I set database connection session to autocommit = True, and the problem is fixed, python code as follows:

#!/usr/bin/python3
import pgdb
conn=pgdb.connect( host='localhost', port=3307, user='your_user_name', password='your_password', dbname='your_db_name')
conn.autocommit = True
cur=conn.cursor()
cur.execute("create table test(id serial);")
print (cur)
cur.close()
conn.close()

It is probably shardingsphere-proxy need to control xa transaction, so it does not support manul transaction. Yet in order to become a more compatible proxy, I think it will be better if shardingsphere-proxy supports manul transaction.

[carlors]

Could you try the latest version?

I had try the latest version 5.2.1, yet the shardingsphere-proxy client will keep waiting for response from postgresql, and the state of the statment in postgresql database was Idle, state type was "Client Read" which meas postgresql was waiting for shardingsphere-proxy response. It seems that all is fall into a infinite loop.