cockroachdb / cockroachdb/cockroach
sql: implement pg_catalog.pg_get_statisticsobjdef_columns() builtin
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Is your feature request related to a problem? Please describe.**
CockroachDB does not implement the `pg_catalog.pg_get_statisticsobjdef_columns()`
builtin. Any query that calls it fails with:
```
ERROR: unknown function: pg_catalog.pg_get_statisticsobjdef_columns()
```
This was discovered via the `pg_regress` roachtest. Recent versions of psql call
this function as part of the `\d ` table-describe query (to display
extended-statistics objects), so `\d` against a CockroachDB cluster errors out
when run from a psql client new enough to issue it. PostgreSQL added the function
in PostgreSQL 13.
CockroachDB already implements the closely related
`pg_get_statisticsobjdef(statobj_oid oid) -> text`, so this is the missing
companion function rather than a brand-new area:
- [pkg/sql/sem/builtins/pg_builtins.go#L1125](https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/sem/builtins/pg_builtins.go#L1125)
**Describe the solution you'd like**
Implement `pg_catalog.pg_get_statisticsobjdef_columns(oid) -> text`, matching
PostgreSQL semantics: given the OID of an extended-statistics object, return the
expression/column list portion of its definition (the part inside the parens of
`CREATE STATISTICS`). PostgreSQL signature:
```
pg_get_statisticsobjdef_columns(oid) → text
```
Since CockroachDB does not support `CREATE STATISTICS` extended-statistics
objects, an acceptable first step (consistent with how some other
`pg_get_*def` functions degrade) may be to register the function so it resolves
and returns `NULL` / empty rather than erroring, which is enough to keep psql
`\d` working. The exact behavior should follow whatever the existing
`pg_get_statisticsobjdef` does for these inputs.
**Describe alternatives you've considered**
- Leaving it unimplemented: rejected, because it breaks `\d` for users on
recent psql versions — a common, basic interactive workflow.
**Additional context**
Surfaced while refreshing the `pg_regress` golden files. The divergence is
recorded in the `bit` regression diff:
- [pkg/cmd/roachtest/testdata/pg_regress/bit.diffs](https://github.com/cockroachdb/cockroach/blob/master/pkg/cmd/roachtest/testdata/pg_regress/bit.diffs)
Observed (CockroachDB) vs expected (PostgreSQL) when psql runs `\d bit_defaults`:
```
-- CockroachDB
ERROR: unknown function: pg_catalog.pg_get_statisticsobjdef_columns()
-- PostgreSQL: query succeeds and \d renders the table description
```
Epic: none
Jira issue: CRDB-64695
Epic CRDB-60810
Contributor guide
Assessment
This issue has not been assessed yet.