bmizerany/pq

pq - A pure Go postgres driver for Go's database/sql package

This package is now deprecated. The up to date version is at github.com/lib/pq.

Install

go get github.com/bmizerany/pq

Docs

http://go.pkgdoc.org/github.com/bmizerany/pq

Use

package main

import (
	_ "github.com/bmizerany/pq"
	"database/sql"
)

func main() {
	db, err := sql.Open("postgres", "user=pqgotest dbname=pqgotest sslmode=verify-full")
	// ...
}

Connection String Parameters

These are a subset of the libpq connection parameters. In addition, a number of the environment variables supported by libpq are also supported. Just like libpq, these have lower precedence than explicitly provided connection parameters.

See http://www.postgresql.org/docs/9.1/static/libpq-connect.html.

• dbname - The name of the database to connect to
• user - The user to sign in as
• password - The user's password
• host - The host to connect to. Values that start with / are for unix domain sockets. (default is localhost)
• port - The port to bind to. (default is 5432)
• sslmode - Whether or not to use SSL (default is require, this is not the default for libpq) Valid values are:
  • disable - No SSL
  • require - Always SSL (skip verification)
  • verify-full - Always SSL (require verification)

See http://golang.org/pkg/database/sql to learn how to use with pq through the database/sql package.

Tests

go test is used for testing. A running PostgreSQL server is required, with the ability to log in. The default database to connect to test with is "pqgotest," but it can be overridden using environment variables.

Example:

PGHOST=/var/run/postgresql go test pq

Features

• SSL
• Handles bad connections for database/sql
• Scan time.Time correctly (i.e. timestamp[tz], time[tz], date)
• Scan binary blobs correctly (i.e. bytea)
• pq.ParseURL for converting urls to connection strings for sql.Open.
• Many libpq compatible environment variables
• Unix socket support

Future / Things you can help with

• Notifications: LISTEN/NOTIFY
• hstore sugar (i.e. handling hstore in rows.Scan)

Thank you (alphabetical)

Some of these contributors are from the original library bmizerany/pq.go whose code still exists in here.

• Andy Balholm (andybalholm)
• Ben Berkert (benburkert)
• Bjørn Madsen (aeons)
• Blake Gentry (bgentry)
• Brad Fitzpatrick (bradfitz)
• Daniel Farina (fdr)
• Everyone at The Go Team
• Federico Romero (federomero)
• Heroku (heroku)
• John Gallagher (jgallagher)
• Kamil Kisiel (kisielk)
• Keith Rarick (kr)
• Marc Brinkmann (mbr)
• Martin Olsen (martinolsen)
• Mike Lewis (mikelikespie)
• Ryan Smith (ryandotsmith)
• Samuel Stauffer (samuel)
• notedit (notedit)

pq's Issues

Suggestion: document how to use bind parameters

Unlike is documented on http://golang.org/pkg/database/sql/#example_DB_Query, this driver cannot handle the "?" placeholder for query parameters. Instead the $ notation must be used. Figuring this out took me far too long while staring at:

SELECT * FROM questions WHERE id=?

pq: S:"ERROR" C:"42883" M:"operator does not exist: integer =?" H:"No operator matches the given name and argument type(s). You might need to add explicit type casts." P:"33" F:"parse_oper.c" L:"755" R:"op_error"

Eventually, I found the correct expression:

db.Query("SELECT * FROM questions WHERE id=$1", 1)

StoredProcedures - Exec issue

Stored procedure invoked using conn.Exec("SELECT sp(....)") is failing with error 'pq: unknown response for simple query: 'D'. 'It is working if conn.Query is used.

With Exec subsequent queries (eg SELECT 1) on same connections are failing with errors like

• pq: unexpected describe rows response: 'C'
• pq: unexpected bind response: '1'

'D' seems to indicate a data row. Exec can be used for queries like 'INSERT ... RETURNING id' So shouldn't 'D' be handled for Exec case ?

