search

ArcadeData / arcadedb

ArcadeDB Multi-Model Database, one DBMS that supports SQL, Cypher, Gremlin, HTTP/JSON, MongoDB and Redis. ArcadeDB is a conceptual fork of OrientDB, the first Multi-Model DBMS. ArcadeDB supports Vector Embeddings.
https://arcadedb.com
Apache License 2.0
503 stars 62 forks source link

Unexpected message type 'H' for message any while executing query from Python #668

Open iborntolearn opened 1 year ago

iborntolearn commented 1 year ago

ArcadeDB Version: 22.10.0

JDK Version: OpenJDK Runtime Environment 18.9

OS: RHEL

Expected behavior

Actual behavior

Unexpected message type 'H' for message any com.arcadedb.postgres.PostgresProtocolException: Unexpected message type 'H' for message any at com.arcadedb.postgres.PostgresNetworkExecutor.readMessage(PostgresNetworkExecutor.java:969) at com.arcadedb.postgres.PostgresNetworkExecutor.run(PostgresNetworkExecutor.java:140)

Steps to reproduce

Step#1: Start the arcadedb server with postgres driver enabled as below: bin/server.sh -Darcadedb.server.plugins="Postgres:com.arcadedb.postgres.PostgresProtocolPlugin" Step#2: Execute the following command: import asyncio import asyncpg

async def run(): conn = await asyncpg.connect(user='xxxx', password='xxxx', database='OpenBeer', host='xxx.xxx.xxx.xx') sql = "select from Beer limit 30"

values = await conn.fetch(sql)

await conn.close()

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

<An SQL script to reproduce the problem or a JUnit test case will increase A LOT the chance to have a quick fix>

lvca commented 1 year ago

This issue looks old to me. Could you please try 22.11.1 or the latest snapshot (22.12.1-SNAPSHOT)?

lvca commented 1 year ago

No response from the author for a while, closing the issue.

LLuke commented 1 year ago

This still happens with 23.10.1. The following code works with postgresql but fail with arcadedb with error message "com.arcadedb.postgres.PostgresProtocolException: Unexpected message type 'H' for message any"

import asyncio
import asyncpg
import nest_asyncio

# Apply nest_asyncio to enable nested event loops in Jupyter-notebook if need
# nest_asyncio.apply()

async def run():
    conn = await asyncpg.connect(host="192.168.20.157", port="3333", ssl=None, database="arcade_testdb", user="root", password="PlayWithData")
    values = await conn.fetch(''' select * from schema:database ''')

    # Print out the results
    i = 1
    for row in values:
        print(i, row)
        i = i + 1

    await conn.close()

loop = asyncio.get_event_loop()
loop.run_until_complete(run())
LLuke commented 1 year ago

Stack trace

2023-11-16 17:17:02.088 INFO  [PostgresNetworkExecutor] PSQL: received not supported SSL connection request. Sending back error message to the clientUnexpected message type 'H' for message any
com.arcadedb.postgres.PostgresProtocolException: Unexpected message type 'H' for message any
        at com.arcadedb.postgres.PostgresNetworkExecutor.readMessage(PostgresNetworkExecutor.java:1006)
        at com.arcadedb.postgres.PostgresNetworkExecutor.run(PostgresNetworkExecutor.java:131)

2023-11-16 17:17:03.072 SEVER [PostgresNetworkExecutor] PSQL: Closing connection with client

This still happens with 23.10.1. The following code works with postgresql but fail with arcadedb with error message "com.arcadedb.postgres.PostgresProtocolException: Unexpected message type 'H' for message any"

import asyncio
import asyncpg
import nest_asyncio

# Apply nest_asyncio to enable nested event loops in Jupyter-notebook if need
# nest_asyncio.apply()

async def run():
    conn = await asyncpg.connect(host="192.168.20.157", port="3333", ssl=None, database="arcade_testdb", user="root", password="PlayWithData")
    values = await conn.fetch(''' select * from schema:database ''')

    # Print out the results
    i = 1
    for row in values:
        print(i, row)
        i = i + 1

    await conn.close()

loop = asyncio.get_event_loop()
loop.run_until_complete(run())
lvca commented 1 year ago

Weird, I've never seen a message with H. From Postgres protocol (https://www.postgresql.org/docs/9.6/protocol-message-formats.html) the H is a flush message: "The Flush message does not cause any specific output to be generated, but forces the backend to deliver any data pending in its output buffers. A Flush must be sent after any extended-query command except Sync, if the frontend wishes to examine the results of that command before issuing more commands. Without Flush, messages returned by the backend will be combined into the minimum possible number of packets to minimize network overhead."

Basically, the Flush can be ignored in ArcadeDB. The Postgres docs don't mention the flow after the flush, if the server should send any message to the client. How can I test that? I'd like to write a test case in Java so it can be included in our regression suite.

If you can test it, please pull the latest main branch, compile a distro mvn clean install -DskipTests and try with a fresh server.

tolgaulas commented 1 year ago

“How can I test that? “ - this may help :

https://guptakumartanuj.wordpress.com/2018/02/20/capture-postgres-database-packets-through-wireshark-on-local-machine/

lvca commented 1 year ago

I'm using the python script @LLuke provided. But I need a way to write it in a Java test, so we avoid future regressions.

lvca commented 1 year ago

With JDBC + Postgres has no issues:

@Test
  public void databaseSchema() throws Exception {
    try (final Statement st = conn.createStatement()) {

      try (final ResultSet rs = st.executeQuery("select * from schema:database")) {
        assertThat(rs.next()).isTrue();
        assertThat(rs.getString("name")).isNotBlank();
        assertThat(rs.next()).isFalse();
      }
    }
  }
