DB-specific extensions and database/sql

[Maciek Sakrejda]

Is there an idiomatic (or otherwise, for that matter) mechanism for adding database-specific extensions in a driver.Driver implementation? Specifically, I'm interested in adding Postgres LISTEN/NOTIFY [1] support to pq. A patch has been submitted [2] that shoehorns this into the existing query mechanism, but while clever, this has a number of disadvantages (awkward, hacky interface; ties up the connection). I was hoping to implement something that would expose a .Listen(chanName string) <- chan *pq.Notification method (or a similar interface), and simply send new notifications on this channel. I think the pq implementation itself would be fairly simple, but I'm not sure how to expose the Listen--obviously I can't do it through the sql.DB struct. Any thoughts?

Thanks,
Maciek

[1]: http://www.postgresql.org/docs/current/static/sql-listen.html and http://www.postgresql.org/docs/current/static/sql-notify.html and
[2]: https://github.com/lib/pq/pull/106
[3]: http://jdbc.postgresql.org/documentation/head/listennotify.html

[Arne Hormann]

I think it's best to add advanced functionality to the driver itself, so instead of

import _ "github.com/lib/pg"

you use

import "github.com/lib/pg"

and call it as pg.Listen(chanName).

As this is very database specific anyway you don't lose anything by an explicit import.

[Kamil Kisiel]

I agree with Arne that this kind of thing is likely best added as a driver-specific API. I don't think it's necessary to try to shoehorn everything in database/sql, especially when its thing which are not general-purpose SQL operations.

[Maciek Sakrejda]

(accidentally replied off-list; following up here)

On Thursday, June 6, 2013 3:06:05 PM UTC-7, Arne Hormann wrote:

Thanks, but is there a way to do this that can reuse an existing connection? That's a big part of the appeal of LISTEN/NOTIFY.

You need the wrapped connection from the driver?
sql.DB wouldn't help you anyway, it's the connection pool and not the "right" connection.
sql.Stmt could help, but it wraps the statement from the driver.

I had a similar issue with column types. My proposal would also solve your problem:
https://groups.google.com/forum/#!searchin/golang-nuts/PROP/golang-nuts/2aLctcVyp6Q/qyl4l7W_SS8J

Yeah, I think the *DB variant here would do exactly what I want. If you look at the way JDBC approaches LISTEN/NOTIFY[1] (I actually referenced this in the last e-mail but forgot to discuss it), it just unwraps the connection by casting and exposes the driver-specific functionality that way. Because database/sql is structured differently, doing the same thing couldn't work, but the Inspector mechanism should.
 

I also created this issue:
https://code.google.com/p/go/issues/detail?id=5606

Thanks, commented.

And I built a workaround for unwrapping driver specific parts using unsafe:
https://github.com/arnehormann/sqlinternals - this can easily be extended for a sql.Stmt

Interesting. I think I prefer the current workaround (tying up a connection with the existing proposed patch), but I might give this a shot.

[1]: http://jdbc.postgresql.org/documentation/head/listennotify.html

[Maciek Sakrejda]

On Thursday, June 6, 2013 4:25:30 PM UTC-7, Kamil Kisiel wrote:

I agree with Arne that this kind of thing is likely best added as a driver-specific API. I don't think it's necessary to try to shoehorn everything in database/sql, especially when its thing which are not general-purpose SQL operations.

The issue is that a large point of the feature is to be able to piggy-back on an existing underlying connection in an async fashion. Sorry that wasn't clear.

[Arne Hormann]

And I built a workaround for unwrapping driver specific parts using unsafe:
https://github.com/arnehormann/sqlinternals - this can easily be extended for a sql.Stmt

Interesting. I think I prefer the current workaround (tying up a connection with the existing proposed patch), but I might give this a shot.

If you try mine, have a look at my helper library https://github.com/arnehormann/mirror

It can be used to check structs for types, names and memory layout, so you can "safely" read unexported fields with unsafe.

It's not fully where I want it to be yet (see issues), but it should do everything that is needed in this case.

For now it's only used in the sqlinternals/mysql subpackage, but that's a rather good example for it (unsafe.go, L105-L138 and L105-L138).

 

[1]: http://jdbc.postgresql.org/documentation/head/listennotify.html

[Kamil Kisiel]

How about adding the methods to the type that implements driver.Conn? Then you could use a type assertion to get the value from the interface and call the the async methods. It would mean making the driver an exported type, but maybe that's an alright tradeoff?

[Arne Hormann]

You want to add the method for LISTEN to the implementation of the driver.Conn interface (if I read you right).

But then there's still the problem that it's impossible to get the driver.Conn implementation.

There is no sql.Conn because sql.DB is a connection pool. So you'd have to get driver.Conn from sql.Stmt (or maybe from sql.DB with a function).

In there, it's wrapped and unexported. This function can't be used by anything but a driver because it uses the driver's unexported fields.

Do you really want to add an exported field or function to sql.Stmt to get this?

I think this would be weird, driver specific stuff belongs in the driver package and should not confuse users of database/sql who can't do anything with it.