Doesn't parse password with spaces in it

sql.Open("postgres", "host=localhost password=correct horse battery staple")

does not work, nor does

sql.Open("postgres", "host=localhost password='correct horse battery staple'")

I encountered a problem when Using Go-pGSQL, which is as follows:

Hi,friend:

I encountered a problem when Using Go-pGSQL, which is as follows:

Execute the SQL statement as follows:

Select * from books where price BETWEEN money(10)and Money (100)

In the use of Golang, the following error occurs:

The function money(Unknown) is not unique and cannot select the best candidate function

Please see the picture below for more information

Looking forward to your reply

Support for bulk imports with COPY FROM STDIN

PostgreSQL's COPY FROM STDIN is much faster for bulk imports (8x in my tests).

It's a bit tricky to implement it with the existing database.sql API, but I think I found a reasonable solution. You can see it here in action: https://github.com/olt/pq/blob/bulk/copy_test.go

The only wart is that sql.Stmt.Close() doesn't pass the errors from the COPY stmt.Close(). You need to call .Exec() without arguments before .Close() to synchronize and get any errors for the otherwise asynchronous inserts. (Don't know if this is a bug in Go and worth reporting?)

What do you think? I could write a bit documentation for it and send a pull request.

How do I know what constraint I just violated?

When I exec a query that violates a constraint, I get an error back, but unfortunately the fields in it seem to be opaque to me. What is most of interest in the error is the name of the constraint that the query violated. Based on this, I could prepare a friendly message to the user explaining what the problem with their request is.

I suppose I could parse the error string given by Error(), but if there were a friendlier way to get at that constraint name, that would be nice.

When running more than 10-20 tests, causes "too many clients already"

It seems that each test case is a goroutine, when running tests, it exceeds the connection limitation then it fails.

any ideal? provide a connection manager or something?

Transaction rollback bug

I have encountered a bug in transaction rollback handling. The bug occurs when one opens a transaction, issues a query, rolls back the transaction, then tries to issue another query on the same DB object that was used to create the transaction.

Error message is:

pq: unexpected describe rows response: 'C'

Error is coming from here: https://github.com/bmizerany/pq/blob/master/conn.go#L216

Postgres manual says 'C' is a legitimate response header:

CommandComplete (B)
Byte1('C')
Identifies the message as a command-completed response.

Int32
Length of message contents in bytes, including self.

String
The command tag. This is usually a single word that identifies which SQL command was completed.

For an INSERT command, the tag is INSERT oid rows, where rows is the number of rows inserted. oid is the object ID of the inserted row if rows is 1 and the target table has OIDs; otherwise oid is 0.

For a DELETE command, the tag is DELETE rows where rows is the number of rows deleted.

For an UPDATE command, the tag is UPDATE rows where rows is the number of rows updated.

For a SELECT or CREATE TABLE AS command, the tag is SELECT rows where rows is the number of rows retrieved.

For a MOVE command, the tag is MOVE rows where rows is the number of rows the cursor's position has been changed by.

For a FETCH command, the tag is FETCH rows where rows is the number of rows that have been retrieved from the cursor.

For a COPY command, the tag is COPY rows where rows is the number of rows copied. (Note: the row count appears only in PostgreSQL 8.2 and later.)

Here's how libpq handles it: http://doxygen.postgresql.org/fe-protocol3_8c_source.html#l00199

Test Case

I wrote a test case that reproduces the bug: https://github.com/jmcvetta/pq/blob/tx_bug/conn_test.go#L469

Impact

This bug is currently blocking development of PostgreSQL dialect of qbs ORM: coocood/qbs#11

add a changelog, begin tagging releases

Support SetMaxIdleConns

db, err := sql.Open("...")
db.SetMaxIdleConns(12)


Error: db.SetMaxIdleConns undefined (type *sql.DB has no field or method SetMaxIdleConns)

Be able to implement it?