LLuke commented 1 year ago

I saw your commit already. It looks good and I will test when I find some time to build the project over the weekend. It is also useful if you provide snapshot builds on github. This way other users can more easily get and test all the issues without even to wait more than a day. :-)

At this moment I have patched my local SQLAlchemy so that it works with pg8000 to connect to arcadedb. You can see that I also commented on issues and discussions with psycopg2 and pg8000 with python code to replace the issues. My thoughts are making sure the simplest test case of these (my versions are fairly current) works so that I can run some benchmark code to see whether we can use arcadedb for some of our data exploration and educational projects. These drivers will be probing the postgres compatiblity layer of arcadedb for sure and make it easier for data guys familiar with SQL.

asyncpg==0.29.0   
pg8000==1.30.3  
psycopg==3.1.12
psycopg-binary==3.1.12
psycopg2==2.9.9
SQLAlchemy==2.0.23

Weird, I've never seen a message with H. From Postgres protocol (https://www.postgresql.org/docs/9.6/protocol-message-formats.html) the H is a flush message: "The Flush message does not cause any specific output to be generated, but forces the backend to deliver any data pending in its output buffers. A Flush must be sent after any extended-query command except Sync, if the frontend wishes to examine the results of that command before issuing more commands. Without Flush, messages returned by the backend will be combined into the minimum possible number of packets to minimize network overhead."

Basically, the Flush can be ignored in ArcadeDB. The Postgres docs don't mention the flow after the flush, if the server should send any message to the client. How can I test that? I'd like to write a test case in Java so it can be included in our regression suite.

If you can test it, please pull the latest main branch, compile a distro mvn clean install -DskipTests and try with a fresh server.

LLuke commented 1 year ago

Typo, to "reproduce the issues".

In the long run my thoughts are besides Java unit tests, test cases with these popular libraries should be in arcadedb CI pipeline and postgres plugin should be default for an analytical database. Surely I understand that the HTTP-JSON interface is the more performant "native" wire protocol and there's pyarcadedb in pypi. Embedding is also a worthwhile idea for a stripped down core. :-)

I saw your commit already. It looks good and I will test when I find some time to build the project over the weekend. It is also useful if you provide snapshot builds on github. This way other users can more easily get and test all the issues without even to wait more than a day. :-)

At this moment I have patched my local SQLAlchemy so that it works with pg8000 to connect to arcadedb. You can see that I also commented on issues and discussions with psycopg2 and pg8000 with python code to replace the issues. My thoughts are making sure the simplest test case of these (my versions are fairly current) works so that I can run some benchmark code to see whether we can use arcadedb for some of our data exploration and educational projects. These drivers will be probing the postgres compatiblity layer of arcadedb for sure and make it easier for data guys familiar with SQL.

asyncpg==0.29.0   
pg8000==1.30.3  
psycopg==3.1.12
psycopg-binary==3.1.12
psycopg2==2.9.9
SQLAlchemy==2.0.23

Weird, I've never seen a message with H. From Postgres protocol (https://www.postgresql.org/docs/9.6/protocol-message-formats.html) the H is a flush message: "The Flush message does not cause any specific output to be generated, but forces the backend to deliver any data pending in its output buffers. A Flush must be sent after any extended-query command except Sync, if the frontend wishes to examine the results of that command before issuing more commands. Without Flush, messages returned by the backend will be combined into the minimum possible number of packets to minimize network overhead." Basically, the Flush can be ignored in ArcadeDB. The Postgres docs don't mention the flow after the flush, if the server should send any message to the client. How can I test that? I'd like to write a test case in Java so it can be included in our regression suite. If you can test it, please pull the latest main branch, compile a distro mvn clean install -DskipTests and try with a fresh server.

LLuke commented 1 year ago

@lvca , I was able to find some time and build it. You know, we have to clearly separate company business and potentially personal contribution to open source projects. I'm very pleasantly surprised by the ease of build and cleanness of the code!

The bug is not yet fixed because the failure is the readMessage() before your switch statement handles it. Her's the exact stack trace:

2023-11-18 18:10:00.711 INFO  [PostgresNetworkExecutor] PSQL: received not supported SSL connection request. Sending back error message to the clientUnexpected message type 'H' for message any        
com.arcadedb.postgres.PostgresProtocolException: Unexpected message type 'H' for message any                                                                                                            
        at com.arcadedb.postgres.PostgresNetworkExecutor.readMessage(PostgresNetworkExecutor.java:1033)                                                                                                 
        at com.arcadedb.postgres.PostgresNetworkExecutor.run(PostgresNetworkExecutor.java:136)       
2023-11-18 18:10:01.101 SEVER [PostgresNetworkExecutor] PSQL: Closing connection with client

I'm not familiar enough with the exact details of the postgres wire protocol to fix for you but I believe it is not a difficult fix. For the testing, I'd like to suggest that you create a fresh python virtual environment with all the major drivers (psycopg2, asyncpg, pscopg(3), pg8000 and SQLAlchemy), then you can more easily reproduce issues users submitted. From that you could capture packets and put in the Java unittests. Comparing with PostgreSQL would also help in case you are not sure whether it is the user's code that has problem. Similarly you can fix for pscopg2 and psycopg (aka the upcoming psycopg3).

If possible, could you implement some of the postgresql server query handlings to make SQLAlchemy happy after taking care of the underlying drivers like asyncpg and psycopg2? I noticed that ArcadeDB was targeting PostgreSQL 10.5 compatibility in the PG_VERSION. SQLAlchemy likes to dig out such information so that it knows what server capabilities are. Treating "BEGIN TRANSACTION" as "BEGIN" will also help. This way I won't need to have to persuade SQLAlchemy project to accept my patch to make ArcadeDB useable via SQLAlchemy. :-)

