Transactions support

[jeromegn]

I noticed transactions are not supported, at least when testing with tokio-postgres.

My pgwire handler receives a BEGIN statement and it's handled by the SimpleQueryHandler implementation. Then the single statement I have in the transaction is handled by the ExtendedQueryHandler. When I finally get to tx.commit().await, there's no transaction to commit because all of these things are disconnected.

Is there a way to handle transactions? I figure it might be possible by keeping a record when we get a BEGIN and somehow associate it with the incoming connection and sharing that information between the simple and extended query handler. It seemed like a lot of work though.

Example test code:

        let (mut pg, connection) = tokio_postgres::connect(
            format!("host={} port={}", pg_addr.ip(), pg_addr.port()).as_str(),
            NoTls,
        )
        .await?;

        tokio::spawn(async move {
            if let Err(e) = connection.await {
                eprintln!("connection error: {e}");
            }
        });

        let rows = {
            let tx = pg.transaction().await?;
            let rows = tx.query("SELECT * FROM sqlite_master;", &[]).await?;
            tx.commit().await?;
            rows
        };

[sunng87]

At the moment, pgwire only parses the wire protocol and developer has to implement those sql commands sent over the protocol.

You can create a struct that implement both SimpleQueryHandler and ExtendedQueryHandler so they can share the state of transaction.

I might add an additional helper layer on top of current API layer for transaction support in future.

[tmokmss]

Hi @sunng87 I also encountered a similar issue while trying to implement a server with transaction support and have a question.

In a transaction, a processor must process e.g. 1. BEGIN, 2. UPDATE xxx, and 3. COMMIT queries in different do_query executions. So now how can I determine that the 2nd query is associated with 1st query, and 3rd query is associated with 2nd query?

I expected something like connection ID to associate those queries but I couldn't find any. Is it currently impossible?

[sunng87]

I think you can use socket address for connection, it can be fetched from ClientInfo, the second argument of handlers.

Also there is a metadata hashmap in ClientInfo can be used to stored some data associated with the client and be dropped when client disconnected.