We could define these as builtins that run the internal executor, like

cockroach/pkg/sql/sem/builtins/pg_builtins.go

Lines 910 to 943 in cac13b8

	"pg_sequence_parameters": makeBuiltin(tree.FunctionProperties{DistsqlBlocklist: true},
	// pg_sequence_parameters is an undocumented Postgres builtin that returns
	// information about a sequence given its OID. It's nevertheless used by
	// at least one UI tool, so we provide an implementation for compatibility.
	// The real implementation returns a record; we fake it by returning a
	// comma-delimited string enclosed by parentheses.
	// TODO(jordan): convert this to return a record type once we support that.
	tree.Overload{
	Types: tree.ArgTypes{{"sequence_oid", types.Oid}},
	ReturnType: tree.FixedReturnType(types.String),
	Fn: func(ctx *tree.EvalContext, args tree.Datums) (tree.Datum, error) {
	r, err := ctx.Planner.QueryRowEx(
	ctx.Ctx(), "pg_sequence_parameters",
	ctx.Txn,
	sessiondata.NoSessionDataOverride,
	`SELECT seqstart, seqmin, seqmax, seqincrement, seqcycle, seqcache, seqtypid `+
	`FROM pg_catalog.pg_sequence WHERE seqrelid=$1`, args[0])
	if err != nil {
	return nil, err
	}
	if len(r) == 0 {
	return nil, pgerror.Newf(pgcode.UndefinedTable, "unknown sequence (OID=%s)", args[0])
	}
	seqstart, seqmin, seqmax, seqincrement, seqcycle, seqcache, seqtypid := r[0], r[1], r[2], r[3], r[4], r[5], r[6]
	seqcycleStr := "t"
	if seqcycle.(*tree.DBool) == tree.DBoolFalse {
	seqcycleStr = "f"
	}
	return tree.NewDString(fmt.Sprintf("(%s,%s,%s,%s,%s,%s,%s)", seqstart, seqmin, seqmax, seqincrement, seqcycleStr, seqcache, seqtypid)), nil
	},
	Info: notUsableInfo,
	Volatility: tree.VolatilityStable,
	},
	),

, but I'm skeptical that this is a pattern we want to repeat.

I was trying to think of ways we can notice and prevent query plan regressions for these delete queries before shipping them. I think it'd be wise to add these queries to a new file pkg/sql/opt/exec/execbuilder/testdata/sql_activity_stats_compaction so we can view the query plans and make sure they are still efficient. This should also help prevent query plan regressions as the optimizer evolves. I'd leave notes both above the definition of these queries to make it clear that the tests you add in execbuilder need to be updated if the queries change, and a note above the tests pointing to where they are defined.

@mgartner for some reason the spans here is not rendered at all when i run this test with -rewrite flag. Any thoughts on how I should fix this?

Or perhaps it's fine to ignore? Since unit test already check for the scan spans through KV request filters

nit: Let's use the default batch size for the LIMIT here so that we mimic the default query as closely as possible.

Ahh, the contradiction is from the filters on aggregated_ts — it cannot be both >= now() and < date_trunc('hour', now()) (unless now() is at the exact top of the hour).

Ah thanks so much! Done.

nit: add the unconstrained queries for both tables here too.

please comment what "interference" this test is concerned with, and how it tests that there's no such interference. I've read it and couldn't figure out.

just curious - is this statsCompactor running in parallel with the server's?