Weird, I've never seen a message with H. From Postgres protocol (https://www.postgresql.org/docs/9.6/protocol-message-formats.html) the H is a flush message: "The Flush message does not cause any specific output to be generated, but forces the backend to deliver any data pending in its output buffers. A Flush must be sent after any extended-query command except Sync, if the frontend wishes to examine the results of that command before issuing more commands. Without Flush, messages returned by the backend will be combined into the minimum possible number of packets to minimize network overhead."

Basically, the Flush can be ignored in ArcadeDB. The Postgres docs don't mention the flow after the flush, if the server should send any message to the client. How can I test that? I'd like to write a test case in Java so it can be included in our regression suite.

If you can test it, please pull the latest main branch, compile a distro mvn clean install -DskipTests and try with a fresh server.

LLuke commented 1 year ago

As expected, members of sqlalchemy project has reservations https://github.com/sqlalchemy/sqlalchemy/issues/10656. I can see merit from both angles -- being strict or relax and allow more database to usable and tested, thus projects grow up together. I guess that further communications and fixes till reaching resolutions for us users are in your court. :-)

lvca commented 1 year ago

Something was missing form my previous commit about the 'H' message, now it's pushed. Also, BEGIN TRANSACTION now should be treated as BEGIN.

LLuke commented 1 year ago

Thanks for looking into my issue report and working on them promptly. I'm happy to see that 'H' was fixed. However, a lot of other issues are uncovered as I looked deeper. Honestly, I feel that aracadedb should create and maintain a python test environment since python much is much more convenient for users with limited time and familiarity with the project's underlying details. If you need help, we can email privately and set up a zoom session when I'm off work. I'm in US Central Time Zone.

All said, here are what I found about arcadedb's current support of common python drivers:

To help findingThe next mesages will be the output of each testing cases. the issues, I added debug print code at these three spots:

