func PrepareStatements(conn *pgx.Conn) error { for name, sql := range preparedStatements { if _, err := conn.Prepare(name, sql); err != nil { return err } } return nil }
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 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) } } }
// 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 }