Exception when using transaction manually

[simolus3]

To implement nested transactions (with savepoints), I want to be able to control transactions manually (that is, I run the BEGIN and COMMIT statements with execute and make myself responsible for blocking other statements while the transaction is active).

However, it seems like this library crashes when doing that:

import 'package:postgres/postgres.dart';

// Reproducing with `docker run -p 5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres postgres`
void main(List<String> args) async {
  final connection = PostgreSQLConnection('localhost', 5432, 'postgres',
      username: 'postgres', password: 'postgres');
  await connection.open();

  await connection.execute('BEGIN');
  print(await connection.execute('SELECT 1 + 2;'));
  await connection.execute('COMMIT');
}

This throws the following exception:

Unhandled exception:
type 'PostgreSQLConnection' is not a subtype of type '_TransactionProxy' in type cast
#0      _PostgreSQLConnectionStateBusy.onMessage (package:postgres/src/connection_fsm.dart:288:31)
#1      PostgreSQLConnection._readData (package:postgres/src/connection.dart:286:47)
#2      _RootZone.runUnaryGuarded (dart:async/zone.dart:1618:10)
#3      _BufferingStreamSubscription._sendData (dart:async/stream_impl.dart:341:11)
#4      _BufferingStreamSubscription._add (dart:async/stream_impl.dart:271:7)
#5      _SyncStreamControllerDispatch._sendData (dart:async/stream_controller.dart:774:19)
#6      _StreamController._add (dart:async/stream_controller.dart:648:7)
#7      _StreamController.add (dart:async/stream_controller.dart:596:5)
#8      _Socket._onData (dart:io-patch/socket_patch.dart:2314:41)
#9      _RootZone.runUnaryGuarded (dart:async/zone.dart:1618:10)
#10     _BufferingStreamSubscription._sendData (dart:async/stream_impl.dart:341:11)
#11     _BufferingStreamSubscription._add (dart:async/stream_impl.dart:271:7)
#12     _SyncStreamControllerDispatch._sendData (dart:async/stream_controller.dart:774:19)
#13     _StreamController._add (dart:async/stream_controller.dart:648:7)
#14     _StreamController.add (dart:async/stream_controller.dart:596:5)
#15     new _RawSocket.<anonymous closure> (dart:io-patch/socket_patch.dart:1839:33)
#16     _NativeSocket.issueReadEvent.issue (dart:io-patch/socket_patch.dart:1322:14)
#17     _microtaskLoop (dart:async/schedule_microtask.dart:40:21)
#18     _startMicrotaskLoop (dart:async/schedule_microtask.dart:49:5)
#19     _runPendingImmediateCallback (dart:isolate-patch/isolate_patch.dart:122:13)
#20     _RawReceivePortImpl._handleMessage (dart:isolate-patch/isolate_patch.dart:193:5)

Is there a way do manage those transactions manually?

[isoos]

Is there a way do manage those transactions manually?

Not that I know of. I am not familiar much with the transaction handling in the library (predates my involvement), but I recall that it wraps/puts the connection to a different state, and the exception you see could be related to that.

What is the use case that you would like to solve manually? So far I have found the transaction callback to be reliable...

[simolus3]

What is the use case that you would like to solve manually?

Basically, I think it boils down to being able to change the BEGIN and COMMIT statements to something else (like SAVEPOINT or ROLLBACK to implement nested transactions). But it looks like nested transactions (even without custom statements) aren't supported by this library at the moment either way, so it will be tricky to get working :D

[isoos]

Ah, yeah, that's missing. :(

I haven't really checked recently how to refactor the transaction code, but if anybody is interested doing it, I'm happy to review and merge.

[marcotas]

The postgresql2 package allows us to do what you want @simolus3. Indeed I've implemented nested transactions myself using that package. The only downside of that package though is that it is not compatible with Postgres 13+. I've implemented nested transactions using Postgres 11 on my end with tons of tests. This was one of the reasons why I choose postgresql2 instead of postgres. Both packages are a fork from Greg's PostgreSQL Driver. The maintenance of this one is much more active than the other one. It would be great if we could get those Pool features and manual transactions in an actively maintained package for Dart.

@isoos do you have any video explaining the architecture or the barebones used for this package? I really would like to contribute but the code seems complicated for me to spend time on it. I didn't decide which one is best: try to understand this codebase and implement manual transactions with the Pool system on this package or try to understand the smaller codebase from postgresql2 package and try to make it compatible with Postgres 14+. 😅

Anyways, IMHO these two features are a "must have" for a database driver package:

• Pool of connections
• Manual transactions at your pleasure. Could be something like:

var connection = new PostgresSQLConnection(...);
await connection.begin();
await connection.execute(...);
await connection.commit();

Or even better:

var connection = new PostgresSQLConnection(...);
await connection.execute('begin');
await connection.execute('savepoint 123');
await connection.execute(...);
await connection.execute('release savepoint 123');
await connection.execute('commit');

[marcotas]

By the way, I've found a really good article explaining how to implement a PostgreSQL driver and how they work. The implementation is in Node, but the idea is the most important thing to learn from. I think this article can help more developers understand how this work so they can be able to contribute to this and other database drivers.

[isoos]

do you have any video explaining the architecture or the barebones used for this package? I really would like to contribute but the code seems complicated for me to spend time on it. I didn't decide which one is best: try to understand this codebase and implement manual transactions with the Pool system on this package or try to understand the smaller codebase from postgresql2 package and try to make it compatible with Postgres 14+.

@marcotas: I've adopted/inherited the package from the stable-kernel team, did a performance-focused refactoring, and did minor bugfixing, merged other PRs, and mostly additional development for pooling in package:postgres_pool and package:cockroachdb_pool. I'm familiar with many parts of the codebase, but connection handling and transaction logic is something I've barely touched (or understood) - yet. I've long wanted to reach out @tomyeh about merging the codebases, but I was distracted by a home renovation project that is about to close, so I shall have more time for it now :)

The article is a great resource, thanks for sharing it! I think we should consider a refactor/rewrite the driver to (a) have a better understanding on how it should work, and (b) have an implementation where people can be confident contributing to it. Of course I'd carry over all the unit tests, with a few adjustment to have an easier time for local testing with docker.

/cc @jonasfj in case there is an interest here

[simolus3]

I think I have figured out this particular problem, I'll open a PR to allow managing transactions manually.