--- a/postgresw/src/main/java/com/arcadedb/postgres/PostgresNetworkExecutor.java
+++ b/postgresw/src/main/java/com/arcadedb/postgres/PostgresNetworkExecutor.java
@@ -354,6 +354,7 @@ public class PostgresNetworkExecutor extends Thread {
       final String language = query[0];
       queryText = query[1];

+      System.err.println("\n### queryText = [" + queryText + "]");
       final ResultSet resultSet;
       if (queryText.startsWith("SET ")) {
         resultSet = new IteratorResultSet(Collections.emptyIterator());
@@ -814,6 +815,7 @@ public class PostgresNetworkExecutor extends Thread {
           final SQLQueryEngine sqlEngine = (SQLQueryEngine) database.getQueryEngine("sql");
           portal.sqlStatement = sqlEngine.parse(queryText, (DatabaseInternal) database);

+          System.err.println("\n### portal.query=[" + portal.query + "]");
           if (portal.query.equalsIgnoreCase("BEGIN") || portal.query.equalsIgnoreCase("BEGIN TRANSACTION")) {
             explicitTransactionStarted = true;
             setEmptyResultSet(portal);
@@ -1022,6 +1024,7 @@ public class PostgresNetworkExecutor extends Thread {
         for (int i = 0; i < expectedMessageCodes.length; i++) {
           if (type == expectedMessageCodes[i]) {
             valid = true;
+            System.err.println("\n### message type " + type + " is valid = " + valid);
             break;
           }
         }

The next messages are the results and discussions of each cases . BTW, besides python environment, I'd also suggest that you have settings to enable protocol wire capture which logs detailed information and explain them in plain English without the sensitive parts (like password), this way users can easily share such logs with you upon failures.

LLuke commented 1 year ago

asyncpg results:

### message type p is valid = true

### message type P is valid = true

### portal.query=[ select * from schema:database ]

### message type D is valid = true

### message type H is valid = true

### message type B is valid = true
Error on reading any message: Connection reset
com.arcadedb.postgres.PostgresProtocolException: Error on reading any message: Connection reset
        at com.arcadedb.postgres.PostgresNetworkExecutor.readMessage(PostgresNetworkExecutor.java:1051)
        at com.arcadedb.postgres.PostgresNetworkExecutor.run(PostgresNetworkExecutor.java:136)
Caused by: java.net.SocketException: Connection reset
        at java.base/java.net.SocketInputStream.read(SocketInputStream.java:186)
        at java.base/java.net.SocketInputStream.read(SocketInputStream.java:140)
        at java.base/java.io.BufferedInputStream.fill(BufferedInputStream.java:252)
        at java.base/java.io.BufferedInputStream.read(BufferedInputStream.java:271)
        at java.base/java.io.DataInputStream.readUnsignedByte(DataInputStream.java:293)
        at com.arcadedb.network.binary.ChannelBinary.readUnsignedByte(ChannelBinary.java:64)
        at com.arcadedb.postgres.PostgresNetworkExecutor.readNextByte(PostgresNetworkExecutor.java:1062)
        at com.arcadedb.postgres.PostgresNetworkExecutor.readBytes(PostgresNetworkExecutor.java:1108)
        at com.arcadedb.postgres.PostgresNetworkExecutor.readMessage(PostgresNetworkExecutor.java:1035)
        ... 1 more

The python program hung and had to be killed. Apparently asyncpg doesn't like the response or lack of for its 'B' message. It was just there waiting for connection reset.

LLuke commented 1 year ago

pg8000: working with autocommit=True as the underlying driver for sqlalchemy with my hack. However, the followings are what it really expected out of box and not yet supported by arcadededb . Your fix for 'BEGIN TRANSACTION" was on a different code path and you should check queryText instead for pg8000 support.

With autocommit=True

### message type p is valid = true

### message type Q is valid = true

### queryText = [select pg_catalog.version()]

### message type Q is valid = true

### queryText = [select current_schema()]

### message type Q is valid = true

### queryText = [show transaction isolation level]

### message type Q is valid = true

### queryText = [show standard_conforming_strings]

### message type Q is valid = true

### queryText = [select * from schema:database]

### message type X is valid = true

Without autocommit=True, sqlchemy will kill the connection (after retries?) when pg8000 raise ProgrammingError:

### message type p is valid = true

### message type Q is valid = true

### queryText = [begin transaction]

### message type Q is valid = true

### queryText = [begin transaction]

### message type Q is valid = true

### queryText = [begin transaction]

### message type Q is valid = true

### queryText = [begin transaction]

### message type Q is valid = true

### queryText = [begin transaction]
Error on reading any message: Connection reset
com.arcadedb.postgres.PostgresProtocolException: Error on reading any message: Connection reset
        at com.arcadedb.postgres.PostgresNetworkExecutor.readMessage(PostgresNetworkExecutor.java:1051)
        at com.arcadedb.postgres.PostgresNetworkExecutor.run(PostgresNetworkExecutor.java:136)
Caused by: java.net.SocketException: Connection reset

Please make sure that you support the "BEGIN TRANSACTION" in autocommit=False case (default), then all the feature checking part in the first working section. After these are implemented, I can help you to conforming support of pg8000+sqlalchemy out of box. :-)

LLuke commented 1 year ago

psycopg2: not working since arcadedb doesn't support "SET datestyle TO 'ISO'" which the driver assumed for all databases. More issues may be found after fix this.

### message type p is valid = true

### message type Q is valid = true

### queryText = [SET datestyle TO 'ISO']

### message type X is valid = true

This is the cause for psycopg2 to fail as discussed in https://github.com/ArcadeData/arcadedb/discussions/399

The python side would print out the error message

psycopg2.DatabaseError: error with status PGRES_TUPLES_OK and no message from the libpq

So, after adding support for "SET datestyle TO 'ISO", we can see whether there are additional issues ad fix them to support pscopg2. This is the default driver for sqlalchemy and almost everyone uses at this moment.

LLuke commented 1 year ago

psycopg(3): working out of box! Unfortunately, it is not yet used by SQLAlchemy and other projects.

### message type p is valid = true

### message type Q is valid = true

### queryText = [select * from schema:database]

### message type X is valid = true

Please notice that except for pg8000, these are the driver's out of box requirement. The pg8000 test showed the additional requirements for sqlalchemy in a minimal viable test case.

Hope these will help. :-)

lvca commented 1 year ago

Thanks for the super detailed messages! By the way, you can enable this in the server to have the trace of the Postgres protocol to the console: -Darcadedb.postgres.debug=true

LLuke commented 1 year ago

Cool. I will check that out next time. :-)

Thanks for the super detailed messages! By the way, you can enable this in the server to have the trace of the Postgres protocol to the console: -Darcadedb.postgres.debug=true

lvca commented 1 year ago

Ok, just pushed the BEGIN TRANSACTION on a different path and the SET datestyle TO 'ISO' which changes the date format to ISO. It's hard to tell if the return is the correct one, the Postgres wire protocol lacks this information :-(

Thanks so much for your testing.

I can't figure out the remaining case where it's stuck after the BIND command. Hopefully, with the trace on, you can have a richer output to send me back ;-)

I'm open to a Python test suite, after all our CI is GitHub Actions and it's totally fine running Python scripts. Hopefully, @robfrank will help with this.

LLuke commented 1 year ago

pg8000: working with autocommit=True as the underlying driver for sqlalchemy with my hack. However, the followings are what it really expected out of box and not yet supported by arcadededb . Your fix for 'BEGIN TRANSACTION" was on a different code path and you should check queryText instead for pg8000 support.

With autocommit=True

### message type p is valid = true

### message type Q is valid = true

### queryText = [select pg_catalog.version()]

### message type Q is valid = true

### queryText = [select current_schema()]

### message type Q is valid = true

### queryText = [show transaction isolation level]

### message type Q is valid = true

### queryText = [show standard_conforming_strings]

### message type Q is valid = true

### queryText = [select * from schema:database]

### message type X is valid = true

Without autocommit=True, sqlchemy will kill the connection (after retries?) when pg8000 raise ProgrammingError:

### message type p is valid = true

### message type Q is valid = true

### queryText = [begin transaction]

### message type Q is valid = true

### queryText = [begin transaction]

### message type Q is valid = true

### queryText = [begin transaction]

### message type Q is valid = true

### queryText = [begin transaction]

### message type Q is valid = true

### queryText = [begin transaction]
Error on reading any message: Connection reset
com.arcadedb.postgres.PostgresProtocolException: Error on reading any message: Connection reset
        at com.arcadedb.postgres.PostgresNetworkExecutor.readMessage(PostgresNetworkExecutor.java:1051)
        at com.arcadedb.postgres.PostgresNetworkExecutor.run(PostgresNetworkExecutor.java:136)
Caused by: java.net.SocketException: Connection reset

Please make sure that you support the "BEGIN TRANSACTION" in autocommit=False case (default), then all the feature checking part in the first working section. After these are implemented, I can help you to conforming support of pg8000+sqlalchemy out of box. :-)

The part still fails when without set autocommit=True, sqlalchemy would instruct pg8000 to send "BEGIN TRANSACTION" immediately after connection is established. Few people would expect that even before select pg_catalog.version() and handle them but ORMs often times resort to such brute-force approach to ensure "correctness".

