sql: race condition with fast path for insert

[rafiss]

#42914 added a specialized execution operator that allows running foreign key checks before (or in parallel with) a mutation.

This seems to have created a race condition, which I discovered because of a test that started failing in the Hibernate repo. The test is org.hibernate.test.annotations.cid.CompositeIdTest.testSecondaryTableWithCompositeId, but here is how to reproduce the issue with just SQL from the CLI interface.

Run the following all at once by copy-pasting into the CLI:

CREATE TABLE presenter (
                name VARCHAR(255) NOT NULL,
                CONSTRAINT "primary" PRIMARY KEY (name ASC),
                FAMILY "primary" (name)
              );

CREATE TABLE channel (
                  id INT4 NOT NULL,
                  name VARCHAR(255) NULL,
                  CONSTRAINT "primary" PRIMARY KEY (id ASC),
                  FAMILY "primary" (id, name)
              );

CREATE TABLE tvprogram (
                  "time" TIME NULL,
                  chan_id INT4 NOT NULL,
                  presenter_name VARCHAR(255) NOT NULL,
                  CONSTRAINT "primary" PRIMARY KEY (chan_id ASC, presenter_name ASC),
                  CONSTRAINT fkoeti4r4k3x1hm4rb2r8o15ce2 FOREIGN KEY (chan_id) REFERENCES channel(id),
                  CONSTRAINT fk96yxppjh6mrelfbgspmfr0i2r FOREIGN KEY (presenter_name) REFERENCES presenter(name),
                  INDEX tvprogram_auto_index_fk96yxppjh6mrelfbgspmfr0i2r (presenter_name ASC),
                  FAMILY "primary" ("time", chan_id, presenter_name)
              );

CREATE TABLE tv_program_ext (
                      txt VARCHAR(255) NULL,
                      channel_id INT4 NOT NULL,
                      presenter_name VARCHAR(255) NOT NULL,
                      CONSTRAINT "primary" PRIMARY KEY (channel_id ASC, presenter_name ASC),
                      CONSTRAINT fktms6bhx8rws68estjr0qvh0tk FOREIGN KEY (channel_id, presenter_name) REFERENCES tvprogram(chan_id, presenter_name),
                      FAMILY "primary" (txt, channel_id, presenter_name)
                  );

insert into Channel (name, id) values (null, 8);
insert into Presenter (name) values ('tim russett');
insert into TvProgram ("time", chan_id, presenter_name) values ('2020-01-13T17:40:02-5:00', 8, 'tim russett');
insert into TV_PROGRAM_EXT (TXT, CHANNEL_ID, PRESENTER_NAME) values ('Award Winning Programming', 8, 'tim russett');

The following error occurs

ERROR: internal error: 2 FK cols, only 1 cols in index
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/insert_fast_path.go:103: init()
github.com/cockroachdb/cockroach/pkg/sql/insert_fast_path.go:235: startExec()
github.com/cockroachdb/cockroach/pkg/sql/plan.go:337: func2()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:142: func1()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:679: visitInternal()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:122: visitConcrete()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:567: visitInternal()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:109: visit()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:73: walkPlan()
github.com/cockroachdb/cockroach/pkg/sql/plan.go:340: startExec()
github.com/cockroachdb/cockroach/pkg/sql/plan_node_to_row_source.go:117: Start()
github.com/cockroachdb/cockroach/pkg/sql/execinfra/processorsbase.go:742: Run()
github.com/cockroachdb/cockroach/pkg/sql/flowinfra/flow.go:373: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:374: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:976: PlanAndRun()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:823: execWithDistSQLEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:715: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:418: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:100: execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1283: execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1212: run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:451: ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:566: func1()
runtime/asm_amd64.s:1337: goexit()

However, if I run the above statements one-by-one, with some time in between each one, it seems to work. Also, usually if I retry running the last statement (inserting into TV_PROGRAM_EXT), then it gets executed successfully.

[RaduBerinde]

The change happens because statistics kick in and for such small tables, the FK checks end up being hash joins (because of #41701). The failure would show up again with more data in the tables. This should be 100% reproducible if the auto stats are turned off.

[RaduBerinde]

The problem is that the optimizer chooses the tvprogram_auto_index_fk96yxppjh6mrelfbgspmfr0i2r index for lookup instead of the PK (which makes sense), but in this index the second FK column is implicit. The fast path insert code, as well as EncodePartialIndexKey doesn't support that correctly.