database/sql: standalone connections

[Marko Tiikkaja]

Hi,

I'd like to propose an API for pulling standalone connections out of a
*sql.DB. There are many uses cases for this, such as:

1) Holding session-level locks while executing certain queries
without being in an explicit transaction.
2) Setting parameters before running a query, and then resetting them
before the connection is put back into the pool.
3) Though not part of this proposal, ultimately I'd like a way for
drivers to be able to export functionality not supported by *sql.DB,
such as bulk loading, large objects or notifications.

The suggested API looks like the following:

- *DB.AcquireConn returns a *Conn from the pool. The connection is
taken out of the pool to ensure that nobody else can use it while the
*Conn object is alive, similarly to how transactions currently work.
- The *Conn object has Exec, Query and QueryRow methods for executing
queries.
- The *Conn object has a Begin method for starting a transaction.
- The *Conn object has an Stmt method similar to the *Tx.Stmt method
for making sure a prepared statement has been prepared on the connection.
- The *Conn object has a Prepare method for preparing a statement on
the connection. Such statements are automatically destroyed once the
*Conn is released.
- Concurrent access is not supported and may lead to undefined
behaviour (similarly to how *Tx works). Additionally, if a transaction
is in progress, most *Conn methods return an error until the transaction
has finished (since operating on the connection would not be safe).
- Once the user is done with the connection, *Conn.Release puts it
back into the pool it came from.

I've implemented this API here in case someone wants to have a look:

https://go-review.googlesource.com/#/q/Ic38bc421428ebc95f2034da4d4e8858182fc746b

Any thoughts?


.marko

[Jay Weisskopf]

It's not clear to me how use-cases 2 or 3 become possible given the methods you've chosen to export.

[Marko Tiikkaja]

On 2015-01-12 21:22, Jay Weisskopf wrote:
> It's not clear to me how use-cases 2 or 3 become possible given the methods
> you've chosen to export.

#3 is not supposed to be possible yet with the exported methods. But
being able to pull connections out of the pool to ensure explicit access
to them is a step in that direction.

As for #2, I don't see why not. A naive example in Postgres terms
(error handling omitted):

func foof() {
conn, _ := db.AcquireConn()
defer conn.Release()

_, _ = db.Exec("SET statement_timeout TO '5s'")
defer db.Exec("RESET statement_timeout")

/* do something here with conn, assuming statement_timeout is 5s */
}

Looking at that example now, it might make sense to export a
conn.Discard() which, instead of returning the connection back to the
pool, throws it away. That seems like a reasonable thing to do if the
RESET query fails, for example.


.marko

[Jay Weisskopf]

If I had to use only a single connection for some reason, I might start experimenting by opening a new *sql.DB and setting the max open connections to 1. Is that not viable?

[Marko Tiikkaja]

On 2015-01-12 21:40, Jay Weisskopf wrote:
> If I had to use only a single connection for some reason, I might start
> experimenting by opening a new *sql.DB and setting the max open connections
> to 1. Is that not viable?

No. Reconnections happen behind your back.


.marko

[Martin Gallagher]

This would be a nice addition to database/sql. To give another use case; when using the MySQL connector with Sphinx, to get meaningful statistics after a query a subsequent 'SHOW META' query must be used. Under high contention sql.DB might switch to a different underlying connection from the pool, thus rendering the results of SHOW META meaningless.

[Brad Fitzpatrick]

I don't want to add this much new API. That's a new type and a bunch of new methods.

What's wrong with using a Tx as the existing docs suggest?

If there's a good reason, then another possibility is re-using the *DB type and having a method on *DB called "Single" which returns another *DB which internally has exactly 1 connection and never reconnects and doesn't allow concurrency (returning an error if you try).  But I haven't fully thought that through. It would at least require a number of doc updates, but the API intrusion would be much less.

.marko

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

[Marko Tiikkaja]

On 4/6/15 3:28 PM, Brad Fitzpatrick wrote:
> What's wrong with using a Tx as the existing docs suggest?

There's often need to hold onto a connection over multiple transactions.
Some examples include:

- Batch processing from a temporary table. It's often desirable to
do a larger number of smaller batches to avoid holding onto row-level
locks for too long. Recreating the temporary table for every is not an
option, since it could take hours.

- Sometimes you want to hold a lock over multiple transactions while
also making sure that if the connection which holds the lock is lost, no
work gets done. That's currently impossible; you'd have to hold the
lock in one connection while doing the work in another, which could lead
to problems and/or data loss.

- It would also be nice for drivers to be able to expose
database-specific APIs on the underlying connections, e.g. being able to
return multiple result sets from a stored procedure. That's currently
impossible without horrifyingly ugly tricks, but being able to observe
single connections could allow that in the future.

> I don't want to add this much new API. That's a new type and a bunch of new
> methods.

I don't see that as a problem.

> If there's a good reason, then another possibility is re-using the *DB type
> and having a method on *DB called "Single" which returns another *DB which
> internally has exactly 1 connection and never reconnects and doesn't allow
> concurrency (returning an error if you try). But I haven't fully thought
> that through. It would at least require a number of doc updates, but the
> API intrusion would be much less.

This to me seems uglier from a coding standpoint; we'd have to litter a
number of methods with exceptions for the single-connection mode.


.m

[Marko Tiikkaja]

Hi,

Just stating this publicly so there's no confusion: I won't be working
on this for Go 1.6 or likely any future version of Go, so anyone
interested in seeing this feature should probably pick it up.


.m

[ro...@kabiev.com]

this is very importany feauture, 
go app must have more control over sql connections.


our app worked on Node, every client (browser app) - have his own connection (postgres). becoz we widely use postgres specific features and temporary tables for big calculations.

now we are write application server with go - and sql drivers design is the biggest  headache.

(we cant even handle pg notices!  http://stackoverflow.com/questions/42789361/how-to-view-postgresql-raise-notice-from-go-app


our case: go app must control list of connections (every conection with its own user/password).
we store it like map[string]*sql.DB   - but it's doesnt properly work , sql driver return randomly another free connect.

what's the profit of this approach?! No one big/serious  application use not-controlled pooling like that.


среда, 12 августа 2015 г., 19:17:10 UTC+3 пользователь marko написал: