Post Commit step on User Creation can block for a long time

[fantapop]

Describe the problem

When running a CREATE USER statement while other long running transactions are happening, I've observed a long delay at transaction commit time. The new user is visible during this delay when running SHOW USERS.

Expected behavior
Ideally there would be no delay after creating a user amid these transactions.

Additional context

@rafiss has looked into this a bit and provided this high level summary:

when creating the user, we bump the "table version" of the CRDB users table. after committing the transaction that creates the user, there's a post-commit step that waits for all other transactions that are on the old version to finish.
(the "table version" is just a counter; the main purpose of it is to implement online schema changes, but it also helps with the cache we have for users)

He also says:

we're trying to determine if there's a different kind of waiting logic we can use. we don't actually need to wait for all the other long-running transactions, but we do need to wait for the other nodes to receive the cache invalidation signal.

I've created this ticket to track the necessary investigation.

Jira issue: CRDB-46303

[blathers-crl]

Hi @fantapop, please add branch-* labels to identify which branch(es) this C-bug affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[rafiss]

The approach we might be able to use here is to change this logic:

cockroach/pkg/sql/conn_executor.go

Lines 4070 to 4089 in 4f224db

	ex.statsCollector.PhaseTimes().SetSessionPhaseTime(sessionphase.SessionStartPostCommitJob, crtime.NowMono())
	if err := ex.waitForTxnJobs(); err != nil {
	handleErr(err)
	}
	ex.statsCollector.PhaseTimes().SetSessionPhaseTime(sessionphase.SessionEndPostCommitJob, crtime.NowMono())
	// If descriptors are either modified or created wait then we may have to
	// wait for one version (if no job exists) or the initial version to be
	// acquired.
	if ex.extraTxnState.descCollection.HasUncommittedDescriptors() {
	cachedRegions, err := regions.NewCachedDatabaseRegions(ex.Ctx(), ex.server.cfg.DB, ex.server.cfg.LeaseManager)
	if err != nil {
	return advanceInfo{}, err
	}
	if err := ex.waitOneVersionForNewVersionDescriptorsWithoutJobs(descIDsInJobs, cachedRegions); err != nil {
	return advanceInfo{}, err
	}
	if err := ex.waitForInitialVersionForNewDescriptors(cachedRegions); err != nil {
	return advanceInfo{}, err
	}
	}

Instead of waiting for one version for the system.role_members table (and other system tables), we should be able to wait for the two version invariant. We would need to find a way to only use this different waiting logic for the case of bumping internal system tables for cache invalidation.