func waitRow(c *C, conn *pgx.Conn, n int) {
var res int64
err := queryAttempts.Run(func() error {
return conn.QueryRow("SELECT id FROM test WHERE id = $1", n).Scan(&res)
})
c.Assert(err, IsNil)
}
// afterConnect creates the prepared statements that this application uses
func afterConnect(conn *pgx.Conn) (err error) {
_, err = conn.Prepare("getUrl", `
select url from shortened_urls where id=$1
`)
if err != nil {
return
}
_, err = conn.Prepare("deleteUrl", `
delete from shortened_urls where id=$1
`)
if err != nil {
return
}
// There technically is a small race condition in doing an upsert with a CTE
// where one of two simultaneous requests to the shortened URL would fail
// with a unique index violation. As the point of this demo is pgx usage and
// not how to perfectly upsert in PostgreSQL it is deemed acceptable.
_, err = conn.Prepare("putUrl", `
with upsert as (
update shortened_urls
set url=$2
where id=$1
returning *
)
insert into shortened_urls(id, url)
select $1, $2 where not exists(select 1 from upsert)
`)
return
}
func insertRow(c *C, conn *pgx.Conn, n int) {
_, err := conn.Exec("INSERT INTO test (id) VALUES ($1)", n)
c.Assert(err, IsNil)
}
func mustExec(t testing.TB, conn *pgx.Conn, sql string, arguments ...interface{}) (commandTag pgx.CommandTag) {
var err error
if commandTag, err = conn.Exec(sql, arguments...); err != nil {
t.Fatalf("Exec unexpectedly failed with %v: %v", sql, err)
}
return
}
// Do a simple query to ensure the connection is still usable
func ensureConnValid(t *testing.T, conn *pgx.Conn) {
var sum, rowCount int32
rows, err := conn.Query("select generate_series(1,$1)", 10)
if err != nil {
t.Fatalf("conn.Query failed: %v", err)
}
defer rows.Close()
for rows.Next() {
var n int32
rows.Scan(&n)
sum += n
rowCount++
}
if rows.Err() != nil {
t.Fatalf("conn.Query failed: %v", err)
}
if rowCount != 10 {
t.Error("Select called onDataRow wrong number of times")
}
if sum != 55 {
t.Error("Wrong values returned")
}
}
func execQuery64(conn *pgx.Conn, stmt string, arguments ...interface{}) int64 {
var err error
var result int64
err = conn.QueryRow(stmt, arguments...).Scan(&result)
checkErr(err)
return result
}
func mustPrepare(db *pgx.Conn, name, query string) *pgx.PreparedStatement {
stmt, err := db.Prepare(name, query)
if err != nil {
log.Fatalf("Error when preparing statement %q: %s", query, err)
}
return stmt
}
// prepQue ensures that the que table exists and que's prepared statements are
// run. It is meant to be used in a pgx.ConnPool's AfterConnect hook.
func prepQue(conn *pgx.Conn) error {
_, err := conn.Exec(queTableSQL)
if err != nil {
return err
}
return que.PrepareStatements(conn)
}
func PrepareStatements(conn *pgx.Conn) error {
for name, sql := range preparedStatements {
if _, err := conn.Prepare(name, sql); err != nil {
return err
}
}
return nil
}
func testJsonInt16ArrayFailureDueToOverflow(t *testing.T, conn *pgx.Conn, typename string) {
input := []int{1, 2, 234432}
var output []int16
err := conn.QueryRow("select $1::"+typename, input).Scan(&output)
if _, ok := err.(*json.UnmarshalTypeError); !ok {
t.Errorf("%s: Expected *json.UnmarkalTypeError, but got %v", typename, err)
}
}
func testJsonInt16ArrayFailureDueToOverflow(t *testing.T, conn *pgx.Conn, typename string) {
input := []int{1, 2, 234432}
var output []int16
err := conn.QueryRow("select $1::"+typename, input).Scan(&output)
if err == nil || err.Error() != "can't scan into dest[0]: json: cannot unmarshal number 234432 into Go value of type int16" {
t.Errorf("%s: Expected *json.UnmarkalTypeError, but got %v", typename, err)
}
}
func mustPrepare(t testing.TB, conn *pgx.Conn, name, sql string) *pgx.PreparedStatement {
ps, err := conn.Prepare(name, sql)
if err != nil {
t.Fatalf("Could not prepare %v: %v", name, err)
}
return ps
}
func execUpdate(conn *pgx.Conn, stmt string, arguments ...interface{}) bool {
var err error
// fmt.Println(stmt)
_, err = conn.Exec(stmt, arguments...)
//if err != nil {
// fmt.Println(err)
//}
return err == nil
}
func benchmarkSelectWithLog(b *testing.B, conn *pgx.Conn) {
_, err := conn.Prepare("test", "select 1::int4, 'johnsmith', '*****@*****.**', 'John Smith', 'male', '1970-01-01'::date, '2015-01-01 00:00:00'::timestamptz")
if err != nil {
b.Fatal(err)
}
b.ResetTimer()
for i := 0; i < b.N; i++ {
var record struct {
id int32
userName string
email string
name string
sex string
birthDate time.Time
lastLoginTime time.Time
}
err = conn.QueryRow("test").Scan(
&record.id,
&record.userName,
&record.email,
&record.name,
&record.sex,
&record.birthDate,
&record.lastLoginTime,
)
if err != nil {
b.Fatal(err)
}
// These checks both ensure that the correct data was returned
// and provide a benchmark of accessing the returned values.
if record.id != 1 {
b.Fatalf("bad value for id: %v", record.id)
}
if record.userName != "johnsmith" {
b.Fatalf("bad value for userName: %v", record.userName)
}
if record.email != "*****@*****.**" {
b.Fatalf("bad value for email: %v", record.email)
}
if record.name != "John Smith" {
b.Fatalf("bad value for name: %v", record.name)
}
if record.sex != "male" {
b.Fatalf("bad value for sex: %v", record.sex)
}
if record.birthDate != time.Date(1970, 1, 1, 0, 0, 0, 0, time.Local) {
b.Fatalf("bad value for birthDate: %v", record.birthDate)
}
if record.lastLoginTime != time.Date(2015, 1, 1, 0, 0, 0, 0, time.Local) {
b.Fatalf("bad value for lastLoginTime: %v", record.lastLoginTime)
}
}
}
func testJsonStringArray(t *testing.T, conn *pgx.Conn, typename string) {
input := []string{"foo", "bar", "baz"}
var output []string
err := conn.QueryRow("select $1::"+typename, input).Scan(&output)
if err != nil {
t.Errorf("%s: QueryRow Scan failed: %v", typename, err)
}
if !reflect.DeepEqual(input, output) {
t.Errorf("%s: Did not transcode []string successfully: %v is not %v", typename, input, output)
}
}
func testJsonInt64Array(t *testing.T, conn *pgx.Conn, typename string) {
input := []int64{1, 2, 234432}
var output []int64
err := conn.QueryRow("select $1::"+typename, input).Scan(&output)
if err != nil {
t.Errorf("%s: QueryRow Scan failed: %v", typename, err)
}
if !reflect.DeepEqual(input, output) {
t.Errorf("%s: Did not transcode []int64 successfully: %v is not %v", typename, input, output)
}
}
func waitReadWrite(c *C, conn *pgx.Conn) {
var readOnly string
err := queryAttempts.Run(func() error {
if err := conn.QueryRow("SHOW default_transaction_read_only").Scan(&readOnly); err != nil {
return err
}
if readOnly == "off" {
return nil
}
return fmt.Errorf("transaction readonly is %q", readOnly)
})
c.Assert(err, IsNil)
}
func waitRecovered(c *C, conn *pgx.Conn) {
var recovery bool
err := queryAttempts.Run(func() error {
err := conn.QueryRow("SELECT pg_is_in_recovery()").Scan(&recovery)
if err != nil {
return err
}
if recovery {
return fmt.Errorf("in recovery")
}
return nil
})
c.Assert(err, IsNil)
}
func testJsonSingleLevelStringMap(t *testing.T, conn *pgx.Conn, typename string) {
input := map[string]string{"key": "value"}
var output map[string]string
err := conn.QueryRow("select $1::"+typename, input).Scan(&output)
if err != nil {
t.Errorf("%s: QueryRow Scan failed: %v", typename, err)
return
}
if !reflect.DeepEqual(input, output) {
t.Errorf("%s: Did not transcode map[string]string successfully: %v is not %v", typename, input, output)
return
}
}
// This function uses a postgresql 9.6 specific column
func getConfirmedFlushLsnFor(t *testing.T, conn *pgx.Conn, slot string) string {
// Fetch the restart LSN of the slot, to establish a starting point
rows, err := conn.Query(fmt.Sprintf("select confirmed_flush_lsn from pg_replication_slots where slot_name='%s'", slot))
if err != nil {
t.Fatalf("conn.Query failed: %v", err)
}
defer rows.Close()
var restartLsn string
for rows.Next() {
rows.Scan(&restartLsn)
}
return restartLsn
}
func testJsonNestedMap(t *testing.T, conn *pgx.Conn, typename string) {
input := map[string]interface{}{
"name": "Uncanny",
"stats": map[string]interface{}{"hp": float64(107), "maxhp": float64(150)},
"inventory": []interface{}{"phone", "key"},
}
var output map[string]interface{}
err := conn.QueryRow("select $1::"+typename, input).Scan(&output)
if err != nil {
t.Errorf("%s: QueryRow Scan failed: %v", typename, err)
return
}
if !reflect.DeepEqual(input, output) {
t.Errorf("%s: Did not transcode map[string]interface{} successfully: %v is not %v", typename, input, output)
return
}
}
func testJsonStruct(t *testing.T, conn *pgx.Conn, typename string) {
type person struct {
Name string `json:"name"`
Age int `json:"age"`
}
input := person{
Name: "John",
Age: 42,
}
var output person
err := conn.QueryRow("select $1::"+typename, input).Scan(&output)
if err != nil {
t.Errorf("%s: QueryRow Scan failed: %v", typename, err)
}
if !reflect.DeepEqual(input, output) {
t.Errorf("%s: Did not transcode struct successfully: %v is not %v", typename, input, output)
}
}
func (self *Database) afterConnect(conn *pgx.Conn) (err error) {
_, err = conn.Prepare("getZoneById", `SELECT
"id", "name"
FROM "stores"
WHERE id=$1
`)
if err != nil {
self.Log.Error("Prepare", "getZoneById", err.Error())
return
}
_, err = conn.Prepare("setShowBanner", `INSERT INTO "banner_shows"
(created_at, updated_at, show_date, show_time, store_id, is_bot, ses_uuid, user_mac, user_ip, ipv4, accept_language,
ua_browser_family, ua_browser_version, ua_os_family, ua_device_family, is_mobile, user_agent, referrer)
VALUES ($1::timestamptz(0), NOW()::timestamptz(0), $1::date, $1::time(0), $3, $4, $2, $5, $6, $7, $8, $9, $10::int2, $11, $12, $13, $14, $15)
`)
if err != nil {
self.Log.Error("Prepare", "setShowBanner", err.Error())
return
}
_, err = conn.Prepare("getZoneByName", `
SELECT
"address",
"city",
"country",
"created_at",
"deleted_at",
"email",
"id",
"latitude",
"longitude",
"name",
"phone",
"position",
"region",
"updated_at",
"zip"
FROM "stores" WHERE lower(name)=lower($1)
`)
if err != nil {
self.Log.Error("Prepare", "getZoneByName", err.Error())
return
}
return
}
func TestPoolReleaseDiscardsDeadConnections(t *testing.T) {
t.Parallel()
// Run timing sensitive test many times
for i := 0; i < 50; i++ {
func() {
maxConnections := 3
pool := createConnPool(t, maxConnections)
defer pool.Close()
var c1, c2 *pgx.Conn
var err error
var stat pgx.ConnPoolStat
if c1, err = pool.Acquire(); err != nil {
t.Fatalf("Unexpected error acquiring connection: %v", err)
}
defer func() {
if c1 != nil {
pool.Release(c1)
}
}()
if c2, err = pool.Acquire(); err != nil {
t.Fatalf("Unexpected error acquiring connection: %v", err)
}
defer func() {
if c2 != nil {
pool.Release(c2)
}
}()
if _, err = c2.Exec("select pg_terminate_backend($1)", c1.Pid); err != nil {
t.Fatalf("Unable to kill backend PostgreSQL process: %v", err)
}
// do something with the connection so it knows it's dead
rows, _ := c1.Query("select 1")
rows.Close()
if rows.Err() == nil {
t.Fatal("Expected error but none occurred")
}
if c1.IsAlive() {
t.Fatal("Expected connection to be dead but it wasn't")
}
stat = pool.Stat()
if stat.CurrentConnections != 2 {
t.Fatalf("Unexpected CurrentConnections: %v", stat.CurrentConnections)
}
if stat.AvailableConnections != 0 {
t.Fatalf("Unexpected AvailableConnections: %v", stat.CurrentConnections)
}
pool.Release(c1)
c1 = nil // so it doesn't get released again by the defer
stat = pool.Stat()
if stat.CurrentConnections != 1 {
t.Fatalf("Unexpected CurrentConnections: %v", stat.CurrentConnections)
}
if stat.AvailableConnections != 0 {
t.Fatalf("Unexpected AvailableConnections: %v", stat.CurrentConnections)
}
}()
}
}
func prepareStatements(database *pgx.Conn) error {
var err error
_, err = database.Prepare("queryCommit", "select product from commit where hash = $1")
if err != nil {
return err
}
_, err = database.Prepare("queryCommitWithProduct", "select commitDate from commit where hash = $1 and product = $2")
if err != nil {
return err
}
_, err = database.Prepare("insertCommit", "insert into commit (hash, commitDate, product, mergeBaseHash) values ($1, $2, $3, $4)")
if err != nil {
return err
}
_, err = database.Prepare("queryBenchmarks", "select name from benchmark")
if err != nil {
return err
}
_, err = database.Prepare("insertBenchmark", "insert into benchmark (name, disabled) values ($1, NULL)")
if err != nil {
return err
}
_, err = database.Prepare("queryConfig", "select monoExecutable, monoEnvironmentVariables, monoOptions from config where name = $1")
if err != nil {
return err
}
_, err = database.Prepare("insertConfig", "insert into config (name, monoExecutable, monoEnvironmentVariables, monoOptions) values ($1, $2, $3, $4)")
if err != nil {
return err
}
_, err = database.Prepare("queryMachine", "select architecture from machine where name = $1")
if err != nil {
return err
}
_, err = database.Prepare("insertMachine", "insert into machine (name, architecture, isDedicated) values ($1, $2, false)")
if err != nil {
return err
}
runSetColumns := "r.startedAt, r.finishedAt, r.buildURL, r.logURLs, c.hash as commit, r.secondaryCommits, m.name as machine, cfg.name as config, timedOutBenchmarks, crashedBenchmarks, pullRequest"
_, err = database.Prepare("queryRunSet", "select "+runSetColumns+" from runset r, commit c, machine m, config cfg where r.id = $1 and r.commit = c.id and r.machine = m.id and r.config = cfg.id")
if err != nil {
return err
}
runSetColumnsInsert := "startedAt, finishedAt, buildURL, logURLs, commit, secondaryCommits, machine, config, timedOutBenchmarks, crashedBenchmarks, pullRequest"
_, err = database.Prepare("insertRunSet", "insert into runSet ("+runSetColumnsInsert+") select $1, $2, $3, $4, c.id, $6, m.id, cfg.id, $9, $10, $11 from machine m, config cfg, commit c where c.hash = $5 and m.name = $7 and cfg.name = $8 returning runset.id")
if err != nil {
return err
}
_, err = database.Prepare("updateRunSet", "update runSet set logURLs = $2, timedOutBenchmarks = $3, crashedBenchmarks = $4 where id = $1")
if err != nil {
return err
}
_, err = database.Prepare("deleteRunSet", "delete from RunSet where id = $1")
if err != nil {
return err
}
_, err = database.Prepare("insertRun", "insert into run (benchmark, runSet) select b.id, $2 from benchmark b where b.name = $1 returning id")
if err != nil {
return err
}
_, err = database.Prepare("deleteRunByRunSetId", "delete from Run where runSet = $1")
if err != nil {
return err
}
_, err = database.Prepare("queryRunMetrics", "select r.id, b.name as benchmark, metric.name as metric, rm.result, rm.resultArray from run r, runMetric rm, benchmark b, metric where r.runSet = $1 and r.benchmark = b.id and rm.run = r.id and rm.metric = metric.id")
if err != nil {
return err
}
_, err = database.Prepare("queryRunMetricsForRun", "select rm.id, metric.name as metric from runMetric rm, metric where rm.run = $1 and rm.metric = metric.id")
if err != nil {
return err
}
_, err = database.Prepare("insertRunMetricNumber", "insert into runMetric (run, metric, result) select $1, metric.id, $3 from metric where metric.name = $2")
if err != nil {
return err
}
_, err = database.Prepare("updateRunMetricNumber", "update runMetric set result = $2 where id = $1")
if err != nil {
return err
}
_, err = database.Prepare("insertRunMetricArray", "insert into runMetric (run, metric, resultArray) select $1, metric.id, $3 from metric where metric.name = $2")
if err != nil {
return err
}
_, err = database.Prepare("deleteRunMetricByRunSetId", "delete from RunMetric where run in (select id from Run where runSet = $1)")
if err != nil {
return err
}
_, err = database.Prepare("updateRunMetricArray", "update runMetric set resultArray = $2 where id = $1")
if err != nil {
return err
}
_, err = database.Prepare("queryRunSetSummaries", "select rs.id, c.hash as commit from runSet rs, commit c, machine m, config cfg where rs.machine = m.id and rs.config = cfg.id and rs.commit = c.id and m.name = $1 and cfg.name = $2")
if err != nil {
return err
}
_, err = database.Prepare("insertPullRequest", "insert into pullRequest (baselineRunSet, url) values ($1, $2) returning id")
if err != nil {
return err
}
return nil
}
func mustPrepare(t testing.TB, conn *pgx.Conn, name, sql string) {
if _, err := conn.Prepare(name, sql); err != nil {
t.Fatalf("Could not prepare %v: %v", name, err)
}
}
func exec(conn *pgx.Conn, stmt string, arguments ...interface{}) {
var err error
// fmt.Println(stmt)
_, err = conn.Exec(stmt, arguments...)
checkErr(err)
}
func closeConn(t testing.TB, conn *pgx.Conn) {
err := conn.Close()
if err != nil {
t.Fatalf("conn.Close unexpectedly failed: %v", err)
}
}
func unlistenAndRelease(pool *pgx.ConnPool, conn *pgx.Conn, channel string) {
if err := conn.Unlisten(channel); err != nil {
conn.Close()
}
pool.Release(conn)
}
// initial all sql
func (pgdb *PostgresDB) afterConnect(conn *pgx.Conn) (err error) {
_, err = conn.Prepare("getTask", `
select id,code, description from tasks where id=$1
`)
checkError(err)
_, err = conn.Prepare("listTask", `
select id,code, description from tasks order by id asc
`)
checkError(err)
_, err = conn.Prepare("addTask", `
insert into tasks(code, description) values( $1, $2 )
`)
checkError(err)
_, err = conn.Prepare("updateTask", `
update tasks
set code = $2, description=$3
where id=$1
`)
checkError(err)
_, err = conn.Prepare("deleteTask", `
delete from tasks where id=$1
`)
checkError(err)
/**
_, err = conn.Prepare("transfer", `select * from transfer('Bob','Mary',14.00)`)
if err != nil {
return
}
*/
// There technically is a small race condition in doing an upsert with a CTE
// where one of two simultaneous requests to the shortened URL would fail
// with a unique index violation. As the point of this demo is pgx usage and
// not how to perfectly upsert in PostgreSQL it is deemed acceptable.
_, err = conn.Prepare("putTask", `
with upsert as (
update tasks
set code = $2, description=$3
where id=$1
returning *
)
insert into tasks(id, code, description)
select $1, $2, $3 where not exists(select 1 from upsert)
`)
checkError(err)
return
}
