Cockroach batch insert bug

main.go

package main

import (
	"database/sql"
	"fmt"
	"strconv"
	"strings"
	"time"

	"github.com/google/uuid"
	_ "github.com/lib/pq"
)

const (
	listInterval = 10 * time.Millisecond
	batchSize    = 1000
)

func main() {
	dbname := "db" + strconv.Itoa(int(uuid.New().ID()))
	connString := fmt.Sprintf("postgresql://root@localhost:26257/%s?sslmode=disable", dbname)
	db, err := sql.Open("postgres", connString)
	if err != nil {
		panic(err)
	}
	_, err = db.Exec(`CREATE DATABASE ` + dbname)
	if err != nil {
		panic("cannot create database: " + err.Error())
	}

	defer db.Exec(`DROP DATABASE ` + dbname)
	reproduceBug(db)
}

func createSchema(db *sql.DB) {
	_, err := db.Exec(`
CREATE TABLE table1 (id STRING PRIMARY KEY);
CREATE TABLE table2 (
    id STRING PRIMARY KEY,
    indexed_id STRING NOT NULL,
    fk STRING REFERENCES table1(id) ON DELETE CASCADE,
    INDEX (indexed_id)
);
`)
	if err != nil {
		panic("cannot create schema: " + err.Error())
	}

	_, err = db.Exec(`INSERT INTO table1(id) VALUES ($1)`, `fk_id`)
	if err != nil {
		panic("cannot insert into table1: " + err.Error())
	}
}

func insertRoutine(db *sql.DB) {
	var insert int

	for {
		insert++
		fmt.Println("insert:", insert)

		cols := []string{"id", "indexed_id", "fk"}

		var params []string
		var args []interface{}
		for i := 0; i < batchSize; i++ {
			var placeholders []string
			for c := 0; c < len(cols); c++ {
				placeholders = append(placeholders, "$"+strconv.Itoa(i*len(cols)+c+1))
			}
			params = append(params, "("+strings.Join(placeholders, ", ")+")")
			args = append(args,
				uuid.New().String(),
				uuid.New().String(),
				"fk_id",
			)
		}

		query := `INSERT INTO table2(` + strings.Join(cols, ", ") + `) VALUES ` + strings.Join(params, ", ")

		_, err := db.Exec(query, args...)
		if err != nil {
			fmt.Println("insert:", insert, "err:", err)

			query := `SELECT COUNT(1) FROM table2`
			var cnt int
			db.QueryRow(query).Scan(&cnt)
			fmt.Println("Number of rows:", cnt)
			return
		}
	}
}

func listRoutine(db *sql.DB) {
	var attempt int
	for {
		attempt++
		fmt.Println("list:", attempt)
		query := `SELECT id FROM table2`
		rows, err := db.Query(query)
		if err != nil {
			fmt.Println("select error:", err)
			return
		}
		defer rows.Close()

		var id string
		for rows.Next() {
			err := rows.Scan(&id)
			if err != nil {
				fmt.Println("scan error:", err)
				return
			}
		}

		if err := rows.Err(); err != nil {
			fmt.Println("iterate error:", err)
			return
		}
		time.Sleep(listInterval)
	}
}

func reproduceBug(db *sql.DB) {
	createSchema(db)
	go listRoutine(db)
	insertRoutine(db)
}