handling multiple simultaneous database/sql queries in pq driver

[Rangel Reale]

This is a discussion we are having on an issue on the pq library:

https://github.com/lib/pq/issues/81

The problem is, because of the way the postgresql protocol works, it is not possible to issue a new query before reading all data from the previous query.

Because of this, if I do a query using db.Query, and while iterating the rows I issue another query, we get an error from the protocol, because it is expecting that we read the next data row.

The discussion is about fetching all rows on query, or just warn on the documentation that it works this way.

Another option would be to just fetch all data when a new query is issued, but we are not sure about how to handle this issue in connection for thread safety, and if it is a goood ideia do store this kind of state on the connection object.

Reading the database/sql documentation, there is no mention if the driver should support more than 1 query at the same time.

Anyone has any opinion about this issue?

Thanks,

Rangel

[Brad Fitzpatrick]

Please open a Go bug tracking this, and referencing that bug.

I imagine we might need a way for drivers to declare this limitation of theirs, so the database/sql package will open up new driver.Conns as needed when this is the case, making sure any Conns with incompletely-read result sets aren't re-used until they're exhausted.

Feel free to put this email in the bug too.

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Tad Glines]

I believe you meant "tx.Query" instead of "db.Query"; db.Query already uses a separate connection for each invocation.

Regarding multiple active queries on a single connection during a transaction, it is possible to do so with PostgreSQL at the protocol level.

See: http://www.postgresql.org/docs/8.1/static/protocol-flow.html

And also: http://www.postgresql.org/docs/8.1/static/plpgsql-cursors.html

The driver can support multiple active queries, but it requires the use of named portals (a named cursor at the statement level) and some internal caching of fetched results. I'm not sure if you can mix named and unnamed cursors, so I don't know if you have to preemptively use named cursors in order to support multiple active queries in the same connection/transaction.

The lowest common denominator for databases is that one connection can have only have one active query at a time. Some databases, like PostgreSQL (and probably Oracle) support multiple active cursors on a single connection. And, some databases, like Oracle, support using a single transaction in multiple connections.

One possibility is that a capability discovery interface be added to the driver spec. Then database/sql could use this to determine if the driver supports multiple active queries. Then it could either lock the TX until Rows.Close is called, or only lock the TX around the call to Rows.Next.

I think the default should be that the TX is locked until the Rows from a query is closed. This will allow multiple goroutines to access the same TX without causing errors.

-Tad

On Wed, Jul 24, 2013 at 9:34 AM, Rangel Reale <range...@gmail.com> wrote:

--

[gwenn...@gmail.com]

* SQLite supports multiple cursors on the same connection/transaction too.

See this recent thread: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg78309.html

* Sybase (at least the Perl DBI driver) doesn't support it (in a transaction):

http://search.cpan.org/~mewp/DBD-Sybase-1.15/Sybase.pm#Multiple_active_statements_on_one_$dbh
Regards.