pq: Can't run two queries in a transaction?

I have some data I’ve loaded into a table from a CSV file that I want to massage into a master table. When I try to do this in a transaction, pq just hangs on the second query I perform for no reason I can ascertain.

Here’s my code. I chopped out everything after the second query; that query checks to see if the data is already migrated, and if not, runs all the create code and such. If you comment out the “select * from community_gnis” line, the loop runs to completion, printing every city in the table. If I leave the query in, it just hangs after the first city name.

if db, err := sql.Open("postgres", "user=postgres dbname=geo sslmode=disable"); err == nil {
    defer db.Close()
    if tx, err := db.Begin(); err == nil {
        if cities, err := tx.Query("select name, state, id, lon, lat from noaa.cities where id is not null"); err == nil {

            var name, state, gnis string
            var longitude, latitude float32

            for cities.Next() {

                if err = cities.Scan(&name, &state, &gnis, &longitude, &latitude); err == nil {

                    fmt.Println("Got city", name, "(", gnis, ")")

                    tx.Query("select * from community_gnis where gnis = $1", gnis)
                }
            }
        }
    }
}

I’m new to Go, so maybe I’m doing things incorrectly here. However, if I move the “select * from community_gnis” up above the “select name, state…” statement, the community_gnis statement runs fine and the “select name, state…” query will then be the one that hangs.

If I remove the transaction code and run everything against “db”, e.g. “db.Query” instead of “tx.Query”, etc., everything runs completely fine (though it doesn’t run in a transaction, which is what I want).

I’ve tried various combinations, using prepared statements, adding Close calls everywhere, etc. Nothing works. I’m 99% sure it’s not the database, because I’m migrating this code from a Node JS app to test whether we should use Go or Node for our project, and the Node version runs completely in a transaction.

About this issue

Original URL
State: open
Created 11 years ago
Reactions: 11
Comments: 41 (11 by maintainers)

Commits related to this issue

Adding test for interleaved statements in a transaction See #81. Currently fails. — committed to lib/pq by deleted user 11 years ago
internal/check-config: fix transaction error in exportCheckConfigObjectTX() We have to call rows.Close() before we issue another query in the same transaction. See https://github.com/lib/pq/issues/81 — committed to hbo/soma by deleted user 5 years ago
dbutil: Introduce DBTx This PR introduces dbutil.DBTx which implements the dbutil.DB interface backed by either a sql.DB or a sql.Tx, with additional support for nested transactions using Postgres sa... — committed to sourcegraph/sourcegraph by deleted user 5 years ago
support/db: Support concurrent queries in a transaction (#2024) This commit adds `Synchronized` flag to `support/db.Session`. When set to `true` and `Session` runs a transaction all `Exec*` and `Sel... — committed to stellar/go by bartekn 5 years ago

Most upvoted comments

Before we do next query or update, we must close the previous rows. For examples:

If we do not close the rows, the next update will fail.

tx, err := utils.GetDB().Begin()
if err != nil {
    log.Fatal(err)
}
rows, err := tx.Query("select id from t_client where data=$1", "test")
if err != nil {
    tx.Rollback()
    log.Fatal(err)
}
var id int32
if rows.Next() {
    rows.Scan(&id)
    rows.Close()   //Very very very important!!!
}
// Here if the rows do not close, the update will fail.
tx.Exec("update t_client set last_upload_time=NOW() where id=$1", id)
err = tx.Commit()

+17

w20089527 on Jun 30, 2016

An option to fetch and buffer all the rows, would be simple to implement, non-magical, consistent with the majority of other Postgres drivers, and give people a simple way forward when they hit this problem.

It could also improve performance on both the client and server sides for some workloads.

It would also let this issue be closed after more than 4 years.

+10

timbunce on Oct 5, 2017

This may be the way the PostgreSQL protocol works, according to this link, maybe there is no solution for this. This is not good. http://www.postgresql.org/message-id/4A8951C5.5030107@portavita.nl

RangelReale on Jul 18, 2013

To help our decision, I made a study of how other platform’s native postgresql do in this case. All except the C# one fetches all rows on Query by default, but the C# one have some notes.

PHP (libpq)

Default method: fetch all on query

Python (http://python.projects.pgfoundry.org/)

Default method: fetch all on query http://python.projects.pgfoundry.org/docs/1.1/driver.html#statement-interface-points

The default execution method, call, produces the entire result set. It is the simplest form of statement execution. Statement objects can be executed in different ways to accommodate for the larger results or random access(scrollable cursors).

node.js (https://github.com/brianc/node-postgres)

Default method: fetch all on query - queues queries so no 2 queries are run at the same time https://github.com/brianc/node-postgres/wiki/Query#example Don’t allow the user to iterate itself, also as queries are queued, it is impossible to run one query inside another

Java (http://jdbc.postgresql.org/)

Default method: fetch all on query http://jdbc.postgresql.org/documentation/head/query.html

Once you have a Statement or PreparedStatement, you can use issue a query. This will return a ResultSet instance, which contains the entire result

C# (http://npgsql.projects.pgfoundry.org/)

Default method: fetches row by row, but warns that only one query can be made at a time, with option to preload all rows http://npgsql.projects.pgfoundry.org/docs/manual/UserManual.html (P.S: according to this page, ADO.NET explicitly says that only one query can be run at a time on a connection. Golang database/sql documentation doesn’t says this)

According to the ADO.NET documentation, while an IDataReader is open the IDbConnection used to obtain it is “busy” and cannot be used for any other operations Note that you can ‘daisy chain’ select statements in a command object’s commandtext to retrieve more than one record set: “select * from tablea; select * from tableb”

Ruby (libpq - https://github.com/ged/ruby-pg)

Default method: fetch all on query

RangelReale on Jul 19, 2013

This is still a problem under go 1.8 with the latest pg driver. We see all of the errors posted above in unit tests that hold tx open for a long time and some parallel go routine kicks off to also perform a query after a Close has been called. It’s a race between rows.Close() and the start of another query.

solarfly73 on Apr 18, 2017