With autocommit=True OK.

2023-11-21 00:36:59.350 INFO  [PostgresNetworkExecutor] PSQL:-> request for password (R - 8b) (thread=29)
### message type p is valid = true

2023-11-21 00:36:59.774 INFO  [PostgresNetworkExecutor] PSQL:-> authentication ok (R - 8b) (thread=29)
2023-11-21 00:36:59.776 INFO  [PostgresNetworkExecutor] PSQL:-> backend key data (K - 12b) (thread=29)
2023-11-21 00:36:59.784 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 24b) (thread=29)
2023-11-21 00:36:59.784 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=29)
2023-11-21 00:36:59.787 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=29)
2023-11-21 00:36:59.787 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=29)
### message type Q is valid = true

2023-11-21 00:36:59.787 INFO  [PostgresNetworkExecutor] PSQL: query -> select pg_catalog.version() (thread=29)
### queryText = [select pg_catalog.version()]

2023-11-21 00:37:00.131 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=29)
### message type Q is valid = true

2023-11-21 00:37:00.131 INFO  [PostgresNetworkExecutor] PSQL: query -> select current_schema() (thread=29)
### queryText = [select current_schema()]

2023-11-21 00:37:00.147 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=29)
### message type Q is valid = true

2023-11-21 00:37:00.162 INFO  [PostgresNetworkExecutor] PSQL: query -> show transaction isolation level (thread=29)
### queryText = [show transaction isolation level]

2023-11-21 00:37:00.178 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=29)
### message type Q is valid = true

2023-11-21 00:37:00.178 INFO  [PostgresNetworkExecutor] PSQL: query -> show standard_conforming_strings (thread=29)
### queryText = [show standard_conforming_strings]

2023-11-21 00:37:00.178 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=29)
### message type Q is valid = true

2023-11-21 00:37:00.193 INFO  [PostgresNetworkExecutor] PSQL: query -> select * from schema:database (thread=29)
### queryText = [select * from schema:database]

2023-11-21 00:37:00.278 INFO  [PostgresNetworkExecutor] PSQL:-> row description (T - 218b) (thread=29)
2023-11-21 00:37:00.278 INFO  [PostgresNetworkExecutor] PSQL:-> 1 row data (5.49KB) (thread=29)
2023-11-21 00:37:00.278 INFO  [PostgresNetworkExecutor] PSQL:-> command complete (C - 13b) (thread=29)
2023-11-21 00:37:00.278 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=29)
### message type X is valid = true

Without autocommit=True fails

2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> request for password (R - 8b) (thread=31)
### message type p is valid = true

2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> authentication ok (R - 8b) (thread=31)
2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> backend key data (K - 12b) (thread=31)
2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 24b) (thread=31)
2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=31)
2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=31)
2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=31)
### message type Q is valid = true

2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL: query -> begin transaction (thread=31)
### queryText = [begin transaction]

2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=31)
### message type Q is valid = true

2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL: query -> begin transaction (thread=31)
### queryText = [begin transaction]

2023-11-21 00:37:56.470 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=31)
### message type Q is valid = true

2023-11-21 00:37:56.470 INFO  [PostgresNetworkExecutor] PSQL: query -> begin transaction (thread=31)
### queryText = [begin transaction]

2023-11-21 00:37:56.470 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=31)
### message type Q is valid = true

2023-11-21 00:37:56.470 INFO  [PostgresNetworkExecutor] PSQL: query -> begin transaction (thread=31)
### queryText = [begin transaction]

2023-11-21 00:37:56.485 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=31)
### message type Q is valid = true

2023-11-21 00:37:56.485 INFO  [PostgresNetworkExecutor] PSQL: query -> begin transaction (thread=31)
### queryText = [begin transaction]

2023-11-21 00:37:56.485 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=31)
LLuke commented 1 year ago

psycopg2: not working since arcadedb doesn't support "SET datestyle TO 'ISO'" which the driver assumed for all databases. More issues may be found after fix this.

### message type p is valid = true

### message type Q is valid = true

### queryText = [SET datestyle TO 'ISO']

### message type X is valid = true

This is the cause for psycopg2 to fail as discussed in #399

The python side would print out the error message

psycopg2.DatabaseError: error with status PGRES_TUPLES_OK and no message from the libpq

So, after adding support for "SET datestyle TO 'ISO", we can see whether there are additional issues ad fix them to support pscopg2. This is the default driver for sqlalchemy and almost everyone uses at this moment.

Although it is handled. pscopg2 doesn't like the response and closes the connection with error message: psycopg2.DatabaseError: error with status PGRES_TUPLES_OK and no message from the libpq You probably want to capture and check how a real PostgreSQL server respond to this since psql doesn't display. Then pscopg2 will proceed with the next steps.

2023-11-21 00:49:27.270 INFO  [PostgresNetworkExecutor] PSQL:-> request for password (R - 8b) (thread=32)
### message type p is valid = true

2023-11-21 00:49:27.270 INFO  [PostgresNetworkExecutor] PSQL:-> authentication ok (R - 8b) (thread=32)
2023-11-21 00:49:27.270 INFO  [PostgresNetworkExecutor] PSQL:-> backend key data (K - 12b) (thread=32)
2023-11-21 00:49:27.270 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 24b) (thread=32)
2023-11-21 00:49:27.270 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=32)
2023-11-21 00:49:27.270 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=32)
2023-11-21 00:49:27.270 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=32)
### message type Q is valid = true

2023-11-21 00:49:27.270 INFO  [PostgresNetworkExecutor] PSQL: query -> SET datestyle TO 'ISO' (thread=32)
### queryText = [SET datestyle TO 'ISO']

