Example #1
1
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)
}
Example #2
1
// 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
}
Example #3
1
func insertRow(c *C, conn *pgx.Conn, n int) {
	_, err := conn.Exec("INSERT INTO test (id) VALUES ($1)", n)
	c.Assert(err, IsNil)
}
Example #4
0
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
}
Example #5
0
// 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")
	}
}
Example #6
0
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
}
Example #7
0
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
}
Example #8
0
// 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)
}
Example #9
0
func PrepareStatements(conn *pgx.Conn) error {
	for name, sql := range preparedStatements {
		if _, err := conn.Prepare(name, sql); err != nil {
			return err
		}
	}
	return nil
}
Example #10
0
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)
	}
}
Example #11
0
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)
	}
}
Example #12
0
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
}
Example #13
0
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
}
Example #14
0
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)
		}
	}
}
Example #15
0
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)
	}
}
Example #16
0
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)
	}
}
Example #17
0
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)
}
Example #18
0
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)
}
Example #19
0
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
	}
}
Example #20
0
// 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
}
Example #21
0
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
	}
}
Example #22
0
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)
	}
}
Example #23
0
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
}
Example #24
0
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)
			}
		}()
	}
}
Example #25
0
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
}
Example #26
0
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)
	}
}
Example #27
0
func exec(conn *pgx.Conn, stmt string, arguments ...interface{}) {
	var err error
	// fmt.Println(stmt)
	_, err = conn.Exec(stmt, arguments...)
	checkErr(err)
}
Example #28
0
func closeConn(t testing.TB, conn *pgx.Conn) {
	err := conn.Close()
	if err != nil {
		t.Fatalf("conn.Close unexpectedly failed: %v", err)
	}
}
Example #29
0
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
}