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(), "TestPGWireDBName", url.User(security.RootUser)) 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) } }
// 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(), "SetupServer", url.User(security.RootUser)) 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 TestDumpBytes(t *testing.T) { defer leaktest.AfterTest(t)() c, err := newCLITest(t, false) if err != nil { t.Fatal(err) } defer c.stop(true) url, cleanup := sqlutils.PGUrl(t, c.ServingAddr(), "TestDumpBytes", url.User(security.RootUser)) 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) } }
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(), "benchmarkCockroach", url.User(security.RootUser)) pgUrl.RawQuery = "sslmode=disable" defer cleanupFn() execPgbench(b, pgUrl) }
// TestRollbackInRestartWait ensures that a ROLLBACK while the txn is in the // RetryWait state works. func TestRollbackInRestartWait(t *testing.T) { defer leaktest.AfterTest(t)() aborter := NewTxnAborter() defer aborter.Close(t) params, _ := createTestServerParams() params.Knobs.SQLExecutor = aborter.executorKnobs() s, sqlDB, _ := serverutils.StartServer(t, params) defer s.Stopper().Stop() { pgURL, cleanup := sqlutils.PGUrl(t, s.ServingAddr(), "TestRollbackInRestartWait", url.User(security.RootUser)) defer cleanup() if err := aborter.Init(pgURL); err != nil { t.Fatal(err) } } if _, err := sqlDB.Exec(` CREATE DATABASE t; CREATE TABLE t.test (k INT PRIMARY KEY, v TEXT); `); err != nil { t.Fatal(err) } // Set up error injection that causes retries. const insertStmt = "INSERT INTO t.test(k, v) VALUES (0, 'boulanger')" if err := aborter.QueueStmtForAbortion( insertStmt, 1 /* abortCount */, false, /* willBeRetriedIbid */ ); err != nil { t.Fatal(err) } tx, err := sqlDB.Begin() if err != nil { t.Fatal(err) } if _, err := tx.Exec("SAVEPOINT cockroach_restart"); err != nil { t.Fatal(err) } if _, err := tx.Exec(insertStmt); err != nil { t.Fatal(err) } if _, err := tx.Exec("RELEASE SAVEPOINT cockroach_restart"); !testutils.IsError( err, "pq: restart transaction") { t.Fatalf("unexpected error: %s", err) } if err := tx.Rollback(); 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() { 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(), "TestLogic", url.User(user)) 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(), "TestPGPrepareNameQual", url.User(security.RootUser)) 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(), "TestPGPrepareFail", url.User(security.RootUser)) 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{1}> (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]": "pq: could not determine data type of placeholder $1", "SELECT ($1 + 2) + ($1 + 2.5::FLOAT)": "pq: unsupported binary operator: <int> + <float>", } 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) } } }
// Test that aborted txn are only retried once. // Prevents regressions of #8456. func TestAbortedTxnOnlyRetriedOnce(t *testing.T) { defer leaktest.AfterTest(t)() aborter := NewTxnAborter() defer aborter.Close(t) params, _ := createTestServerParams() params.Knobs.SQLExecutor = aborter.executorKnobs() // Disable one phase commits because they cannot be restarted. params.Knobs.Store.(*storage.StoreTestingKnobs).DisableOnePhaseCommits = true s, sqlDB, _ := serverutils.StartServer(t, params) defer s.Stopper().Stop() { pgURL, cleanup := sqlutils.PGUrl(t, s.ServingAddr(), "TestAbortedTxnOnlyRetriedOnce", url.User(security.RootUser)) defer cleanup() if err := aborter.Init(pgURL); err != nil { t.Fatal(err) } } const insertStmt = "INSERT INTO t.test(k, v) VALUES (1, 'boulanger')" if err := aborter.QueueStmtForAbortion( insertStmt, 1 /* abortCount */, true, /* willBeRetriedIbid */ ); err != nil { t.Fatal(err) } if _, err := sqlDB.Exec(` CREATE DATABASE t; CREATE TABLE t.test (k INT PRIMARY KEY, v TEXT); `); err != nil { t.Fatal(err) } if _, err := sqlDB.Exec(insertStmt); err != nil { t.Fatalf("unexpected error: %s", err) } execCount, ok := aborter.GetExecCount(insertStmt) if !ok { t.Fatalf("aborter has no state on %q", insertStmt) } if execCount != 2 { t.Fatalf("expected %q to be executed 2 times, but got %d", insertStmt, execCount) } }
// A DDL should return "CommandComplete", not "EmptyQuery" Response. func TestCmdCompleteVsEmptyStatements(t *testing.T) { defer leaktest.AfterTest(t)() s, _, _ := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() pgURL, cleanupFn := sqlutils.PGUrl( t, s.ServingAddr(), "TestCmdCompleteVsEmptyStatements", url.User(security.RootUser)) defer cleanupFn() db, err := gosql.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 gosql.Exec wraps that, nil or not, in a gosql.Result which doesn't // expose the underlying driver.Result. // gosql.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) } rows, err := empty.RowsAffected() if rows != 0 { t.Fatalf("expected 0 rows, got %d", rows) } if err == nil { t.Fatal("expected error") } }
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(), "TestParallel", url.User(security.RootUser)) 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] }
func TestPrepareSyntax(t *testing.T) { defer leaktest.AfterTest(t)() s, _, _ := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() pgURL, cleanupFn := sqlutils.PGUrl(t, s.ServingAddr(), "TestPrepareSyntax", url.User(security.RootUser)) defer cleanupFn() db, err := gosql.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(), "StartServer", url.User(security.RootUser)) 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 TestPGPreparedExec(t *testing.T) { defer leaktest.AfterTest(t)() var baseTest preparedExecTest execTests := []struct { query string tests []preparedExecTest }{ { "CREATE DATABASE d", []preparedExecTest{ baseTest, }, }, { "CREATE TABLE d.t (i INT, s STRING, d INT)", []preparedExecTest{ baseTest, baseTest.Error(`pq: relation "t" already exists`), }, }, { "INSERT INTO d.t VALUES ($1, $2, $3)", []preparedExecTest{ baseTest.SetArgs(1, "one", 2).RowsAffected(1), baseTest.SetArgs("two", 2, 2).Error(`pq: error in argument for $1: strconv.ParseInt: parsing "two": invalid syntax`), }, }, { "UPDATE d.t SET s = $1, i = i + $2, d = 1 + $3 WHERE i = $4", []preparedExecTest{ baseTest.SetArgs(4, 3, 2, 1).RowsAffected(1), }, }, { "UPDATE d.t SET i = $1 WHERE (i, s) = ($2, $3)", []preparedExecTest{ baseTest.SetArgs(8, 4, "4").RowsAffected(1), }, }, { "DELETE FROM d.t WHERE s = $1 and i = $2 and d = 2 + $3", []preparedExecTest{ baseTest.SetArgs(1, 2, 3).RowsAffected(0), }, }, { "INSERT INTO d.t VALUES ($1), ($2)", []preparedExecTest{ baseTest.SetArgs(1, 2).RowsAffected(2), }, }, { "INSERT INTO d.t VALUES ($1), ($2) RETURNING $3 + 1", []preparedExecTest{ baseTest.SetArgs(3, 4, 5).RowsAffected(2), }, }, { "UPDATE d.t SET i = CASE WHEN $1 THEN i-$3 WHEN $2 THEN i+$3 END", []preparedExecTest{ baseTest.SetArgs(true, true, 3).RowsAffected(5), }, }, { "UPDATE d.t SET i = CASE i WHEN $1 THEN i-$3 WHEN $2 THEN i+$3 END", []preparedExecTest{ baseTest.SetArgs(1, 2, 3).RowsAffected(5), }, }, { "UPDATE d.t SET d = CASE WHEN TRUE THEN $1 END", []preparedExecTest{ baseTest.SetArgs(2).RowsAffected(5), }, }, { "DELETE FROM d.t RETURNING $1+1", []preparedExecTest{ baseTest.SetArgs(1).RowsAffected(5), }, }, { "DROP TABLE d.t", []preparedExecTest{ baseTest, baseTest.Error(`pq: table "d.t" does not exist`), }, }, { "CREATE TABLE d.types (i int, f float, s string, b bytes, d date, m timestamp, z timestamp with time zone, n interval, o bool, e decimal)", []preparedExecTest{ baseTest, }, }, { "INSERT INTO d.types VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)", []preparedExecTest{ baseTest.RowsAffected(1).SetArgs( int64(0), float64(0), "", []byte{}, time.Time{}, // date time.Time{}, // timestamp time.Time{}, // timestamptz time.Hour.String(), true, "0.0", // decimal ), }, }, { "DROP DATABASE d", []preparedExecTest{ baseTest, }, }, // An empty string is valid in postgres. { "", []preparedExecTest{ baseTest.RowsAffectedErr("no RowsAffected available after the empty statement"), }, }, // Empty statements are permitted. { ";", []preparedExecTest{ baseTest.RowsAffectedErr("no RowsAffected available after the empty statement"), }, }, // Any number of empty statements are permitted with a single statement // anywhere. { "; ; SET DATABASE = system; ;", []preparedExecTest{ baseTest, }, }, } s, _, _ := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() pgURL, cleanupFn := sqlutils.PGUrl(t, s.ServingAddr(), "TestPGPreparedExec", url.User(security.RootUser)) defer cleanupFn() db, err := gosql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } defer db.Close() runTests := func(query string, tests []preparedExecTest, execFunc func(...interface{}) (gosql.Result, error)) { for _, test := range tests { if testing.Verbose() || log.V(1) { log.Infof(context.Background(), "exec: %s", query) } if result, err := execFunc(test.qargs...); err != nil { if test.error == "" { t.Errorf("%s: %v: unexpected error: %s", query, test.qargs, err) } else if err.Error() != test.error { t.Errorf("%s: %v: expected error: %s, got %s", query, test.qargs, test.error, err) } } else { rowsAffected, err := result.RowsAffected() if !testutils.IsError(err, test.rowsAffectedErr) { t.Errorf("%s: %v: expected %q, got %v", query, test.qargs, test.rowsAffectedErr, err) } else if rowsAffected != test.rowsAffected { t.Errorf("%s: %v: expected %v, got %v", query, test.qargs, test.rowsAffected, rowsAffected) } } } } for _, execTest := range execTests { runTests(execTest.query, execTest.tests, func(args ...interface{}) (gosql.Result, error) { return db.Exec(execTest.query, args...) }) } for _, execTest := range execTests { if testing.Verbose() || log.V(1) { log.Infof(context.Background(), "prepare: %s", execTest.query) } 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 baseTest preparedQueryTest queryTests := map[string][]preparedQueryTest{ "SELECT $1 > 0": { baseTest.SetArgs(1).Results(true), baseTest.SetArgs("1").Results(true), baseTest.SetArgs(1.1).Error(`pq: error in argument for $1: strconv.ParseInt: parsing "1.1": invalid syntax`).Results(true), baseTest.SetArgs("1.0").Error(`pq: error in argument for $1: strconv.ParseInt: parsing "1.0": invalid syntax`), baseTest.SetArgs(true).Error(`pq: error in argument for $1: strconv.ParseInt: parsing "true": invalid syntax`), }, "SELECT ($1) > 0": { baseTest.SetArgs(1).Results(true), baseTest.SetArgs(-1).Results(false), }, "SELECT ((($1))) > 0": { baseTest.SetArgs(1).Results(true), baseTest.SetArgs(-1).Results(false), }, "SELECT TRUE AND $1": { baseTest.SetArgs(true).Results(true), baseTest.SetArgs(false).Results(false), baseTest.SetArgs(1).Results(true), baseTest.SetArgs("").Error(`pq: error in argument for $1: strconv.ParseBool: parsing "": invalid syntax`), // Make sure we can run another after a failure. baseTest.SetArgs(true).Results(true), }, "SELECT $1::bool": { baseTest.SetArgs(true).Results(true), baseTest.SetArgs("true").Results(true), baseTest.SetArgs("false").Results(false), baseTest.SetArgs("1").Results(true), baseTest.SetArgs(2).Error(`pq: error in argument for $1: strconv.ParseBool: parsing "2": invalid syntax`), baseTest.SetArgs(3.1).Error(`pq: error in argument for $1: strconv.ParseBool: parsing "3.1": invalid syntax`), baseTest.SetArgs("").Error(`pq: error in argument for $1: strconv.ParseBool: parsing "": invalid syntax`), }, "SELECT CASE 40+2 WHEN 42 THEN 51 ELSE $1::INT END": { baseTest.Error( "pq: no value provided for placeholder: $1", ).PreparedError( "sql: statement expects 1 inputs; got 0", ), }, "SELECT $1::int > $2::float": { baseTest.SetArgs(2, 1).Results(true), baseTest.SetArgs("2", 1).Results(true), baseTest.SetArgs(1, "2").Results(false), baseTest.SetArgs("2", "1.0").Results(true), baseTest.SetArgs("2.0", "1").Error(`pq: error in argument for $1: strconv.ParseInt: parsing "2.0": invalid syntax`), baseTest.SetArgs(2.1, 1).Error(`pq: error in argument for $1: strconv.ParseInt: parsing "2.1": invalid syntax`), }, "SELECT GREATEST($1, 0, $2), $2": { baseTest.SetArgs(1, -1).Results(1, -1), baseTest.SetArgs(-1, 10).Results(10, 10), baseTest.SetArgs("-2", "-1").Results(0, -1), baseTest.SetArgs(1, 2.1).Error(`pq: error in argument for $2: strconv.ParseInt: parsing "2.1": invalid syntax`), }, "SELECT $1::int, $1::float": { baseTest.SetArgs(1).Results(1, 1.0), baseTest.SetArgs("1").Results(1, 1.0), }, "SELECT 3 + $1, $1 + $2": { baseTest.SetArgs("1", "2").Results(4, 3), baseTest.SetArgs(3, "4").Results(6, 7), baseTest.SetArgs(0, "a").Error(`pq: error in argument for $2: strconv.ParseInt: parsing "a": invalid syntax`), }, // Check for name resolution. "SELECT COUNT(*)": { baseTest.Results(1), }, "SELECT CASE WHEN $1 THEN 1-$3 WHEN $2 THEN 1+$3 END": { baseTest.SetArgs(true, false, 2).Results(-1), baseTest.SetArgs(false, true, 3).Results(4), baseTest.SetArgs(false, false, 2).Results(gosql.NullBool{}), }, "SELECT CASE 1 WHEN $1 THEN $2 ELSE 2 END": { baseTest.SetArgs(1, 3).Results(3), baseTest.SetArgs(2, 3).Results(2), baseTest.SetArgs(true, 0).Error(`pq: error in argument for $1: strconv.ParseInt: parsing "true": invalid syntax`), }, // TODO(nvanbenschoten) Blocked on #10713. // "SELECT $1[2] LIKE 'b'": { // baseTest.SetArgs(pq.Array([]string{"a", "b", "c"})).Results(true), // }, "SHOW DATABASE": { baseTest.Results(""), }, "SELECT descriptor FROM system.descriptor WHERE descriptor != $1 LIMIT 1": { baseTest.SetArgs([]byte("abc")).Results([]byte("\x12\x16\n\x06system\x10\x01\x1a\n\n\b\n\x04root\x100")), }, "SHOW COLUMNS FROM system.users": { baseTest. Results("username", "STRING", false, gosql.NullBool{}). Results("hashedPassword", "BYTES", true, gosql.NullBool{}), }, "SHOW DATABASES": { baseTest.Results("d").Results("information_schema").Results("pg_catalog").Results("system"), }, "SHOW GRANTS ON system.users": { baseTest.Results("users", security.RootUser, "DELETE"). Results("users", security.RootUser, "GRANT"). Results("users", security.RootUser, "INSERT"). Results("users", security.RootUser, "SELECT"). Results("users", security.RootUser, "UPDATE"), }, "SHOW INDEXES FROM system.users": { baseTest.Results("users", "primary", true, 1, "username", "ASC", false), }, "SHOW TABLES FROM system": { baseTest.Results("descriptor").Others(7), }, "SHOW CONSTRAINTS FROM system.users": { baseTest.Results("users", "primary", "PRIMARY KEY", "username", gosql.NullString{}), }, "SHOW TIME ZONE": { baseTest.Results("UTC"), }, "SHOW USERS": { baseTest, }, "HELP LEAST": { baseTest.Results("least", "(anyelement...) -> anyelement", "Comparison", ""), }, "SELECT (SELECT 1+$1)": { baseTest.SetArgs(1).Results(2), }, "SELECT CASE WHEN $1 THEN $2 ELSE 3 END": { baseTest.SetArgs(true, 2).Results(2), baseTest.SetArgs(false, 2).Results(3), }, "SELECT CASE WHEN TRUE THEN 1 ELSE $1 END": { baseTest.SetArgs(2).Results(1), }, "SELECT CASE $1 WHEN 1 THEN 1 END": { baseTest.SetArgs(1).Results(1), baseTest.SetArgs(2).Results(gosql.NullInt64{}), }, "SELECT $1::timestamp, $2::date": { baseTest.SetArgs("2001-01-02 03:04:05", "2006-07-08").Results( time.Date(2001, 1, 2, 3, 4, 5, 0, time.FixedZone("", 0)), time.Date(2006, 7, 8, 0, 0, 0, 0, time.FixedZone("", 0)), ), }, "SELECT $1::date, $2::timestamp": { baseTest.SetArgs( time.Date(2006, 7, 8, 0, 0, 0, 9, time.FixedZone("", 0)), time.Date(2001, 1, 2, 3, 4, 5, 6000, time.FixedZone("", 0)), ).Results( time.Date(2006, 7, 8, 0, 0, 0, 0, time.FixedZone("", 0)), time.Date(2001, 1, 2, 3, 4, 5, 6000, time.FixedZone("", 0)), ), }, "SELECT (CASE a WHEN 10 THEN 'one' WHEN 11 THEN (CASE 'en' WHEN 'en' THEN $1 END) END) AS ret FROM d.T ORDER BY ret DESC LIMIT 2": { baseTest.SetArgs("hello").Results("one").Results("hello"), }, "INSERT INTO d.ts VALUES($1, $2) RETURNING *": { baseTest.SetArgs("2001-01-02 03:04:05", "2006-07-08").Results( time.Date(2001, 1, 2, 3, 4, 5, 0, time.FixedZone("", 0)), time.Date(2006, 7, 8, 0, 0, 0, 0, time.FixedZone("", 0)), ), }, "INSERT INTO d.ts VALUES(CURRENT_TIMESTAMP(), $1) RETURNING b": { baseTest.SetArgs("2006-07-08").Results( time.Date(2006, 7, 8, 0, 0, 0, 0, time.FixedZone("", 0)), ), }, "INSERT INTO d.ts VALUES(STATEMENT_TIMESTAMP(), $1) RETURNING b": { baseTest.SetArgs("2006-07-08").Results( time.Date(2006, 7, 8, 0, 0, 0, 0, time.FixedZone("", 0)), ), }, "INSERT INTO d.ts (a) VALUES ($1) RETURNING a": { baseTest.SetArgs( time.Date(2006, 7, 8, 0, 0, 0, 123000, time.FixedZone("", 0)), ).Results( time.Date(2006, 7, 8, 0, 0, 0, 123000, time.FixedZone("", 0)), ), }, "INSERT INTO d.T VALUES ($1) RETURNING 1": { baseTest.SetArgs(1).Results(1), baseTest.SetArgs(nil).Results(1), }, "INSERT INTO d.T VALUES ($1::INT) RETURNING 1": { baseTest.SetArgs(1).Results(1), }, "INSERT INTO d.T VALUES ($1) RETURNING $1": { baseTest.SetArgs(1).Results(1), baseTest.SetArgs(3).Results(3), }, "INSERT INTO d.T VALUES ($1) RETURNING $1, 1 + $1": { baseTest.SetArgs(1).Results(1, 2), baseTest.SetArgs(3).Results(3, 4), }, "INSERT INTO d.T VALUES (GREATEST(42, $1)) RETURNING a": { baseTest.SetArgs(40).Results(42), baseTest.SetArgs(45).Results(45), }, "SELECT a FROM d.T WHERE a = $1 AND (SELECT a >= $2 FROM d.T WHERE a = $1)": { baseTest.SetArgs(10, 5).Results(10), baseTest.Error( "pq: no value provided for placeholders: $1, $2", ).PreparedError( "sql: statement expects 2 inputs; got 0", ), }, "SELECT * FROM (VALUES (1), (2), (3), (4)) AS foo (a) LIMIT $1 OFFSET $2": { baseTest.SetArgs(1, 0).Results(1), baseTest.SetArgs(1, 1).Results(2), baseTest.SetArgs(1, 2).Results(3), }, "SELECT 3 + CASE (4) WHEN 4 THEN $1 ELSE 42 END": { baseTest.SetArgs(12).Results(15), baseTest.SetArgs(-12).Results(-9), }, "SELECT DATE '2001-01-02' + ($1 + $1)": { baseTest.SetArgs(12).Results("2001-01-26T00:00:00Z"), }, "SELECT TO_HEX(~(~$1))": { baseTest.SetArgs(12).Results("c"), }, "SELECT $1::INT": { baseTest.SetArgs(12).Results(12), }, "SELECT ANNOTATE_TYPE($1, int)": { baseTest.SetArgs(12).Results(12), }, "SELECT $1 + $2, ANNOTATE_TYPE($2, float)": { baseTest.SetArgs(12, 23).Results(35, 23), }, "INSERT INTO d.T VALUES ($1 + 1) RETURNING a": { baseTest.SetArgs(1).Results(2), baseTest.SetArgs(11).Results(12), }, "INSERT INTO d.T VALUES (-$1) RETURNING a": { baseTest.SetArgs(1).Results(-1), baseTest.SetArgs(-999).Results(999), }, "INSERT INTO d.two (a, b) VALUES (~$1, $1 + $2) RETURNING a, b": { baseTest.SetArgs(5, 6).Results(-6, 11), }, "INSERT INTO d.str (s) VALUES (LEFT($1, 3)) RETURNING s": { baseTest.SetArgs("abcdef").Results("abc"), baseTest.SetArgs("123456").Results("123"), }, "INSERT INTO d.str (b) VALUES (COALESCE($1, 'strLit')) RETURNING b": { baseTest.SetArgs(nil).Results("strLit"), baseTest.SetArgs("123456").Results("123456"), }, "INSERT INTO d.intStr VALUES ($1, 'hello ' || $1::TEXT) RETURNING *": { baseTest.SetArgs(123).Results(123, "hello 123"), }, } s, _, _ := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() pgURL, cleanupFn := sqlutils.PGUrl(t, s.ServingAddr(), "TestPGPreparedQuery", url.User(security.RootUser)) defer cleanupFn() db, err := gosql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } defer db.Close() runTests := func(query string, prepared bool, tests []preparedQueryTest, queryFunc func(...interface{}) (*gosql.Rows, error)) { for _, test := range tests { if testing.Verbose() || log.V(1) { log.Infof(context.Background(), "query: %s", query) } rows, err := queryFunc(test.qargs...) if err != nil { if test.error == "" { t.Errorf("%s: %v: unexpected error: %s", query, test.qargs, err) } else { expectedErr := test.error if prepared && test.preparedError != "" { expectedErr = test.preparedError } if err.Error() != expectedErr { t.Errorf("%s: %v: expected error: %s, got %s", query, test.qargs, expectedErr, err) } continue } } defer rows.Close() if test.error != "" { t.Errorf("expected error: %s: %v", query, test.qargs) continue } for _, expected := range test.results { if !rows.Next() { t.Errorf("expected row: %s: %v", query, test.qargs) continue } dst := make([]interface{}, len(expected)) for i, d := range expected { 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, expected) { t.Errorf("%s: %v: expected %v, got %v", query, test.qargs, expected, dst) } } for rows.Next() { if test.others > 0 { test.others-- continue } cols, err := rows.Columns() if err != nil { t.Errorf("%s: %s", query, err) continue } // Unexpected line. Get and print out the details. dst := make([]interface{}, len(cols)) for i := range dst { dst[i] = new(interface{}) } if err := rows.Scan(dst...); err != nil { t.Errorf("%s: %s", query, err) continue } b, err := json.Marshal(dst) if err != nil { t.Errorf("%s: %s", query, err) continue } t.Errorf("%s: unexpected row: %s", query, b) } if test.others > 0 { t.Errorf("%s: expected %d more rows", query, test.others) continue } } } initStmt := ` CREATE DATABASE d; CREATE TABLE d.t (a INT); INSERT INTO d.t VALUES (10),(11); CREATE TABLE d.ts (a TIMESTAMP, b DATE); CREATE TABLE d.two (a INT, b INT); CREATE TABLE d.intStr (a INT, s STRING); CREATE TABLE d.str (s STRING, b BYTES);` if _, err := db.Exec(initStmt); err != nil { t.Fatal(err) } for query, tests := range queryTests { runTests(query, false, tests, func(args ...interface{}) (*gosql.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, true, tests, stmt.Query) }() } } }
// TestDumpRandom generates a random number of random rows with all data // types. This data is dumped, inserted, and dumped again. The two dumps // are compared for exactness. The data from the inserted dump is then // SELECT'd and compared to the original generated data to ensure it is // round-trippable. func TestDumpRandom(t *testing.T) { defer leaktest.AfterTest(t)() c, err := newCLITest(t, false) if err != nil { t.Fatal(err) } defer c.stop(true) url, cleanup := sqlutils.PGUrl(t, c.ServingAddr(), "TestDumpRandom", url.User(security.RootUser)) defer cleanup() conn := makeSQLConn(url.String()) defer conn.Close() if err := conn.Exec(` CREATE DATABASE d; CREATE DATABASE o; CREATE TABLE d.t ( rowid int, i int, f float, d date, m timestamp, n interval, o bool, e decimal, s string, b bytes, PRIMARY KEY (rowid, i, f, d, m, n, o, e, s, b) ); `, nil); err != nil { t.Fatal(err) } rnd, seed := randutil.NewPseudoRand() t.Logf("random seed: %v", seed) start := timeutil.Now() for iteration := 0; timeutil.Since(start) < *randomTestTime; iteration++ { if err := conn.Exec(`DELETE FROM d.t`, nil); err != nil { t.Fatal(err) } var generatedRows [][]driver.Value count := rnd.Int63n(500) t.Logf("random iteration %v: %v rows", iteration, count) for _i := int64(0); _i < count; _i++ { // Generate a random number of random inserts. i := rnd.Int63() f := rnd.Float64() d := time.Unix(0, rnd.Int63()).Round(time.Hour * 24).UTC() m := time.Unix(0, rnd.Int63()).Round(time.Microsecond).UTC() n := time.Duration(rnd.Int63()).String() o := rnd.Intn(2) == 1 e := strings.TrimRight(inf.NewDec(rnd.Int63(), inf.Scale(rnd.Int31n(20)-10)).String(), ".0") sr := make([]byte, rnd.Intn(500)) if _, err := rnd.Read(sr); err != nil { t.Fatal(err) } s := make([]byte, 0, len(sr)) for _, b := range sr { r := rune(b) if !utf8.ValidRune(r) { continue } s = append(s, []byte(string(r))...) } b := make([]byte, rnd.Intn(500)) if _, err := rnd.Read(b); err != nil { t.Fatal(err) } vals := []driver.Value{ _i, i, f, d, m, []byte(n), // intervals come out as `[]byte`s o, []byte(e), // decimals come out as `[]byte`s string(s), b, } if err := conn.Exec("INSERT INTO d.t VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)", vals); err != nil { t.Fatal(err) } generatedRows = append(generatedRows, vals[1:]) } check := func(table string) { q := fmt.Sprintf("SELECT i, f, d, m, n, o, e, s, b FROM %s ORDER BY rowid", table) nrows, err := conn.Query(q, nil) if err != nil { t.Fatal(err) } defer func() { if err := nrows.Close(); err != nil { t.Fatal(err) } }() for gi, generatedRow := range generatedRows { fetched := make([]driver.Value, len(nrows.Columns())) if err := nrows.Next(fetched); err != nil { t.Fatal(err) } for i, fetchedVal := range fetched { generatedVal := generatedRow[i] if t, ok := fetchedVal.(time.Time); ok { // dates and timestamps come out with offset zero (but // not UTC specifically). fetchedVal = t.UTC() } if !reflect.DeepEqual(fetchedVal, generatedVal) { t.Errorf("NOT EQUAL: table %s, row %d, col %d\ngenerated (%T): %v\nselected (%T): %v\n", table, gi, i, generatedVal, generatedVal, fetchedVal, fetchedVal) } } if t.Failed() { t.FailNow() } } } check("d.t") var buf bytes.Buffer if err := DumpTable(&buf, conn, "d", "t"); err != nil { t.Fatal(err) } dump := buf.String() buf.Reset() if err := conn.Exec(` SET DATABASE = o; DROP TABLE IF EXISTS t; `, nil); err != nil { t.Fatal(err) } if err := conn.Exec(dump, nil); err != nil { t.Fatal(err) } check("o.t") if err := DumpTable(&buf, conn, "o", "t"); err != nil { t.Fatal(err) } dump2 := buf.String() if dump != dump2 { t.Fatalf("unmatching dumps:\nFIRST:\n%s\n\nSECOND:\n%s", dump, dump2) } } }
// 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, _, _ := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() pgURL, cleanupFn := sqlutils.PGUrl(t, s.ServingAddr(), "TestPGCommandTags", url.User(security.RootUser)) 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) } 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) } tx, err = db.Begin() if err != nil { t.Fatal(err) } if _, err := tx.Exec("INSERT INTO testing.tags VALUES (4, 1)"); err != nil { t.Fatal(err) } // Rollback also checks the correct tag is returned. if err := tx.Rollback(); err != nil { t.Fatal(err) } tx, err = db.Begin() if err != nil { t.Fatal(err) } // An error will abort the server's transaction. if _, err := tx.Exec("INSERT INTO testing.tags VALUES (4, 1), (4, 1)"); err == nil { t.Fatal("expected an error on duplicate k") } // Rollback, even of an aborted txn, should also return the correct tag. if err := tx.Rollback(); 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 TestLogRebalances(t *testing.T) { defer leaktest.AfterTest(t)() s, _, db := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() // Use a client to get the RangeDescriptor for the first range. We will use // this range's information to log fake rebalance events. desc := &roachpb.RangeDescriptor{} if err := db.GetProto(context.TODO(), keys.RangeDescriptorKey(roachpb.RKeyMin), desc); err != nil { t.Fatal(err) } // This code assumes that there is only one TestServer, and thus that // StoreID 1 is present on the testserver. If this assumption changes in the // future, *any* store will work, but a new method will need to be added to // Stores (or a creative usage of VisitStores could suffice). store, err := s.(*server.TestServer).Stores().GetStore(roachpb.StoreID(1)) if err != nil { t.Fatal(err) } // Log several fake events using the store. logEvent := func(changeType roachpb.ReplicaChangeType) { if err := db.Txn(context.TODO(), func(txn *client.Txn) error { return store.LogReplicaChangeTest(txn, changeType, desc.Replicas[0], *desc) }); err != nil { t.Fatal(err) } } checkMetrics := func(expAdds, expRemoves int64) { if a, e := store.Metrics().RangeAdds.Count(), expAdds; a != e { t.Errorf("range adds %d != expected %d", a, e) } if a, e := store.Metrics().RangeRemoves.Count(), expRemoves; a != e { t.Errorf("range removes %d != expected %d", a, e) } } logEvent(roachpb.ADD_REPLICA) checkMetrics(1 /*add*/, 0 /*remove*/) logEvent(roachpb.ADD_REPLICA) checkMetrics(2 /*adds*/, 0 /*remove*/) logEvent(roachpb.REMOVE_REPLICA) checkMetrics(2 /*adds*/, 1 /*remove*/) // Open a SQL connection to verify that the events have been logged. pgURL, cleanupFn := sqlutils.PGUrl(t, s.ServingAddr(), "TestLogRebalances", url.User(security.RootUser)) defer cleanupFn() sqlDB, err := gosql.Open("postgres", pgURL.String()) if err != nil { t.Fatal(err) } defer sqlDB.Close() // verify that two add replica events have been logged. // TODO(mrtracy): placeholders still appear to be broken, this query should // be using a string placeholder for the eventType value. rows, err := sqlDB.Query(`SELECT rangeID, info FROM system.rangelog WHERE eventType = 'add'`) if err != nil { t.Fatal(err) } var count int for rows.Next() { count++ var rangeID int64 var infoStr gosql.NullString if err := rows.Scan(&rangeID, &infoStr); err != nil { t.Fatal(err) } if a, e := roachpb.RangeID(rangeID), desc.RangeID; a != e { t.Errorf("wrong rangeID %d recorded for add event, expected %d", a, e) } // Verify that info returns a json struct. if !infoStr.Valid { t.Errorf("info not recorded for add replica of range %d", rangeID) } var info struct { AddReplica roachpb.ReplicaDescriptor UpdatedDesc roachpb.RangeDescriptor } if err := json.Unmarshal([]byte(infoStr.String), &info); err != nil { t.Errorf("error unmarshalling info string for add replica %d: %s", rangeID, err) continue } if int64(info.UpdatedDesc.RangeID) != rangeID { t.Errorf("recorded wrong updated descriptor %s for add replica of range %d", info.UpdatedDesc, rangeID) } if a, e := info.AddReplica, desc.Replicas[0]; a != e { t.Errorf("recorded wrong updated replica %s for add replica of range %d, expected %s", a, rangeID, e) } } if rows.Err() != nil { t.Fatal(rows.Err()) } if a, e := count, 2; a != e { t.Errorf("expected %d AddReplica events logged, found %d", e, a) } // verify that one remove replica event was logged. rows, err = sqlDB.Query(`SELECT rangeID, info FROM system.rangelog WHERE eventType = 'remove'`) if err != nil { t.Fatal(err) } count = 0 for rows.Next() { count++ var rangeID int64 var infoStr gosql.NullString if err := rows.Scan(&rangeID, &infoStr); err != nil { t.Fatal(err) } if a, e := roachpb.RangeID(rangeID), desc.RangeID; a != e { t.Errorf("wrong rangeID %d recorded for remove event, expected %d", a, e) } // Verify that info returns a json struct. if !infoStr.Valid { t.Errorf("info not recorded for remove replica of range %d", rangeID) } var info struct { RemovedReplica roachpb.ReplicaDescriptor UpdatedDesc roachpb.RangeDescriptor } if err := json.Unmarshal([]byte(infoStr.String), &info); err != nil { t.Errorf("error unmarshalling info string for remove replica %d: %s", rangeID, err) continue } if int64(info.UpdatedDesc.RangeID) != rangeID { t.Errorf("recorded wrong updated descriptor %s for remove replica of range %d", info.UpdatedDesc, rangeID) } if a, e := info.RemovedReplica, desc.Replicas[0]; a != e { t.Errorf("recorded wrong updated replica %s for remove replica of range %d, expected %s", a, rangeID, e) } } if rows.Err() != nil { t.Fatal(rows.Err()) } if a, e := count, 1; a != e { t.Errorf("expected %d RemoveReplica events logged, found %d", e, a) } }
// Test that a COMMIT getting an error, retryable or not, leaves the txn // finalized and not in Aborted/RestartWait (i.e. COMMIT, like ROLLBACK, is // always final). As opposed to an error on a COMMIT in an auto-retry // txn, where we retry the txn (not tested here). func TestErrorOnCommitFinalizesTxn(t *testing.T) { defer leaktest.AfterTest(t)() aborter := NewTxnAborter() defer aborter.Close(t) params, _ := createTestServerParams() params.Knobs.SQLExecutor = aborter.executorKnobs() s, sqlDB, _ := serverutils.StartServer(t, params) defer s.Stopper().Stop() { pgURL, cleanup := sqlutils.PGUrl(t, s.ServingAddr(), "TestErrorOnCommitFinalizesTxn", url.User(security.RootUser)) defer cleanup() if err := aborter.Init(pgURL); err != nil { t.Fatal(err) } } if _, err := sqlDB.Exec(` CREATE DATABASE t; CREATE TABLE t.test (k INT PRIMARY KEY, v TEXT); `); err != nil { t.Fatal(err) } // We need to do everything on one connection as we'll want to observe the // connection state after a COMMIT. sqlDB.SetMaxOpenConns(1) // We're going to test both errors that would leave the transaction in the // RestartWait state and errors that would leave the transaction in Aborted, // if they were to happen on any other statement than COMMIT. // We do that by always injecting a retryable error at COMMIT, but once in a // txn that had a "retry intent" (SAVEPOINT cockroach_restart), and once in a // txn without it. testCases := []struct { retryIntent bool }{ {false}, {true}, } for _, tc := range testCases { const insertStmt = "INSERT INTO t.test(k, v) VALUES (0, 'boulanger')" if err := aborter.QueueStmtForAbortion( insertStmt, 1 /* abortCount */, false, /* willBeRetriedIbid */ ); err != nil { t.Fatal(err) } if _, err := sqlDB.Exec("BEGIN"); err != nil { t.Fatal(err) } if tc.retryIntent { if _, err := sqlDB.Exec("SAVEPOINT cockroach_restart"); err != nil { t.Fatal(err) } } if _, err := sqlDB.Exec(insertStmt); err != nil { t.Fatal(err) } if _, err := sqlDB.Exec("COMMIT"); !testutils.IsError(err, "pq: restart transaction") { t.Fatalf("unexpected error: %v", err) } // Check that we can start another txn on the (one and only) connection. if _, err := sqlDB.Exec("BEGIN"); err != nil { t.Fatal(err) } // Check that we don't see any rows, so the previous txn was rolled back. rows, err := sqlDB.Query("SELECT * FROM t.test") if err != nil { t.Fatal(err) } if rows.Next() { var k int var v string err := rows.Scan(&k, &v) t.Fatalf("found unexpected row: %d %s, %v", k, v, err) } rows.Close() if _, err := sqlDB.Exec("END"); err != nil { t.Fatal(err) } } }
// TestUserTxnRestart tests user-directed txn restarts. // The test will inject and otherwise create retriable errors of various kinds // and checks that we still manage to run a txn despite them. func TestTxnUserRestart(t *testing.T) { defer leaktest.AfterTest(t)() aborter := NewTxnAborter() defer aborter.Close(t) params, cmdFilters := createTestServerParams() params.Knobs.SQLExecutor = aborter.executorKnobs() s, sqlDB, _ := serverutils.StartServer(t, params) defer s.Stopper().Stop() { pgURL, cleanup := sqlutils.PGUrl(t, s.ServingAddr(), "TestTxnUserRestart", url.User(security.RootUser)) defer cleanup() if err := aborter.Init(pgURL); err != nil { t.Fatal(err) } } if _, err := sqlDB.Exec(` CREATE DATABASE t; CREATE TABLE t.test (k INT PRIMARY KEY, v TEXT); `); err != nil { t.Fatal(err) } // Set up error injection that causes retries. testCases := []struct { magicVals *filterVals expectedErr string }{ { magicVals: createFilterVals( map[string]int{"boulanger": 2}, // restartCounts nil), expectedErr: ".*encountered previous write with future timestamp.*", }, { magicVals: createFilterVals( nil, map[string]int{"boulanger": 2}), // abortCounts expectedErr: ".*txn aborted.*", }, } for _, tc := range testCases { for _, rs := range []rollbackStrategy{rollbackToSavepoint, declareSavepoint} { cleanupFilter := cmdFilters.AppendFilter( func(args storagebase.FilterArgs) *roachpb.Error { if err := injectErrors(args.Req, args.Hdr, tc.magicVals); err != nil { return roachpb.NewErrorWithTxn(err, args.Hdr.Txn) } return nil }, false) // Also inject an error at RELEASE time, besides the error injected by magicVals. const sentinelInsert = "INSERT INTO t.test(k, v) VALUES (0, 'sentinel')" if err := aborter.QueueStmtForAbortion( sentinelInsert, 1 /* abortCount */, true, /* willBeRetriedIbid */ ); err != nil { t.Fatal(err) } commitCount := s.MustGetSQLCounter(sql.MetaTxnCommit.Name) // This is the magic. Run the txn closure until all the retries are exhausted. retryExec(t, sqlDB, rs, func(tx *gosql.Tx) bool { return runTestTxn(t, tc.magicVals, tc.expectedErr, sqlDB, tx, sentinelInsert) }) checkRestarts(t, tc.magicVals) // Check that we only wrote the sentinel row. rows, err := sqlDB.Query("SELECT * FROM t.test") if err != nil { t.Fatal(err) } for rows.Next() { var k int var v string err = rows.Scan(&k, &v) if err != nil { t.Fatal(err) } if k != 0 || v != "sentinel" { t.Fatalf("didn't find expected row: %d %s", k, v) } } // Check that the commit counter was incremented. It could have been // incremented by more than 1 because of the transactions we use to force // aborts, plus who knows what else the server is doing in the background. checkCounterGE(t, s, sql.MetaTxnCommit, commitCount+1) // Clean up the table for the next test iteration. _, err = sqlDB.Exec("DELETE FROM t.test WHERE true") if err != nil { t.Fatal(err) } rows.Close() cleanupFilter() } } }
// Test that a connection closed abruptly while a SQL txn is in progress results // in that txn being rolled back. func TestSessionFinishRollsBackTxn(t *testing.T) { defer leaktest.AfterTest(t)() aborter := NewTxnAborter() defer aborter.Close(t) params, _ := createTestServerParams() params.Knobs.SQLExecutor = aborter.executorKnobs() s, mainDB, _ := serverutils.StartServer(t, params) defer s.Stopper().Stop() { pgURL, cleanup := sqlutils.PGUrl( t, s.ServingAddr(), "TestSessionFinishRollsBackTxn", url.User(security.RootUser)) defer cleanup() if err := aborter.Init(pgURL); err != nil { t.Fatal(err) } } if _, err := mainDB.Exec(` CREATE DATABASE t; CREATE TABLE t.test (k INT PRIMARY KEY, v TEXT); `); err != nil { t.Fatal(err) } // We're going to test the rollback of transactions left in various states // when the connection closes abruptly. // For the state CommitWait, there's no actual rollback we can test for (since // the kv-level transaction has already been committed). But we still // exercise this state to check that the server doesn't crash (which used to // happen - #9879). tests := []sql.TxnStateEnum{sql.Open, sql.RestartWait, sql.CommitWait} for _, state := range tests { t.Run(state.String(), func(t *testing.T) { // Create a low-level lib/pq connection so we can close it at will. pgURL, cleanupDB := sqlutils.PGUrl( t, s.ServingAddr(), state.String(), url.User(security.RootUser)) defer cleanupDB() conn, err := pq.Open(pgURL.String()) if err != nil { t.Fatal(err) } connClosed := false defer func() { if connClosed { return } if err := conn.Close(); err != nil { t.Fatal(err) } }() txn, err := conn.Begin() if err != nil { t.Fatal(err) } tx := txn.(driver.Execer) if _, err := tx.Exec("SET TRANSACTION PRIORITY NORMAL", nil); err != nil { t.Fatal(err) } if state == sql.RestartWait || state == sql.CommitWait { if _, err := tx.Exec("SAVEPOINT cockroach_restart", nil); err != nil { t.Fatal(err) } } insertStmt := "INSERT INTO t.test(k, v) VALUES (1, 'a')" if state == sql.RestartWait { // To get a txn in RestartWait, we'll use an aborter. if err := aborter.QueueStmtForAbortion( insertStmt, 1 /* restartCount */, false /* willBeRetriedIbid */); err != nil { t.Fatal(err) } } if _, err := tx.Exec(insertStmt, nil); err != nil { t.Fatal(err) } if err := aborter.VerifyAndClear(); err != nil { t.Fatal(err) } if state == sql.RestartWait || state == sql.CommitWait { _, err := tx.Exec("RELEASE SAVEPOINT cockroach_restart", nil) if state == sql.CommitWait { if err != nil { t.Fatal(err) } } else if !testutils.IsError(err, "pq: restart transaction:.*") { t.Fatal(err) } } // Abruptly close the connection. connClosed = true if err := conn.Close(); err != nil { t.Fatal(err) } // Check that the txn we had above was rolled back. We do this by reading // after the preceding txn and checking that we don't get an error and // that we haven't been blocked by intents (we can't exactly test that we // haven't been blocked but we assert that the query didn't take too // long). // We do the read in an explicit txn so that automatic retries don't hide // any errors. // TODO(andrei): Figure out a better way to test for non-blocking. // Use a trace when the client-side tracing story gets good enough. txCheck, err := mainDB.Begin() if err != nil { t.Fatal(err) } // Run check at low priority so we don't push the previous transaction and // fool ourselves into thinking it had been rolled back. if _, err := txCheck.Exec("SET TRANSACTION PRIORITY LOW"); err != nil { t.Fatal(err) } ts := timeutil.Now() var count int if err := txCheck.QueryRow("SELECT COUNT(1) FROM t.test").Scan(&count); err != nil { t.Fatal(err) } // CommitWait actually committed, so we'll need to clean up. if state != sql.CommitWait { if count != 0 { t.Fatalf("expected no rows, got: %d", count) } } else { if _, err := txCheck.Exec("DELETE FROM t.test"); err != nil { t.Fatal(err) } } if err := txCheck.Commit(); err != nil { t.Fatal(err) } if d := timeutil.Since(ts); d > time.Second { t.Fatalf("Looks like the checking tx was unexpectedly blocked. "+ "It took %s to commit.", d) } }) } }
func TestRunQuery(t *testing.T) { defer leaktest.AfterTest(t)() s, _, _ := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() url, cleanup := sqlutils.PGUrl(t, s.ServingAddr(), "TestRunQuery", url.User(security.RootUser)) defer cleanup() conn := makeSQLConn(url.String()) defer conn.Close() // Use a buffer as the io.Writer. var b bytes.Buffer // Non-query statement. if err := runQueryAndFormatResults(conn, &b, makeQuery(`SET DATABASE=system`), true); err != nil { t.Fatal(err) } expected := ` SET ` if a, e := b.String(), expected[1:]; a != e { t.Fatalf("expected output:\n%s\ngot:\n%s", e, a) } b.Reset() // Use system database for sample query/output as they are fairly fixed. cols, rows, _, err := runQuery(conn, makeQuery(`SHOW COLUMNS FROM system.namespace`), false) if err != nil { t.Fatal(err) } expectedCols := []string{"Field", "Type", "Null", "Default"} if !reflect.DeepEqual(expectedCols, cols) { t.Fatalf("expected:\n%v\ngot:\n%v", expectedCols, cols) } expectedRows := [][]string{ {`parentID`, `INT`, `false`, `NULL`}, {`name`, `STRING`, `false`, `NULL`}, {`id`, `INT`, `true`, `NULL`}, } if !reflect.DeepEqual(expectedRows, rows) { t.Fatalf("expected:\n%v\ngot:\n%v", expectedRows, rows) } if err := runQueryAndFormatResults(conn, &b, makeQuery(`SHOW COLUMNS FROM system.namespace`), true); err != nil { t.Fatal(err) } expected = ` +----------+--------+-------+---------+ | Field | Type | Null | Default | +----------+--------+-------+---------+ | parentID | INT | false | NULL | | name | STRING | false | NULL | | id | INT | true | NULL | +----------+--------+-------+---------+ (3 rows) ` if a, e := b.String(), expected[1:]; a != e { t.Fatalf("expected output:\n%s\ngot:\n%s", e, a) } b.Reset() // Test placeholders. if err := runQueryAndFormatResults(conn, &b, makeQuery(`SELECT * FROM system.namespace WHERE name=$1`, "descriptor"), true); err != nil { t.Fatal(err) } expected = ` +----------+------------+----+ | parentID | name | id | +----------+------------+----+ | 1 | descriptor | 3 | +----------+------------+----+ (1 row) ` if a, e := b.String(), expected[1:]; a != e { t.Fatalf("expected output:\n%s\ngot:\n%s", e, a) } b.Reset() // Test multiple results. if err := runQueryAndFormatResults(conn, &b, makeQuery(`SELECT 1; SELECT 2, 3; SELECT 'hello'`), true); err != nil { t.Fatal(err) } expected = ` +---+ | 1 | +---+ | 1 | +---+ (1 row) +---+---+ | 2 | 3 | +---+---+ | 2 | 3 | +---+---+ (1 row) +---------+ | 'hello' | +---------+ | hello | +---------+ (1 row) ` if a, e := b.String(), expected[1:]; a != e { t.Fatalf("expected output:\n%s\ngot:\n%s", e, a) } b.Reset() }
func TestPGWireAuth(t *testing.T) { defer leaktest.AfterTest(t)() s, _, _ := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() { unicodeUser := "******" t.Run("RootUserAuth", func(t *testing.T) { // Authenticate as root with certificate and expect success. rootPgURL, cleanupFn := sqlutils.PGUrl( t, s.ServingAddr(), "TestPGWireAuth", url.User(security.RootUser)) defer cleanupFn() if err := trivialQuery(rootPgURL); err != nil { t.Fatal(err) } // Create server.TestUser with a unicode password and a user with a // unicode username for later tests. // Only root is allowed to create users. db, err := gosql.Open("postgres", rootPgURL.String()) if err != nil { t.Fatal(err) } defer db.Close() if _, err := db.Exec(fmt.Sprintf("CREATE USER %s;", server.TestUser)); err != nil { t.Fatal(err) } if _, err := db.Exec(fmt.Sprintf("CREATE USER %s WITH PASSWORD '蟑♫螂';", unicodeUser)); err != nil { t.Fatal(err) } }) t.Run("UnicodeUserAuth", func(t *testing.T) { // Try to perform authentication with unicodeUser and no password. // This case is equivalent to supplying a wrong password. host, port, err := net.SplitHostPort(s.ServingAddr()) if err != nil { t.Fatal(err) } unicodeUserPgURL := url.URL{ Scheme: "postgres", User: url.User(unicodeUser), Host: net.JoinHostPort(host, port), RawQuery: "sslmode=require", } if err := trivialQuery(unicodeUserPgURL); !testutils.IsError(err, "pq: invalid password") { t.Fatalf("unexpected error: %v", err) } // Supply correct password. unicodeUserPgURL.User = url.UserPassword(unicodeUser, "蟑♫螂") if err := trivialQuery(unicodeUserPgURL); err != nil { t.Fatal(err) } }) } t.Run("TestUserAuth", func(t *testing.T) { testUserPgURL, cleanupFn := sqlutils.PGUrl( t, s.ServingAddr(), "TestPGWireAuth", url.User(server.TestUser)) defer cleanupFn() // No password supplied but valid certificate should result in // successful authentication. if err := trivialQuery(testUserPgURL); err != nil { t.Fatal(err) } // Test case insensitivity for certificate and password authentication. testUserPgURL.User = url.User("TesTUser") if err := trivialQuery(testUserPgURL); err != nil { t.Fatal(err) } // Remove certificates to default to password authentication. testUserPgURL.RawQuery = "sslmode=require" // Even though the correct password is supplied (empty string), this // should fail because we do not support password authentication for // users with empty passwords. if err := trivialQuery(testUserPgURL); !testutils.IsError(err, "pq: invalid password") { t.Fatalf("unexpected error: %v", err) } }) }
func TestSQLNetworkMetrics(t *testing.T) { defer leaktest.AfterTest(t)() s, _, _ := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() // Setup pgwire client. pgURL, cleanupFn := sqlutils.PGUrl( t, s.ServingAddr(), "TestSQLNetworkMetrics", url.User(security.RootUser)) defer cleanupFn() const minbytes = 20 // Make sure we're starting at 0. if _, _, err := checkSQLNetworkMetrics(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 := checkSQLNetworkMetrics(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 = checkSQLNetworkMetrics(s, bytesIn+minbytes, bytesOut+minbytes, 300, 300) if err != nil { t.Fatal(err) } // Verify connection counter. expectConns := func(n int) { testutils.SucceedsSoon(t, func() error { if conns := s.MustGetSQLNetworkCounter(pgwire.MetaConns.Name); conns != int64(n) { return errors.Errorf("connections %d != expected %d", conns, n) } return nil }) } var conns [10]*gosql.DB for i := range conns { var err error if conns[i], err = gosql.Open("postgres", pgURL.String()); err != nil { t.Fatal(err) } defer conns[i].Close() rows, err := conns[i].Query("SELECT 1") if err != nil { t.Fatal(err) } rows.Close() expectConns(i + 1) } for i := len(conns) - 1; i >= 0; i-- { conns[i].Close() expectConns(i) } }
// Test the logic in the sql executor for automatically retrying txns in case of // retriable errors. func TestTxnAutoRetry(t *testing.T) { defer leaktest.AfterTest(t)() aborter := NewTxnAborter() defer aborter.Close(t) params, cmdFilters := createTestServerParams() params.Knobs.SQLExecutor = aborter.executorKnobs() // Disable one phase commits because they cannot be restarted. params.Knobs.Store.(*storage.StoreTestingKnobs).DisableOnePhaseCommits = true s, sqlDB, _ := serverutils.StartServer(t, params) defer s.Stopper().Stop() { pgURL, cleanup := sqlutils.PGUrl(t, s.ServingAddr(), "TestTxnAutoRetry", url.User(security.RootUser)) defer cleanup() if err := aborter.Init(pgURL); err != nil { t.Fatal(err) } } // Make sure all the commands we send in this test are sent over the same connection. // This is a bit of a hack; in Go you're not supposed to have connection state // outside of using a db.Tx. But we can't use a db.Tx here, because we want // to control the batching of BEGIN/COMMIT statements. // This SetMaxOpenConns is pretty shady, it doesn't guarantee that you'll be using // the *same* one connection across calls. A proper solution would be to use a // lib/pq connection directly. As of Feb 2016, there's code in cli/sql_util.go to // do that. sqlDB.SetMaxOpenConns(1) if _, err := sqlDB.Exec(` CREATE DATABASE t; CREATE TABLE t.test (k INT PRIMARY KEY, v TEXT, t DECIMAL); `); err != nil { t.Fatal(err) } // Set up error injection that causes retries. magicVals := createFilterVals(nil, nil) magicVals.restartCounts = map[string]int{ "boulanger": 2, "dromedary": 2, "fajita": 2, "hooly": 2, "josephine": 2, "laureal": 2, } magicVals.abortCounts = map[string]int{ "boulanger": 2, } cleanupFilter := cmdFilters.AppendFilter( func(args storagebase.FilterArgs) *roachpb.Error { if err := injectErrors(args.Req, args.Hdr, magicVals); err != nil { return roachpb.NewErrorWithTxn(err, args.Hdr.Txn) } return nil }, false) if err := aborter.QueueStmtForAbortion( "INSERT INTO t.test(k, v, t) VALUES (1, 'boulanger', cluster_logical_timestamp())", 2 /* abortCount */, true, /* willBeRetriedIbid */ ); err != nil { t.Fatal(err) } if err := aborter.QueueStmtForAbortion( "INSERT INTO t.test(k, v, t) VALUES (2, 'dromedary', cluster_logical_timestamp())", 2 /* abortCount */, true, /* willBeRetriedIbid */ ); err != nil { t.Fatal(err) } if err := aborter.QueueStmtForAbortion( "INSERT INTO t.test(k, v, t) VALUES (3, 'fajita', cluster_logical_timestamp())", 2 /* abortCount */, true, /* willBeRetriedIbid */ ); err != nil { t.Fatal(err) } if err := aborter.QueueStmtForAbortion( "INSERT INTO t.test(k, v, t) VALUES (4, 'hooly', cluster_logical_timestamp())", 2 /* abortCount */, true, /* willBeRetriedIbid */ ); err != nil { t.Fatal(err) } // Test that implicit txns - txns for which we see all the statements and prefixes // of txns (statements batched together with the BEGIN stmt) - are retried. // We also exercise the SQL cluster logical timestamp in here, because // this must be properly propagated across retries. // // The SELECT within the transaction also checks that discarded // intermediate result sets are properly released: the result set it // produces is accounted for by the session monitor, and if it is // not properly released upon a retry the monitor will cause the // server to panic (and thus the test to fail) when the connection // is closed. // // TODO(knz) This test can be made more robust by exposing the // current allocation count in monitor and checking that it has the // same value at the beginning of each retry. if _, err := sqlDB.Exec(` INSERT INTO t.test(k, v, t) VALUES (1, 'boulanger', cluster_logical_timestamp()); BEGIN; SELECT * FROM t.test; INSERT INTO t.test(k, v, t) VALUES (2, 'dromedary', cluster_logical_timestamp()); INSERT INTO t.test(k, v, t) VALUES (3, 'fajita', cluster_logical_timestamp()); END; INSERT INTO t.test(k, v, t) VALUES (4, 'hooly', cluster_logical_timestamp()); BEGIN; INSERT INTO t.test(k, v, t) VALUES (5, 'josephine', cluster_logical_timestamp()); INSERT INTO t.test(k, v, t) VALUES (6, 'laureal', cluster_logical_timestamp()); `); err != nil { t.Fatal(err) } cleanupFilter() checkRestarts(t, magicVals) if _, err := sqlDB.Exec("END"); err != nil { t.Fatal(err) } // Check that the txns succeeded by reading the rows. var count int if err := sqlDB.QueryRow("SELECT count(*) FROM t.test").Scan(&count); err != nil { t.Fatal(err) } if count != 6 { t.Fatalf("Expected 6 rows, got %d", count) } // Now test that we don't retry what we shouldn't: insert an error into a txn // we can't automatically retry (because it spans requests). magicVals = createFilterVals(nil, nil) magicVals.restartCounts = map[string]int{ "hooly": 2, } cleanupFilter = cmdFilters.AppendFilter( func(args storagebase.FilterArgs) *roachpb.Error { if err := injectErrors(args.Req, args.Hdr, magicVals); err != nil { return roachpb.NewErrorWithTxn(err, args.Hdr.Txn) } return nil }, false) defer cleanupFilter() // Start a txn. if _, err := sqlDB.Exec(` DELETE FROM t.test WHERE true; BEGIN; `); err != nil { t.Fatal(err) } // Continue the txn in a new request, which is not retriable. _, err := sqlDB.Exec("INSERT INTO t.test(k, v, t) VALUES (4, 'hooly', cluster_logical_timestamp())") if !testutils.IsError( err, "encountered previous write with future timestamp") { t.Errorf("didn't get expected injected error. Got: %v", err) } }