2023-11-21 00:49:27.286 INFO  [PostgresNetworkExecutor] PSQL:-> row description (T - 6b) (thread=32)
2023-11-21 00:49:27.286 INFO  [PostgresNetworkExecutor] PSQL:-> command complete (C - 5b) (thread=32)
2023-11-21 00:49:27.286 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=32)
### message type X is valid = true
lvca commented 1 year ago

@LLuke I'd like to have some python scripts to reproduce such issues locally. Any chance you can share them?

LLuke commented 1 year ago

pg8000: working with autocommit=True as the underlying driver for sqlalchemy with my hack. However, the followings are what it really expected out of box and not yet supported by arcadededb . Your fix for 'BEGIN TRANSACTION" was on a different code path and you should check queryText instead for pg8000 support. With autocommit=True

### message type p is valid = true

### message type Q is valid = true

### queryText = [select pg_catalog.version()]

### message type Q is valid = true

### queryText = [select current_schema()]

### message type Q is valid = true

### queryText = [show transaction isolation level]

### message type Q is valid = true

### queryText = [show standard_conforming_strings]

### message type Q is valid = true

### queryText = [select * from schema:database]

### message type X is valid = true

Without autocommit=True, sqlchemy will kill the connection (after retries?) when pg8000 raise ProgrammingError:

### message type p is valid = true

### message type Q is valid = true

### queryText = [begin transaction]

### message type Q is valid = true

### queryText = [begin transaction]

### message type Q is valid = true

### queryText = [begin transaction]

### message type Q is valid = true

### queryText = [begin transaction]

### message type Q is valid = true

### queryText = [begin transaction]
Error on reading any message: Connection reset
com.arcadedb.postgres.PostgresProtocolException: Error on reading any message: Connection reset
        at com.arcadedb.postgres.PostgresNetworkExecutor.readMessage(PostgresNetworkExecutor.java:1051)
        at com.arcadedb.postgres.PostgresNetworkExecutor.run(PostgresNetworkExecutor.java:136)
Caused by: java.net.SocketException: Connection reset

Please make sure that you support the "BEGIN TRANSACTION" in autocommit=False case (default), then all the feature checking part in the first working section. After these are implemented, I can help you to conforming support of pg8000+sqlalchemy out of box. :-)

The part still fails when without set autocommit=True, sqlalchemy would instruct pg8000 to send "BEGIN TRANSACTION" immediately after connection is established. Few people would expect that even before select pg_catalog.version() and handle them but ORMs often times resort to such brute-force approach to ensure "correctness".

With autocommit=True OK.

2023-11-21 00:36:59.350 INFO  [PostgresNetworkExecutor] PSQL:-> request for password (R - 8b) (thread=29)
### message type p is valid = true

2023-11-21 00:36:59.774 INFO  [PostgresNetworkExecutor] PSQL:-> authentication ok (R - 8b) (thread=29)
2023-11-21 00:36:59.776 INFO  [PostgresNetworkExecutor] PSQL:-> backend key data (K - 12b) (thread=29)
2023-11-21 00:36:59.784 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 24b) (thread=29)
2023-11-21 00:36:59.784 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=29)
2023-11-21 00:36:59.787 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=29)
2023-11-21 00:36:59.787 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=29)
### message type Q is valid = true

2023-11-21 00:36:59.787 INFO  [PostgresNetworkExecutor] PSQL: query -> select pg_catalog.version() (thread=29)
### queryText = [select pg_catalog.version()]

2023-11-21 00:37:00.131 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=29)
### message type Q is valid = true

2023-11-21 00:37:00.131 INFO  [PostgresNetworkExecutor] PSQL: query -> select current_schema() (thread=29)
### queryText = [select current_schema()]

2023-11-21 00:37:00.147 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=29)
### message type Q is valid = true

2023-11-21 00:37:00.162 INFO  [PostgresNetworkExecutor] PSQL: query -> show transaction isolation level (thread=29)
### queryText = [show transaction isolation level]

2023-11-21 00:37:00.178 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=29)
### message type Q is valid = true

2023-11-21 00:37:00.178 INFO  [PostgresNetworkExecutor] PSQL: query -> show standard_conforming_strings (thread=29)
### queryText = [show standard_conforming_strings]

2023-11-21 00:37:00.178 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=29)
### message type Q is valid = true

2023-11-21 00:37:00.193 INFO  [PostgresNetworkExecutor] PSQL: query -> select * from schema:database (thread=29)
### queryText = [select * from schema:database]

2023-11-21 00:37:00.278 INFO  [PostgresNetworkExecutor] PSQL:-> row description (T - 218b) (thread=29)
2023-11-21 00:37:00.278 INFO  [PostgresNetworkExecutor] PSQL:-> 1 row data (5.49KB) (thread=29)
2023-11-21 00:37:00.278 INFO  [PostgresNetworkExecutor] PSQL:-> command complete (C - 13b) (thread=29)
2023-11-21 00:37:00.278 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=29)
### message type X is valid = true

Without autocommit=True fails

2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> request for password (R - 8b) (thread=31)
### message type p is valid = true

2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> authentication ok (R - 8b) (thread=31)
2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> backend key data (K - 12b) (thread=31)
2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 24b) (thread=31)
2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=31)
2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=31)
2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=31)
### message type Q is valid = true

2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL: query -> begin transaction (thread=31)
### queryText = [begin transaction]

2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=31)
### message type Q is valid = true

2023-11-21 00:37:56.454 INFO  [PostgresNetworkExecutor] PSQL: query -> begin transaction (thread=31)
### queryText = [begin transaction]

2023-11-21 00:37:56.470 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=31)
### message type Q is valid = true

