Executing parallel queries with different connections in QueryMode.extended throws error

[johannes-honeder]

I tried using QueryMode.extended with named parameters instead of QueryMode.simple but when changing my code, I run into parallelization issues somewhere along the parse/bind/execute steps in Postgres.

In my code, I have implemented something like this:

Future executeQuery(String query, {Map<String, String>? parameters) async {
    # 1. get new connection from the pool
    var dbConn = await getConnection();
    
    # 2. execute the query
    if (parameters == null) {
        return dbConn.connection.execute(query);
    } else {
        return dbConn.connection.execute(Sql.named(query), parameters: parameters);
    }
}

I make sure that for each call of executeQuery, a new connection is assigned from a Connection Pool. However, if I run parallel queries with different parameter settings

result.addAll([
    executeQuery(query1, parameters1),         # parameterized query
    executeQuery(query2),                                # no parameters
]);

, I run into errors like:

• "08P01: bind message supplies 1 parameters, but prepared statement "s/0" requires 0"
• "42P05: prepared statement "s/1" already exist"
• "42P05: prepared statement "s/2" does not exist"
  ...

I am guessing that the parse, bind and execute steps of the parallel queries are mixed up internally somehow. Is this a known behavior? To me this would make sense if using the same connection, but I explicitly make sure that when running parallel queries, each query is assigned a unique connection.

I appreciate any help or insights into this issue!

[isoos]

A postgres client connection does not support parallel queries, esp. not parameter-bound prepared statements. (There is a limited queue support, which we not use in this client library for keeping things simpler.) Mixing them like your example is not supported and should be avoided. If you need parallel queries, you should run them on different connections.

Could you please make sure that the connection you are using is a different one? Are you using a connection pool or how are you creating an instance?

[johannes-honeder]

Thanks for the clarification. I already guessed that these parallel queries are not supported when using the same connection.

I have implemented a custom connection pooler, which creates a new Connection with Connection.open(...) in the getConnection() method. Therefore, a single query is always executed with a unique Connection instance, which is returned to the pool once the query has finished.
When executing the parallel queries, I have already confirmed that at least the identity hash of the Connection objects in Dart are different, therefore I assumed that they are treated as distinct connections. But I have to admit that my experience in this area is rather limited. Could it be that under the hood, the Postgres server still treats them as the same connection?

[isoos]

Could it be that under the hood, the Postgres server still treats them as the same connection?

I don't think so.

I am guessing that the parse, bind and execute steps of the parallel queries are mixed up internally somehow.

I think this shouldn't happen, but better to be sure. We need to create a reproducible unit test in the repository. Would you be up for creating such a minimal test case?

[johannes-honeder]

Sure, I'll try to write a small unit test to reproduce this issue in the next days once I find some spare time.

[isoos]

That would be great, thank you! As a start I'd suggest to copy e.g. the setup from https://github.com/isoos/postgresql-dart/blob/master/test/transaction_isolation_test.dart