How to connect to Amazon Redshift database?

[ChrisLu]

I tried to use github.com/lib/pq to connect to Amazon Redshift, but failed.

Has anyone done it successfully?

I know Amazon Redshift is based on PostgreSQL 8.0.2. But behaves differently. For example, I can get it working with DbVisualizer, but not pgAdmin.

Chris

[Gyu-Ho Lee]

Which part? Error? I have been using `pq` driver frequently and am happy with that.

[Chris Lu]

I found the second query always fail, even when running the same query twice. The error seems showing the library is trying to create a new connection.

For example, the following code output:

2014/07/17 14:29:01 selecting 1... 2014/07/17 14:29:03 second select... 2014/07/17 14:29:09 Failed on second query:dial tcp 54.198.68.200:5439: connection refused

Chris

log.Println("selecting 1...")

err = db.QueryRow("select id, name from table1 where name = $1", name).Scan(&n.Id, &n.Name)

if err != nil {

log.Fatal(err)

}

log.Println("second select...")

err = db.QueryRow("select id, name from tablw1 where name = $1", name).Scan(&n.Id, &n.Name)

if err != nil {

log.Fatal("Failed on second query:", err)

}

--
You received this message because you are subscribed to a topic in the Google Groups "golang-nuts" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/golang-nuts/YEG2dXfKYRI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Bryan Whitehead]

I don't have any experience with lib/pg or pg at all - but using mysql drivers I would guess that you don't have a connection being established at all.

As the database/sql docs state, try checking a ping to make sure the driver is working:

err = db.Ping()

also if there are connection limits you need to make sure db.SetMaxOpenConns is correctly set.

--
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.

[Chris Lu]

The first query does return the data. So at least one connection was established.

[ma...@joh.to]

While pq doesn't "officially" support RedShift, I've heard of people using it there successfully.

On Thursday, July 17, 2014 11:33:55 PM UTC+2, ChrisLu wrote:

I found the second query always fail, even when running the same query twice. The error seems showing the library is trying to create a new connection.

For example, the following code output:

2014/07/17 14:29:01 selecting 1... 2014/07/17 14:29:03 second select... 2014/07/17 14:29:09 Failed on second query:dial tcp 54.198.68.200:5439: connection refused

Could you check the server's logs to see if RedShift crashed?  There's been reports that a certain sequence of events done by pq could crash the RedShift server instance, which would explain why database/sql (not pq, mind you) is trying to create a new connection here.
 

[Chris Lu]

Thanks for the info! I do found out that RedShift crashed.

However, I am not really doing anything special except sending 2 queries, one by one. How come pq crashes the RedShift? Do you have any clue?

Chris

--

[Marko Tiikkaja]

On 2014-07-29 1:07 AM, Chris Lu wrote:
> Thanks for the info! I do found out that RedShift crashed.
>
> However, I am not really doing anything special except sending 2 queries,
> one by one. How come pq crashes the RedShift? Do you have any clue?

I don't remember the exact conditions under which it occurs, and I
stopped being interested once Amazon refused to look at the minimal Go
program which reproduced the problem. What pq does is completely valid
and postgres 8.0.2 (which they claim to have branched off of) doesn't
crash given the same exact sequence of messages -- at least in the test
case I had the last time.

Perhaps if you care enough, you could construct a program in their
language of choice which simply uses TCP sockets to send the correct
sequence of bytes to log in and crash the server.


.marko

[r.b...@modcloth.com]

I found that if you create a statement use db.Prepare, then defer a stmt.Close() and then perform your QueryRow using stmt.QueryRow, the resources get released correctly.  

Looking through the database/sql code, there's a releaseConn function passed back in the Rows struct, but QueryRow never runs the release, so at least for Redshift, that causes the database to hang for a bit.