sql: could not publish new descriptors when creating users within a transaction

[jaylim-crl]

Describe the problem

When trying to create a SQL user through the CRDB transaction wrapper within cockroach-go, we get the following error: pq: cannot publish new versions for descriptors: [{users 4 1} {role_options 33 1}], old versions still in use.

To Reproduce

Script to reproduce:

package main

import (
	"context"
	"database/sql"
	"fmt"
	"sync"

	"github.com/cockroachdb/cockroach-go/v2/crdb"
	_ "github.com/lib/pq"
)

func main() {
	db, err := sql.Open("postgres", "postgresql://root@localhost:26257/defaultdb?sslmode=disable")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	err = db.Ping()
	if err != nil {
		panic(err)
	}

	ctx := context.Background()
	var wg sync.WaitGroup
	for i := 170; i < 180; i++ {
		wg.Add(1)
		go func(i int) {
			defer wg.Done()
			if err := crdb.ExecuteTx(ctx, db, nil, func(tx *sql.Tx) error {
				query := fmt.Sprintf(`CREATE USER IF NOT EXISTS "user%d"`, i)
				_, err := tx.ExecContext(ctx, query)
				return err
			}); err != nil {
				panic(err)
			}
		}(i)
	}
	wg.Wait()
}

Expected behavior

We expected that the script completes successfully.

Additional data

This issue seems to manifest on v22.2.0 onwards. The error doesn't show up on v22.1.21 (and prior versions).

Environment:

• CockroachDB version: v22.2.0 onwards
• cockroach-go SHA: 558bee73bf0634385a862b19708de4ed19316fbb
• Server OS: MacOS

Jira issue: CRDB-29933

[jaylim-crl]

cc: @rafiss

[rafiss]

Triage notes:

This cannot publish new versions for descriptors error is supposed to be retryable - in the code it is a twoVersionInvariantViolationError. We should check to make sure we are marking it is retryable correctly. The cockroach-go library already has logic to retry all 40001 errors: https://github.com/cockroachdb/cockroach-go/blob/687b669a01b75ff47d5e74d01f340280e42e5ca0/crdb/tx.go#L204