Select from table through ShardingSphere not working through asyncpg

[jyotisharma7]

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

ShardingSphere Proxy 5.3.2

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

ShardingSphere- Proxy

Expected behavior

Select from tables through ShardingSphere should work. When we replace the database connection parameters with a direct postgres database then we are able to do a select on table.

Actual behavior

Select from tables through ShardingSphere are not working. The issue is mentioned below:- asyncpg.exceptions._base.InternalClientError: cannot switch to state 12; another operation (2) is in progress

Reason analyze (If you can)

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

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

import asyncio
import asyncpg

async def run():
    conn = await asyncpg.connect(user='root', password='root',
                                 database='hint_db', host='127.0.0.1', port=3307)
    values = await conn.fetchval(
        'SELECT * FROM tracking_info;'
    )
    print(values)
    await conn.close()

loop = asyncio.get_event_loop()
task = loop.create_task(run())
loop.run_until_complete(task)

[jyotisharma7]

@TeslaCN I tried to fix this issue by using a connection pool. After that I am getting another error which is given below:-

asyncpg.exceptions.SyntaxOrAccessError: You have an error in your SQL syntax: SELECT pg_advisory_unlock_all();
CLOSE ALL;
UNLISTEN *;
RESET ALL;, no viable alternative at input '();CLOSE' at line 2, position 0, near [@5,33:37='CLOSE',<547

These are advisory SQL statements which runs by default but somehow these statements are getting executed in one line and inverted commas are not coming. Below is the code of asyncpg connection:-

 def _get_reset_query(self):
        if self._reset_query is not None:
            return self._reset_query

        caps = self._server_caps

        _reset_query = []
        if caps.advisory_locks:
            _reset_query.append('SELECT pg_advisory_unlock_all();')
        if caps.sql_close_all:
            _reset_query.append('CLOSE ALL;')
        if caps.notifications and caps.plpgsql:
            _reset_query.append('UNLISTEN *;')
        if caps.sql_reset:
             _reset_query.append('RESET ALL;')

        _reset_query = '\n'.join(_reset_query)
        self._reset_query = _reset_query
        print(_reset_query)
        return _reset_query

Could you please let me know which file in ShardingSphere is actually causing this issue?

[TeslaCN]

ShardingSphere doesn't support multi statements for now. Here is the entry of SQL parser.

https://github.com/apache/shardingsphere/blob/ade41696f7cc16340caf737a8b5889f8a288f2ce/infra/parser/src/main/java/org/apache/shardingsphere/infra/parser/ShardingSphereSQLParserEngine.java#L58

[surukonda]

@TeslaCN thanks for your inputs. Is there any roadmap to support multi statements in future? If there is any active work going on, happy to contribute to the same.

[TeslaCN]

We have discussed support parsing multi statements before. But we haven't got a plan. https://github.com/apache/shardingsphere/issues/14636

[surukonda]

@TeslaCN, does proxy support row level security feature of postgres?

[TeslaCN]

@TeslaCN, does proxy support row level security feature of postgres?

Maybe not. You may file a new issue about this feature.

[surukonda]

@TeslaCN, does proxy support row level security feature of postgres?

Maybe not. You may file a new issue about this feature.

24055 Submitted