Attempting to execute query on connection while inside a runTx call.

[richardo-gluon]

I wonder why there is this check in the code

void _verifyStateBeforeQuery() {
    if (this == _connection && _connection._activeTransaction != null) {
      throw PgException(
          'Attempting to execute query on connection while inside a `runTx` call.');
    }
  }

as far as I understand, using context inside a transaction to query is okay, because sometimes we need to query the data from other tables, etc.

[isoos]

@richardo-gluon: Could you please clarify what you want to achieve here? Are you trying to run statements that are outside of the transaction?

[richardo-gluon]

What I want to achieve here is this

context.runTx((context){
// do context.insert to table 1
// do context.fetchOne to table 2 >> throws error
});

[isoos]

You can do that by not using the outer connection inside the callback block:

await conn.runTx((tx) async {
   await tx.execute('INSERT');
   await tx.execute('SELECT');
});

Background: if a physical connection gets into the transaction via the BEGIN statement (which the runTx uses), every statement is run in that transaction context. If you want to run statements outside of the transaction block, you mustn't use the conn in this example, you need a new physical connection. (Which is conveniently accessible via the connection Pool).

[richardo-gluon]

I see, that makes sense. Thanks for explaining!

[richardo-gluon]

@isoos I am running server side using Dart, conduit. The way it works is I put some controllers on stand by for each endpoint I have, then I run the server. Something like this

"v1/help" => HelpController

The problem that I see is, if inside the HelpController I have to run a DB transaction, does that mean I have to create a new connection? if yes, what if there are 100 occurrences that hit my API v1/help, does that mean I have to establish 100 connection in parallel?

[isoos]

Use connection pooling for that. Not sure what conduit recommends for it, package:postgres has https://pub.dev/documentation/postgres/latest/postgres/Pool-class.html for it.