2023-11-21 00:37:56.470 INFO  [PostgresNetworkExecutor] PSQL: query -> begin transaction (thread=31)
### queryText = [begin transaction]

2023-11-21 00:37:56.470 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=31)
### message type Q is valid = true

2023-11-21 00:37:56.470 INFO  [PostgresNetworkExecutor] PSQL: query -> begin transaction (thread=31)
### queryText = [begin transaction]

2023-11-21 00:37:56.485 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=31)
### message type Q is valid = true

2023-11-21 00:37:56.485 INFO  [PostgresNetworkExecutor] PSQL: query -> begin transaction (thread=31)
### queryText = [begin transaction]

2023-11-21 00:37:56.485 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=31)

Sure.

pg8000

import pg8000

conn = pg8000.connect(host="192.168.20.157", port="3333", ssl_context=None, database="arcadedb_test", user="root", password="PlayWithData")
conn.autocommit = True

cursor = conn.cursor()

cursor.execute("""
    select * from schema:database
""")

results = cursor.fetchall()
for row in results:
    for column in row:
        print(str(column) + '\n')

conn.close()

sqlalchemy+pg8000, need my hack to run to completion, https://github.com/LLuke/sqlalchemy/tree/arcadedb_23.10.1_patch also handling all of the above mentioned more "BEGIN TRANSACTION" to allow autocommit=False (default of DBAPI behavior) 

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine(
    'postgresql+pg8000://root:PlayWithData@localhost:3333/arcadedb_test',
    isolation_level="AUTOCOMMIT",
    connect_args={'ssl_context': None}
)
df = pd.read_sql_query("select * from schema:database", con=engine)
print(df)
engine.dispose()

psycopg2 (change to psycopg for psycopg3, which works)

import psycopg2

# can also use a standard URL format for the connection string:
#   psycopg2.connect('postgres://username:password@host:port/database')
with psycopg2.connect(user="root", password="PlayWithData",
                    host='localhost',
                    port='3333',
                    dbname='arcadedb_test',
                    sslmode='disable'
                    ) as connection:
    connection.autocommit = True

    with connection.cursor() as cursor:
        # list all tables in this database
        cursor.execute('select * from schema:database')
        results = cursor.fetchall()
        print(results)

asyncpg

import asyncio
import asyncpg
import nest_asyncio

# Apply nest_asyncio to enable nested event loops in Jupyter-notebook
# nest_asyncio.apply()

async def run():
    conn = await asyncpg.connect(host="192.168.20.157", port="3333", ssl='disable',
                                 database="arcadedb_test", user="root", password="PlayWithData")
    values = await conn.fetch(''' select * from schema:database ''')

    # Print out the results
    i = 1
    for row in values:
        print(i, row)
        i = i + 1

    await conn.close()

loop = asyncio.get_event_loop()
loop.run_until_complete(run())
LLuke commented 1 year ago

@LLuke I'd like to have some python scripts to reproduce such issues locally. Any chance you can share them?

see above.

lvca commented 1 year ago

Thanks! Trying to run all of them, but something is missing. Apart from a pip3 install sqlalchemy, what else is missing?

% python3 sqlalchemy.py
Traceback (most recent call last):
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
ImportError: cannot import name 'create_engine' from partially initialized module 'sqlalchemy' (most likely due to a circular import) (/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py)
LLuke commented 1 year ago

Thanks! Trying to run all of them, but something is missing. Apart from a pip3 install sqlalchemy, what else is missing?

% python3 sqlalchemy.py
Traceback (most recent call last):
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
ImportError: cannot import name 'create_engine' from partially initialized module 'sqlalchemy' (most likely due to a circular import) (/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py)

Did you also install pg8000? To reproduce my environment, you can put these in a requirement.txt file

asyncpg==0.29.0   
pg8000==1.30.3  
psycopg==3.1.12
psycopg-binary==3.1.12
psycopg2==2.9.9
SQLAlchemy==2.0.23

