func TestPGWireDBName(t *testing.T) { defer leaktest.AfterTest(t)() s, _, _ := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() pgURL, cleanupFn := sqlutils.PGUrl(t, s.ServingAddr(), security.RootUser, "TestPGWireDBName") pgURL.Path = "foo" defer cleanupFn() { db, err := gosql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } defer db.Close() if _, err := db.Exec(`CREATE DATABASE foo`); err != nil { t.Fatal(err) } if _, err := db.Exec(`CREATE TABLE bar (i INT PRIMARY KEY)`); err != nil { t.Fatal(err) } } db, err := gosql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } defer db.Close() if _, err := db.Exec(`INSERT INTO bar VALUES ($1)`, 1); err != nil { t.Fatal(err) } }
func TestPGWireMetrics(t *testing.T) { defer leaktest.AfterTest(t) s := server.StartTestServer(t) defer s.Stop() // Setup pgwire client. pgUrl, cleanupFn := sqlutils.PGUrl(t, s, security.RootUser, "TestPGWireMetrics") defer cleanupFn() const minbytes = 20 // Make sure we're starting at 0. if _, _, err := checkPGWireMetrics(s, 0, 0, 0, 0); err != nil { t.Fatal(err) } // A single query should give us some I/O. if err := trivialQuery(pgUrl); err != nil { t.Fatal(err) } bytesIn, bytesOut, err := checkPGWireMetrics(s, minbytes, minbytes, 300, 300) if err != nil { t.Fatal(err) } if err := trivialQuery(pgUrl); err != nil { t.Fatal(err) } // A second query should give us more I/O. _, _, err = checkPGWireMetrics(s, bytesIn+minbytes, bytesOut+minbytes, 300, 300) if err != nil { t.Fatal(err) } }
func TestDropAndCreateTable(t *testing.T) { defer leaktest.AfterTest(t)() s := server.StartTestServer(t) defer s.Stop() pgURL, cleanupFn := sqlutils.PGUrl(t, s.ServingAddr(), security.RootUser, "TestDropAndCreateTable") pgURL.Path = "test" defer cleanupFn() db, err := gosql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } defer db.Close() if _, err := db.Exec(`CREATE DATABASE test`); err != nil { t.Fatal(err) } for i := 0; i < 20; i++ { if _, err := db.Exec(`DROP TABLE IF EXISTS foo`); err != nil { t.Fatal(err) } if _, err := db.Exec(`CREATE TABLE foo (k INT PRIMARY KEY)`); err != nil { t.Fatal(err) } if _, err := db.Exec(`INSERT INTO foo VALUES (1), (2), (3)`); err != nil { t.Fatal(err) } } }
// setUser sets the DB client to the specified user. // It returns a cleanup function to be run when the credentials // are no longer needed. func (t *logicTest) setUser(user string) func() { if t.db != nil { var dbName string if err := t.db.QueryRow("SHOW DATABASE").Scan(&dbName); err != nil { t.Fatal(err) } defer func() { // Propagate the DATABASE setting to the newly-live connection. if _, err := t.db.Exec(fmt.Sprintf("SET DATABASE = %s", dbName)); err != nil { t.Fatal(err) } }() } if t.clients == nil { t.clients = map[string]*sql.DB{} } if db, ok := t.clients[user]; ok { t.db = db // No cleanup necessary, but return a no-op func to avoid nil pointer dereference. return func() {} } pgUrl, cleanupFunc := sqlutils.PGUrl(t.T, &t.srv.TestServer, user, "TestLogic") db, err := sql.Open("postgres", pgUrl.String()) if err != nil { t.Fatal(err) } t.clients[user] = db t.db = db return cleanupFunc }
// setUser sets the DB client to the specified user. func (t *logicTest) setUser(tempDir, user string) { if t.db != nil { var dbName string if err := t.db.QueryRow("SHOW DATABASE").Scan(&dbName); err != nil { t.Fatal(err) } defer func() { // Propagate the DATABASE setting to the newly-live connection. if _, err := t.db.Exec(fmt.Sprintf("SET DATABASE = %s", dbName)); err != nil { t.Fatal(err) } }() } if t.clients == nil { t.clients = map[string]*sql.DB{} } if db, ok := t.clients[user]; ok { t.db = db return } // The entire tempDir will be deleted later, so the cleanup function can be // ignored. pgUrl, _ := sqlutils.PGUrl(t.T, t.srv, user, tempDir, "TestLogic") db, err := sql.Open("postgres", pgUrl.String()) if err != nil { t.Fatal(err) } t.clients[user] = db t.db = db }
func TestDropAndCreateTable(t *testing.T) { defer leaktest.AfterTest(t)() t.Skip(`TODO(andrei, dt): Fails with 'table "foo" does not exist'`) s := server.StartTestServer(t) defer s.Stop() pgURL, cleanupFn := sqlutils.PGUrl(t, s.ServingAddr(), security.RootUser, "TestDropAndCreateTable") pgURL.Path = "test" defer cleanupFn() db, err := gosql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } defer db.Close() if _, err := db.Exec(`CREATE DATABASE test`); err != nil { t.Fatal(err) } for i := 0; i < 20; i++ { if _, err := db.Exec(`DROP TABLE IF EXISTS foo`); err != nil { t.Fatal(err) } //NB: a `time.Sleep(time.Second)` here makes this pass. if _, err := db.Exec(`CREATE TABLE foo (k INT PRIMARY KEY)`); err != nil { t.Fatal(err) } if _, err := db.Exec(`INSERT INTO foo VALUES (1), (2), (3)`); err != nil { t.Fatal(err) } } }
// Test that abruptly closing a pgwire connection releases all leases held by // that session. func TestPGWireConnectionCloseReleasesLeases(t *testing.T) { defer leaktest.AfterTest(t)() s, _, kvDB := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() url, cleanupConn := sqlutils.PGUrl(t, s.ServingAddr(), security.RootUser, "SetupServer") defer cleanupConn() conn, err := pq.Open(url.String()) if err != nil { t.Fatal(err) } ex := conn.(driver.Execer) if _, err := ex.Exec("CREATE DATABASE test", nil); err != nil { t.Fatal(err) } if _, err := ex.Exec("CREATE TABLE test.t (i INT PRIMARY KEY)", nil); err != nil { t.Fatal(err) } // Start a txn so leases are accumulated by queries. if _, err := ex.Exec("BEGIN", nil); err != nil { t.Fatal(err) } // Get a table lease. if _, err := ex.Exec("SELECT * FROM test.t", nil); err != nil { t.Fatal(err) } // Abruptly close the connection. if err := conn.Close(); err != nil { t.Fatal(err) } // Verify that there are no leases held. tableDesc := sqlbase.GetTableDescriptor(kvDB, "test", "t") lm := s.LeaseManager().(*LeaseManager) // Looking for a table state validates that there used to be a lease on the // table. ts := lm.findTableState(tableDesc.ID, false /* create */) if ts == nil { t.Fatal("table state not found") } ts.mu.Lock() leases := ts.active.data ts.mu.Unlock() if len(leases) != 1 { t.Fatalf("expected one lease, found: %d", len(leases)) } // Wait for the lease to be released. util.SucceedsSoon(t, func() error { ts.mu.Lock() refcount := ts.active.data[0].refcount ts.mu.Unlock() if refcount != 0 { return errors.Errorf( "expected lease to be unused, found refcount: %d", refcount) } return nil }) }
func BenchmarkPgbenchExec_Cockroach(b *testing.B) { defer tracing.Disable()() s := server.StartInsecureTestServer(b) defer s.Stop() pgUrl, cleanupFn := sqlutils.PGUrl(b, s, security.RootUser, "benchmarkCockroach") pgUrl.RawQuery = "sslmode=disable" defer cleanupFn() execPgbench(b, pgUrl) }
func BenchmarkPgbenchExec_Cockroach(b *testing.B) { defer tracing.Disable()() s, _, _ := serverutils.StartServer(b, base.TestServerArgs{Insecure: true}) defer s.Stopper().Stop() pgUrl, cleanupFn := sqlutils.PGUrl(b, s.ServingAddr(), security.RootUser, "benchmarkCockroach") pgUrl.RawQuery = "sslmode=disable" defer cleanupFn() execPgbench(b, pgUrl) }
func setupWithContext(t *testing.T, ctx *server.Context) (*testServer, *sql.DB, *client.DB) { s := setupTestServerWithContext(t, ctx) // SQL requests use security.RootUser which has ALL permissions on everything. url, cleanupFn := sqlutils.PGUrl(t, &s.TestServer, security.RootUser, "setupWithContext") sqlDB, err := sql.Open("postgres", url.String()) if err != nil { t.Fatal(err) } s.cleanupFns = append(s.cleanupFns, cleanupFn) return s, sqlDB, s.DB() }
func TestDumpBytes(t *testing.T) { defer leaktest.AfterTest(t)() s, _, _ := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() url, cleanup := sqlutils.PGUrl(t, s.ServingAddr(), security.RootUser, "TestDumpBytes") defer cleanup() conn := makeSQLConn(url.String()) defer conn.Close() if err := conn.Exec(` CREATE DATABASE d; SET DATABASE = d; CREATE TABLE t (b BYTES PRIMARY KEY); `, nil); err != nil { t.Fatal(err) } for i := int64(0); i < 256; i++ { if err := conn.Exec("INSERT INTO t VALUES ($1)", []driver.Value{[]byte{byte(i)}}); err != nil { t.Fatal(err) } } var b bytes.Buffer if err := dumpTable(&b, conn, "d", "t"); err != nil { t.Fatal(err) } dump := b.String() b.Reset() if err := conn.Exec(` CREATE DATABASE o; SET DATABASE = o; `, nil); err != nil { t.Fatal(err) } if err := conn.Exec(dump, nil); err != nil { t.Fatal(err) } if err := dumpTable(&b, conn, "o", "t"); err != nil { t.Fatal(err) } dump2 := b.String() if dump != dump2 { t.Fatalf("unmatching dumps:\n%s\n%s", dump, dump2) } }
// setUser sets the DB client to the specified user. // It returns a cleanup function to be run when the credentials // are no longer needed. func (t *logicTest) setUser(user string) func() { var outDBName string if t.db != nil { var inDBName string if err := t.db.QueryRow("SHOW DATABASE").Scan(&inDBName); err != nil { t.Fatal(err) } defer func() { if inDBName != outDBName { // Propagate the DATABASE setting to the newly-live connection. if _, err := t.db.Exec(fmt.Sprintf("SET DATABASE = %s", inDBName)); err != nil { t.Fatal(err) } } }() } if t.clients == nil { t.clients = map[string]*gosql.DB{} } if db, ok := t.clients[user]; ok { t.db = db t.user = user if err := t.db.QueryRow("SHOW DATABASE").Scan(&outDBName); err != nil { t.Fatal(err) } // No cleanup necessary, but return a no-op func to avoid nil pointer dereference. return func() {} } pgURL, cleanupFunc := sqlutils.PGUrl(t.T, t.srv.ServingAddr(), user, "TestLogic") db, err := gosql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } t.clients[user] = db t.db = db t.user = user if t.verbose { fmt.Printf("--- new user: %s\n", user) } return cleanupFunc }
// Names should be qualified automatically during Prepare when a database name // was given in the connection string. func TestPGPrepareNameQual(t *testing.T) { defer leaktest.AfterTest(t)() s, _, _ := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() pgURL, cleanupFn := sqlutils.PGUrl(t, s.ServingAddr(), security.RootUser, "TestPGPrepareNameQual") defer cleanupFn() db, err := gosql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } defer db.Close() if _, err := db.Exec(`CREATE DATABASE IF NOT EXISTS testing`); err != nil { t.Fatal(err) } pgURL.Path = "/testing" db2, err := gosql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } defer db2.Close() statements := []string{ `CREATE TABLE IF NOT EXISTS f (v INT)`, `INSERT INTO f VALUES (42)`, `SELECT * FROM f`, `DELETE FROM f WHERE v = 42`, `DROP TABLE IF EXISTS f`, } for _, stmtString := range statements { if _, err = db2.Exec(stmtString); err != nil { t.Fatal(err) } stmt, err := db2.Prepare(stmtString) if err != nil { t.Fatal(err) } if _, err = stmt.Exec(); err != nil { t.Fatal(err) } } }
func TestPGPrepareFail(t *testing.T) { defer leaktest.AfterTest(t)() s, _, _ := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() pgURL, cleanupFn := sqlutils.PGUrl(t, s.ServingAddr(), security.RootUser, "TestPGPrepareFail") defer cleanupFn() db, err := gosql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } defer db.Close() testFailures := map[string]string{ "SELECT $1 = $1": "pq: could not determine data type of placeholder $1", "SELECT $1": "pq: could not determine data type of placeholder $1", "SELECT $1 + $1": "pq: could not determine data type of placeholder $1", "SELECT CASE WHEN TRUE THEN $1 END": "pq: could not determine data type of placeholder $1", "SELECT CASE WHEN TRUE THEN $1 ELSE $2 END": "pq: could not determine data type of placeholder $1", "SELECT $1 > 0 AND NOT $1": "pq: incompatible NOT argument type: int", "CREATE TABLE $1 (id INT)": "pq: syntax error at or near \"1\"\nCREATE TABLE $1 (id INT)\n ^\n", "UPDATE d.t SET s = i + $1": "pq: unsupported binary operator: <int> + <placeholder> (desired <string>)", "SELECT $0 > 0": "pq: invalid placeholder name: $0", "SELECT $2 > 0": "pq: could not determine data type of placeholder $1", "SELECT 3 + CASE (4) WHEN 4 THEN $1 END": "pq: could not determine data type of placeholder $1", "SELECT ($1 + $1) + CURRENT_DATE()": "pq: could not determine data type of placeholder $1", "SELECT $1 + $2, $2::FLOAT": "pq: could not determine data type of placeholder $1", "SELECT ($1 + 2) + ($1 + 2.5)": "pq: unsupported binary operator: <int> + <decimal>", } if _, err := db.Exec(`CREATE DATABASE d; CREATE TABLE d.t (i INT, s STRING, d INT)`); err != nil { t.Fatal(err) } for query, reason := range testFailures { if stmt, err := db.Prepare(query); err == nil { t.Errorf("expected error: %s", query) if err := stmt.Close(); err != nil { t.Fatal(err) } } else if err.Error() != reason { t.Errorf(`%s: got: %q, expected: %q`, query, err, reason) } } }
func TestPGPrepareFail(t *testing.T) { defer leaktest.AfterTest(t)() s := server.StartTestServer(t) defer s.Stop() pgURL, cleanupFn := sqlutils.PGUrl(t, s, security.RootUser, "TestPGPrepareFail") defer cleanupFn() db, err := sql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } defer db.Close() testFailures := map[string]string{ "SELECT $1 = $1": "pq: unsupported comparison operator: <parameter> = <parameter>", "SELECT $1 > 0 AND NOT $1": "pq: incompatible NOT argument type: int", "SELECT $1": "pq: could not determine data type of parameter $1", "SELECT $1 + $1": "pq: unsupported binary operator: <parameter> + <parameter>", "SELECT now() + $1": "pq: unsupported binary operator: <timestamp> + <parameter>", "SELECT CASE $1 WHEN 1 THEN 1 END": "pq: could not determine data type of parameter $1", "SELECT CASE WHEN TRUE THEN $1 END": "pq: could not determine data type of parameter $1", "SELECT CASE WHEN TRUE THEN $1 ELSE $2 END": "pq: could not determine data type of parameter $2", "SELECT CASE WHEN TRUE THEN 1 ELSE $1 END": "pq: could not determine data type of parameter $1", "UPDATE d.t SET d = CASE WHEN TRUE THEN $1 END": "pq: could not determine data type of parameter $1", "CREATE TABLE $1 (id INT)": "pq: syntax error at or near \"1\"\nCREATE TABLE $1 (id INT)\n ^\n", "UPDATE d.t SET s = i + $1": "pq: value type int doesn't match type STRING of column \"s\"", "SELECT $0 > 0": "pq: there is no parameter $0", "SELECT $2 > 0": "pq: could not determine data type of parameter $1", } if _, err := db.Exec(`CREATE DATABASE d; CREATE TABLE d.t (i INT, s STRING, d INT)`); err != nil { t.Fatal(err) } for query, reason := range testFailures { if stmt, err := db.Prepare(query); err == nil { t.Errorf("expected error: %s", query) if err := stmt.Close(); err != nil { t.Fatal(err) } } else if err.Error() != reason { t.Errorf("%s: unexpected error: %s", query, err) } } }
// Starts up a cluster made of up `nodes` in-memory testing servers, // creates database `name and returns open gosql.DB connections to each // node (to the named db), as well as a cleanup func that stops and // cleans up all nodes and connections. // TODO(davidt): Change zone config to actually add replication. // TODO(davidt): Ensure that ranges are actually replicated before returning. // Until these TODOs are resolved, the cluster returned is not particularly // useful for benchmarking, as without replication overhead it is the same as // single-node operation, except without the local-call optimization for the // additional nodes. func SetupMultinodeTestCluster(t testing.TB, nodes int, name string) ([]*gosql.DB, func()) { if nodes < 1 { t.Fatal("invalid cluster size: ", nodes) } var servers []server.TestServer first := server.StartTestServer(t) servers = append(servers, first) for i := 1; i < nodes; i++ { servers = append(servers, server.StartTestServerJoining(t, first)) } var conns []*gosql.DB var closes []func() error var cleanups []func() for i, s := range servers { pgURL, cleanupFn := sqlutils.PGUrl(t, s.ServingAddr(), security.RootUser, fmt.Sprintf("node%d", i)) pgURL.Path = name db, err := gosql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } closes = append(closes, db.Close) cleanups = append(cleanups, cleanupFn) conns = append(conns, db) } if _, err := conns[0].Exec(fmt.Sprintf(`CREATE DATABASE %s`, name)); err != nil { t.Fatal(err) } f := func() { for _, fn := range closes { _ = fn() } for _, s := range servers { s.Stop() } for _, fn := range cleanups { fn() } } return conns, f }
func (t *parallelTest) addClient(createDB bool) { pgURL, cleanupFunc := sqlutils.PGUrl(t.T, &t.srv.TestServer, security.RootUser, "TestParallel") db, err := sql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } if createDB { if _, err := db.Exec("CREATE DATABASE test;"); err != nil { t.Fatal(err) } } if _, err := db.Exec("SET DATABASE = test;"); err != nil { t.Fatal(err) } t.clients = append(t.clients, testDB{db: db, cleanup: cleanupFunc}) }
func benchmarkCockroach(b *testing.B, f func(b *testing.B, db *sql.DB)) { s := server.StartTestServer(b) defer s.Stop() pgUrl, cleanupFn := sqlutils.PGUrl(b, s, security.RootUser, os.TempDir(), "benchmarkCockroach") defer cleanupFn() db, err := sql.Open("postgres", pgUrl.String()) if err != nil { b.Fatal(err) } defer db.Close() if _, err := db.Exec(`CREATE DATABASE IF NOT EXISTS bench`); err != nil { b.Fatal(err) } f(b, db) }
func TestPGPrepareFail(t *testing.T) { defer leaktest.AfterTest(t) s := server.StartTestServer(t) defer s.Stop() pgUrl, cleanupFn := sqlutils.PGUrl(t, s, security.RootUser, os.TempDir(), "TestPGPrepareFail") defer cleanupFn() db, err := sql.Open("postgres", pgUrl.String()) if err != nil { t.Fatal(err) } defer db.Close() testFailures := map[string]string{ "SELECT $1 = $1": "pq: unsupported comparison operator: <valarg> = <valarg>", "SELECT $1 > 0 AND NOT $1": "pq: incompatible NOT argument type: int", "SELECT $1": "pq: unsupported result type: valarg", "SELECT $1 + $1": "pq: unsupported binary operator: <valarg> + <valarg>", "SELECT now() + $1": "pq: unsupported binary operator: <timestamp> + <valarg>", "CREATE TABLE $1 (id INT)": "pq: syntax error at or near \"1\"\nCREATE TABLE $1 (id INT)\n ^\n", "DROP TABLE t": "pq: prepare statement not supported: DROP TABLE", "UPDATE d.t SET s = i + $1": "pq: value type int doesn't match type STRING of column \"s\"", } if _, err := db.Exec(`CREATE DATABASE d; CREATE TABLE d.t (i INT, s STRING, d INT)`); err != nil { t.Fatal(err) } for query, reason := range testFailures { if stmt, err := db.Prepare(query); err == nil { t.Errorf("expected error: %s", query) if err := stmt.Close(); err != nil { t.Fatal(err) } } else if err.Error() != reason { t.Errorf("%s: unexpected error: %s", query, err) } } }
func benchmarkCockroach(b *testing.B, f func(b *testing.B, db *gosql.DB)) { defer tracing.Disable()() s := server.StartTestServer(b) defer s.Stop() pgURL, cleanupFn := sqlutils.PGUrl(b, s.ServingAddr(), security.RootUser, "benchmarkCockroach") pgURL.Path = "bench" defer cleanupFn() db, err := gosql.Open("postgres", pgURL.String()) if err != nil { b.Fatal(err) } defer db.Close() if _, err := db.Exec(`CREATE DATABASE IF NOT EXISTS bench`); err != nil { b.Fatal(err) } f(b, db) }
func (t *parallelTest) getClient(nodeIdx, clientIdx int) *gosql.DB { for len(t.clients[nodeIdx]) <= clientIdx { // Add a client. pgURL, cleanupFunc := sqlutils.PGUrl(t.T, t.cluster.Server(nodeIdx).ServingAddr(), security.RootUser, "TestParallel") db, err := gosql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } sqlutils.MakeSQLRunner(t, db).Exec("SET DATABASE = test") t.cluster.Stopper().AddCloser( stop.CloserFn(func() { _ = db.Close() cleanupFunc() })) t.clients[nodeIdx] = append(t.clients[nodeIdx], db) } return t.clients[nodeIdx][clientIdx] }
// A DDL should return "CommandComplete", not "EmptyQuery" Response. func TestCmdCompleteVsEmptyStatements(t *testing.T) { defer leaktest.AfterTest(t) s := server.StartTestServer(t) defer s.Stop() pgUrl, cleanupFn := sqlutils.PGUrl(t, s, security.RootUser, "TestCmdCompleteVsEmptyStatements") defer cleanupFn() db, err := sql.Open("postgres", pgUrl.String()) if err != nil { t.Fatal(err) } defer db.Close() // lib/pq handles the empty query response by returning a nil driver.Result. // Unfortunately sql.Exec wraps that, nil or not, in a sql.Result which doesn't // expose the underlying driver.Result. // sql.Result does however have methods which attempt to dereference the underlying // driver.Result and can thus be used to determine if it is nil. // TODO(dt): This would be prettier and generate better failures with testify/assert's helpers. // Result of a DDL (command complete) yields a non-nil underlying driver result. nonempty, err := db.Exec(`CREATE DATABASE IF NOT EXISTS testing`) if err != nil { t.Fatal(err) } _, _ = nonempty.RowsAffected() // should not panic if lib/pq returned a non-nil result. empty, err := db.Exec(" ") if err != nil { t.Fatal(err) } defer func() { _ = recover() }() _, _ = empty.RowsAffected() // should panic if lib/pq returned a nil result as expected. t.Fatal("should not get here -- empty result from empty query should panic first") // TODO(dt): clean this up with testify/assert and add tests for less trivial empty queries. }
func TestPrepareSyntax(t *testing.T) { defer leaktest.AfterTest(t) s := server.StartTestServer(t) defer s.Stop() pgUrl, cleanupFn := sqlutils.PGUrl(t, s, security.RootUser, "TestPrepareSyntax") defer cleanupFn() db, err := sql.Open("postgres", pgUrl.String()) if err != nil { t.Fatal(err) } defer db.Close() const strTest = `SELECT """test"""` if _, err := db.Exec(`SET SYNTAX = traditional`); err != nil { t.Fatal(err) } if _, err := db.Prepare(strTest); err == nil { t.Fatal("expected error") } if _, err := db.Exec(`SET SYNTAX = modern`); err != nil { t.Fatal(err) } stmt, err := db.Prepare(strTest) if err != nil { t.Fatalf("unexpected error: %s", err) } var v string if err := stmt.QueryRow().Scan(&v); err != nil { t.Fatalf("unexpected error: %s", err) } else if v != "test" { t.Fatalf("unexpected result: %q", v) } }
// StartServer creates a test server and sets up a gosql DB connection. // The server should be stopped by calling server.Stopper().Stop(). func StartServer(t testing.TB, params base.TestServerArgs) ( TestServerInterface, *gosql.DB, *client.DB, ) { server, err := StartServerRaw(params) if err != nil { t.Fatal(err) } kvClient := server.KVClient().(*client.DB) pgURL, cleanupGoDB := sqlutils.PGUrl( t, server.ServingAddr(), security.RootUser, "StartServer") pgURL.Path = params.UseDatabase goDB, err := gosql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } server.Stopper().AddCloser( stop.CloserFn(func() { _ = goDB.Close() cleanupGoDB() })) return server, goDB, kvClient }
func newKVSQL(b *testing.B) kvInterface { enableTracing := tracing.Disable() s := server.StartTestServer(b) pgURL, cleanupURL := sqlutils.PGUrl(b, s, security.RootUser, "benchmarkCockroach") pgURL.Path = "bench" db, err := sql.Open("postgres", pgURL.String()) if err != nil { b.Fatal(err) } if _, err := db.Exec(`CREATE DATABASE IF NOT EXISTS bench`); err != nil { b.Fatal(err) } kv := &kvSQL{} kv.db = db kv.doneFn = func() { db.Close() cleanupURL() s.Stop() enableTracing() } return kv }
// Unfortunately lib/pq doesn't expose returned command tags directly, but we can test // the methods where it depends on their values (Begin, Commit, RowsAffected for INSERTs). func TestPGCommandTags(t *testing.T) { defer leaktest.AfterTest(t) s := server.StartTestServer(t) defer s.Stop() pgUrl, cleanupFn := sqlutils.PGUrl(t, s, security.RootUser, "TestPGCommandTags") defer cleanupFn() db, err := sql.Open("postgres", pgUrl.String()) if err != nil { t.Fatal(err) } defer db.Close() if _, err := db.Exec(`CREATE DATABASE IF NOT EXISTS testing`); err != nil { t.Fatal(err) } if _, err := db.Exec(`CREATE TABLE testing.tags (k INT PRIMARY KEY, v INT)`); err != nil { t.Fatal(err) } // Begin will error if the returned tag is not BEGIN. tx, err := db.Begin() if err != nil { t.Fatal(err) } // Commit also checks the correct tag is returned. if err := tx.Commit(); err != nil { t.Fatal(err) } // lib/pq has a special-case for INSERT (due to oids), so test insert and update statements. res, err := db.Exec("INSERT INTO testing.tags VALUES (1, 1), (2, 2)") if err != nil { t.Fatal(err) } affected, err := res.RowsAffected() if err != nil { t.Fatal(err) } if affected != 2 { t.Fatal("unexpected number of rows affected:", affected) } res, err = db.Exec("INSERT INTO testing.tags VALUES (3, 3)") if err != nil { t.Fatal(err) } affected, err = res.RowsAffected() if err != nil { t.Fatal(err) } if affected != 1 { t.Fatal("unexpected number of rows affected:", affected) } res, err = db.Exec("UPDATE testing.tags SET v = 3") if err != nil { t.Fatal(err) } affected, err = res.RowsAffected() if err != nil { t.Fatal(err) } if affected != 3 { t.Fatal("unexpected number of rows affected:", affected) } }
func TestPGPreparedExec(t *testing.T) { defer leaktest.AfterTest(t) var base preparedExecTest execTests := []struct { query string tests []preparedExecTest }{ { "INSERT INTO d.t VALUES ($1, $2, $3)", []preparedExecTest{ base.Params(1, "one", 2).RowsAffected(1), base.Params("two", 2, 2).Error(`pq: param $1: strconv.ParseInt: parsing "two": invalid syntax`), }, }, { "UPDATE d.t SET s = $1, i = i + $2, d = 1 + $3 WHERE i = $4", []preparedExecTest{ base.Params(4, 3, 2, 1).RowsAffected(1), }, }, { "DELETE FROM d.t WHERE s = $1 and i = $2 and d = 2 + $3", []preparedExecTest{ base.Params(1, 2, 3).RowsAffected(0), }, }, { "INSERT INTO d.t VALUES ($1), ($2)", []preparedExecTest{ base.Params(1, 2).RowsAffected(2), }, }, { "UPDATE d.t SET i = CASE WHEN $1 THEN i-$3 WHEN $2 THEN i+$3 END", []preparedExecTest{ base.Params(true, true, 3).RowsAffected(3), }, }, { "UPDATE d.t SET i = CASE i WHEN $1 THEN i-$3 WHEN $2 THEN i+$3 END", []preparedExecTest{ base.Params(1, 2, 3).RowsAffected(3), }, }, } s := server.StartTestServer(t) defer s.Stop() pgUrl, cleanupFn := sqlutils.PGUrl(t, s, security.RootUser, "TestPGPreparedExec") defer cleanupFn() db, err := sql.Open("postgres", pgUrl.String()) if err != nil { t.Fatal(err) } defer db.Close() if _, err := db.Exec(`CREATE DATABASE d; CREATE TABLE d.t (i INT, s STRING, d INT)`); err != nil { t.Fatal(err) } runTests := func(query string, tests []preparedExecTest, execFunc func(...interface{}) (sql.Result, error)) { for _, test := range tests { if result, err := execFunc(test.params...); err != nil { if test.error == "" { t.Errorf("%s: %v: unexpected error: %s", query, test.params, err) } else if err.Error() != test.error { t.Errorf("%s: %v: expected error: %s, got %s", query, test.params, test.error, err) } } else { if rowsAffected, err := result.RowsAffected(); err != nil { t.Errorf("%s: %v: unexpected error: %s", query, test.params, err) } else if rowsAffected != test.rowsAffected { t.Errorf("%s: %v: expected %v, got %v", query, test.params, test.rowsAffected, rowsAffected) } } } } if _, err := db.Exec(`TRUNCATE TABLE d.t`); err != nil { t.Fatal(err) } for _, execTest := range execTests { runTests(execTest.query, execTest.tests, func(args ...interface{}) (sql.Result, error) { return db.Exec(execTest.query, args...) }) } if _, err := db.Exec(`TRUNCATE TABLE d.t`); err != nil { t.Fatal(err) } for _, execTest := range execTests { if stmt, err := db.Prepare(execTest.query); err != nil { t.Errorf("%s: prepare error: %s", execTest.query, err) } else { func() { defer stmt.Close() runTests(execTest.query, execTest.tests, stmt.Exec) }() } } }
func TestPGPreparedQuery(t *testing.T) { defer leaktest.AfterTest(t) var base preparedQueryTest queryTests := map[string][]preparedQueryTest{ "SELECT $1 > 0": { base.Params(1).Results(true), base.Params("1").Results(true), base.Params(1.1).Error(`pq: param $1: strconv.ParseInt: parsing "1.1": invalid syntax`).Results(true), base.Params("1.0").Error(`pq: param $1: strconv.ParseInt: parsing "1.0": invalid syntax`), base.Params(true).Error(`pq: param $1: strconv.ParseInt: parsing "true": invalid syntax`), }, "SELECT TRUE AND $1": { base.Params(true).Results(true), base.Params(false).Results(false), base.Params(1).Results(true), base.Params("").Error(`pq: param $1: strconv.ParseBool: parsing "": invalid syntax`), // Make sure we can run another after a failure. base.Params(true).Results(true), }, "SELECT $1::bool": { base.Params(true).Results(true), base.Params("true").Results(true), base.Params("false").Results(false), base.Params("1").Results(true), base.Params(2).Error(`pq: strconv.ParseBool: parsing "2": invalid syntax`), base.Params(3.1).Error(`pq: strconv.ParseBool: parsing "3.1": invalid syntax`), base.Params("").Error(`pq: strconv.ParseBool: parsing "": invalid syntax`), }, "SELECT $1::int > $2::float": { base.Params("2", 1).Results(true), base.Params(1, "2").Results(false), base.Params("2", "1.0").Results(true), base.Params("2.0", "1").Error(`pq: strconv.ParseInt: parsing "2.0": invalid syntax`), base.Params(2.1, 1).Error(`pq: strconv.ParseInt: parsing "2.1": invalid syntax`), }, "SELECT GREATEST($1, 0, $2), $2": { base.Params(1, -1).Results(1, -1), base.Params(-1, 10).Results(10, 10), base.Params("-2", "-1").Results(0, -1), base.Params(1, 2.1).Error(`pq: param $2: strconv.ParseInt: parsing "2.1": invalid syntax`), }, "SELECT $1::int, $1::float": { base.Params("1").Results(1, 1.0), }, "SELECT 3 + $1, $1 + $2": { base.Params("1", "2").Results(4, 3), base.Params(3, "4").Results(6, 7), base.Params(0, "a").Error(`pq: param $2: strconv.ParseInt: parsing "a": invalid syntax`), }, // Check for QualifiedName resolution. "SELECT COUNT(*)": { base.Results(1), }, "SELECT CASE WHEN $1 THEN 1-$3 WHEN $2 THEN 1+$3 END": { base.Params(true, false, 2).Results(-1), base.Params(false, true, 3).Results(4), base.Params(false, false, 2).Results(sql.NullBool{}), }, "SELECT CASE 1 WHEN $1 THEN $2 ELSE 2 END": { base.Params(1, 3).Results(3), base.Params(2, 3).Results(2), base.Params(true, 0).Error(`pq: param $1: strconv.ParseInt: parsing "true": invalid syntax`), }, "SHOW database": { base.Results(""), }, "SELECT descriptor FROM system.descriptor WHERE descriptor != $1": { base.Params([]byte("abc")).Results([]byte("\x12\x16\n\x06system\x10\x01\x1a\n\n\b\n\x04root\x100")), }, "SHOW COLUMNS FROM system.users": { base.Results("username", "STRING", false, sql.NullBool{}), }, "SHOW DATABASES": { base.Results("system"), }, "SHOW GRANTS ON system.users": { base.Results("users", "root", "DELETE,GRANT,INSERT,SELECT,UPDATE"), }, "SHOW INDEX FROM system.users": { base.Results("users", "primary", true, 1, "username", "ASC", false), }, "SHOW TABLES FROM system": { base.Results("descriptor"), }, "SHOW TIME ZONE": { base.Results("UTC"), }, "SELECT (SELECT 1+$1)": { base.Params(1).Results(2), }, // TODO(mjibson): test date/time types } s := server.StartTestServer(t) defer s.Stop() pgUrl, cleanupFn := sqlutils.PGUrl(t, s, security.RootUser, "TestPGPreparedQuery") defer cleanupFn() db, err := sql.Open("postgres", pgUrl.String()) if err != nil { t.Fatal(err) } defer db.Close() runTests := func(query string, tests []preparedQueryTest, queryFunc func(...interface{}) (*sql.Rows, error)) { for _, test := range tests { if rows, err := queryFunc(test.params...); err != nil { if test.error == "" { t.Errorf("%s: %v: unexpected error: %s", query, test.params, err) } else if err.Error() != test.error { t.Errorf("%s: %v: expected error: %s, got %s", query, test.params, test.error, err) } } else { defer rows.Close() if test.error != "" { t.Errorf("expected error: %s: %v", query, test.params) } else { if !rows.Next() { t.Errorf("expected row: %s: %v", query, test.params) } else { dst := make([]interface{}, len(test.results)) for i, d := range test.results { dst[i] = reflect.New(reflect.TypeOf(d)).Interface() } if err := rows.Scan(dst...); err != nil { t.Error(err) } for i, d := range dst { dst[i] = reflect.Indirect(reflect.ValueOf(d)).Interface() } if !reflect.DeepEqual(dst, test.results) { t.Errorf("%s: %v: expected %v, got %v", query, test.params, test.results, dst) } } } } } } for query, tests := range queryTests { runTests(query, tests, func(args ...interface{}) (*sql.Rows, error) { return db.Query(query, args...) }) } for query, tests := range queryTests { if stmt, err := db.Prepare(query); err != nil { t.Errorf("%s: prepare error: %s", query, err) } else { func() { defer stmt.Close() runTests(query, tests, stmt.Query) }() } } }
func TestPGPreparedExec(t *testing.T) { defer leaktest.AfterTest(t) var base preparedTest queryTests := []struct { query string tests []preparedTest }{ { "INSERT INTO d.t VALUES ($1, $2, $3)", []preparedTest{ base.Params(1, "one", 2), base.Params("two", 2, 2).Error(`pq: param $1: strconv.ParseInt: parsing "two": invalid syntax`), }, }, { "UPDATE d.t SET s = $1, i = i + $2, d = 1 + $3 WHERE i = $4", []preparedTest{ base.Params(4, 3, 2, 1), }, }, { "DELETE FROM d.t WHERE s = $1 and i = $2 and d = 2 + $3", []preparedTest{ base.Params(1, 2, 3), }, }, } s := server.StartTestServer(t) defer s.Stop() pgUrl, cleanupFn := sqlutils.PGUrl(t, s, security.RootUser, os.TempDir(), "TestPGPrepared") defer cleanupFn() db, err := sql.Open("postgres", pgUrl.String()) if err != nil { t.Fatal(err) } defer db.Close() setup := `CREATE DATABASE d; CREATE TABLE d.t (i INT, s STRING, d INT);` if _, err := db.Exec(setup); err != nil { t.Fatal(err) } for _, queryTest := range queryTests { stmt, err := db.Prepare(queryTest.query) if err != nil { t.Fatalf("prepare error: %s: %s", queryTest.query, err) } for _, test := range queryTest.tests { _, err := stmt.Exec(test.params...) if err != nil { if test.error == "" { t.Fatalf("%s: %#v: unexpected error: %s", queryTest.query, test.params, err) } else if test.error != err.Error() { t.Fatalf("%s: %#v: expected error: %s, got %s", queryTest.query, test.params, test.error, err) } } if test.error != "" && err == nil { t.Fatalf("expected error: %s: %#v", queryTest.query, test.params) } } if err := stmt.Close(); err != nil { t.Fatal(err) } } testFailures := map[string]string{ "CREATE TABLE $1 (id INT)": "pq: syntax error at or near \"1\"\nCREATE TABLE $1 (id INT)\n ^\n", "DROP TABLE t": "pq: prepare statement not supported: *parser.DropTable", "UPDATE d.t SET s = i + $1": "pq: value type int doesn't match type STRING of column \"s\"", } for query, reason := range testFailures { stmt, err := db.Prepare(query) if err == nil { t.Errorf("expected error: %s", query) stmt.Close() continue } if err.Error() != reason { t.Errorf("unexpected error: %s: %q", query, err) } } }
func TestPGPreparedQuery(t *testing.T) { defer leaktest.AfterTest(t) var base preparedTest queryTests := map[string][]preparedTest{ "SELECT $1 > 0": { base.Params(1).Results(true), base.Params("1").Results(true), base.Params(1.1).Error(`pq: param $1: strconv.ParseInt: parsing "1.1": invalid syntax`).Results(true), base.Params("1.0").Error(`pq: param $1: strconv.ParseInt: parsing "1.0": invalid syntax`), base.Params(true).Error(`pq: param $1: strconv.ParseInt: parsing "true": invalid syntax`), }, "SELECT TRUE AND $1": { base.Params(true).Results(true), base.Params(false).Results(false), base.Params(1).Results(true), base.Params("").Error(`pq: param $1: strconv.ParseBool: parsing "": invalid syntax`), // Make sure we can run another after a failure. base.Params(true).Results(true), }, "SELECT $1::bool": { base.Params(true).Results(true), base.Params("true").Results(true), base.Params("false").Results(false), base.Params("1").Results(true), base.Params(2).Error(`pq: strconv.ParseBool: parsing "2": invalid syntax`), base.Params(3.1).Error(`pq: strconv.ParseBool: parsing "3.1": invalid syntax`), base.Params("").Error(`pq: strconv.ParseBool: parsing "": invalid syntax`), }, "SELECT $1::int > $2::float": { base.Params("2", 1).Results(true), base.Params(1, "2").Results(false), base.Params("2", "1.0").Results(true), base.Params("2.0", "1").Error(`pq: strconv.ParseInt: parsing "2.0": invalid syntax`), base.Params(2.1, 1).Error(`pq: strconv.ParseInt: parsing "2.1": invalid syntax`), }, "SELECT GREATEST($1, 0, $2), $2": { base.Params(1, -1).Results(1, -1), base.Params(-1, 10).Results(10, 10), base.Params("-2", "-1").Results(0, -1), base.Params(1, 2.1).Error(`pq: param $2: strconv.ParseInt: parsing "2.1": invalid syntax`), }, "SELECT $1::int, $1::float": { base.Params("1").Results(1, 1.0), }, "SELECT 3 + $1, $1 + $2": { base.Params("1", "2").Results(4, 3), base.Params(3, "4").Results(6, 7), base.Params(0, "a").Error(`pq: param $2: strconv.ParseInt: parsing "a": invalid syntax`), }, // Check for QualifiedName resolution. "SELECT COUNT(*)": { base.Results(1), }, // TODO(mjibson): test date/time types } s := server.StartTestServer(t) defer s.Stop() pgUrl, cleanupFn := sqlutils.PGUrl(t, s, security.RootUser, os.TempDir(), "TestPGPrepared") defer cleanupFn() db, err := sql.Open("postgres", pgUrl.String()) if err != nil { t.Fatal(err) } defer db.Close() for query, tests := range queryTests { stmt, err := db.Prepare(query) if err != nil { t.Errorf("prepare error: %s: %s", query, err) continue } for _, test := range tests { for _, queryFunc := range []func(...interface{}) (*sql.Rows, error){ func(args ...interface{}) (*sql.Rows, error) { return db.Query(query, args...) }, stmt.Query, } { rows, err := queryFunc(test.params...) if err != nil { if test.error == "" { t.Errorf("%s: %#v: unexpected error: %s", query, test.params, err) } else if test.error != err.Error() { t.Errorf("%s: %#v: expected error: %s, got %s", query, test.params, test.error, err) } continue } if test.error != "" && err == nil { t.Errorf("expected error: %s: %#v", query, test.params) continue } dst := make([]interface{}, len(test.results)) for i, d := range test.results { dst[i] = reflect.New(reflect.TypeOf(d)).Interface() } if !rows.Next() { t.Errorf("expected row: %s: %#v", query, test.params) continue } if err := rows.Scan(dst...); err != nil { t.Error(err) continue } rows.Close() for i, d := range dst { v := reflect.Indirect(reflect.ValueOf(d)).Interface() dst[i] = v } if !reflect.DeepEqual(dst, test.results) { t.Errorf("%s: %#v: expected %v, got %v", query, test.params, test.results, dst) continue } } } if err := stmt.Close(); err != nil { t.Error(err) } } testFailures := map[string]string{ "SELECT $1 = $1": "pq: unsupported comparison operator: <valarg> = <valarg>", "SELECT $1 > 0 AND NOT $1": "pq: incompatible NOT argument type: int", "SELECT $1": "pq: unsupported result type: valarg", "SELECT $1 + $1": "pq: unsupported binary operator: <valarg> + <valarg>", "SELECT now() + $1": "pq: unsupported binary operator: <timestamp> + <valarg>", } for query, reason := range testFailures { stmt, err := db.Prepare(query) if err == nil { t.Errorf("expected error: %s", query) stmt.Close() continue } if err.Error() != reason { t.Errorf("unexpected error: %s: %s", query, err) } } }