Conn.Exec timezone

[ghost]

What is the wrong with this program? ... if _, err := Conn.Exec("SET TIME ZONE $1", site.TimeZone); err != nil { logger.Lgr.Println(err) } ... /src/db/db.go:26: pq: syntax error at or near "$1"

package site const ( TimeZone = "GMT" )

[cbandy]

1. When using parameters, the wire protocol is actually creating a prepared statement. Only SELECT, INSERT, UPDATE, DELETE, or VALUES statements can be prepared. This StackExchange answer goes into more detail.

   It's possible to rewrite SET and SHOW statements as UPDATE and SELECT statements against the pg_settings view. I believe your example would be:

   Conn.Exec(`UPDATE pg_settings SET setting = $1 WHERE name = 'TimeZone'`, site.TimeZone)

2. If Conn is *sql.DB it won't exactly do what you want. Each *sql.DB is a pool of connections to PostgreSQL. When you call *sql.DB.Exec, a connection is checked out of the pool, used, then returned to the pool. The next time you call Exec or Query it may use a different connection from the pool with a different session state. The only way to hold/re-use a connection with database/sql is to first call *sql.DB.Begin. The Exec and Query methods on *sql.Tx are guaranteed to use the same connection to PostgreSQL.