Question About Prepared Statement Behavior in Transaction

[qlina911-commits]

Hi,

I’ve encountered some strange behavior in your package. If I prepare a statement using a given connection, and then try to execute that prepared statement inside a transaction on the same connection, the execution hangs indefinitely.

However, if I prepare the statement inside the transaction, everything works as expected.

Does this mean that it’s not possible to use pre-prepared statements within a transaction, even if they are created on the same connection? Is this the intended behavior?

Thanks in advance for your clarification.

Best regards,

import 'dart:async';
import 'package:postgres/postgres.dart';
import 'package:dotenv/dotenv.dart';
import 'dart:io';

void main() async {
  final envPath = Platform.environment['DB_CONFIG'];
  final env = DotEnv()..load([envPath!]);

  final endpoint = Endpoint(
    host: env['DB_HOST']!,
    database: env['DB_NAME']!,
    port: int.parse(env['DB_PORT']!),
    username: env['DB_USER']!,
    password: env['DB_PASSWORD']!,
  );

  var c1 = await Connection.open(endpoint);

  var s1 = await c1.prepare('SELECT * FROM catalog.test FOR SHARE');
  var completer1 = Completer();
  c1.runTx(
    (txSession) async {
      print('try lock');

      var s2 = await txSession.prepare('SELECT * FROM catalog.test FOR SHARE');
      // s1 = s2;

      // Here the request hangs for some reason.
      // But if you use the second query instead of the current value, then
      // everything is fine (s1 = s2).
      await s1.run(null);

      print('blocking completed');
      return completer1.future;
    },
  );
}

[qlina911-commits]

Can you make edits to the connection.dart file?

Future<_PreparedStatement> _prepare(Object query) async {
  final trace = Trace.current();
  final conn = _connection;

  // New simple solution.
  // Cloning a request with binding to the current session.
  // A connection field has been added to the class for checking here.
  if (query is _PreparedStatement && conn == query._connection) {
    return _PreparedStatement(
          query._description, query._name, this, trace, conn);
  }

  final name = 's/${conn._statementCounter++}';
  final description = InternalQueryDescription.wrap(
    query,
    typeRegistry: _connection._settings.typeRegistry,
  );

  await _sendAndWaitForQuery<ParseCompleteMessage>(ParseMessage(
    description.transformedSql,
    statementName: name,
    typeOids: description.parameterTypes?.map((e) => e?.oid).toList(),
  ));

  // A connection field has been added to the class for checking here.
  return _PreparedStatement(description, name, this, trace, conn);
}

[isoos]

Does this mean that it’s not possible to use pre-prepared statements within a transaction, even if they are created on the same connection? Is this the intended behavior?

I'm actually not sure if this is supported by the underlying protocol (since the transaction puts it in a different state), or it is only a limitation of the package. I always thought of it that you use prepared statements when you want to execute the same statement multiple times in a short sequence, because otherwise it takes up some resource.

[isoos]

I've labeled this as a bug, but for the time being, please don't use long-lived prepared statements like that.