cockroachdb / cockroachdb/cockroach

sql: implement pg_catalog.pg_get_statisticsobjdef_columns() builtin

Open
#171,572 0 comments 0 reactions 0 assignees View on GitHub
A-sql-builtins A-sql-pgcatalog A-sql-pgcompat C-enhancement O-agent O-pg-regress T-sql-foundations
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.