github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/ccl/changefeedccl/changefeed_test.go (about) 1 // Copyright 2018 The Cockroach Authors. 2 // 3 // Licensed as a CockroachDB Enterprise file under the Cockroach Community 4 // License (the "License"); you may not use this file except in compliance with 5 // the License. You may obtain a copy of the License at 6 // 7 // https://github.com/cockroachdb/cockroach/blob/master/licenses/CCL.txt 8 9 package changefeedccl 10 11 import ( 12 "context" 13 gosql "database/sql" 14 "fmt" 15 "math" 16 "net/url" 17 "regexp" 18 "sort" 19 "strconv" 20 "strings" 21 "sync" 22 "sync/atomic" 23 "testing" 24 "time" 25 26 "github.com/cockroachdb/cockroach-go/crdb" 27 "github.com/cockroachdb/cockroach/pkg/base" 28 "github.com/cockroachdb/cockroach/pkg/ccl/changefeedccl/cdctest" 29 "github.com/cockroachdb/cockroach/pkg/ccl/changefeedccl/changefeedbase" 30 "github.com/cockroachdb/cockroach/pkg/ccl/utilccl" 31 "github.com/cockroachdb/cockroach/pkg/jobs" 32 "github.com/cockroachdb/cockroach/pkg/jobs/jobspb" 33 "github.com/cockroachdb/cockroach/pkg/keys" 34 "github.com/cockroachdb/cockroach/pkg/kv" 35 "github.com/cockroachdb/cockroach/pkg/kv/kvserver" 36 "github.com/cockroachdb/cockroach/pkg/kv/kvserver/kvserverbase" 37 "github.com/cockroachdb/cockroach/pkg/kv/kvserver/protectedts" 38 "github.com/cockroachdb/cockroach/pkg/kv/kvserver/protectedts/ptpb" 39 "github.com/cockroachdb/cockroach/pkg/roachpb" 40 "github.com/cockroachdb/cockroach/pkg/security" 41 "github.com/cockroachdb/cockroach/pkg/server" 42 "github.com/cockroachdb/cockroach/pkg/server/telemetry" 43 "github.com/cockroachdb/cockroach/pkg/sql" 44 "github.com/cockroachdb/cockroach/pkg/sql/distsql" 45 "github.com/cockroachdb/cockroach/pkg/sql/execinfra" 46 "github.com/cockroachdb/cockroach/pkg/sql/flowinfra" 47 "github.com/cockroachdb/cockroach/pkg/sql/sqlbase" 48 "github.com/cockroachdb/cockroach/pkg/storage" 49 "github.com/cockroachdb/cockroach/pkg/testutils" 50 "github.com/cockroachdb/cockroach/pkg/testutils/serverutils" 51 "github.com/cockroachdb/cockroach/pkg/testutils/sqlutils" 52 "github.com/cockroachdb/cockroach/pkg/util" 53 "github.com/cockroachdb/cockroach/pkg/util/encoding" 54 "github.com/cockroachdb/cockroach/pkg/util/hlc" 55 "github.com/cockroachdb/cockroach/pkg/util/leaktest" 56 "github.com/cockroachdb/cockroach/pkg/util/log" 57 "github.com/cockroachdb/cockroach/pkg/util/protoutil" 58 "github.com/cockroachdb/cockroach/pkg/util/retry" 59 "github.com/cockroachdb/cockroach/pkg/util/timeutil" 60 "github.com/cockroachdb/cockroach/pkg/util/uuid" 61 "github.com/cockroachdb/errors" 62 crdberrors "github.com/cockroachdb/errors" 63 "github.com/stretchr/testify/assert" 64 "github.com/stretchr/testify/require" 65 ) 66 67 func TestChangefeedBasics(t *testing.T) { 68 defer leaktest.AfterTest(t)() 69 70 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 71 sqlDB := sqlutils.MakeSQLRunner(db) 72 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 73 sqlDB.Exec(t, `INSERT INTO foo VALUES (0, 'initial')`) 74 sqlDB.Exec(t, `UPSERT INTO foo VALUES (0, 'updated')`) 75 76 foo := feed(t, f, `CREATE CHANGEFEED FOR foo`) 77 defer closeFeed(t, foo) 78 79 // 'initial' is skipped because only the latest value ('updated') is 80 // emitted by the initial scan. 81 assertPayloads(t, foo, []string{ 82 `foo: [0]->{"after": {"a": 0, "b": "updated"}}`, 83 }) 84 85 sqlDB.Exec(t, `INSERT INTO foo VALUES (1, 'a'), (2, 'b')`) 86 assertPayloads(t, foo, []string{ 87 `foo: [1]->{"after": {"a": 1, "b": "a"}}`, 88 `foo: [2]->{"after": {"a": 2, "b": "b"}}`, 89 }) 90 91 sqlDB.Exec(t, `UPSERT INTO foo VALUES (2, 'c'), (3, 'd')`) 92 assertPayloads(t, foo, []string{ 93 `foo: [2]->{"after": {"a": 2, "b": "c"}}`, 94 `foo: [3]->{"after": {"a": 3, "b": "d"}}`, 95 }) 96 97 sqlDB.Exec(t, `DELETE FROM foo WHERE a = 1`) 98 assertPayloads(t, foo, []string{ 99 `foo: [1]->{"after": null}`, 100 }) 101 } 102 103 t.Run(`sinkless`, sinklessTest(testFn)) 104 t.Run(`enterprise`, enterpriseTest(testFn)) 105 t.Run(`cloudstorage`, cloudStorageTest(testFn)) 106 107 // NB running TestChangefeedBasics, which includes a DELETE, with 108 // cloudStorageTest is a regression test for #36994. 109 } 110 111 func TestChangefeedDiff(t *testing.T) { 112 defer leaktest.AfterTest(t)() 113 114 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 115 sqlDB := sqlutils.MakeSQLRunner(db) 116 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 117 sqlDB.Exec(t, `INSERT INTO foo VALUES (0, 'initial')`) 118 sqlDB.Exec(t, `UPSERT INTO foo VALUES (0, 'updated')`) 119 120 foo := feed(t, f, `CREATE CHANGEFEED FOR foo WITH diff`) 121 defer closeFeed(t, foo) 122 123 // 'initial' is skipped because only the latest value ('updated') is 124 // emitted by the initial scan. 125 assertPayloads(t, foo, []string{ 126 `foo: [0]->{"after": {"a": 0, "b": "updated"}, "before": null}`, 127 }) 128 129 sqlDB.Exec(t, `INSERT INTO foo VALUES (1, 'a'), (2, 'b')`) 130 assertPayloads(t, foo, []string{ 131 `foo: [1]->{"after": {"a": 1, "b": "a"}, "before": null}`, 132 `foo: [2]->{"after": {"a": 2, "b": "b"}, "before": null}`, 133 }) 134 135 sqlDB.Exec(t, `UPSERT INTO foo VALUES (2, 'c'), (3, 'd')`) 136 assertPayloads(t, foo, []string{ 137 `foo: [2]->{"after": {"a": 2, "b": "c"}, "before": {"a": 2, "b": "b"}}`, 138 `foo: [3]->{"after": {"a": 3, "b": "d"}, "before": null}`, 139 }) 140 141 sqlDB.Exec(t, `DELETE FROM foo WHERE a = 1`) 142 assertPayloads(t, foo, []string{ 143 `foo: [1]->{"after": null, "before": {"a": 1, "b": "a"}}`, 144 }) 145 146 sqlDB.Exec(t, `INSERT INTO foo VALUES (1, 'new a')`) 147 assertPayloads(t, foo, []string{ 148 `foo: [1]->{"after": {"a": 1, "b": "new a"}, "before": null}`, 149 }) 150 } 151 152 t.Run(`sinkless`, sinklessTest(testFn)) 153 t.Run(`enterprise`, enterpriseTest(testFn)) 154 t.Run(`cloudstorage`, cloudStorageTest(testFn)) 155 } 156 157 func TestChangefeedEnvelope(t *testing.T) { 158 defer leaktest.AfterTest(t)() 159 160 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 161 sqlDB := sqlutils.MakeSQLRunner(db) 162 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 163 sqlDB.Exec(t, `INSERT INTO foo VALUES (1, 'a')`) 164 165 t.Run(`envelope=row`, func(t *testing.T) { 166 foo := feed(t, f, `CREATE CHANGEFEED FOR foo WITH envelope='row'`) 167 defer closeFeed(t, foo) 168 assertPayloads(t, foo, []string{`foo: [1]->{"a": 1, "b": "a"}`}) 169 }) 170 t.Run(`envelope=deprecated_row`, func(t *testing.T) { 171 foo := feed(t, f, `CREATE CHANGEFEED FOR foo WITH envelope='deprecated_row'`) 172 defer closeFeed(t, foo) 173 assertPayloads(t, foo, []string{`foo: [1]->{"a": 1, "b": "a"}`}) 174 }) 175 t.Run(`envelope=key_only`, func(t *testing.T) { 176 foo := feed(t, f, `CREATE CHANGEFEED FOR foo WITH envelope='key_only'`) 177 defer closeFeed(t, foo) 178 assertPayloads(t, foo, []string{`foo: [1]->`}) 179 }) 180 t.Run(`envelope=wrapped`, func(t *testing.T) { 181 foo := feed(t, f, `CREATE CHANGEFEED FOR foo WITH envelope='wrapped'`) 182 defer closeFeed(t, foo) 183 assertPayloads(t, foo, []string{`foo: [1]->{"after": {"a": 1, "b": "a"}}`}) 184 }) 185 t.Run(`envelope=wrapped,key_in_value`, func(t *testing.T) { 186 foo := feed(t, f, `CREATE CHANGEFEED FOR foo WITH key_in_value, envelope='wrapped'`) 187 defer closeFeed(t, foo) 188 assertPayloads(t, foo, []string{`foo: [1]->{"after": {"a": 1, "b": "a"}, "key": [1]}`}) 189 }) 190 } 191 192 t.Run(`sinkless`, sinklessTest(testFn)) 193 t.Run(`enterprise`, enterpriseTest(testFn)) 194 } 195 196 func TestChangefeedMultiTable(t *testing.T) { 197 defer leaktest.AfterTest(t)() 198 199 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 200 sqlDB := sqlutils.MakeSQLRunner(db) 201 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 202 sqlDB.Exec(t, `INSERT INTO foo VALUES (1, 'a')`) 203 sqlDB.Exec(t, `CREATE TABLE bar (a INT PRIMARY KEY, b STRING)`) 204 sqlDB.Exec(t, `INSERT INTO bar VALUES (2, 'b')`) 205 206 fooAndBar := feed(t, f, `CREATE CHANGEFEED FOR foo, bar`) 207 defer closeFeed(t, fooAndBar) 208 209 assertPayloads(t, fooAndBar, []string{ 210 `foo: [1]->{"after": {"a": 1, "b": "a"}}`, 211 `bar: [2]->{"after": {"a": 2, "b": "b"}}`, 212 }) 213 } 214 215 t.Run(`sinkless`, sinklessTest(testFn)) 216 t.Run(`enterprise`, enterpriseTest(testFn)) 217 } 218 219 func TestChangefeedCursor(t *testing.T) { 220 defer leaktest.AfterTest(t)() 221 222 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 223 sqlDB := sqlutils.MakeSQLRunner(db) 224 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 225 226 // To make sure that these timestamps are after 'before' and before 227 // 'after', throw a couple sleeps around them. We round timestamps to 228 // Microsecond granularity for Postgres compatibility, so make the 229 // sleeps 10x that. 230 sqlDB.Exec(t, `INSERT INTO foo VALUES (1, 'before')`) 231 time.Sleep(10 * time.Microsecond) 232 233 var tsLogical string 234 sqlDB.QueryRow(t, `SELECT cluster_logical_timestamp()`).Scan(&tsLogical) 235 var tsClock time.Time 236 sqlDB.QueryRow(t, `SELECT clock_timestamp()`).Scan(&tsClock) 237 238 time.Sleep(10 * time.Microsecond) 239 sqlDB.Exec(t, `INSERT INTO foo VALUES (2, 'after')`) 240 241 fooLogical := feed(t, f, `CREATE CHANGEFEED FOR foo WITH cursor=$1`, tsLogical) 242 defer closeFeed(t, fooLogical) 243 assertPayloads(t, fooLogical, []string{ 244 `foo: [2]->{"after": {"a": 2, "b": "after"}}`, 245 }) 246 247 nanosStr := strconv.FormatInt(tsClock.UnixNano(), 10) 248 fooNanosStr := feed(t, f, `CREATE CHANGEFEED FOR foo WITH cursor=$1`, nanosStr) 249 defer closeFeed(t, fooNanosStr) 250 assertPayloads(t, fooNanosStr, []string{ 251 `foo: [2]->{"after": {"a": 2, "b": "after"}}`, 252 }) 253 254 timeStr := tsClock.Format(`2006-01-02 15:04:05.999999`) 255 fooString := feed(t, f, `CREATE CHANGEFEED FOR foo WITH cursor=$1`, timeStr) 256 defer closeFeed(t, fooString) 257 assertPayloads(t, fooString, []string{ 258 `foo: [2]->{"after": {"a": 2, "b": "after"}}`, 259 }) 260 261 // Check that the cursor is properly hooked up to the job statement 262 // time. The sinkless tests currently don't have a way to get the 263 // statement timestamp, so only verify this for enterprise. 264 if e, ok := fooLogical.(*cdctest.TableFeed); ok { 265 var bytes []byte 266 sqlDB.QueryRow(t, `SELECT payload FROM system.jobs WHERE id=$1`, e.JobID).Scan(&bytes) 267 var payload jobspb.Payload 268 require.NoError(t, protoutil.Unmarshal(bytes, &payload)) 269 require.Equal(t, parseTimeToHLC(t, tsLogical), payload.GetChangefeed().StatementTime) 270 } 271 } 272 273 t.Run(`sinkless`, sinklessTest(testFn)) 274 t.Run(`enterprise`, enterpriseTest(testFn)) 275 } 276 277 func TestChangefeedTimestamps(t *testing.T) { 278 defer leaktest.AfterTest(t)() 279 280 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 281 ctx := context.Background() 282 sqlDB := sqlutils.MakeSQLRunner(db) 283 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY)`) 284 sqlDB.Exec(t, `INSERT INTO foo VALUES (0)`) 285 286 foo := feed(t, f, `CREATE CHANGEFEED FOR foo WITH updated, resolved`) 287 defer closeFeed(t, foo) 288 289 // Grab the first non resolved-timestamp row. 290 var row0 *cdctest.TestFeedMessage 291 for { 292 var err error 293 row0, err = foo.Next() 294 assert.NoError(t, err) 295 if len(row0.Value) > 0 { 296 break 297 } 298 } 299 300 // If this changefeed uses jobs (and thus stores a ChangefeedDetails), get 301 // the statement timestamp from row0 and verify that they match. Otherwise, 302 // just skip the row. 303 if !strings.Contains(t.Name(), `sinkless`) { 304 d, err := foo.(*cdctest.TableFeed).Details() 305 assert.NoError(t, err) 306 expected := `{"after": {"a": 0}, "updated": "` + d.StatementTime.AsOfSystemTime() + `"}` 307 assert.Equal(t, expected, string(row0.Value)) 308 } 309 310 // Assert the remaining key using assertPayloads, since we know the exact 311 // timestamp expected. 312 var ts1 string 313 if err := crdb.ExecuteTx(ctx, db, nil /* txopts */, func(tx *gosql.Tx) error { 314 return tx.QueryRow( 315 `INSERT INTO foo VALUES (1) RETURNING cluster_logical_timestamp()`, 316 ).Scan(&ts1) 317 }); err != nil { 318 t.Fatal(err) 319 } 320 assertPayloads(t, foo, []string{ 321 `foo: [1]->{"after": {"a": 1}, "updated": "` + ts1 + `"}`, 322 }) 323 324 // Check that we eventually get a resolved timestamp greater than ts1. 325 parsed := parseTimeToHLC(t, ts1) 326 for { 327 if resolved := expectResolvedTimestamp(t, foo); parsed.Less(resolved) { 328 break 329 } 330 } 331 } 332 333 t.Run(`sinkless`, sinklessTest(testFn)) 334 t.Run(`enterprise`, enterpriseTest(testFn)) 335 } 336 337 func TestChangefeedResolvedFrequency(t *testing.T) { 338 defer leaktest.AfterTest(t)() 339 340 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 341 sqlDB := sqlutils.MakeSQLRunner(db) 342 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY)`) 343 344 const freq = 10 * time.Millisecond 345 foo := feed(t, f, `CREATE CHANGEFEED FOR foo WITH resolved=$1`, freq.String()) 346 defer closeFeed(t, foo) 347 348 // We get each resolved timestamp notification once in each partition. 349 // Grab the first `2 * #partitions`, sort because we might get all from 350 // one partition first, and compare the first and last. 351 resolved := make([]hlc.Timestamp, 2*len(foo.Partitions())) 352 for i := range resolved { 353 resolved[i] = expectResolvedTimestamp(t, foo) 354 } 355 sort.Slice(resolved, func(i, j int) bool { return resolved[i].Less(resolved[j]) }) 356 first, last := resolved[0], resolved[len(resolved)-1] 357 358 if d := last.GoTime().Sub(first.GoTime()); d < freq { 359 t.Errorf(`expected %s between resolved timestamps, but got %s`, freq, d) 360 } 361 } 362 363 t.Run(`sinkless`, sinklessTest(testFn)) 364 t.Run(`enterprise`, enterpriseTest(testFn)) 365 } 366 367 // Test how Changefeeds react to schema changes that do not require a backfill 368 // operation. 369 func TestChangefeedInitialScan(t *testing.T) { 370 defer leaktest.AfterTest(t)() 371 scope := log.Scope(t) 372 defer scope.Close(t) 373 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 374 sqlDB := sqlutils.MakeSQLRunner(db) 375 sqlDB.Exec(t, `SET CLUSTER SETTING kv.closed_timestamp.target_duration = '10ms'`) 376 377 t.Run(`no cursor - no initial scan`, func(t *testing.T) { 378 sqlDB.Exec(t, `CREATE TABLE no_initial_scan (a INT PRIMARY KEY)`) 379 sqlDB.Exec(t, `INSERT INTO no_initial_scan VALUES (1)`) 380 381 noInitialScan := feed(t, f, `CREATE CHANGEFEED FOR no_initial_scan `+ 382 `WITH no_initial_scan, resolved='10ms'`) 383 defer closeFeed(t, noInitialScan) 384 expectResolvedTimestamp(t, noInitialScan) 385 sqlDB.Exec(t, `INSERT INTO no_initial_scan VALUES (2)`) 386 assertPayloads(t, noInitialScan, []string{ 387 `no_initial_scan: [2]->{"after": {"a": 2}}`, 388 }) 389 }) 390 391 t.Run(`cursor - with initial scan`, func(t *testing.T) { 392 sqlDB.Exec(t, `CREATE TABLE initial_scan (a INT PRIMARY KEY)`) 393 sqlDB.Exec(t, `INSERT INTO initial_scan VALUES (1), (2), (3)`) 394 var tsStr string 395 var i int 396 sqlDB.QueryRow(t, `SELECT count(*), cluster_logical_timestamp() from initial_scan`).Scan(&i, &tsStr) 397 initialScan := feed(t, f, `CREATE CHANGEFEED FOR initial_scan `+ 398 `WITH initial_scan, resolved='10ms', cursor='`+tsStr+`'`) 399 defer closeFeed(t, initialScan) 400 assertPayloads(t, initialScan, []string{ 401 `initial_scan: [1]->{"after": {"a": 1}}`, 402 `initial_scan: [2]->{"after": {"a": 2}}`, 403 `initial_scan: [3]->{"after": {"a": 3}}`, 404 }) 405 sqlDB.Exec(t, `INSERT INTO initial_scan VALUES (4)`) 406 assertPayloads(t, initialScan, []string{ 407 `initial_scan: [4]->{"after": {"a": 4}}`, 408 }) 409 }) 410 } 411 412 t.Run(`sinkless`, sinklessTest(testFn)) 413 t.Run(`enterprise`, enterpriseTest(testFn)) 414 } 415 416 // Test how Changefeeds react to schema changes that do not require a backfill 417 // operation. 418 func TestChangefeedSchemaChangeNoBackfill(t *testing.T) { 419 defer leaktest.AfterTest(t)() 420 scope := log.Scope(t) 421 defer scope.Close(t) 422 423 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 424 sqlDB := sqlutils.MakeSQLRunner(db) 425 426 // Schema changes that predate the changefeed. 427 t.Run(`historical`, func(t *testing.T) { 428 sqlDB.Exec(t, `CREATE TABLE historical (a INT PRIMARY KEY, b STRING DEFAULT 'before')`) 429 var start string 430 sqlDB.QueryRow(t, `SELECT cluster_logical_timestamp()`).Scan(&start) 431 sqlDB.Exec(t, `INSERT INTO historical (a, b) VALUES (0, '0')`) 432 sqlDB.Exec(t, `INSERT INTO historical (a) VALUES (1)`) 433 sqlDB.Exec(t, `ALTER TABLE historical ALTER COLUMN b SET DEFAULT 'after'`) 434 sqlDB.Exec(t, `INSERT INTO historical (a) VALUES (2)`) 435 sqlDB.Exec(t, `ALTER TABLE historical ADD COLUMN c INT`) 436 sqlDB.Exec(t, `INSERT INTO historical (a) VALUES (3)`) 437 sqlDB.Exec(t, `INSERT INTO historical (a, c) VALUES (4, 14)`) 438 historical := feed(t, f, `CREATE CHANGEFEED FOR historical WITH cursor=$1`, start) 439 defer closeFeed(t, historical) 440 assertPayloads(t, historical, []string{ 441 `historical: [0]->{"after": {"a": 0, "b": "0"}}`, 442 `historical: [1]->{"after": {"a": 1, "b": "before"}}`, 443 `historical: [2]->{"after": {"a": 2, "b": "after"}}`, 444 `historical: [3]->{"after": {"a": 3, "b": "after", "c": null}}`, 445 `historical: [4]->{"after": {"a": 4, "b": "after", "c": 14}}`, 446 }) 447 }) 448 449 t.Run(`add column`, func(t *testing.T) { 450 // NB: the default is a nullable column 451 sqlDB.Exec(t, `CREATE TABLE add_column (a INT PRIMARY KEY)`) 452 sqlDB.Exec(t, `INSERT INTO add_column VALUES (1)`) 453 addColumn := feed(t, f, `CREATE CHANGEFEED FOR add_column`) 454 defer closeFeed(t, addColumn) 455 assertPayloads(t, addColumn, []string{ 456 `add_column: [1]->{"after": {"a": 1}}`, 457 }) 458 sqlDB.Exec(t, `ALTER TABLE add_column ADD COLUMN b STRING`) 459 sqlDB.Exec(t, `INSERT INTO add_column VALUES (2, '2')`) 460 assertPayloads(t, addColumn, []string{ 461 `add_column: [2]->{"after": {"a": 2, "b": "2"}}`, 462 }) 463 }) 464 465 t.Run(`rename column`, func(t *testing.T) { 466 sqlDB.Exec(t, `CREATE TABLE rename_column (a INT PRIMARY KEY, b STRING)`) 467 sqlDB.Exec(t, `INSERT INTO rename_column VALUES (1, '1')`) 468 renameColumn := feed(t, f, `CREATE CHANGEFEED FOR rename_column`) 469 defer closeFeed(t, renameColumn) 470 assertPayloads(t, renameColumn, []string{ 471 `rename_column: [1]->{"after": {"a": 1, "b": "1"}}`, 472 }) 473 sqlDB.Exec(t, `ALTER TABLE rename_column RENAME COLUMN b TO c`) 474 sqlDB.Exec(t, `INSERT INTO rename_column VALUES (2, '2')`) 475 assertPayloads(t, renameColumn, []string{ 476 `rename_column: [2]->{"after": {"a": 2, "c": "2"}}`, 477 }) 478 }) 479 480 t.Run(`add default`, func(t *testing.T) { 481 sqlDB.Exec(t, `CREATE TABLE add_default (a INT PRIMARY KEY, b STRING)`) 482 sqlDB.Exec(t, `INSERT INTO add_default (a, b) VALUES (1, '1')`) 483 addDefault := feed(t, f, `CREATE CHANGEFEED FOR add_default`) 484 defer closeFeed(t, addDefault) 485 sqlDB.Exec(t, `ALTER TABLE add_default ALTER COLUMN b SET DEFAULT 'd'`) 486 sqlDB.Exec(t, `INSERT INTO add_default (a) VALUES (2)`) 487 assertPayloads(t, addDefault, []string{ 488 `add_default: [1]->{"after": {"a": 1, "b": "1"}}`, 489 `add_default: [2]->{"after": {"a": 2, "b": "d"}}`, 490 }) 491 }) 492 493 t.Run(`drop default`, func(t *testing.T) { 494 sqlDB.Exec(t, `CREATE TABLE drop_default (a INT PRIMARY KEY, b STRING DEFAULT 'd')`) 495 sqlDB.Exec(t, `INSERT INTO drop_default (a) VALUES (1)`) 496 dropDefault := feed(t, f, `CREATE CHANGEFEED FOR drop_default`) 497 defer closeFeed(t, dropDefault) 498 sqlDB.Exec(t, `ALTER TABLE drop_default ALTER COLUMN b DROP DEFAULT`) 499 sqlDB.Exec(t, `INSERT INTO drop_default (a) VALUES (2)`) 500 assertPayloads(t, dropDefault, []string{ 501 `drop_default: [1]->{"after": {"a": 1, "b": "d"}}`, 502 `drop_default: [2]->{"after": {"a": 2, "b": null}}`, 503 }) 504 }) 505 506 t.Run(`drop not null`, func(t *testing.T) { 507 sqlDB.Exec(t, `CREATE TABLE drop_notnull (a INT PRIMARY KEY, b STRING NOT NULL)`) 508 sqlDB.Exec(t, `INSERT INTO drop_notnull VALUES (1, '1')`) 509 dropNotNull := feed(t, f, `CREATE CHANGEFEED FOR drop_notnull`) 510 defer closeFeed(t, dropNotNull) 511 sqlDB.Exec(t, `ALTER TABLE drop_notnull ALTER b DROP NOT NULL`) 512 sqlDB.Exec(t, `INSERT INTO drop_notnull VALUES (2, NULL)`) 513 assertPayloads(t, dropNotNull, []string{ 514 `drop_notnull: [1]->{"after": {"a": 1, "b": "1"}}`, 515 `drop_notnull: [2]->{"after": {"a": 2, "b": null}}`, 516 }) 517 }) 518 519 t.Run(`checks`, func(t *testing.T) { 520 sqlDB.Exec(t, `CREATE TABLE checks (a INT PRIMARY KEY)`) 521 sqlDB.Exec(t, `INSERT INTO checks VALUES (1)`) 522 checks := feed(t, f, `CREATE CHANGEFEED FOR checks`) 523 defer closeFeed(t, checks) 524 sqlDB.Exec(t, `ALTER TABLE checks ADD CONSTRAINT c CHECK (a < 5) NOT VALID`) 525 sqlDB.Exec(t, `INSERT INTO checks VALUES (2)`) 526 sqlDB.Exec(t, `ALTER TABLE checks VALIDATE CONSTRAINT c`) 527 sqlDB.Exec(t, `INSERT INTO checks VALUES (3)`) 528 sqlDB.Exec(t, `ALTER TABLE checks DROP CONSTRAINT c`) 529 sqlDB.Exec(t, `INSERT INTO checks VALUES (6)`) 530 assertPayloads(t, checks, []string{ 531 `checks: [1]->{"after": {"a": 1}}`, 532 `checks: [2]->{"after": {"a": 2}}`, 533 `checks: [3]->{"after": {"a": 3}}`, 534 `checks: [6]->{"after": {"a": 6}}`, 535 }) 536 }) 537 538 t.Run(`add index`, func(t *testing.T) { 539 sqlDB.Exec(t, `CREATE TABLE add_index (a INT PRIMARY KEY, b STRING)`) 540 sqlDB.Exec(t, `INSERT INTO add_index VALUES (1, '1')`) 541 addIndex := feed(t, f, `CREATE CHANGEFEED FOR add_index`) 542 defer closeFeed(t, addIndex) 543 sqlDB.Exec(t, `CREATE INDEX b_idx ON add_index (b)`) 544 sqlDB.Exec(t, `SELECT * FROM add_index@b_idx`) 545 sqlDB.Exec(t, `INSERT INTO add_index VALUES (2, '2')`) 546 assertPayloads(t, addIndex, []string{ 547 `add_index: [1]->{"after": {"a": 1, "b": "1"}}`, 548 `add_index: [2]->{"after": {"a": 2, "b": "2"}}`, 549 }) 550 }) 551 552 t.Run(`unique`, func(t *testing.T) { 553 sqlDB.Exec(t, `CREATE TABLE "unique" (a INT PRIMARY KEY, b STRING)`) 554 sqlDB.Exec(t, `INSERT INTO "unique" VALUES (1, '1')`) 555 unique := feed(t, f, `CREATE CHANGEFEED FOR "unique"`) 556 defer closeFeed(t, unique) 557 sqlDB.Exec(t, `ALTER TABLE "unique" ADD CONSTRAINT u UNIQUE (b)`) 558 sqlDB.Exec(t, `INSERT INTO "unique" VALUES (2, '2')`) 559 assertPayloads(t, unique, []string{ 560 `unique: [1]->{"after": {"a": 1, "b": "1"}}`, 561 `unique: [2]->{"after": {"a": 2, "b": "2"}}`, 562 }) 563 }) 564 565 t.Run(`alter default`, func(t *testing.T) { 566 sqlDB.Exec( 567 t, `CREATE TABLE alter_default (a INT PRIMARY KEY, b STRING DEFAULT 'before')`) 568 sqlDB.Exec(t, `INSERT INTO alter_default (a) VALUES (1)`) 569 alterDefault := feed(t, f, `CREATE CHANGEFEED FOR alter_default`) 570 defer closeFeed(t, alterDefault) 571 sqlDB.Exec(t, `ALTER TABLE alter_default ALTER COLUMN b SET DEFAULT 'after'`) 572 sqlDB.Exec(t, `INSERT INTO alter_default (a) VALUES (2)`) 573 assertPayloads(t, alterDefault, []string{ 574 `alter_default: [1]->{"after": {"a": 1, "b": "before"}}`, 575 `alter_default: [2]->{"after": {"a": 2, "b": "after"}}`, 576 }) 577 }) 578 579 // Test adding a column with explicitly setting the default value to be NULL 580 t.Run(`add column with DEFAULT NULL`, func(t *testing.T) { 581 sqlDB.Exec(t, `CREATE TABLE t (id INT PRIMARY KEY)`) 582 sqlDB.Exec(t, `INSERT INTO t VALUES (1)`) 583 defaultNull := feed(t, f, `CREATE CHANGEFEED FOR t`) 584 defer closeFeed(t, defaultNull) 585 sqlDB.Exec(t, `ALTER TABLE t ADD COLUMN c INT DEFAULT NULL`) 586 sqlDB.Exec(t, `INSERT INTO t VALUES (2, 2)`) 587 assertPayloads(t, defaultNull, []string{ 588 // Verify that no column backfill occurs 589 `t: [1]->{"after": {"id": 1}}`, 590 `t: [2]->{"after": {"c": 2, "id": 2}}`, 591 }) 592 }) 593 } 594 595 t.Run(`sinkless`, sinklessTest(testFn)) 596 t.Run(`enterprise`, enterpriseTest(testFn)) 597 log.Flush() 598 entries, err := log.FetchEntriesFromFiles(0, math.MaxInt64, 1, regexp.MustCompile("cdc ux violation")) 599 if err != nil { 600 t.Fatal(err) 601 } 602 if len(entries) > 0 { 603 t.Fatalf("Found violation of CDC's guarantees: %v", entries) 604 } 605 } 606 607 // Test schema changes that require a backfill when the backfill option is 608 // allowed. 609 func TestChangefeedSchemaChangeAllowBackfill(t *testing.T) { 610 defer leaktest.AfterTest(t)() 611 scope := log.Scope(t) 612 defer scope.Close(t) 613 614 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 615 sqlDB := sqlutils.MakeSQLRunner(db) 616 617 // Expected semantics: 618 // 619 // 1) DROP COLUMN 620 // If the table descriptor is at version 1 when the `ALTER TABLE` stmt is issued, 621 // we expect the changefeed level backfill to be triggered at the `ModificationTime` of 622 // version 2 of the said descriptor. This is because this is the descriptor 623 // version at which the dropped column stops being visible to SELECTs. Note that 624 // this means we will see row updates resulting from the schema-change level 625 // backfill _after_ the changefeed level backfill. 626 // 627 // 2) ADD COLUMN WITH DEFAULT & ADD COLUMN AS ... STORED 628 // If the table descriptor is at version 1 when the `ALTER TABLE` stmt is issued, 629 // we expect the changefeed level backfill to be triggered at the 630 // `ModificationTime` of version 4 of said descriptor. This is because this is the 631 // descriptor version which makes the schema-change level backfill for the 632 // newly-added column public. This means we wil see row updates resulting from the 633 // schema-change level backfill _before_ the changefeed level backfill. 634 635 t.Run(`add column with default`, func(t *testing.T) { 636 sqlDB.Exec(t, `CREATE TABLE add_column_def (a INT PRIMARY KEY)`) 637 sqlDB.Exec(t, `INSERT INTO add_column_def VALUES (1)`) 638 sqlDB.Exec(t, `INSERT INTO add_column_def VALUES (2)`) 639 addColumnDef := feed(t, f, `CREATE CHANGEFEED FOR add_column_def WITH updated`) 640 defer closeFeed(t, addColumnDef) 641 assertPayloadsStripTs(t, addColumnDef, []string{ 642 `add_column_def: [1]->{"after": {"a": 1}}`, 643 `add_column_def: [2]->{"after": {"a": 2}}`, 644 }) 645 sqlDB.Exec(t, `ALTER TABLE add_column_def ADD COLUMN b STRING DEFAULT 'd'`) 646 ts := fetchDescVersionModificationTime(t, db, f, `add_column_def`, 4) 647 // Schema change backfill 648 assertPayloadsStripTs(t, addColumnDef, []string{ 649 `add_column_def: [1]->{"after": {"a": 1}}`, 650 `add_column_def: [2]->{"after": {"a": 2}}`, 651 }) 652 // Changefeed level backfill 653 assertPayloads(t, addColumnDef, []string{ 654 fmt.Sprintf(`add_column_def: [1]->{"after": {"a": 1, "b": "d"}, "updated": "%s"}`, 655 ts.AsOfSystemTime()), 656 fmt.Sprintf(`add_column_def: [2]->{"after": {"a": 2, "b": "d"}, "updated": "%s"}`, 657 ts.AsOfSystemTime()), 658 }) 659 }) 660 661 t.Run(`add column computed`, func(t *testing.T) { 662 sqlDB.Exec(t, `CREATE TABLE add_col_comp (a INT PRIMARY KEY, b INT AS (a + 5) STORED)`) 663 sqlDB.Exec(t, `INSERT INTO add_col_comp VALUES (1)`) 664 sqlDB.Exec(t, `INSERT INTO add_col_comp (a) VALUES (2)`) 665 addColComp := feed(t, f, `CREATE CHANGEFEED FOR add_col_comp WITH updated`) 666 defer closeFeed(t, addColComp) 667 assertPayloadsStripTs(t, addColComp, []string{ 668 `add_col_comp: [1]->{"after": {"a": 1, "b": 6}}`, 669 `add_col_comp: [2]->{"after": {"a": 2, "b": 7}}`, 670 }) 671 sqlDB.Exec(t, `ALTER TABLE add_col_comp ADD COLUMN c INT AS (a + 10) STORED`) 672 assertPayloadsStripTs(t, addColComp, []string{ 673 `add_col_comp: [1]->{"after": {"a": 1, "b": 6}}`, 674 `add_col_comp: [2]->{"after": {"a": 2, "b": 7}}`, 675 }) 676 ts := fetchDescVersionModificationTime(t, db, f, `add_col_comp`, 4) 677 assertPayloads(t, addColComp, []string{ 678 fmt.Sprintf(`add_col_comp: [1]->{"after": {"a": 1, "b": 6, "c": 11}, "updated": "%s"}`, 679 ts.AsOfSystemTime()), 680 fmt.Sprintf(`add_col_comp: [2]->{"after": {"a": 2, "b": 7, "c": 12}, "updated": "%s"}`, 681 ts.AsOfSystemTime()), 682 }) 683 }) 684 685 t.Run(`drop column`, func(t *testing.T) { 686 sqlDB.Exec(t, `CREATE TABLE drop_column (a INT PRIMARY KEY, b STRING)`) 687 sqlDB.Exec(t, `INSERT INTO drop_column VALUES (1, '1')`) 688 sqlDB.Exec(t, `INSERT INTO drop_column VALUES (2, '2')`) 689 dropColumn := feed(t, f, `CREATE CHANGEFEED FOR drop_column WITH updated`) 690 defer closeFeed(t, dropColumn) 691 assertPayloadsStripTs(t, dropColumn, []string{ 692 `drop_column: [1]->{"after": {"a": 1, "b": "1"}}`, 693 `drop_column: [2]->{"after": {"a": 2, "b": "2"}}`, 694 }) 695 sqlDB.Exec(t, `ALTER TABLE drop_column DROP COLUMN b`) 696 ts := fetchDescVersionModificationTime(t, db, f, `drop_column`, 2) 697 assertPayloads(t, dropColumn, []string{ 698 fmt.Sprintf(`drop_column: [1]->{"after": {"a": 1}, "updated": "%s"}`, ts.AsOfSystemTime()), 699 fmt.Sprintf(`drop_column: [2]->{"after": {"a": 2}, "updated": "%s"}`, ts.AsOfSystemTime()), 700 }) 701 sqlDB.Exec(t, `INSERT INTO drop_column VALUES (3)`) 702 assertPayloadsStripTs(t, dropColumn, []string{ 703 `drop_column: [3]->{"after": {"a": 3}}`, 704 `drop_column: [1]->{"after": {"a": 1}}`, 705 `drop_column: [2]->{"after": {"a": 2}}`, 706 }) 707 }) 708 709 t.Run(`multiple alters`, func(t *testing.T) { 710 sqlDB.Exec(t, `CREATE TABLE multiple_alters (a INT PRIMARY KEY, b STRING)`) 711 sqlDB.Exec(t, `INSERT INTO multiple_alters VALUES (1, '1')`) 712 sqlDB.Exec(t, `INSERT INTO multiple_alters VALUES (2, '2')`) 713 714 // Set up a hook to pause the changfeed on the next emit. 715 var wg sync.WaitGroup 716 waitSinkHook := func(_ context.Context) error { 717 wg.Wait() 718 return nil 719 } 720 knobs := f.Server().(*server.TestServer).Cfg.TestingKnobs. 721 DistSQL.(*execinfra.TestingKnobs). 722 Changefeed.(*TestingKnobs) 723 knobs.BeforeEmitRow = waitSinkHook 724 725 multipleAlters := feed(t, f, `CREATE CHANGEFEED FOR multiple_alters WITH updated`) 726 defer closeFeed(t, multipleAlters) 727 assertPayloadsStripTs(t, multipleAlters, []string{ 728 `multiple_alters: [1]->{"after": {"a": 1, "b": "1"}}`, 729 `multiple_alters: [2]->{"after": {"a": 2, "b": "2"}}`, 730 }) 731 732 // Wait on the next emit, queue up three ALTERs. The next poll process 733 // will see all of them at once. 734 wg.Add(1) 735 waitForSchemaChange(t, sqlDB, `ALTER TABLE multiple_alters DROP COLUMN b`) 736 waitForSchemaChange(t, sqlDB, `ALTER TABLE multiple_alters ADD COLUMN c STRING DEFAULT 'cee'`) 737 waitForSchemaChange(t, sqlDB, `ALTER TABLE multiple_alters ADD COLUMN d STRING DEFAULT 'dee'`) 738 wg.Done() 739 740 ts := fetchDescVersionModificationTime(t, db, f, `multiple_alters`, 2) 741 // Changefeed level backfill for DROP COLUMN b. 742 assertPayloads(t, multipleAlters, []string{ 743 fmt.Sprintf(`multiple_alters: [1]->{"after": {"a": 1}, "updated": "%s"}`, ts.AsOfSystemTime()), 744 fmt.Sprintf(`multiple_alters: [2]->{"after": {"a": 2}, "updated": "%s"}`, ts.AsOfSystemTime()), 745 }) 746 assertPayloadsStripTs(t, multipleAlters, []string{ 747 // Schema-change backfill for DROP COLUMN b. 748 `multiple_alters: [1]->{"after": {"a": 1}}`, 749 `multiple_alters: [2]->{"after": {"a": 2}}`, 750 // Schema-change backfill for ADD COLUMN c. 751 `multiple_alters: [1]->{"after": {"a": 1}}`, 752 `multiple_alters: [2]->{"after": {"a": 2}}`, 753 }) 754 ts = fetchDescVersionModificationTime(t, db, f, `multiple_alters`, 7) 755 // Changefeed level backfill for ADD COLUMN c. 756 assertPayloads(t, multipleAlters, []string{ 757 fmt.Sprintf(`multiple_alters: [1]->{"after": {"a": 1, "c": "cee"}, "updated": "%s"}`, ts.AsOfSystemTime()), 758 fmt.Sprintf(`multiple_alters: [2]->{"after": {"a": 2, "c": "cee"}, "updated": "%s"}`, ts.AsOfSystemTime()), 759 }) 760 // Schema change level backfill for ADD COLUMN d. 761 assertPayloadsStripTs(t, multipleAlters, []string{ 762 `multiple_alters: [1]->{"after": {"a": 1, "c": "cee"}}`, 763 `multiple_alters: [2]->{"after": {"a": 2, "c": "cee"}}`, 764 }) 765 ts = fetchDescVersionModificationTime(t, db, f, `multiple_alters`, 10) 766 // Changefeed level backfill for ADD COLUMN d. 767 assertPayloads(t, multipleAlters, []string{ 768 // Backfill no-ops for column D (C schema change is complete) 769 // TODO(dan): Track duplicates more precisely in sinklessFeed/tableFeed. 770 // Scan output for column C 771 fmt.Sprintf(`multiple_alters: [1]->{"after": {"a": 1, "c": "cee", "d": "dee"}, "updated": "%s"}`, ts.AsOfSystemTime()), 772 fmt.Sprintf(`multiple_alters: [2]->{"after": {"a": 2, "c": "cee", "d": "dee"}, "updated": "%s"}`, ts.AsOfSystemTime()), 773 }) 774 }) 775 } 776 777 t.Run(`sinkless`, sinklessTest(testFn)) 778 t.Run(`enterprise`, enterpriseTest(testFn)) 779 log.Flush() 780 entries, err := log.FetchEntriesFromFiles(0, math.MaxInt64, 1, regexp.MustCompile("cdc ux violation")) 781 if err != nil { 782 t.Fatal(err) 783 } 784 if len(entries) > 0 { 785 t.Fatalf("Found violation of CDC's guarantees: %v", entries) 786 } 787 } 788 789 // fetchDescVersionModificationTime fetches the `ModificationTime` of the specified 790 // `version` of `tableName`'s table descriptor. 791 func fetchDescVersionModificationTime( 792 t testing.TB, db *gosql.DB, f cdctest.TestFeedFactory, tableName string, version int, 793 ) hlc.Timestamp { 794 tblKey := keys.SystemSQLCodec.TablePrefix(keys.DescriptorTableID) 795 header := roachpb.RequestHeader{ 796 Key: tblKey, 797 EndKey: tblKey.PrefixEnd(), 798 } 799 dropColTblID := sqlutils.QueryTableID(t, db, `d`, "public", tableName) 800 req := &roachpb.ExportRequest{ 801 RequestHeader: header, 802 MVCCFilter: roachpb.MVCCFilter_All, 803 StartTime: hlc.Timestamp{}, 804 ReturnSST: true, 805 } 806 clock := hlc.NewClock(hlc.UnixNano, time.Minute) 807 hh := roachpb.Header{Timestamp: clock.Now()} 808 res, pErr := kv.SendWrappedWith(context.Background(), 809 f.Server().DB().NonTransactionalSender(), hh, req) 810 if pErr != nil { 811 t.Fatal(pErr.GoError()) 812 } 813 for _, file := range res.(*roachpb.ExportResponse).Files { 814 it, err := storage.NewMemSSTIterator(file.SST, false /* verify */) 815 if err != nil { 816 t.Fatal(err) 817 } 818 defer it.Close() 819 for it.SeekGE(storage.NilKey); ; it.Next() { 820 if ok, err := it.Valid(); err != nil { 821 t.Fatal(err) 822 } else if !ok { 823 continue 824 } 825 k := it.UnsafeKey() 826 remaining, _, _, err := keys.SystemSQLCodec.DecodeIndexPrefix(k.Key) 827 if err != nil { 828 t.Fatal(err) 829 } 830 _, tableID, err := encoding.DecodeUvarintAscending(remaining) 831 if err != nil { 832 t.Fatal(err) 833 } 834 if tableID != uint64(dropColTblID) { 835 continue 836 } 837 unsafeValue := it.UnsafeValue() 838 if unsafeValue == nil { 839 t.Fatal(errors.New(`value was dropped or truncated`)) 840 } 841 value := roachpb.Value{RawBytes: unsafeValue} 842 var desc sqlbase.Descriptor 843 if err := value.GetProto(&desc); err != nil { 844 t.Fatal(err) 845 } 846 if tableDesc := desc.Table(k.Timestamp); tableDesc != nil { 847 if int(tableDesc.Version) == version { 848 return tableDesc.ModificationTime 849 } 850 } 851 } 852 } 853 t.Fatal(errors.New(`couldn't find table desc for given version`)) 854 return hlc.Timestamp{} 855 } 856 857 // Regression test for #34314 858 func TestChangefeedAfterSchemaChangeBackfill(t *testing.T) { 859 defer leaktest.AfterTest(t)() 860 scope := log.Scope(t) 861 defer scope.Close(t) 862 863 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 864 sqlDB := sqlutils.MakeSQLRunner(db) 865 sqlDB.Exec(t, `CREATE TABLE after_backfill (a INT PRIMARY KEY)`) 866 sqlDB.Exec(t, `INSERT INTO after_backfill VALUES (0)`) 867 sqlDB.Exec(t, `ALTER TABLE after_backfill ADD COLUMN b INT DEFAULT 1`) 868 sqlDB.Exec(t, `INSERT INTO after_backfill VALUES (2, 3)`) 869 afterBackfill := feed(t, f, `CREATE CHANGEFEED FOR after_backfill`) 870 defer closeFeed(t, afterBackfill) 871 assertPayloads(t, afterBackfill, []string{ 872 `after_backfill: [0]->{"after": {"a": 0, "b": 1}}`, 873 `after_backfill: [2]->{"after": {"a": 2, "b": 3}}`, 874 }) 875 } 876 877 t.Run(`sinkless`, sinklessTest(testFn)) 878 t.Run(`enterprise`, enterpriseTest(testFn)) 879 log.Flush() 880 entries, err := log.FetchEntriesFromFiles(0, math.MaxInt64, 1, regexp.MustCompile("cdc ux violation")) 881 if err != nil { 882 t.Fatal(err) 883 } 884 if len(entries) > 0 { 885 t.Fatalf("Found violation of CDC's guarantees: %v", entries) 886 } 887 } 888 889 func TestChangefeedInterleaved(t *testing.T) { 890 defer leaktest.AfterTest(t)() 891 892 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 893 sqlDB := sqlutils.MakeSQLRunner(db) 894 895 sqlDB.Exec(t, `CREATE TABLE grandparent (a INT PRIMARY KEY, b STRING)`) 896 sqlDB.Exec(t, `INSERT INTO grandparent VALUES (0, 'grandparent-0')`) 897 grandparent := feed(t, f, `CREATE CHANGEFEED FOR grandparent`) 898 defer closeFeed(t, grandparent) 899 assertPayloads(t, grandparent, []string{ 900 `grandparent: [0]->{"after": {"a": 0, "b": "grandparent-0"}}`, 901 }) 902 903 sqlDB.Exec(t, 904 `CREATE TABLE parent (a INT PRIMARY KEY, b STRING) `+ 905 `INTERLEAVE IN PARENT grandparent (a)`) 906 sqlDB.Exec(t, `INSERT INTO grandparent VALUES (1, 'grandparent-1')`) 907 sqlDB.Exec(t, `INSERT INTO parent VALUES (1, 'parent-1')`) 908 parent := feed(t, f, `CREATE CHANGEFEED FOR parent`) 909 defer closeFeed(t, parent) 910 assertPayloads(t, grandparent, []string{ 911 `grandparent: [1]->{"after": {"a": 1, "b": "grandparent-1"}}`, 912 }) 913 assertPayloads(t, parent, []string{ 914 `parent: [1]->{"after": {"a": 1, "b": "parent-1"}}`, 915 }) 916 917 sqlDB.Exec(t, 918 `CREATE TABLE child (a INT PRIMARY KEY, b STRING) INTERLEAVE IN PARENT parent (a)`) 919 sqlDB.Exec(t, `INSERT INTO grandparent VALUES (2, 'grandparent-2')`) 920 sqlDB.Exec(t, `INSERT INTO parent VALUES (2, 'parent-2')`) 921 sqlDB.Exec(t, `INSERT INTO child VALUES (2, 'child-2')`) 922 child := feed(t, f, `CREATE CHANGEFEED FOR child`) 923 defer closeFeed(t, child) 924 assertPayloads(t, grandparent, []string{ 925 `grandparent: [2]->{"after": {"a": 2, "b": "grandparent-2"}}`, 926 }) 927 assertPayloads(t, parent, []string{ 928 `parent: [2]->{"after": {"a": 2, "b": "parent-2"}}`, 929 }) 930 assertPayloads(t, child, []string{ 931 `child: [2]->{"after": {"a": 2, "b": "child-2"}}`, 932 }) 933 } 934 935 t.Run(`sinkless`, sinklessTest(testFn)) 936 t.Run(`enterprise`, enterpriseTest(testFn)) 937 } 938 939 func TestChangefeedColumnFamily(t *testing.T) { 940 defer leaktest.AfterTest(t)() 941 942 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 943 sqlDB := sqlutils.MakeSQLRunner(db) 944 945 // Table with 2 column families. 946 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING, FAMILY (a), FAMILY (b))`) 947 if strings.Contains(t.Name(), `enterprise`) { 948 sqlDB.ExpectErr(t, `exactly 1 column family`, `CREATE CHANGEFEED FOR foo`) 949 } else { 950 sqlDB.ExpectErr(t, `exactly 1 column family`, `EXPERIMENTAL CHANGEFEED FOR foo`) 951 } 952 953 // Table with a second column family added after the changefeed starts. 954 sqlDB.Exec(t, `CREATE TABLE bar (a INT PRIMARY KEY, FAMILY f_a (a))`) 955 sqlDB.Exec(t, `INSERT INTO bar VALUES (0)`) 956 bar := feed(t, f, `CREATE CHANGEFEED FOR bar`) 957 defer closeFeed(t, bar) 958 assertPayloads(t, bar, []string{ 959 `bar: [0]->{"after": {"a": 0}}`, 960 }) 961 sqlDB.Exec(t, `ALTER TABLE bar ADD COLUMN b STRING CREATE FAMILY f_b`) 962 sqlDB.Exec(t, `INSERT INTO bar VALUES (1)`) 963 if _, err := bar.Next(); !testutils.IsError(err, `exactly 1 column family`) { 964 t.Errorf(`expected "exactly 1 column family" error got: %+v`, err) 965 } 966 } 967 968 t.Run(`sinkless`, sinklessTest(testFn)) 969 t.Run(`enterprise`, enterpriseTest(testFn)) 970 } 971 972 func TestChangefeedStopOnSchemaChange(t *testing.T) { 973 defer leaktest.AfterTest(t)() 974 975 if testing.Short() || util.RaceEnabled { 976 t.Skip("takes too long with race enabled") 977 } 978 schemaChangeTimestampRegexp := regexp.MustCompile(`schema change occurred at ([0-9]+\.[0-9]+)`) 979 timestampStrFromError := func(t *testing.T, err error) string { 980 require.Regexp(t, schemaChangeTimestampRegexp, err) 981 m := schemaChangeTimestampRegexp.FindStringSubmatch(err.Error()) 982 return m[1] 983 } 984 waitForSchemaChangeErrorAndCloseFeed := func(t *testing.T, f cdctest.TestFeed) (tsStr string) { 985 t.Helper() 986 for { 987 if ev, err := f.Next(); err != nil { 988 log.Infof(context.Background(), "got event %v %v", ev, err) 989 tsStr = timestampStrFromError(t, err) 990 _ = f.Close() 991 return tsStr 992 } 993 } 994 } 995 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 996 sqlDB := sqlutils.MakeSQLRunner(db) 997 // Shorten the intervals so this test doesn't take so long. We need to wait 998 // for timestamps to get resolved. 999 sqlDB.Exec(t, "SET CLUSTER SETTING changefeed.experimental_poll_interval = '200ms'") 1000 sqlDB.Exec(t, "SET CLUSTER SETTING kv.closed_timestamp.target_duration = '50ms'") 1001 sqlDB.Exec(t, "SET CLUSTER SETTING kv.closed_timestamp.close_fraction = .99") 1002 1003 t.Run("add column not null", func(t *testing.T) { 1004 sqlDB.Exec(t, `CREATE TABLE add_column_not_null (a INT PRIMARY KEY)`) 1005 defer sqlDB.Exec(t, `DROP TABLE add_column_not_null`) 1006 sqlDB.Exec(t, `INSERT INTO add_column_not_null VALUES (0)`) 1007 addColumnNotNull := feed(t, f, `CREATE CHANGEFEED FOR add_column_not_null `+ 1008 `WITH schema_change_events='column_changes', schema_change_policy='stop'`) 1009 sqlDB.Exec(t, `INSERT INTO add_column_not_null VALUES (1)`) 1010 assertPayloads(t, addColumnNotNull, []string{ 1011 `add_column_not_null: [0]->{"after": {"a": 0}}`, 1012 `add_column_not_null: [1]->{"after": {"a": 1}}`, 1013 }) 1014 sqlDB.Exec(t, `ALTER TABLE add_column_not_null ADD COLUMN b INT NOT NULL DEFAULT 0`) 1015 sqlDB.Exec(t, "INSERT INTO add_column_not_null VALUES (2, 1)") 1016 tsStr := waitForSchemaChangeErrorAndCloseFeed(t, addColumnNotNull) 1017 addColumnNotNull = feed(t, f, `CREATE CHANGEFEED FOR add_column_not_null `+ 1018 `WITH schema_change_events='column_changes', schema_change_policy='stop', cursor = '`+tsStr+`'`) 1019 defer closeFeed(t, addColumnNotNull) 1020 assertPayloads(t, addColumnNotNull, []string{ 1021 `add_column_not_null: [2]->{"after": {"a": 2, "b": 1}}`, 1022 }) 1023 }) 1024 t.Run("add column null", func(t *testing.T) { 1025 sqlDB.Exec(t, `CREATE TABLE add_column_null (a INT PRIMARY KEY)`) 1026 defer sqlDB.Exec(t, `DROP TABLE add_column_null`) 1027 sqlDB.Exec(t, `INSERT INTO add_column_null VALUES (0)`) 1028 addColumnNull := feed(t, f, `CREATE CHANGEFEED FOR add_column_null `+ 1029 `WITH schema_change_events='column_changes', schema_change_policy='stop'`) 1030 sqlDB.Exec(t, `INSERT INTO add_column_null VALUES (1)`) 1031 assertPayloads(t, addColumnNull, []string{ 1032 `add_column_null: [0]->{"after": {"a": 0}}`, 1033 `add_column_null: [1]->{"after": {"a": 1}}`, 1034 }) 1035 sqlDB.Exec(t, `ALTER TABLE add_column_null ADD COLUMN b INT`) 1036 sqlDB.Exec(t, "INSERT INTO add_column_null VALUES (2, NULL)") 1037 tsStr := waitForSchemaChangeErrorAndCloseFeed(t, addColumnNull) 1038 addColumnNull = feed(t, f, `CREATE CHANGEFEED FOR add_column_null `+ 1039 `WITH schema_change_events='column_changes', schema_change_policy='stop', cursor = '`+tsStr+`'`) 1040 defer closeFeed(t, addColumnNull) 1041 assertPayloads(t, addColumnNull, []string{ 1042 `add_column_null: [2]->{"after": {"a": 2, "b": null}}`, 1043 }) 1044 }) 1045 t.Run(`add column computed`, func(t *testing.T) { 1046 sqlDB.Exec(t, `CREATE TABLE add_comp_col (a INT PRIMARY KEY)`) 1047 defer sqlDB.Exec(t, `DROP TABLE add_comp_col`) 1048 sqlDB.Exec(t, `INSERT INTO add_comp_col VALUES (0)`) 1049 addCompCol := feed(t, f, `CREATE CHANGEFEED FOR add_comp_col `+ 1050 `WITH schema_change_events='column_changes', schema_change_policy='stop'`) 1051 sqlDB.Exec(t, `INSERT INTO add_comp_col VALUES (1)`) 1052 assertPayloads(t, addCompCol, []string{ 1053 `add_comp_col: [0]->{"after": {"a": 0}}`, 1054 `add_comp_col: [1]->{"after": {"a": 1}}`, 1055 }) 1056 sqlDB.Exec(t, `ALTER TABLE add_comp_col ADD COLUMN b INT AS (a + 1) STORED`) 1057 sqlDB.Exec(t, "INSERT INTO add_comp_col VALUES (2)") 1058 tsStr := waitForSchemaChangeErrorAndCloseFeed(t, addCompCol) 1059 addCompCol = feed(t, f, `CREATE CHANGEFEED FOR add_comp_col `+ 1060 `WITH schema_change_events='column_changes', schema_change_policy='stop', cursor = '`+tsStr+`'`) 1061 defer closeFeed(t, addCompCol) 1062 assertPayloads(t, addCompCol, []string{ 1063 `add_comp_col: [2]->{"after": {"a": 2, "b": 3}}`, 1064 }) 1065 }) 1066 t.Run("drop column", func(t *testing.T) { 1067 sqlDB.Exec(t, `CREATE TABLE drop_column (a INT PRIMARY KEY, b INT)`) 1068 defer sqlDB.Exec(t, `DROP TABLE drop_column`) 1069 sqlDB.Exec(t, `INSERT INTO drop_column VALUES (0, NULL)`) 1070 dropColumn := feed(t, f, `CREATE CHANGEFEED FOR drop_column `+ 1071 `WITH schema_change_events='column_changes', schema_change_policy='stop'`) 1072 sqlDB.Exec(t, `INSERT INTO drop_column VALUES (1, 2)`) 1073 assertPayloads(t, dropColumn, []string{ 1074 `drop_column: [0]->{"after": {"a": 0, "b": null}}`, 1075 `drop_column: [1]->{"after": {"a": 1, "b": 2}}`, 1076 }) 1077 sqlDB.Exec(t, `ALTER TABLE drop_column DROP COLUMN b`) 1078 sqlDB.Exec(t, `INSERT INTO drop_column VALUES (2)`) 1079 tsStr := waitForSchemaChangeErrorAndCloseFeed(t, dropColumn) 1080 dropColumn = feed(t, f, `CREATE CHANGEFEED FOR drop_column `+ 1081 `WITH schema_change_events='column_changes', schema_change_policy='stop', cursor = '`+tsStr+`'`) 1082 defer closeFeed(t, dropColumn) 1083 // NB: You might expect to only see the new row here but we'll see them 1084 // all because we cannot distinguish between the index backfill and 1085 // foreground writes. See #35738. 1086 assertPayloads(t, dropColumn, []string{ 1087 `drop_column: [0]->{"after": {"a": 0}}`, 1088 `drop_column: [1]->{"after": {"a": 1}}`, 1089 `drop_column: [2]->{"after": {"a": 2}}`, 1090 }) 1091 }) 1092 t.Run("add index", func(t *testing.T) { 1093 // This case does not exit 1094 sqlDB.Exec(t, `CREATE TABLE add_index (a INT PRIMARY KEY, b INT)`) 1095 defer sqlDB.Exec(t, `DROP TABLE add_index`) 1096 sqlDB.Exec(t, `INSERT INTO add_index VALUES (0, NULL)`) 1097 addIndex := feed(t, f, `CREATE CHANGEFEED FOR add_index `+ 1098 `WITH schema_change_events='column_changes', schema_change_policy='stop'`) 1099 defer closeFeed(t, addIndex) 1100 sqlDB.Exec(t, `INSERT INTO add_index VALUES (1, 2)`) 1101 assertPayloads(t, addIndex, []string{ 1102 `add_index: [0]->{"after": {"a": 0, "b": null}}`, 1103 `add_index: [1]->{"after": {"a": 1, "b": 2}}`, 1104 }) 1105 sqlDB.Exec(t, `CREATE INDEX ON add_index (b)`) 1106 sqlDB.Exec(t, `INSERT INTO add_index VALUES (2, NULL)`) 1107 assertPayloads(t, addIndex, []string{ 1108 `add_index: [2]->{"after": {"a": 2, "b": null}}`, 1109 }) 1110 }) 1111 } 1112 1113 t.Run(`sinkless`, sinklessTest(testFn)) 1114 t.Run(`enterprise`, enterpriseTest(testFn)) 1115 } 1116 1117 func TestChangefeedNoBackfill(t *testing.T) { 1118 defer leaktest.AfterTest(t)() 1119 1120 if testing.Short() || util.RaceEnabled { 1121 t.Skip("takes too long with race enabled") 1122 } 1123 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 1124 sqlDB := sqlutils.MakeSQLRunner(db) 1125 // Shorten the intervals so this test doesn't take so long. We need to wait 1126 // for timestamps to get resolved. 1127 sqlDB.Exec(t, "SET CLUSTER SETTING changefeed.experimental_poll_interval = '200ms'") 1128 sqlDB.Exec(t, "SET CLUSTER SETTING kv.closed_timestamp.target_duration = '50ms'") 1129 sqlDB.Exec(t, "SET CLUSTER SETTING kv.closed_timestamp.close_fraction = .99") 1130 1131 t.Run("add column not null", func(t *testing.T) { 1132 sqlDB.Exec(t, `CREATE TABLE add_column_not_null (a INT PRIMARY KEY)`) 1133 defer sqlDB.Exec(t, `DROP TABLE add_column_not_null`) 1134 sqlDB.Exec(t, `INSERT INTO add_column_not_null VALUES (0)`) 1135 addColumnNotNull := feed(t, f, `CREATE CHANGEFEED FOR add_column_not_null `+ 1136 `WITH schema_change_policy='nobackfill'`) 1137 defer closeFeed(t, addColumnNotNull) 1138 sqlDB.Exec(t, `INSERT INTO add_column_not_null VALUES (1)`) 1139 assertPayloads(t, addColumnNotNull, []string{ 1140 `add_column_not_null: [0]->{"after": {"a": 0}}`, 1141 `add_column_not_null: [1]->{"after": {"a": 1}}`, 1142 }) 1143 sqlDB.Exec(t, `ALTER TABLE add_column_not_null ADD COLUMN b INT NOT NULL DEFAULT 0`) 1144 sqlDB.Exec(t, "INSERT INTO add_column_not_null VALUES (2, 1)") 1145 assertPayloads(t, addColumnNotNull, []string{ 1146 `add_column_not_null: [2]->{"after": {"a": 2, "b": 1}}`, 1147 }) 1148 }) 1149 t.Run("add column null", func(t *testing.T) { 1150 sqlDB.Exec(t, `CREATE TABLE add_column_null (a INT PRIMARY KEY)`) 1151 defer sqlDB.Exec(t, `DROP TABLE add_column_null`) 1152 sqlDB.Exec(t, `INSERT INTO add_column_null VALUES (0)`) 1153 addColumnNull := feed(t, f, `CREATE CHANGEFEED FOR add_column_null `+ 1154 `WITH schema_change_policy='nobackfill'`) 1155 defer closeFeed(t, addColumnNull) 1156 sqlDB.Exec(t, `INSERT INTO add_column_null VALUES (1)`) 1157 assertPayloads(t, addColumnNull, []string{ 1158 `add_column_null: [0]->{"after": {"a": 0}}`, 1159 `add_column_null: [1]->{"after": {"a": 1}}`, 1160 }) 1161 sqlDB.Exec(t, `ALTER TABLE add_column_null ADD COLUMN b INT`) 1162 sqlDB.Exec(t, "INSERT INTO add_column_null VALUES (2, NULL)") 1163 assertPayloads(t, addColumnNull, []string{ 1164 `add_column_null: [2]->{"after": {"a": 2, "b": null}}`, 1165 }) 1166 }) 1167 t.Run(`add column computed`, func(t *testing.T) { 1168 sqlDB.Exec(t, `CREATE TABLE add_comp_col (a INT PRIMARY KEY)`) 1169 defer sqlDB.Exec(t, `DROP TABLE add_comp_col`) 1170 sqlDB.Exec(t, `INSERT INTO add_comp_col VALUES (0)`) 1171 addCompCol := feed(t, f, `CREATE CHANGEFEED FOR add_comp_col `+ 1172 `WITH schema_change_policy='nobackfill'`) 1173 defer closeFeed(t, addCompCol) 1174 sqlDB.Exec(t, `INSERT INTO add_comp_col VALUES (1)`) 1175 assertPayloads(t, addCompCol, []string{ 1176 `add_comp_col: [0]->{"after": {"a": 0}}`, 1177 `add_comp_col: [1]->{"after": {"a": 1}}`, 1178 }) 1179 sqlDB.Exec(t, `ALTER TABLE add_comp_col ADD COLUMN b INT AS (a + 1) STORED`) 1180 sqlDB.Exec(t, "INSERT INTO add_comp_col VALUES (2)") 1181 assertPayloads(t, addCompCol, []string{ 1182 `add_comp_col: [2]->{"after": {"a": 2, "b": 3}}`, 1183 }) 1184 }) 1185 t.Run("drop column", func(t *testing.T) { 1186 sqlDB.Exec(t, `CREATE TABLE drop_column (a INT PRIMARY KEY, b INT)`) 1187 defer sqlDB.Exec(t, `DROP TABLE drop_column`) 1188 sqlDB.Exec(t, `INSERT INTO drop_column VALUES (0, NULL)`) 1189 dropColumn := feed(t, f, `CREATE CHANGEFEED FOR drop_column `+ 1190 `WITH schema_change_policy='nobackfill'`) 1191 defer closeFeed(t, dropColumn) 1192 sqlDB.Exec(t, `INSERT INTO drop_column VALUES (1, 2)`) 1193 assertPayloads(t, dropColumn, []string{ 1194 `drop_column: [0]->{"after": {"a": 0, "b": null}}`, 1195 `drop_column: [1]->{"after": {"a": 1, "b": 2}}`, 1196 }) 1197 sqlDB.Exec(t, `ALTER TABLE drop_column DROP COLUMN b`) 1198 sqlDB.Exec(t, `INSERT INTO drop_column VALUES (2)`) 1199 // NB: You might expect to only see the new row here but we'll see them 1200 // all because we cannot distinguish between the index backfill and 1201 // foreground writes. See #35738. 1202 assertPayloads(t, dropColumn, []string{ 1203 `drop_column: [0]->{"after": {"a": 0}}`, 1204 `drop_column: [1]->{"after": {"a": 1}}`, 1205 `drop_column: [2]->{"after": {"a": 2}}`, 1206 }) 1207 }) 1208 t.Run("add index", func(t *testing.T) { 1209 // This case does not exit 1210 sqlDB.Exec(t, `CREATE TABLE add_index (a INT PRIMARY KEY, b INT)`) 1211 defer sqlDB.Exec(t, `DROP TABLE add_index`) 1212 sqlDB.Exec(t, `INSERT INTO add_index VALUES (0, NULL)`) 1213 addIndex := feed(t, f, `CREATE CHANGEFEED FOR add_index `+ 1214 `WITH schema_change_policy='nobackfill'`) 1215 defer closeFeed(t, addIndex) 1216 sqlDB.Exec(t, `INSERT INTO add_index VALUES (1, 2)`) 1217 assertPayloads(t, addIndex, []string{ 1218 `add_index: [0]->{"after": {"a": 0, "b": null}}`, 1219 `add_index: [1]->{"after": {"a": 1, "b": 2}}`, 1220 }) 1221 sqlDB.Exec(t, `CREATE INDEX ON add_index (b)`) 1222 sqlDB.Exec(t, `INSERT INTO add_index VALUES (2, NULL)`) 1223 assertPayloads(t, addIndex, []string{ 1224 `add_index: [2]->{"after": {"a": 2, "b": null}}`, 1225 }) 1226 }) 1227 } 1228 1229 t.Run(`sinkless`, sinklessTest(testFn)) 1230 t.Run(`enterprise`, enterpriseTest(testFn)) 1231 } 1232 1233 func TestChangefeedComputedColumn(t *testing.T) { 1234 defer leaktest.AfterTest(t)() 1235 1236 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 1237 sqlDB := sqlutils.MakeSQLRunner(db) 1238 // TODO(dan): Also test a non-STORED computed column once we support them. 1239 sqlDB.Exec(t, `CREATE TABLE cc ( 1240 a INT, b INT AS (a + 1) STORED, c INT AS (a + 2) STORED, PRIMARY KEY (b, a) 1241 )`) 1242 sqlDB.Exec(t, `INSERT INTO cc (a) VALUES (1)`) 1243 1244 cc := feed(t, f, `CREATE CHANGEFEED FOR cc`) 1245 defer closeFeed(t, cc) 1246 1247 assertPayloads(t, cc, []string{ 1248 `cc: [2, 1]->{"after": {"a": 1, "b": 2, "c": 3}}`, 1249 }) 1250 1251 sqlDB.Exec(t, `INSERT INTO cc (a) VALUES (10)`) 1252 assertPayloads(t, cc, []string{ 1253 `cc: [11, 10]->{"after": {"a": 10, "b": 11, "c": 12}}`, 1254 }) 1255 } 1256 1257 t.Run(`sinkless`, sinklessTest(testFn)) 1258 t.Run(`enterprise`, enterpriseTest(testFn)) 1259 } 1260 1261 func TestChangefeedUpdatePrimaryKey(t *testing.T) { 1262 defer leaktest.AfterTest(t)() 1263 1264 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 1265 sqlDB := sqlutils.MakeSQLRunner(db) 1266 // This NOT NULL column checks a regression when used with UPDATE-ing a 1267 // primary key column or with DELETE. 1268 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING NOT NULL)`) 1269 sqlDB.Exec(t, `INSERT INTO foo VALUES (0, 'bar')`) 1270 1271 foo := feed(t, f, `CREATE CHANGEFEED FOR foo`) 1272 defer closeFeed(t, foo) 1273 assertPayloads(t, foo, []string{ 1274 `foo: [0]->{"after": {"a": 0, "b": "bar"}}`, 1275 }) 1276 1277 sqlDB.Exec(t, `UPDATE foo SET a = 1`) 1278 assertPayloads(t, foo, []string{ 1279 `foo: [0]->{"after": null}`, 1280 `foo: [1]->{"after": {"a": 1, "b": "bar"}}`, 1281 }) 1282 1283 sqlDB.Exec(t, `DELETE FROM foo`) 1284 assertPayloads(t, foo, []string{ 1285 `foo: [1]->{"after": null}`, 1286 }) 1287 } 1288 1289 t.Run(`sinkless`, sinklessTest(testFn)) 1290 t.Run(`enterprise`, enterpriseTest(testFn)) 1291 } 1292 1293 func TestChangefeedTruncateRenameDrop(t *testing.T) { 1294 defer leaktest.AfterTest(t)() 1295 1296 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 1297 sqlDB := sqlutils.MakeSQLRunner(db) 1298 1299 sqlDB.Exec(t, `CREATE TABLE truncate (a INT PRIMARY KEY)`) 1300 sqlDB.Exec(t, `CREATE TABLE truncate_cascade (b INT PRIMARY KEY REFERENCES truncate (a))`) 1301 sqlDB.Exec(t, 1302 `BEGIN; INSERT INTO truncate VALUES (1); INSERT INTO truncate_cascade VALUES (1); COMMIT`) 1303 truncate := feed(t, f, `CREATE CHANGEFEED FOR truncate`) 1304 defer closeFeed(t, truncate) 1305 truncateCascade := feed(t, f, `CREATE CHANGEFEED FOR truncate_cascade`) 1306 defer closeFeed(t, truncateCascade) 1307 assertPayloads(t, truncate, []string{`truncate: [1]->{"after": {"a": 1}}`}) 1308 assertPayloads(t, truncateCascade, []string{`truncate_cascade: [1]->{"after": {"b": 1}}`}) 1309 sqlDB.Exec(t, `TRUNCATE TABLE truncate CASCADE`) 1310 if _, err := truncate.Next(); !testutils.IsError(err, `"truncate" was dropped or truncated`) { 1311 t.Errorf(`expected ""truncate" was dropped or truncated" error got: %+v`, err) 1312 } 1313 if _, err := truncateCascade.Next(); !testutils.IsError( 1314 err, `"truncate_cascade" was dropped or truncated`, 1315 ) { 1316 t.Errorf(`expected ""truncate_cascade" was dropped or truncated" error got: %+v`, err) 1317 } 1318 1319 sqlDB.Exec(t, `CREATE TABLE rename (a INT PRIMARY KEY)`) 1320 sqlDB.Exec(t, `INSERT INTO rename VALUES (1)`) 1321 rename := feed(t, f, `CREATE CHANGEFEED FOR rename`) 1322 defer closeFeed(t, rename) 1323 assertPayloads(t, rename, []string{`rename: [1]->{"after": {"a": 1}}`}) 1324 sqlDB.Exec(t, `ALTER TABLE rename RENAME TO renamed`) 1325 sqlDB.Exec(t, `INSERT INTO renamed VALUES (2)`) 1326 if _, err := rename.Next(); !testutils.IsError(err, `"rename" was renamed to "renamed"`) { 1327 t.Errorf(`expected ""rename" was renamed to "renamed"" error got: %+v`, err) 1328 } 1329 1330 sqlDB.Exec(t, `CREATE TABLE drop (a INT PRIMARY KEY)`) 1331 sqlDB.Exec(t, `INSERT INTO drop VALUES (1)`) 1332 drop := feed(t, f, `CREATE CHANGEFEED FOR drop`) 1333 defer closeFeed(t, drop) 1334 assertPayloads(t, drop, []string{`drop: [1]->{"after": {"a": 1}}`}) 1335 sqlDB.Exec(t, `DROP TABLE drop`) 1336 if _, err := drop.Next(); !testutils.IsError(err, `"drop" was dropped or truncated`) { 1337 t.Errorf(`expected ""drop" was dropped or truncated" error got: %+v`, err) 1338 } 1339 } 1340 1341 t.Run(`sinkless`, sinklessTest(testFn)) 1342 t.Run(`enterprise`, enterpriseTest(testFn)) 1343 } 1344 1345 func TestChangefeedMonitoring(t *testing.T) { 1346 defer leaktest.AfterTest(t)() 1347 1348 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 1349 beforeEmitRowCh := make(chan struct{}, 2) 1350 knobs := f.Server().(*server.TestServer).Cfg.TestingKnobs. 1351 DistSQL.(*execinfra.TestingKnobs). 1352 Changefeed.(*TestingKnobs) 1353 knobs.BeforeEmitRow = func(_ context.Context) error { 1354 <-beforeEmitRowCh 1355 return nil 1356 } 1357 1358 sqlDB := sqlutils.MakeSQLRunner(db) 1359 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY)`) 1360 sqlDB.Exec(t, `INSERT INTO foo VALUES (1)`) 1361 1362 s := f.Server() 1363 if c := s.MustGetSQLCounter(`changefeed.emitted_messages`); c != 0 { 1364 t.Errorf(`expected 0 got %d`, c) 1365 } 1366 if c := s.MustGetSQLCounter(`changefeed.emitted_bytes`); c != 0 { 1367 t.Errorf(`expected 0 got %d`, c) 1368 } 1369 if c := s.MustGetSQLCounter(`changefeed.emit_nanos`); c != 0 { 1370 t.Errorf(`expected 0 got %d`, c) 1371 } 1372 if c := s.MustGetSQLCounter(`changefeed.flushes`); c != 0 { 1373 t.Errorf(`expected 0 got %d`, c) 1374 } 1375 if c := s.MustGetSQLCounter(`changefeed.flush_nanos`); c != 0 { 1376 t.Errorf(`expected 0 got %d`, c) 1377 } 1378 if c := s.MustGetSQLCounter(`changefeed.max_behind_nanos`); c != 0 { 1379 t.Errorf(`expected %d got %d`, 0, c) 1380 } 1381 if c := s.MustGetSQLCounter(`changefeed.buffer_entries.in`); c != 0 { 1382 t.Errorf(`expected 0 got %d`, c) 1383 } 1384 if c := s.MustGetSQLCounter(`changefeed.buffer_entries.out`); c != 0 { 1385 t.Errorf(`expected 0 got %d`, c) 1386 } 1387 1388 beforeEmitRowCh <- struct{}{} 1389 foo := feed(t, f, `CREATE CHANGEFEED FOR foo`) 1390 _, _ = foo.Next() 1391 testutils.SucceedsSoon(t, func() error { 1392 if c := s.MustGetSQLCounter(`changefeed.emitted_messages`); c != 1 { 1393 return errors.Errorf(`expected 1 got %d`, c) 1394 } 1395 if c := s.MustGetSQLCounter(`changefeed.emitted_bytes`); c != 22 { 1396 return errors.Errorf(`expected 22 got %d`, c) 1397 } 1398 if c := s.MustGetSQLCounter(`changefeed.emit_nanos`); c <= 0 { 1399 return errors.Errorf(`expected > 0 got %d`, c) 1400 } 1401 if c := s.MustGetSQLCounter(`changefeed.flushes`); c <= 0 { 1402 return errors.Errorf(`expected > 0 got %d`, c) 1403 } 1404 if c := s.MustGetSQLCounter(`changefeed.flush_nanos`); c <= 0 { 1405 return errors.Errorf(`expected > 0 got %d`, c) 1406 } 1407 if c := s.MustGetSQLCounter(`changefeed.max_behind_nanos`); c <= 0 { 1408 return errors.Errorf(`expected > 0 got %d`, c) 1409 } 1410 if c := s.MustGetSQLCounter(`changefeed.buffer_entries.in`); c <= 0 { 1411 return errors.Errorf(`expected > 0 got %d`, c) 1412 } 1413 if c := s.MustGetSQLCounter(`changefeed.buffer_entries.out`); c <= 0 { 1414 return errors.Errorf(`expected > 0 got %d`, c) 1415 } 1416 return nil 1417 }) 1418 1419 // Not reading from foo will backpressure it and max_behind_nanos will grow. 1420 sqlDB.Exec(t, `INSERT INTO foo VALUES (2)`) 1421 const expectedLatency = 100 * time.Millisecond 1422 sqlDB.Exec(t, `SET CLUSTER SETTING kv.closed_timestamp.target_duration = $1`, 1423 (expectedLatency / 3).String()) 1424 sqlDB.Exec(t, `SET CLUSTER SETTING kv.closed_timestamp.close_fraction = 1.0`) 1425 1426 testutils.SucceedsSoon(t, func() error { 1427 waitForBehindNanos := 2 * expectedLatency.Nanoseconds() 1428 if c := s.MustGetSQLCounter(`changefeed.max_behind_nanos`); c < waitForBehindNanos { 1429 return errors.Errorf( 1430 `waiting for the feed to be > %d nanos behind got %d`, waitForBehindNanos, c) 1431 } 1432 return nil 1433 }) 1434 1435 // Unblocking the emit should bring the max_behind_nanos back down. 1436 // Unfortunately, this is sensitive to how many closed timestamp updates are 1437 // received. If we get them too fast, it takes longer to process them then 1438 // they come in and we fall continually further behind. The target_duration 1439 // and close_fraction settings above are tuned to try to avoid this. 1440 close(beforeEmitRowCh) 1441 _, _ = foo.Next() 1442 testutils.SucceedsSoon(t, func() error { 1443 waitForBehindNanos := expectedLatency.Nanoseconds() 1444 if c := s.MustGetSQLCounter(`changefeed.max_behind_nanos`); c > waitForBehindNanos { 1445 return errors.Errorf( 1446 `waiting for the feed to be < %d nanos behind got %d`, waitForBehindNanos, c) 1447 } 1448 return nil 1449 }) 1450 1451 // Check that two changefeeds add correctly. 1452 // Set cluster settings back so we don't interfere with schema changes. 1453 sqlDB.Exec(t, `SET CLUSTER SETTING kv.closed_timestamp.target_duration = '1s'`) 1454 fooCopy := feed(t, f, `CREATE CHANGEFEED FOR foo`) 1455 _, _ = fooCopy.Next() 1456 _, _ = fooCopy.Next() 1457 testutils.SucceedsSoon(t, func() error { 1458 // We can't assert exactly 4 or 88 in case we get (allowed) duplicates 1459 // from RangeFeed. 1460 if c := s.MustGetSQLCounter(`changefeed.emitted_messages`); c < 4 { 1461 return errors.Errorf(`expected >= 4 got %d`, c) 1462 } 1463 if c := s.MustGetSQLCounter(`changefeed.emitted_bytes`); c < 88 { 1464 return errors.Errorf(`expected >= 88 got %d`, c) 1465 } 1466 return nil 1467 }) 1468 1469 // Cancel all the changefeeds and check that max_behind_nanos returns to 0. 1470 require.NoError(t, foo.Close()) 1471 require.NoError(t, fooCopy.Close()) 1472 testutils.SucceedsSoon(t, func() error { 1473 if c := s.MustGetSQLCounter(`changefeed.max_behind_nanos`); c != 0 { 1474 return errors.Errorf(`expected 0 got %d`, c) 1475 } 1476 return nil 1477 }) 1478 } 1479 1480 t.Run(`sinkless`, sinklessTest(testFn)) 1481 t.Run(`enterprise`, func(t *testing.T) { 1482 t.Skip("https://github.com/cockroachdb/cockroach/issues/38443") 1483 enterpriseTest(testFn) 1484 }) 1485 } 1486 1487 func TestChangefeedRetryableError(t *testing.T) { 1488 defer leaktest.AfterTest(t)() 1489 defer utilccl.TestingEnableEnterprise()() 1490 1491 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 1492 knobs := f.Server().(*server.TestServer).Cfg.TestingKnobs. 1493 DistSQL.(*execinfra.TestingKnobs). 1494 Changefeed.(*TestingKnobs) 1495 origAfterSinkFlushHook := knobs.AfterSinkFlush 1496 var failSink int64 1497 failSinkHook := func() error { 1498 switch atomic.LoadInt64(&failSink) { 1499 case 1: 1500 return MarkRetryableError(fmt.Errorf("synthetic retryable error")) 1501 case 2: 1502 return fmt.Errorf("synthetic terminal error") 1503 } 1504 return origAfterSinkFlushHook() 1505 } 1506 knobs.AfterSinkFlush = failSinkHook 1507 1508 // Set up a new feed and verify that the sink is started up. 1509 sqlDB := sqlutils.MakeSQLRunner(db) 1510 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY)`) 1511 foo := feed(t, f, `CREATE CHANGEFEED FOR foo`) 1512 defer closeFeed(t, foo) 1513 sqlDB.Exec(t, `INSERT INTO foo VALUES (1)`) 1514 assertPayloads(t, foo, []string{ 1515 `foo: [1]->{"after": {"a": 1}}`, 1516 }) 1517 1518 // Set sink to return unique retryable errors and insert a row. Verify that 1519 // sink is failing requests. 1520 atomic.StoreInt64(&failSink, 1) 1521 sqlDB.Exec(t, `INSERT INTO foo VALUES (2)`) 1522 registry := f.Server().JobRegistry().(*jobs.Registry) 1523 retryCounter := registry.MetricsStruct().Changefeed.(*Metrics).ErrorRetries 1524 testutils.SucceedsSoon(t, func() error { 1525 if retryCounter.Counter.Count() < 3 { 1526 return fmt.Errorf("insufficient error retries detected") 1527 } 1528 return nil 1529 }) 1530 1531 // Fix the sink and insert another row. Check that nothing funky happened. 1532 atomic.StoreInt64(&failSink, 0) 1533 sqlDB.Exec(t, `INSERT INTO foo VALUES (3)`) 1534 assertPayloads(t, foo, []string{ 1535 `foo: [2]->{"after": {"a": 2}}`, 1536 `foo: [3]->{"after": {"a": 3}}`, 1537 }) 1538 1539 // Set sink to return a terminal error and insert a row. Ensure that we 1540 // eventually get the error message back out. 1541 atomic.StoreInt64(&failSink, 2) 1542 sqlDB.Exec(t, `INSERT INTO foo VALUES (4)`) 1543 for { 1544 _, err := foo.Next() 1545 if err == nil { 1546 continue 1547 } 1548 require.EqualError(t, err, `synthetic terminal error`) 1549 break 1550 } 1551 } 1552 1553 // Only the enterprise version uses jobs. 1554 t.Run(`enterprise`, enterpriseTest(testFn)) 1555 } 1556 1557 // TestChangefeedDataTTL ensures that changefeeds fail with an error in the case 1558 // where the feed has fallen behind the GC TTL of the table data. 1559 func TestChangefeedDataTTL(t *testing.T) { 1560 defer leaktest.AfterTest(t)() 1561 1562 t.Skip("https://github.com/cockroachdb/cockroach/issues/37154") 1563 1564 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 1565 ctx := context.Background() 1566 // Set a very simple channel-based, wait-and-resume function as the 1567 // BeforeEmitRow hook. 1568 var shouldWait int32 1569 wait := make(chan struct{}) 1570 resume := make(chan struct{}) 1571 knobs := f.Server().(*server.TestServer).Cfg.TestingKnobs. 1572 DistSQL.(*execinfra.TestingKnobs). 1573 Changefeed.(*TestingKnobs) 1574 knobs.BeforeEmitRow = func(_ context.Context) error { 1575 if atomic.LoadInt32(&shouldWait) == 0 { 1576 return nil 1577 } 1578 wait <- struct{}{} 1579 <-resume 1580 return nil 1581 } 1582 1583 sqlDB := sqlutils.MakeSQLRunner(db) 1584 1585 // Create the data table; it will only contain a single row with multiple 1586 // versions. 1587 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 1588 1589 counter := 0 1590 upsertRow := func() { 1591 counter++ 1592 sqlDB.Exec(t, `UPSERT INTO foo (a, b) VALUES (1, $1)`, fmt.Sprintf("version %d", counter)) 1593 } 1594 1595 // Create the initial version of the row and the changefeed itself. The initial 1596 // version is necessary to prevent CREATE CHANGEFEED itself from hanging. 1597 upsertRow() 1598 dataExpiredRows := feed(t, f, "CREATE CHANGEFEED FOR TABLE foo") 1599 defer closeFeed(t, dataExpiredRows) 1600 1601 // Set up our emit trap and update the row, which will allow us to "pause" the 1602 // changefeed in order to force a GC. 1603 atomic.StoreInt32(&shouldWait, 1) 1604 upsertRow() 1605 <-wait 1606 1607 // Upsert two additional versions. One of these will be deleted by the GC 1608 // process before changefeed polling is resumed. 1609 upsertRow() 1610 upsertRow() 1611 1612 // Force a GC of the table. This should cause both older versions of the 1613 // table to be deleted, with the middle version being lost to the changefeed. 1614 forceTableGC(t, f.Server(), sqlDB, "d", "foo") 1615 1616 // Resume our changefeed normally. 1617 atomic.StoreInt32(&shouldWait, 0) 1618 resume <- struct{}{} 1619 1620 // Verify that, at some point, Next() returns a "must be after replica GC 1621 // threshold" error. In the common case, that'll be the third call, but 1622 // various conditions will cause RangeFeed to emit duplicates and so it may 1623 // be a few more. 1624 // 1625 // TODO(tbg): this should keep track of the values seen and once we have 1626 // observed all four (which should never happen), fail the test. 1627 for { 1628 msg, err := dataExpiredRows.Next() 1629 if testutils.IsError(err, `must be after replica GC threshold`) { 1630 break 1631 } 1632 if msg != nil { 1633 log.Infof(ctx, "ignoring message %s", msg) 1634 } 1635 } 1636 } 1637 1638 t.Run("sinkless", sinklessTest(testFn)) 1639 t.Run("enterprise", enterpriseTest(testFn)) 1640 } 1641 1642 // TestChangefeedSchemaTTL ensures that changefeeds fail with an error in the case 1643 // where the feed has fallen behind the GC TTL of the table's schema. 1644 func TestChangefeedSchemaTTL(t *testing.T) { 1645 defer leaktest.AfterTest(t)() 1646 1647 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 1648 // Set a very simple channel-based, wait-and-resume function as the 1649 // BeforeEmitRow hook. 1650 var shouldWait int32 1651 wait := make(chan struct{}) 1652 resume := make(chan struct{}) 1653 knobs := f.Server().(*server.TestServer).Cfg.TestingKnobs. 1654 DistSQL.(*execinfra.TestingKnobs). 1655 Changefeed.(*TestingKnobs) 1656 knobs.BeforeEmitRow = func(_ context.Context) error { 1657 if atomic.LoadInt32(&shouldWait) == 0 { 1658 return nil 1659 } 1660 wait <- struct{}{} 1661 <-resume 1662 return nil 1663 } 1664 1665 sqlDB := sqlutils.MakeSQLRunner(db) 1666 1667 // Create the data table; it will only contain a single row with multiple 1668 // versions. 1669 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 1670 1671 counter := 0 1672 upsertRow := func() { 1673 counter++ 1674 sqlDB.Exec(t, `UPSERT INTO foo (a, b) VALUES (1, $1)`, fmt.Sprintf("version %d", counter)) 1675 } 1676 1677 // Create the initial version of the row and the changefeed itself. The initial 1678 // version is necessary to prevent CREATE CHANGEFEED itself from hanging. 1679 upsertRow() 1680 dataExpiredRows := feed(t, f, "CREATE CHANGEFEED FOR TABLE foo") 1681 defer closeFeed(t, dataExpiredRows) 1682 1683 // Set up our emit trap and update the row, which will allow us to "pause" the 1684 // changefeed in order to force a GC. 1685 atomic.StoreInt32(&shouldWait, 1) 1686 upsertRow() 1687 <-wait 1688 1689 // Upsert two additional versions. One of these will be deleted by the GC 1690 // process before changefeed polling is resumed. 1691 waitForSchemaChange(t, sqlDB, "ALTER TABLE foo ADD COLUMN c STRING") 1692 upsertRow() 1693 waitForSchemaChange(t, sqlDB, "ALTER TABLE foo ADD COLUMN d STRING") 1694 upsertRow() 1695 1696 // Force a GC of the table. This should cause both older versions of the 1697 // table to be deleted, with the middle version being lost to the changefeed. 1698 forceTableGC(t, f.Server(), sqlDB, "system", "descriptor") 1699 1700 // Resume our changefeed normally. 1701 atomic.StoreInt32(&shouldWait, 0) 1702 resume <- struct{}{} 1703 1704 // Verify that the third call to Next() returns an error (the first is the 1705 // initial row, the second is the first change. The third should detect the 1706 // GC interval mismatch). 1707 _, _ = dataExpiredRows.Next() 1708 _, _ = dataExpiredRows.Next() 1709 if _, err := dataExpiredRows.Next(); !testutils.IsError(err, `GC threshold`) { 1710 t.Errorf(`expected "GC threshold" error got: %+v`, err) 1711 } 1712 } 1713 1714 t.Run("sinkless", sinklessTest(testFn)) 1715 t.Run("enterprise", enterpriseTest(testFn)) 1716 } 1717 1718 func TestChangefeedErrors(t *testing.T) { 1719 defer leaktest.AfterTest(t)() 1720 1721 ctx := context.Background() 1722 s, db, _ := serverutils.StartServer(t, base.TestServerArgs{}) 1723 defer s.Stopper().Stop(ctx) 1724 sqlDB := sqlutils.MakeSQLRunner(db) 1725 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 1726 sqlDB.Exec(t, `CREATE DATABASE d`) 1727 1728 // Changefeeds default to rangefeed, but for now, rangefeed defaults to off. 1729 // Verify that this produces a useful error. 1730 sqlDB.Exec(t, `SET CLUSTER SETTING kv.rangefeed.enabled = false`) 1731 sqlDB.Exec(t, `CREATE TABLE rangefeed_off (a INT PRIMARY KEY)`) 1732 sqlDB.ExpectErr( 1733 t, `rangefeeds require the kv.rangefeed.enabled setting`, 1734 `EXPERIMENTAL CHANGEFEED FOR rangefeed_off`, 1735 ) 1736 sqlDB.Exec(t, `SET CLUSTER SETTING kv.rangefeed.enabled TO DEFAULT`) 1737 1738 sqlDB.ExpectErr( 1739 t, `unknown format: nope`, 1740 `EXPERIMENTAL CHANGEFEED FOR foo WITH format=nope`, 1741 ) 1742 1743 sqlDB.ExpectErr( 1744 t, `unknown envelope: nope`, 1745 `EXPERIMENTAL CHANGEFEED FOR foo WITH envelope=nope`, 1746 ) 1747 sqlDB.ExpectErr( 1748 t, `negative durations are not accepted: resolved='-1s'`, 1749 `EXPERIMENTAL CHANGEFEED FOR foo WITH resolved='-1s'`, 1750 ) 1751 sqlDB.ExpectErr( 1752 t, `cannot specify timestamp in the future`, 1753 `EXPERIMENTAL CHANGEFEED FOR foo WITH cursor=$1`, timeutil.Now().Add(time.Hour), 1754 ) 1755 1756 sqlDB.ExpectErr( 1757 t, `omit the SINK clause`, 1758 `CREATE CHANGEFEED FOR foo INTO ''`, 1759 ) 1760 sqlDB.ExpectErr( 1761 t, `omit the SINK clause`, 1762 `CREATE CHANGEFEED FOR foo INTO $1`, ``, 1763 ) 1764 1765 enableEnterprise := utilccl.TestingDisableEnterprise() 1766 sqlDB.ExpectErr( 1767 t, `CHANGEFEED requires an enterprise license`, 1768 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope`, 1769 ) 1770 enableEnterprise() 1771 1772 // Watching system.jobs would create a cycle, since the resolved timestamp 1773 // high-water mark is saved in it. 1774 sqlDB.ExpectErr( 1775 t, `not supported on system tables`, 1776 `EXPERIMENTAL CHANGEFEED FOR system.jobs`, 1777 ) 1778 sqlDB.ExpectErr( 1779 t, `table "bar" does not exist`, 1780 `EXPERIMENTAL CHANGEFEED FOR bar`, 1781 ) 1782 sqlDB.Exec(t, `CREATE SEQUENCE seq`) 1783 sqlDB.ExpectErr( 1784 t, `CHANGEFEED cannot target sequences: seq`, 1785 `EXPERIMENTAL CHANGEFEED FOR seq`, 1786 ) 1787 sqlDB.Exec(t, `CREATE VIEW vw AS SELECT a, b FROM foo`) 1788 sqlDB.ExpectErr( 1789 t, `CHANGEFEED cannot target views: vw`, 1790 `EXPERIMENTAL CHANGEFEED FOR vw`, 1791 ) 1792 // Backup has the same bad error message #28170. 1793 sqlDB.ExpectErr( 1794 t, `"information_schema.tables" does not exist`, 1795 `EXPERIMENTAL CHANGEFEED FOR information_schema.tables`, 1796 ) 1797 1798 // TODO(dan): These two tests shouldn't need initial data in the table 1799 // to pass. 1800 sqlDB.Exec(t, `CREATE TABLE dec (a DECIMAL PRIMARY KEY)`) 1801 sqlDB.Exec(t, `INSERT INTO dec VALUES (1.0)`) 1802 sqlDB.ExpectErr( 1803 t, `pq: column a: decimal with no precision`, 1804 `EXPERIMENTAL CHANGEFEED FOR dec WITH format=$1, confluent_schema_registry=$2`, 1805 changefeedbase.OptFormatAvro, `bar`, 1806 ) 1807 sqlDB.Exec(t, `CREATE TABLE "oid" (a OID PRIMARY KEY)`) 1808 sqlDB.Exec(t, `INSERT INTO "oid" VALUES (3::OID)`) 1809 sqlDB.ExpectErr( 1810 t, `pq: column a: type OID not yet supported with avro`, 1811 `EXPERIMENTAL CHANGEFEED FOR "oid" WITH format=$1, confluent_schema_registry=$2`, 1812 changefeedbase.OptFormatAvro, `bar`, 1813 ) 1814 1815 // Check that confluent_schema_registry is only accepted if format is avro. 1816 sqlDB.ExpectErr( 1817 t, `unknown sink query parameter: confluent_schema_registry`, 1818 `CREATE CHANGEFEED FOR foo INTO $1`, `experimental-sql://d/?confluent_schema_registry=foo`, 1819 ) 1820 1821 // Check unavailable kafka. 1822 sqlDB.ExpectErr( 1823 t, `client has run out of available brokers`, 1824 `CREATE CHANGEFEED FOR foo INTO 'kafka://nope'`, 1825 ) 1826 1827 // kafka_topic_prefix was referenced by an old version of the RFC, it's 1828 // "topic_prefix" now. 1829 sqlDB.ExpectErr( 1830 t, `unknown sink query parameter: kafka_topic_prefix`, 1831 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?kafka_topic_prefix=foo`, 1832 ) 1833 1834 // schema_topic will be implemented but isn't yet. 1835 sqlDB.ExpectErr( 1836 t, `schema_topic is not yet supported`, 1837 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?schema_topic=foo`, 1838 ) 1839 1840 // Sanity check kafka tls parameters. 1841 sqlDB.ExpectErr( 1842 t, `param tls_enabled must be a bool`, 1843 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?tls_enabled=foo`, 1844 ) 1845 sqlDB.ExpectErr( 1846 t, `param ca_cert must be base 64 encoded`, 1847 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?ca_cert=!`, 1848 ) 1849 sqlDB.ExpectErr( 1850 t, `ca_cert requires tls_enabled=true`, 1851 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?&ca_cert=Zm9v`, 1852 ) 1853 sqlDB.ExpectErr( 1854 t, `param client_cert must be base 64 encoded`, 1855 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?client_cert=!`, 1856 ) 1857 sqlDB.ExpectErr( 1858 t, `param client_key must be base 64 encoded`, 1859 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?client_key=!`, 1860 ) 1861 sqlDB.ExpectErr( 1862 t, `client_cert requires tls_enabled=true`, 1863 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?client_cert=Zm9v`, 1864 ) 1865 sqlDB.ExpectErr( 1866 t, `client_cert requires client_key to be set`, 1867 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?tls_enabled=true&client_cert=Zm9v`, 1868 ) 1869 sqlDB.ExpectErr( 1870 t, `client_key requires client_cert to be set`, 1871 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?tls_enabled=true&client_key=Zm9v`, 1872 ) 1873 sqlDB.ExpectErr( 1874 t, `invalid client certificate`, 1875 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?tls_enabled=true&client_cert=Zm9v&client_key=Zm9v`, 1876 ) 1877 1878 // Sanity check kafka sasl parameters. 1879 sqlDB.ExpectErr( 1880 t, `param sasl_enabled must be a bool`, 1881 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?sasl_enabled=maybe`, 1882 ) 1883 sqlDB.ExpectErr( 1884 t, `param sasl_handshake must be a bool`, 1885 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?sasl_enabled=true&sasl_handshake=maybe`, 1886 ) 1887 sqlDB.ExpectErr( 1888 t, `sasl_enabled must be enabled to configure SASL handshake behavior`, 1889 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?sasl_handshake=false`, 1890 ) 1891 sqlDB.ExpectErr( 1892 t, `sasl_user must be provided when SASL is enabled`, 1893 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?sasl_enabled=true`, 1894 ) 1895 sqlDB.ExpectErr( 1896 t, `sasl_password must be provided when SASL is enabled`, 1897 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?sasl_enabled=true&sasl_user=a`, 1898 ) 1899 sqlDB.ExpectErr( 1900 t, `sasl_enabled must be enabled if a SASL user is provided`, 1901 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?sasl_user=a`, 1902 ) 1903 sqlDB.ExpectErr( 1904 t, `sasl_enabled must be enabled if a SASL password is provided`, 1905 `CREATE CHANGEFEED FOR foo INTO $1`, `kafka://nope/?sasl_password=a`, 1906 ) 1907 1908 // The avro format doesn't support key_in_value yet. 1909 sqlDB.ExpectErr( 1910 t, `key_in_value is not supported with format=experimental_avro`, 1911 `CREATE CHANGEFEED FOR foo INTO $1 WITH key_in_value, format='experimental_avro'`, 1912 `kafka://nope`, 1913 ) 1914 1915 // The cloudStorageSink is particular about the options it will work with. 1916 sqlDB.ExpectErr( 1917 t, `this sink is incompatible with format=experimental_avro`, 1918 `CREATE CHANGEFEED FOR foo INTO $1 WITH format='experimental_avro', confluent_schema_registry=$2`, 1919 `experimental-nodelocal://0/bar`, `schemareg-nope`, 1920 ) 1921 sqlDB.ExpectErr( 1922 t, `this sink is incompatible with envelope=key_only`, 1923 `CREATE CHANGEFEED FOR foo INTO $1 WITH envelope='key_only'`, 1924 `experimental-nodelocal://0/bar`, 1925 ) 1926 1927 // WITH key_in_value requires envelope=wrapped 1928 sqlDB.ExpectErr( 1929 t, `key_in_value is only usable with envelope=wrapped`, 1930 `CREATE CHANGEFEED FOR foo INTO $1 WITH key_in_value, envelope='key_only'`, `kafka://nope`, 1931 ) 1932 sqlDB.ExpectErr( 1933 t, `key_in_value is only usable with envelope=wrapped`, 1934 `CREATE CHANGEFEED FOR foo INTO $1 WITH key_in_value, envelope='row'`, `kafka://nope`, 1935 ) 1936 1937 // WITH diff requires envelope=wrapped 1938 sqlDB.ExpectErr( 1939 t, `diff is only usable with envelope=wrapped`, 1940 `CREATE CHANGEFEED FOR foo INTO $1 WITH diff, envelope='key_only'`, `kafka://nope`, 1941 ) 1942 sqlDB.ExpectErr( 1943 t, `diff is only usable with envelope=wrapped`, 1944 `CREATE CHANGEFEED FOR foo INTO $1 WITH diff, envelope='row'`, `kafka://nope`, 1945 ) 1946 1947 // WITH initial_scan and no_initial_scan disallowed 1948 sqlDB.ExpectErr( 1949 t, `cannot specify both initial_scan and no_initial_scan`, 1950 `CREATE CHANGEFEED FOR foo INTO $1 WITH initial_scan, no_initial_scan`, `kafka://nope`, 1951 ) 1952 sqlDB.ExpectErr( 1953 t, `cannot specify both initial_scan and no_initial_scan`, 1954 `CREATE CHANGEFEED FOR foo INTO $1 WITH no_initial_scan, initial_scan`, `kafka://nope`, 1955 ) 1956 } 1957 1958 func TestChangefeedPermissions(t *testing.T) { 1959 defer leaktest.AfterTest(t)() 1960 1961 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 1962 sqlDB := sqlutils.MakeSQLRunner(db) 1963 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 1964 sqlDB.Exec(t, `CREATE USER testuser`) 1965 1966 s := f.Server() 1967 pgURL, cleanupFunc := sqlutils.PGUrl( 1968 t, s.ServingSQLAddr(), "TestChangefeedPermissions-testuser", url.User("testuser"), 1969 ) 1970 defer cleanupFunc() 1971 testuser, err := gosql.Open("postgres", pgURL.String()) 1972 if err != nil { 1973 t.Fatal(err) 1974 } 1975 defer testuser.Close() 1976 1977 stmt := `EXPERIMENTAL CHANGEFEED FOR foo` 1978 if strings.Contains(t.Name(), `enterprise`) { 1979 stmt = `CREATE CHANGEFEED FOR foo` 1980 } 1981 if _, err := testuser.Exec(stmt); !testutils.IsError(err, `only users with the admin role`) { 1982 t.Errorf(`expected 'only users with the admin role' error got: %+v`, err) 1983 } 1984 } 1985 1986 t.Run(`sinkless`, sinklessTest(testFn)) 1987 t.Run(`enterprise`, enterpriseTest(testFn)) 1988 } 1989 1990 func TestChangefeedDescription(t *testing.T) { 1991 defer leaktest.AfterTest(t)() 1992 1993 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 1994 sqlDB := sqlutils.MakeSQLRunner(db) 1995 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY)`) 1996 sqlDB.Exec(t, `INSERT INTO foo VALUES (1)`) 1997 1998 // Intentionally don't use the TestFeedFactory because we want to 1999 // control the placeholders. 2000 s := f.Server() 2001 sink, cleanup := sqlutils.PGUrl(t, s.ServingSQLAddr(), t.Name(), url.User(security.RootUser)) 2002 defer cleanup() 2003 sink.Scheme = changefeedbase.SinkSchemeExperimentalSQL 2004 sink.Path = `d` 2005 2006 var jobID int64 2007 sqlDB.QueryRow(t, 2008 `CREATE CHANGEFEED FOR foo INTO $1 WITH updated, envelope = $2`, sink.String(), `wrapped`, 2009 ).Scan(&jobID) 2010 2011 var description string 2012 sqlDB.QueryRow(t, 2013 `SELECT description FROM [SHOW JOBS] WHERE job_id = $1`, jobID, 2014 ).Scan(&description) 2015 expected := `CREATE CHANGEFEED FOR TABLE foo INTO '` + sink.String() + 2016 `' WITH envelope = 'wrapped', updated` 2017 if description != expected { 2018 t.Errorf(`got "%s" expected "%s"`, description, expected) 2019 } 2020 } 2021 2022 // Only the enterprise version uses jobs. 2023 t.Run(`enterprise`, enterpriseTest(testFn)) 2024 } 2025 2026 func TestChangefeedPauseUnpause(t *testing.T) { 2027 defer leaktest.AfterTest(t)() 2028 2029 defer func(i time.Duration) { jobs.DefaultAdoptInterval = i }(jobs.DefaultAdoptInterval) 2030 jobs.DefaultAdoptInterval = 10 * time.Millisecond 2031 2032 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 2033 sqlDB := sqlutils.MakeSQLRunner(db) 2034 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 2035 sqlDB.Exec(t, `INSERT INTO foo VALUES (1, 'a'), (2, 'b'), (4, 'c'), (7, 'd'), (8, 'e')`) 2036 2037 foo := feed(t, f, `CREATE CHANGEFEED FOR foo WITH resolved`).(*cdctest.TableFeed) 2038 defer closeFeed(t, foo) 2039 2040 assertPayloads(t, foo, []string{ 2041 `foo: [1]->{"after": {"a": 1, "b": "a"}}`, 2042 `foo: [2]->{"after": {"a": 2, "b": "b"}}`, 2043 `foo: [4]->{"after": {"a": 4, "b": "c"}}`, 2044 `foo: [7]->{"after": {"a": 7, "b": "d"}}`, 2045 `foo: [8]->{"after": {"a": 8, "b": "e"}}`, 2046 }) 2047 2048 // Wait for the high-water mark on the job to be updated after the initial 2049 // scan, to make sure we don't get the initial scan data again. 2050 m, err := foo.Next() 2051 if err != nil { 2052 t.Fatal(err) 2053 } else if m.Key != nil { 2054 t.Fatalf(`expected a resolved timestamp got %s: %s->%s`, m.Topic, m.Key, m.Value) 2055 } 2056 2057 sqlDB.Exec(t, `PAUSE JOB $1`, foo.JobID) 2058 // PAUSE JOB only requests the job to be paused. Block until it's paused. 2059 opts := retry.Options{ 2060 InitialBackoff: 1 * time.Millisecond, 2061 MaxBackoff: time.Second, 2062 Multiplier: 2, 2063 } 2064 ctx := context.Background() 2065 if err := retry.WithMaxAttempts(ctx, opts, 10, func() error { 2066 var status string 2067 sqlDB.QueryRow(t, `SELECT status FROM system.jobs WHERE id = $1`, foo.JobID).Scan(&status) 2068 if jobs.Status(status) != jobs.StatusPaused { 2069 return errors.New("could not pause job") 2070 } 2071 return nil 2072 }); err != nil { 2073 t.Fatal(err) 2074 } 2075 sqlDB.Exec(t, `INSERT INTO foo VALUES (16, 'f')`) 2076 sqlDB.Exec(t, `RESUME JOB $1`, foo.JobID) 2077 assertPayloads(t, foo, []string{ 2078 `foo: [16]->{"after": {"a": 16, "b": "f"}}`, 2079 }) 2080 } 2081 2082 // Only the enterprise version uses jobs. 2083 t.Run(`enterprise`, enterpriseTest(testFn)) 2084 } 2085 2086 func TestChangefeedPauseUnpauseCursorAndInitialScan(t *testing.T) { 2087 defer leaktest.AfterTest(t)() 2088 2089 defer func(i time.Duration) { jobs.DefaultAdoptInterval = i }(jobs.DefaultAdoptInterval) 2090 jobs.DefaultAdoptInterval = 10 * time.Millisecond 2091 2092 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 2093 sqlDB := sqlutils.MakeSQLRunner(db) 2094 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 2095 2096 sqlDB.Exec(t, `INSERT INTO foo VALUES (1, 'a'), (2, 'b'), (4, 'c'), (7, 'd'), (8, 'e')`) 2097 var tsStr string 2098 sqlDB.QueryRow(t, `SELECT cluster_logical_timestamp() from foo`).Scan(&tsStr) 2099 foo := feed(t, f, `CREATE CHANGEFEED FOR foo `+ 2100 `WITH initial_scan, resolved='10ms', cursor='`+tsStr+`'`).(*cdctest.TableFeed) 2101 defer closeFeed(t, foo) 2102 2103 assertPayloads(t, foo, []string{ 2104 `foo: [1]->{"after": {"a": 1, "b": "a"}}`, 2105 `foo: [2]->{"after": {"a": 2, "b": "b"}}`, 2106 `foo: [4]->{"after": {"a": 4, "b": "c"}}`, 2107 `foo: [7]->{"after": {"a": 7, "b": "d"}}`, 2108 `foo: [8]->{"after": {"a": 8, "b": "e"}}`, 2109 }) 2110 2111 // Wait for the high-water mark on the job to be updated after the initial 2112 // scan, to make sure we don't get the initial scan data again. 2113 expectResolvedTimestamp(t, foo) 2114 expectResolvedTimestamp(t, foo) 2115 2116 sqlDB.Exec(t, `PAUSE JOB $1`, foo.JobID) 2117 // PAUSE JOB only requests the job to be paused. Block until it's paused. 2118 opts := retry.Options{ 2119 InitialBackoff: 1 * time.Millisecond, 2120 MaxBackoff: time.Second, 2121 Multiplier: 2, 2122 } 2123 ctx := context.Background() 2124 if err := retry.WithMaxAttempts(ctx, opts, 10, func() error { 2125 var status string 2126 sqlDB.QueryRow(t, `SELECT status FROM system.jobs WHERE id = $1`, foo.JobID).Scan(&status) 2127 if jobs.Status(status) != jobs.StatusPaused { 2128 return errors.New("could not pause job") 2129 } 2130 return nil 2131 }); err != nil { 2132 t.Fatal(err) 2133 } 2134 foo.ResetSeen() 2135 sqlDB.Exec(t, `INSERT INTO foo VALUES (16, 'f')`) 2136 sqlDB.Exec(t, `RESUME JOB $1`, foo.JobID) 2137 assertPayloads(t, foo, []string{ 2138 `foo: [16]->{"after": {"a": 16, "b": "f"}}`, 2139 }) 2140 } 2141 2142 // Only the enterprise version uses jobs. 2143 t.Run(`enterprise`, enterpriseTest(testFn)) 2144 } 2145 2146 func TestChangefeedProtectedTimestamps(t *testing.T) { 2147 defer leaktest.AfterTest(t)() 2148 2149 defer func(i time.Duration) { jobs.DefaultAdoptInterval = i }(jobs.DefaultAdoptInterval) 2150 jobs.DefaultAdoptInterval = 10 * time.Millisecond 2151 2152 var ( 2153 ctx = context.Background() 2154 userSpan = roachpb.Span{ 2155 Key: keys.UserTableDataMin, 2156 EndKey: keys.TableDataMax, 2157 } 2158 done = make(chan struct{}) 2159 blockRequestCh = make(chan chan chan struct{}, 1) 2160 requestBlockedScan = func() (waitForBlockedScan func() (unblockScan func())) { 2161 blockRequest := make(chan chan struct{}) 2162 blockRequestCh <- blockRequest // test sends to filter to request a block 2163 return func() (unblockScan func()) { 2164 toClose := <-blockRequest // filter sends back to test to report blocked 2165 return func() { 2166 close(toClose) // test closes to unblock filter 2167 } 2168 } 2169 } 2170 requestFilter = kvserverbase.ReplicaRequestFilter(func( 2171 ctx context.Context, ba roachpb.BatchRequest, 2172 ) *roachpb.Error { 2173 if ba.Txn == nil || ba.Txn.Name != "changefeed backfill" { 2174 return nil 2175 } 2176 scanReq, ok := ba.GetArg(roachpb.Scan) 2177 if !ok { 2178 return nil 2179 } 2180 if !userSpan.Contains(scanReq.Header().Span()) { 2181 return nil 2182 } 2183 select { 2184 case notifyCh := <-blockRequestCh: 2185 waitUntilClosed := make(chan struct{}) 2186 notifyCh <- waitUntilClosed 2187 select { 2188 case <-waitUntilClosed: 2189 case <-done: 2190 } 2191 default: 2192 } 2193 return nil 2194 }) 2195 mkGetPtsRec = func(t *testing.T, ptp protectedts.Provider, clock *hlc.Clock) func() *ptpb.Record { 2196 return func() (r *ptpb.Record) { 2197 t.Helper() 2198 require.NoError(t, ptp.Refresh(ctx, clock.Now())) 2199 ptp.Iterate(ctx, userSpan.Key, userSpan.EndKey, func(record *ptpb.Record) (wantMore bool) { 2200 r = record 2201 return false 2202 }) 2203 return r 2204 } 2205 } 2206 mkCheckRecord = func(t *testing.T, tableID int) func(r *ptpb.Record) error { 2207 expectedKeys := map[string]struct{}{ 2208 string(keys.SystemSQLCodec.TablePrefix(uint32(tableID))): {}, 2209 string(keys.SystemSQLCodec.TablePrefix(keys.DescriptorTableID)): {}, 2210 } 2211 return func(ptr *ptpb.Record) error { 2212 if ptr == nil { 2213 return errors.Errorf("expected protected timestamp") 2214 } 2215 require.Equal(t, len(ptr.Spans), len(expectedKeys), ptr.Spans, expectedKeys) 2216 for _, s := range ptr.Spans { 2217 require.Contains(t, expectedKeys, string(s.Key)) 2218 } 2219 return nil 2220 } 2221 } 2222 checkNoRecord = func(ptr *ptpb.Record) error { 2223 if ptr != nil { 2224 return errors.Errorf("expected protected timestamp to not exist, found %v", ptr) 2225 } 2226 return nil 2227 } 2228 mkWaitForRecordCond = func(t *testing.T, getRecord func() *ptpb.Record, check func(record *ptpb.Record) error) func() { 2229 return func() { 2230 t.Helper() 2231 testutils.SucceedsSoon(t, func() error { return check(getRecord()) }) 2232 } 2233 } 2234 ) 2235 2236 t.Run(`enterprise`, enterpriseTestWithServerArgs( 2237 func(args *base.TestServerArgs) { 2238 storeKnobs := &kvserver.StoreTestingKnobs{} 2239 storeKnobs.TestingRequestFilter = requestFilter 2240 args.Knobs.Store = storeKnobs 2241 }, 2242 func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 2243 defer close(done) 2244 sqlDB := sqlutils.MakeSQLRunner(db) 2245 sqlDB.Exec(t, `ALTER RANGE default CONFIGURE ZONE USING gc.ttlseconds = 1`) 2246 sqlDB.Exec(t, `ALTER RANGE system CONFIGURE ZONE USING gc.ttlseconds = 1`) 2247 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 2248 sqlDB.Exec(t, `INSERT INTO foo VALUES (1, 'a'), (2, 'b'), (4, 'c'), (7, 'd'), (8, 'e')`) 2249 2250 var tableID int 2251 sqlDB.QueryRow(t, `SELECT table_id FROM crdb_internal.tables `+ 2252 `WHERE name = 'foo' AND database_name = current_database()`). 2253 Scan(&tableID) 2254 2255 ptp := f.Server().DistSQLServer().(*distsql.ServerImpl).ServerConfig.ProtectedTimestampProvider 2256 getPtsRec := mkGetPtsRec(t, ptp, f.Server().Clock()) 2257 waitForRecord := mkWaitForRecordCond(t, getPtsRec, mkCheckRecord(t, tableID)) 2258 waitForNoRecord := mkWaitForRecordCond(t, getPtsRec, checkNoRecord) 2259 waitForBlocked := requestBlockedScan() 2260 2261 foo := feed(t, f, `CREATE CHANGEFEED FOR foo WITH resolved`).(*cdctest.TableFeed) 2262 defer closeFeed(t, foo) 2263 { 2264 // Ensure that there's a protected timestamp on startup that goes 2265 // away after the initial scan. 2266 unblock := waitForBlocked() 2267 require.NotNil(t, getPtsRec()) 2268 unblock() 2269 assertPayloads(t, foo, []string{ 2270 `foo: [1]->{"after": {"a": 1, "b": "a"}}`, 2271 `foo: [2]->{"after": {"a": 2, "b": "b"}}`, 2272 `foo: [4]->{"after": {"a": 4, "b": "c"}}`, 2273 `foo: [7]->{"after": {"a": 7, "b": "d"}}`, 2274 `foo: [8]->{"after": {"a": 8, "b": "e"}}`, 2275 }) 2276 expectResolvedTimestamp(t, foo) 2277 waitForNoRecord() 2278 } 2279 2280 { 2281 // Ensure that a protected timestamp is created for a backfill due 2282 // to a schema change and removed after. 2283 waitForBlocked = requestBlockedScan() 2284 sqlDB.Exec(t, `ALTER TABLE foo ADD COLUMN c INT NOT NULL DEFAULT 1`) 2285 unblock := waitForBlocked() 2286 waitForRecord() 2287 unblock() 2288 assertPayloads(t, foo, []string{ 2289 `foo: [1]->{"after": {"a": 1, "b": "a", "c": 1}}`, 2290 `foo: [2]->{"after": {"a": 2, "b": "b", "c": 1}}`, 2291 `foo: [4]->{"after": {"a": 4, "b": "c", "c": 1}}`, 2292 `foo: [7]->{"after": {"a": 7, "b": "d", "c": 1}}`, 2293 `foo: [8]->{"after": {"a": 8, "b": "e", "c": 1}}`, 2294 }) 2295 expectResolvedTimestamp(t, foo) 2296 waitForNoRecord() 2297 } 2298 2299 { 2300 // Ensure that the protected timestamp is removed when the job is 2301 // canceled. 2302 waitForBlocked = requestBlockedScan() 2303 sqlDB.Exec(t, `ALTER TABLE foo ADD COLUMN d INT NOT NULL DEFAULT 2`) 2304 unblock := waitForBlocked() 2305 waitForRecord() 2306 sqlDB.Exec(t, `CANCEL JOB $1`, foo.JobID) 2307 waitForNoRecord() 2308 unblock() 2309 } 2310 })) 2311 } 2312 2313 func TestChangefeedProtectedTimestampOnPause(t *testing.T) { 2314 defer leaktest.AfterTest(t)() 2315 2316 defer func(i time.Duration) { jobs.DefaultAdoptInterval = i }(jobs.DefaultAdoptInterval) 2317 jobs.DefaultAdoptInterval = 10 * time.Millisecond 2318 2319 testutils.RunTrueAndFalse(t, "protect_on_pause", func(t *testing.T, shouldPause bool) { 2320 t.Run(`enterprise`, enterpriseTest(func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 2321 sqlDB := sqlutils.MakeSQLRunner(db) 2322 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 2323 sqlDB.Exec(t, `INSERT INTO foo VALUES (1, 'a'), (2, 'b'), (4, 'c'), (7, 'd'), (8, 'e')`) 2324 2325 var tableID int 2326 sqlDB.QueryRow(t, `SELECT table_id FROM crdb_internal.tables `+ 2327 `WHERE name = 'foo' AND database_name = current_database()`). 2328 Scan(&tableID) 2329 stmt := `CREATE CHANGEFEED FOR foo WITH resolved` 2330 if shouldPause { 2331 stmt += ", " + changefeedbase.OptProtectDataFromGCOnPause 2332 } 2333 foo := feed(t, f, stmt).(*cdctest.TableFeed) 2334 defer closeFeed(t, foo) 2335 assertPayloads(t, foo, []string{ 2336 `foo: [1]->{"after": {"a": 1, "b": "a"}}`, 2337 `foo: [2]->{"after": {"a": 2, "b": "b"}}`, 2338 `foo: [4]->{"after": {"a": 4, "b": "c"}}`, 2339 `foo: [7]->{"after": {"a": 7, "b": "d"}}`, 2340 `foo: [8]->{"after": {"a": 8, "b": "e"}}`, 2341 }) 2342 expectResolvedTimestamp(t, foo) 2343 2344 // Pause the job then ensure that it has a reasonable protected timestamp. 2345 2346 ctx := context.Background() 2347 serverCfg := f.Server().DistSQLServer().(*distsql.ServerImpl).ServerConfig 2348 jr := serverCfg.JobRegistry 2349 pts := serverCfg.ProtectedTimestampProvider 2350 2351 require.NoError(t, foo.Pause()) 2352 { 2353 j, err := jr.LoadJob(ctx, foo.JobID) 2354 require.NoError(t, err) 2355 progress := j.Progress() 2356 details := progress.Details.(*jobspb.Progress_Changefeed).Changefeed 2357 if shouldPause { 2358 require.NotEqual(t, uuid.Nil, details.ProtectedTimestampRecord) 2359 var r *ptpb.Record 2360 require.NoError(t, serverCfg.DB.Txn(ctx, func(ctx context.Context, txn *kv.Txn) (err error) { 2361 r, err = pts.GetRecord(ctx, txn, details.ProtectedTimestampRecord) 2362 return err 2363 })) 2364 require.Equal(t, r.Timestamp, *progress.GetHighWater()) 2365 } else { 2366 require.Equal(t, uuid.Nil, details.ProtectedTimestampRecord) 2367 } 2368 } 2369 2370 // Resume the job and ensure that the protected timestamp is removed once 2371 // the changefeed has caught up. 2372 require.NoError(t, foo.Resume()) 2373 testutils.SucceedsSoon(t, func() error { 2374 expectResolvedTimestamp(t, foo) 2375 j, err := jr.LoadJob(ctx, foo.JobID) 2376 require.NoError(t, err) 2377 details := j.Progress().Details.(*jobspb.Progress_Changefeed).Changefeed 2378 if details.ProtectedTimestampRecord != uuid.Nil { 2379 return fmt.Errorf("expected no protected timestamp record") 2380 } 2381 return nil 2382 }) 2383 2384 })) 2385 }) 2386 2387 } 2388 2389 // This test ensures that the changefeed attempts to verify its initial protected 2390 // timestamp record and that when that verification fails, the job is canceled 2391 // and the record removed. 2392 func TestChangefeedProtectedTimestampsVerificationFails(t *testing.T) { 2393 defer leaktest.AfterTest(t)() 2394 2395 defer func(i time.Duration) { jobs.DefaultAdoptInterval = i }(jobs.DefaultAdoptInterval) 2396 jobs.DefaultAdoptInterval = 10 * time.Millisecond 2397 2398 verifyRequestCh := make(chan *roachpb.AdminVerifyProtectedTimestampRequest, 1) 2399 requestFilter := kvserverbase.ReplicaRequestFilter(func( 2400 ctx context.Context, ba roachpb.BatchRequest, 2401 ) *roachpb.Error { 2402 if r, ok := ba.GetArg(roachpb.AdminVerifyProtectedTimestamp); ok { 2403 req := r.(*roachpb.AdminVerifyProtectedTimestampRequest) 2404 verifyRequestCh <- req 2405 return roachpb.NewError(errors.Errorf("failed to verify protection %v on %v", req.RecordID, ba.RangeID)) 2406 } 2407 return nil 2408 }) 2409 t.Run(`enterprise`, enterpriseTestWithServerArgs( 2410 func(args *base.TestServerArgs) { 2411 storeKnobs := &kvserver.StoreTestingKnobs{} 2412 storeKnobs.TestingRequestFilter = requestFilter 2413 args.Knobs.Store = storeKnobs 2414 }, 2415 func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 2416 ctx := context.Background() 2417 sqlDB := sqlutils.MakeSQLRunner(db) 2418 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 2419 _, err := f.Feed(`CREATE CHANGEFEED FOR foo WITH resolved`) 2420 // Make sure we got the injected error. 2421 require.Regexp(t, "failed to verify", err) 2422 // Make sure we tried to verify the request. 2423 r := <-verifyRequestCh 2424 cfg := f.Server().ExecutorConfig().(sql.ExecutorConfig) 2425 kvDB := cfg.DB 2426 pts := cfg.ProtectedTimestampProvider 2427 // Make sure that the canceled job gets moved through its OnFailOrCancel 2428 // phase and removes its protected timestamp. 2429 testutils.SucceedsSoon(t, func() error { 2430 err := kvDB.Txn(ctx, func(ctx context.Context, txn *kv.Txn) error { 2431 _, err := pts.GetRecord(ctx, txn, r.RecordID) 2432 return err 2433 }) 2434 if err == nil { 2435 return errors.Errorf("expected record to be removed") 2436 } 2437 if crdberrors.Is(err, protectedts.ErrNotExists) { 2438 return nil 2439 } 2440 return err 2441 }) 2442 })) 2443 } 2444 2445 func TestManyChangefeedsOneTable(t *testing.T) { 2446 defer leaktest.AfterTest(t)() 2447 2448 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 2449 sqlDB := sqlutils.MakeSQLRunner(db) 2450 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 2451 sqlDB.Exec(t, `INSERT INTO foo VALUES (0, 'init')`) 2452 2453 foo1 := feed(t, f, `CREATE CHANGEFEED FOR foo WITH diff`) 2454 defer closeFeed(t, foo1) 2455 foo2 := feed(t, f, `CREATE CHANGEFEED FOR foo`) // without diff 2456 defer closeFeed(t, foo2) 2457 foo3 := feed(t, f, `CREATE CHANGEFEED FOR foo WITH diff`) 2458 defer closeFeed(t, foo3) 2459 2460 // Make sure all the changefeeds are going. 2461 assertPayloads(t, foo1, []string{`foo: [0]->{"after": {"a": 0, "b": "init"}, "before": null}`}) 2462 assertPayloads(t, foo2, []string{`foo: [0]->{"after": {"a": 0, "b": "init"}}`}) 2463 assertPayloads(t, foo3, []string{`foo: [0]->{"after": {"a": 0, "b": "init"}, "before": null}`}) 2464 2465 sqlDB.Exec(t, `UPSERT INTO foo VALUES (0, 'v0')`) 2466 assertPayloads(t, foo1, []string{ 2467 `foo: [0]->{"after": {"a": 0, "b": "v0"}, "before": {"a": 0, "b": "init"}}`, 2468 }) 2469 2470 sqlDB.Exec(t, `INSERT INTO foo VALUES (1, 'v1')`) 2471 assertPayloads(t, foo1, []string{ 2472 `foo: [1]->{"after": {"a": 1, "b": "v1"}, "before": null}`, 2473 }) 2474 assertPayloads(t, foo2, []string{ 2475 `foo: [0]->{"after": {"a": 0, "b": "v0"}}`, 2476 `foo: [1]->{"after": {"a": 1, "b": "v1"}}`, 2477 }) 2478 2479 sqlDB.Exec(t, `UPSERT INTO foo VALUES (0, 'v2')`) 2480 assertPayloads(t, foo1, []string{ 2481 `foo: [0]->{"after": {"a": 0, "b": "v2"}, "before": {"a": 0, "b": "v0"}}`, 2482 }) 2483 assertPayloads(t, foo2, []string{ 2484 `foo: [0]->{"after": {"a": 0, "b": "v2"}}`, 2485 }) 2486 assertPayloads(t, foo3, []string{ 2487 `foo: [0]->{"after": {"a": 0, "b": "v0"}, "before": {"a": 0, "b": "init"}}`, 2488 `foo: [0]->{"after": {"a": 0, "b": "v2"}, "before": {"a": 0, "b": "v0"}}`, 2489 `foo: [1]->{"after": {"a": 1, "b": "v1"}, "before": null}`, 2490 }) 2491 } 2492 2493 t.Run(`sinkless`, sinklessTest(testFn)) 2494 t.Run(`enterprise`, enterpriseTest(testFn)) 2495 } 2496 2497 func TestUnspecifiedPrimaryKey(t *testing.T) { 2498 defer leaktest.AfterTest(t)() 2499 2500 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 2501 sqlDB := sqlutils.MakeSQLRunner(db) 2502 sqlDB.Exec(t, `CREATE TABLE foo (a INT)`) 2503 var id0 int 2504 sqlDB.QueryRow(t, `INSERT INTO foo VALUES (0) RETURNING rowid`).Scan(&id0) 2505 2506 foo := feed(t, f, `CREATE CHANGEFEED FOR foo`) 2507 defer closeFeed(t, foo) 2508 2509 var id1 int 2510 sqlDB.QueryRow(t, `INSERT INTO foo VALUES (1) RETURNING rowid`).Scan(&id1) 2511 2512 assertPayloads(t, foo, []string{ 2513 fmt.Sprintf(`foo: [%d]->{"after": {"a": 0, "rowid": %d}}`, id0, id0), 2514 fmt.Sprintf(`foo: [%d]->{"after": {"a": 1, "rowid": %d}}`, id1, id1), 2515 }) 2516 } 2517 2518 t.Run(`sinkless`, sinklessTest(testFn)) 2519 t.Run(`enterprise`, enterpriseTest(testFn)) 2520 } 2521 2522 // TestChangefeedNodeShutdown ensures that an enterprise changefeed continues 2523 // running after the original job-coordinator node is shut down. 2524 func TestChangefeedNodeShutdown(t *testing.T) { 2525 defer leaktest.AfterTest(t)() 2526 t.Skip("#32232") 2527 2528 defer func(oldInterval time.Duration) { 2529 jobs.DefaultAdoptInterval = oldInterval 2530 }(jobs.DefaultAdoptInterval) 2531 jobs.DefaultAdoptInterval = 100 * time.Millisecond 2532 2533 flushCh := make(chan struct{}, 1) 2534 defer close(flushCh) 2535 knobs := base.TestingKnobs{DistSQL: &execinfra.TestingKnobs{Changefeed: &TestingKnobs{ 2536 AfterSinkFlush: func() error { 2537 select { 2538 case flushCh <- struct{}{}: 2539 default: 2540 } 2541 return nil 2542 }, 2543 }}} 2544 2545 tc := serverutils.StartTestCluster(t, 3, base.TestClusterArgs{ 2546 ServerArgs: base.TestServerArgs{ 2547 UseDatabase: "d", 2548 Knobs: knobs, 2549 }, 2550 }) 2551 defer tc.Stopper().Stop(context.Background()) 2552 2553 db := tc.ServerConn(1) 2554 sqlDB := sqlutils.MakeSQLRunner(db) 2555 sqlDB.Exec(t, `SET CLUSTER SETTING changefeed.experimental_poll_interval = '0ns'`) 2556 sqlDB.Exec(t, `CREATE DATABASE d`) 2557 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 2558 sqlDB.Exec(t, `INSERT INTO foo VALUES (0, 'initial')`) 2559 2560 // Create a factory which uses server 1 as the output of the Sink, but 2561 // executes the CREATE CHANGEFEED statement on server 0. 2562 sink, cleanup := sqlutils.PGUrl( 2563 t, tc.Server(0).ServingSQLAddr(), t.Name(), url.User(security.RootUser)) 2564 defer cleanup() 2565 f := cdctest.MakeTableFeedFactory(tc.Server(1), tc.ServerConn(0), flushCh, sink) 2566 foo := feed(t, f, "CREATE CHANGEFEED FOR foo") 2567 defer closeFeed(t, foo) 2568 2569 sqlDB.Exec(t, `INSERT INTO foo VALUES (1, 'second')`) 2570 assertPayloads(t, foo, []string{ 2571 `foo: [0]->{"after": {"a": 0, "b": "initial"}}`, 2572 `foo: [1]->{"after": {"a": 1, "b": "second"}}`, 2573 }) 2574 2575 // TODO(mrtracy): At this point we need to wait for a resolved timestamp, 2576 // in order to ensure that there isn't a repeat when the job is picked up 2577 // again. As an alternative, we could use a verifier instead of assertPayloads. 2578 2579 // Wait for the high-water mark on the job to be updated after the initial 2580 // scan, to make sure we don't get the initial scan data again. 2581 2582 // Stop server 0, which is where the table feed connects. 2583 tc.StopServer(0) 2584 2585 sqlDB.Exec(t, `UPSERT INTO foo VALUES(0, 'updated')`) 2586 sqlDB.Exec(t, `INSERT INTO foo VALUES (3, 'third')`) 2587 2588 assertPayloads(t, foo, []string{ 2589 `foo: [0]->{"after": {"a": 0, "b": "updated"}}`, 2590 `foo: [3]->{"after": {"a": 3, "b": "third"}}`, 2591 }) 2592 } 2593 2594 func TestChangefeedTelemetry(t *testing.T) { 2595 defer leaktest.AfterTest(t)() 2596 2597 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 2598 sqlDB := sqlutils.MakeSQLRunner(db) 2599 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY)`) 2600 sqlDB.Exec(t, `INSERT INTO foo VALUES (1)`) 2601 sqlDB.Exec(t, `CREATE TABLE bar (a INT PRIMARY KEY)`) 2602 sqlDB.Exec(t, `INSERT INTO bar VALUES (1)`) 2603 2604 // Reset the counts. 2605 _ = telemetry.GetFeatureCounts(telemetry.Raw, telemetry.ResetCounts) 2606 2607 // Start some feeds (and read from them to make sure they've started. 2608 foo := feed(t, f, `CREATE CHANGEFEED FOR foo`) 2609 defer closeFeed(t, foo) 2610 fooBar := feed(t, f, `CREATE CHANGEFEED FOR foo, bar WITH format=json`) 2611 defer closeFeed(t, fooBar) 2612 assertPayloads(t, foo, []string{ 2613 `foo: [1]->{"after": {"a": 1}}`, 2614 }) 2615 assertPayloads(t, fooBar, []string{ 2616 `bar: [1]->{"after": {"a": 1}}`, 2617 `foo: [1]->{"after": {"a": 1}}`, 2618 }) 2619 2620 var expectedSink string 2621 if strings.Contains(t.Name(), `sinkless`) || strings.Contains(t.Name(), `poller`) { 2622 expectedSink = `sinkless` 2623 } else { 2624 expectedSink = `experimental-sql` 2625 } 2626 2627 counts := telemetry.GetFeatureCounts(telemetry.Raw, telemetry.ResetCounts) 2628 require.Equal(t, int32(2), counts[`changefeed.create.sink.`+expectedSink]) 2629 require.Equal(t, int32(2), counts[`changefeed.create.format.json`]) 2630 require.Equal(t, int32(1), counts[`changefeed.create.num_tables.1`]) 2631 require.Equal(t, int32(1), counts[`changefeed.create.num_tables.2`]) 2632 } 2633 2634 t.Run(`sinkless`, sinklessTest(testFn)) 2635 t.Run(`enterprise`, enterpriseTest(testFn)) 2636 } 2637 2638 func TestChangefeedMemBufferCapacity(t *testing.T) { 2639 defer leaktest.AfterTest(t)() 2640 2641 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 2642 knobs := f.Server().(*server.TestServer).Cfg.TestingKnobs. 2643 DistSQL.(*execinfra.TestingKnobs). 2644 Changefeed.(*TestingKnobs) 2645 // The RowContainer used internally by the memBuffer seems to request from 2646 // the budget in 10240 chunks. Set this number high enough for one but not 2647 // for a second. I'd love to be able to derive this from constants, but I 2648 // don't see how to do that without a refactor. 2649 knobs.MemBufferCapacity = 20000 2650 beforeEmitRowCh := make(chan struct{}, 1) 2651 knobs.BeforeEmitRow = func(ctx context.Context) error { 2652 select { 2653 case <-ctx.Done(): 2654 return ctx.Err() 2655 case <-beforeEmitRowCh: 2656 } 2657 return nil 2658 } 2659 defer close(beforeEmitRowCh) 2660 2661 sqlDB := sqlutils.MakeSQLRunner(db) 2662 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY, b STRING)`) 2663 sqlDB.Exec(t, `INSERT INTO foo VALUES (0, 'small')`) 2664 2665 foo := feed(t, f, `CREATE CHANGEFEED FOR foo`) 2666 defer closeFeed(t, foo) 2667 2668 // Small amounts of data fit in the buffer. 2669 beforeEmitRowCh <- struct{}{} 2670 assertPayloads(t, foo, []string{ 2671 `foo: [0]->{"after": {"a": 0, "b": "small"}}`, 2672 }) 2673 2674 // Put enough data in to overflow the buffer and verify that at some point 2675 // we get the "memory budget exceeded" error. 2676 sqlDB.Exec(t, `INSERT INTO foo SELECT i, 'foofoofoo' FROM generate_series(1, $1) AS g(i)`, 1000) 2677 if _, err := foo.Next(); !testutils.IsError(err, `memory budget exceeded`) { 2678 t.Fatalf(`expected "memory budget exceeded" error got: %v`, err) 2679 } 2680 } 2681 2682 // The mem buffer is only used with RangeFeed. 2683 t.Run(`sinkless`, sinklessTest(testFn)) 2684 t.Run(`enterprise`, enterpriseTest(testFn)) 2685 } 2686 2687 // Regression test for #41694. 2688 func TestChangefeedRestartDuringBackfill(t *testing.T) { 2689 defer leaktest.AfterTest(t)() 2690 2691 defer func(i time.Duration) { jobs.DefaultAdoptInterval = i }(jobs.DefaultAdoptInterval) 2692 jobs.DefaultAdoptInterval = 10 * time.Millisecond 2693 2694 testFn := func(t *testing.T, db *gosql.DB, f cdctest.TestFeedFactory) { 2695 knobs := f.Server().(*server.TestServer).Cfg.TestingKnobs. 2696 DistSQL.(*execinfra.TestingKnobs). 2697 Changefeed.(*TestingKnobs) 2698 beforeEmitRowCh := make(chan error, 20) 2699 knobs.BeforeEmitRow = func(ctx context.Context) error { 2700 select { 2701 case <-ctx.Done(): 2702 return ctx.Err() 2703 case err := <-beforeEmitRowCh: 2704 return err 2705 } 2706 } 2707 2708 sqlDB := sqlutils.MakeSQLRunner(db) 2709 sqlDB.Exec(t, `CREATE TABLE foo (a INT PRIMARY KEY)`) 2710 sqlDB.Exec(t, `INSERT INTO foo VALUES (0), (1), (2), (3)`) 2711 2712 foo := feed(t, f, `CREATE CHANGEFEED FOR foo WITH diff`).(*cdctest.TableFeed) 2713 defer closeFeed(t, foo) 2714 2715 // TODO(dan): At a high level, all we're doing is trying to restart a 2716 // changefeed in the middle of changefeed backfill after a schema change 2717 // finishes. It turns out this is pretty hard to do with our current testing 2718 // knobs and this test ends up being pretty brittle. I'd love it if anyone 2719 // thought of a better way to do this. 2720 2721 // Read the initial data in the rows. 2722 for i := 0; i < 4; i++ { 2723 beforeEmitRowCh <- nil 2724 } 2725 assertPayloads(t, foo, []string{ 2726 `foo: [0]->{"after": {"a": 0}, "before": null}`, 2727 `foo: [1]->{"after": {"a": 1}, "before": null}`, 2728 `foo: [2]->{"after": {"a": 2}, "before": null}`, 2729 `foo: [3]->{"after": {"a": 3}, "before": null}`, 2730 }) 2731 2732 // Run a schema change that backfills kvs. 2733 sqlDB.Exec(t, `ALTER TABLE foo ADD COLUMN b STRING DEFAULT 'backfill'`) 2734 2735 // Unblock emit for each kv written by the schema change's backfill. The 2736 // changefeed actually emits these, but we lose it to overaggressive 2737 // duplicate detection in tableFeed. 2738 // TODO(dan): Track duplicates more precisely in tableFeed. 2739 for i := 0; i < 4; i++ { 2740 beforeEmitRowCh <- nil 2741 } 2742 2743 // Unblock the emit for *all but one* of the rows emitted by the changefeed 2744 // backfill (run after the schema change completes and the final table 2745 // descriptor is written). The reason this test has 4 rows is because the 2746 // `sqlSink` that powers `tableFeed` only flushes after it has 3 rows, so we 2747 // need 1 more than that to guarantee that this first one gets flushed. 2748 for i := 0; i < 3; i++ { 2749 beforeEmitRowCh <- nil 2750 } 2751 assertPayloads(t, foo, []string{ 2752 `foo: [0]->{"after": {"a": 0}, "before": {"a": 0}}`, 2753 `foo: [1]->{"after": {"a": 1}, "before": {"a": 1}}`, 2754 `foo: [2]->{"after": {"a": 2}, "before": {"a": 2}}`, 2755 `foo: [3]->{"after": {"a": 3}, "before": {"a": 3}}`, 2756 `foo: [0]->{"after": {"a": 0, "b": "backfill"}, "before": {"a": 0}}`, 2757 }) 2758 2759 // Restart the changefeed without allowing the second row to be backfilled. 2760 sqlDB.Exec(t, `PAUSE JOB $1`, foo.JobID) 2761 // PAUSE JOB only requests the job to be paused. Block until it's paused. 2762 opts := retry.Options{ 2763 InitialBackoff: 1 * time.Millisecond, 2764 MaxBackoff: time.Second, 2765 Multiplier: 2, 2766 } 2767 ctx := context.Background() 2768 if err := retry.WithMaxAttempts(ctx, opts, 10, func() error { 2769 var status string 2770 sqlDB.QueryRow(t, `SELECT status FROM system.jobs WHERE id = $1`, foo.JobID).Scan(&status) 2771 if jobs.Status(status) != jobs.StatusPaused { 2772 return errors.New("could not pause job") 2773 } 2774 return nil 2775 }); err != nil { 2776 t.Fatal(err) 2777 } 2778 // Make extra sure that the zombie changefeed can't write any more data. 2779 beforeEmitRowCh <- MarkRetryableError(errors.New(`nope don't write it`)) 2780 2781 // Insert some data that we should only see out of the changefeed after it 2782 // re-runs the backfill. 2783 sqlDB.Exec(t, `INSERT INTO foo VALUES (6, 'bar')`) 2784 2785 // Unblock all later emits, we don't need this control anymore. 2786 close(beforeEmitRowCh) 2787 2788 // Resume the changefeed and the backfill should start up again. Currently 2789 // this does the entire backfill again, you could imagine in the future that 2790 // we do some sort of backfill checkpointing and start the backfill up from 2791 // the last checkpoint. 2792 sqlDB.Exec(t, `RESUME JOB $1`, foo.JobID) 2793 assertPayloads(t, foo, []string{ 2794 // The changefeed actually emits this row, but we lose it to 2795 // overaggressive duplicate detection in tableFeed. 2796 // TODO(dan): Track duplicates more precisely in sinklessFeed/tableFeed. 2797 // `foo: [0]->{"after": {"a": 0, "b": "backfill"}}`, 2798 `foo: [1]->{"after": {"a": 1, "b": "backfill"}, "before": {"a": 1}}`, 2799 `foo: [2]->{"after": {"a": 2, "b": "backfill"}, "before": {"a": 2}}`, 2800 `foo: [3]->{"after": {"a": 3, "b": "backfill"}, "before": {"a": 3}}`, 2801 }) 2802 2803 assertPayloads(t, foo, []string{ 2804 `foo: [6]->{"after": {"a": 6, "b": "bar"}, "before": null}`, 2805 }) 2806 } 2807 2808 // Only the enterprise version uses jobs. 2809 t.Run(`enterprise`, enterpriseTest(testFn)) 2810 } 2811 2812 func TestChangefeedHandlesDrainingNodes(t *testing.T) { 2813 defer leaktest.AfterTest(t)() 2814 flushCh := make(chan struct{}, 1) 2815 defer close(flushCh) 2816 2817 if util.RaceEnabled { 2818 t.Skip("takes too long with race enabled") 2819 } 2820 2821 shouldDrain := true 2822 knobs := base.TestingKnobs{DistSQL: &execinfra.TestingKnobs{ 2823 DrainFast: true, 2824 Changefeed: &TestingKnobs{ 2825 AfterSinkFlush: func() error { 2826 select { 2827 case flushCh <- struct{}{}: 2828 default: 2829 } 2830 return nil 2831 }, 2832 }, 2833 Flowinfra: &flowinfra.TestingKnobs{ 2834 FlowRegistryDraining: func() bool { 2835 if shouldDrain { 2836 shouldDrain = false 2837 return true 2838 } 2839 return false 2840 }, 2841 }, 2842 }} 2843 2844 sinkDir, cleanupFn := testutils.TempDir(t) 2845 defer cleanupFn() 2846 2847 tc := serverutils.StartTestCluster(t, 4, base.TestClusterArgs{ 2848 ServerArgs: base.TestServerArgs{ 2849 UseDatabase: "test", 2850 Knobs: knobs, 2851 ExternalIODir: sinkDir, 2852 }}) 2853 defer tc.Stopper().Stop(context.Background()) 2854 2855 db := tc.ServerConn(1) 2856 sqlDB := sqlutils.MakeSQLRunner(db) 2857 sqlDB.Exec(t, `SET CLUSTER SETTING kv.rangefeed.enabled = true`) 2858 sqlDB.Exec(t, `SET CLUSTER SETTING kv.closed_timestamp.target_duration = '1s'`) 2859 sqlDB.Exec(t, `SET CLUSTER SETTING changefeed.experimental_poll_interval = '10ms'`) 2860 2861 sqlutils.CreateTable( 2862 t, db, "foo", 2863 "k INT PRIMARY KEY, v INT", 2864 10, 2865 sqlutils.ToRowFn(sqlutils.RowIdxFn, sqlutils.RowModuloFn(2)), 2866 ) 2867 2868 // Introduce 4 splits to get 5 ranges. We need multiple ranges in order to run distributed 2869 // flow. 2870 sqlDB.Exec(t, "ALTER TABLE test.foo SPLIT AT (SELECT i*2 FROM generate_series(1, 4) AS g(i))") 2871 sqlDB.Exec(t, "ALTER TABLE test.foo SCATTER") 2872 2873 // Create a factory which executes the CREATE CHANGEFEED statement on server 0. 2874 // This statement should fail, but the job itself ought to be creaated. 2875 // After some time, that job should be adopted by another node, and executed successfully. 2876 f := cdctest.MakeCloudFeedFactory(tc.Server(1), tc.ServerConn(0), sinkDir, flushCh) 2877 2878 feed := feed(t, f, "CREATE CHANGEFEED FOR foo") 2879 defer closeFeed(t, feed) 2880 2881 // At this point, the job created by feed will fail to start running on node 0 due to draining 2882 // registry. However, this job will be retried, and it should succeeded. 2883 // Note: This test is a bit unrealistic in that if the registry is draining, that 2884 // means that the server is draining (i.e being shut down). We don't do a full shutdown 2885 // here, but we are simulating a restart by failing to start a flow the first time around. 2886 assertPayloads(t, feed, []string{ 2887 `foo: [1]->{"after": {"k": 1, "v": 1}}`, 2888 `foo: [2]->{"after": {"k": 2, "v": 0}}`, 2889 `foo: [3]->{"after": {"k": 3, "v": 1}}`, 2890 `foo: [4]->{"after": {"k": 4, "v": 0}}`, 2891 `foo: [5]->{"after": {"k": 5, "v": 1}}`, 2892 `foo: [6]->{"after": {"k": 6, "v": 0}}`, 2893 `foo: [7]->{"after": {"k": 7, "v": 1}}`, 2894 `foo: [8]->{"after": {"k": 8, "v": 0}}`, 2895 `foo: [9]->{"after": {"k": 9, "v": 1}}`, 2896 `foo: [10]->{"after": {"k": 10, "v": 0}}`, 2897 }) 2898 }