and run pip install -r requirements.txt to create the environment (make sure that you use virtual environment support so that they don't change the system).

Just FYI my particular test my Python version is 3.10.11 directly from python.org. However, I don't see differences with other versions or distributions I have (like anaconda or versions from OS).

With unpatched sqlalchemy+pg8000 you should immediately get error

sqlalchemy.exc.ProgrammingError: (pg8000.dbapi.ProgrammingError) {'M': 'Error on executing query: Unknown method name: version', 'S': 'ERROR', 'C': 'XX000'}

since select pg_catalog.version() and such were not in the queryText path. Then you can setup another virtual environment with sqlalchemy replaced by the branch (https://github.com/LLuke/sqlalchemy/tree/arcadedb_23.10.1_patch) by checking out and run "pip -e ..." (pip uninstall the stock SQLAlchemy first if there's any).

The sqlalchemy+psycopg2 also has a few things to implement besides the ISO date. It is the default as it is much faster than pg8000. While in the short run it is better to start with existing postgres support in sqlalchemy, at one point you may want to do an arcadedb dialect for sqlalchemy to included so that you don't have to worry about changes in sqlalchemy with these PostgreSQL functionalities requirements. In turn you can mandate certain functinalities and provide backward compatibility support.

lvca commented 1 year ago

Thanks @LLuke, I've parked all your files under https://github.com/ArcadeData/arcadedb/tree/aa2af00cdae9c13f899acf72380998dd2b9310e2/postgresw/src/test/resources/python.

I still have issues that I believe are related to the installation. 

luca@Luca's Mac python % python3 asyncpg.py
Traceback (most recent call last):
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/asyncpg.py", line 2, in <module>
    import asyncpg
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/asyncpg.py", line 22, in <module>
    loop.run_until_complete(run())
  File "/usr/local/Cellar/python@3.11/3.11.5/Frameworks/Python.framework/Versions/3.11/lib/python3.11/asyncio/base_events.py", line 653, in run_until_complete
    return future.result()
           ^^^^^^^^^^^^^^^
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/asyncpg.py", line 9, in run
    conn = await asyncpg.connect(host="localhost", port="5432", ssl='disable',
                 ^^^^^^^^^^^^^^^
AttributeError: partially initialized module 'asyncpg' has no attribute 'connect' (most likely due to a circular import)

and

luca@Luca's Mac python % python3 sqlalchemy.py
Traceback (most recent call last):
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
ImportError: cannot import name 'create_engine' from partially initialized module 'sqlalchemy' (most likely due to a circular import) (/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py)
luca@Luca's Mac python % python3 sqlalchemy.py
Traceback (most recent call last):
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
ImportError: cannot import name 'create_engine' from partially initialized module 'sqlalchemy' (most likely due to a circular import) (/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py)
LLuke commented 1 year ago

I was able to reproduce with python 3.11 under osx like you have. On the surface the error message doesn't make sense -- for example the pg8000 code has only one import and the environment still suspects a "circular" import. This really makes me curious and I will investigate.

% cat requirements.txt
asyncpg==0.29.0
nest_asyncio==1.5.8
pg8000==1.30.3
psycopg==3.1.12
psycopg-binary==3.1.12
psycopg2-binary==2.9.9
SQLAlchemy==2.0.23

FYI, there could be a bit delays since it is very unusual for such errors and it is Thanksgiving today in the United States. Thank you for including the code in source tree. This way for any workaround needed I can easily create a PR for you. :-)

Thanks @LLuke, I've parked all your files under https://github.com/ArcadeData/arcadedb/tree/aa2af00cdae9c13f899acf72380998dd2b9310e2/postgresw/src/test/resources/python.

I still have issues that I believe are related to the installation.

luca@Luca's Mac python % python3 asyncpg.py
Traceback (most recent call last):
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/asyncpg.py", line 2, in <module>
    import asyncpg
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/asyncpg.py", line 22, in <module>
    loop.run_until_complete(run())
  File "/usr/local/Cellar/python@3.11/3.11.5/Frameworks/Python.framework/Versions/3.11/lib/python3.11/asyncio/base_events.py", line 653, in run_until_complete
    return future.result()
           ^^^^^^^^^^^^^^^
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/asyncpg.py", line 9, in run
    conn = await asyncpg.connect(host="localhost", port="5432", ssl='disable',
                 ^^^^^^^^^^^^^^^
AttributeError: partially initialized module 'asyncpg' has no attribute 'connect' (most likely due to a circular import)

and

luca@Luca's Mac python % python3 sqlalchemy.py
Traceback (most recent call last):
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
ImportError: cannot import name 'create_engine' from partially initialized module 'sqlalchemy' (most likely due to a circular import) (/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py)
luca@Luca's Mac python % python3 sqlalchemy.py
Traceback (most recent call last):
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
ImportError: cannot import name 'create_engine' from partially initialized module 'sqlalchemy' (most likely due to a circular import) (/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py)
LLuke commented 1 year ago

It turns out to be an easy fix. The test code's names are conflicting with library file names. See PR https://github.com/ArcadeData/arcadedb/pull/1342

I was able to reproduce with python 3.11 under osx like you have. On the surface the error message doesn't make sense -- for example the pg8000 code has only one import and the environment still suspects a "circular" import. This really makes me curious and I will investigate.

% cat requirements.txt
asyncpg==0.29.0
nest_asyncio==1.5.8
pg8000==1.30.3
psycopg==3.1.12
psycopg-binary==3.1.12
psycopg2-binary==2.9.9
SQLAlchemy==2.0.23

FYI, there could be a bit delays since it is very unusual for such errors and it is Thanksgiving today in the United States. Thank you for including the code in source tree. This way for any workaround needed I can easily create a PR for you. :-)

Thanks @LLuke, I've parked all your files under https://github.com/ArcadeData/arcadedb/tree/aa2af00cdae9c13f899acf72380998dd2b9310e2/postgresw/src/test/resources/python. I still have issues that I believe are related to the installation.

luca@Luca's Mac python % python3 asyncpg.py
Traceback (most recent call last):
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/asyncpg.py", line 2, in <module>
    import asyncpg
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/asyncpg.py", line 22, in <module>
    loop.run_until_complete(run())
  File "/usr/local/Cellar/python@3.11/3.11.5/Frameworks/Python.framework/Versions/3.11/lib/python3.11/asyncio/base_events.py", line 653, in run_until_complete
    return future.result()
           ^^^^^^^^^^^^^^^
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/asyncpg.py", line 9, in run
    conn = await asyncpg.connect(host="localhost", port="5432", ssl='disable',
                 ^^^^^^^^^^^^^^^
AttributeError: partially initialized module 'asyncpg' has no attribute 'connect' (most likely due to a circular import)

and

luca@Luca's Mac python % python3 sqlalchemy.py
Traceback (most recent call last):
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
ImportError: cannot import name 'create_engine' from partially initialized module 'sqlalchemy' (most likely due to a circular import) (/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py)
luca@Luca's Mac python % python3 sqlalchemy.py
Traceback (most recent call last):
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
  File "/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py", line 1, in <module>
    from sqlalchemy import create_engine
ImportError: cannot import name 'create_engine' from partially initialized module 'sqlalchemy' (most likely due to a circular import) (/Users/luca/Documents/GitHub/arcadedb/postgresw/src/test/resources/python/sqlalchemy.py)