sql: pg_database.dattablespace is 0 but pg_tablespace.oid is 1663, so they no longer join

Open Beginner friendly
#176,228 1 comment 0 reactions 0 assignees View on GitHub

@1AbdullahButt is already working on this.

Since Nov 6, 2026.

  • #176229 by @1AbdullahButt — open

Assessment

Difficulty
2/5
Estimated time
1-3 hours
Newbie friendliness
68/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
go, postgresql, sql
Domain
backend, databases

Research direction

Start in pkg/sql/pg_catalog.go at the pg_database.dattablespace and pg_tablespace.oid definitions cited in the issue, then inspect the change from #167973 and nearby catalog tests. Update the inconsistent catalog value and add or adjust a test for the join. Done means dattablespace matches the pg_default OID and the reported query returns a row for each database.

Written by the indexing model from the issue text.

Description

C-bug O-community X-blathers-triaged X-move-internal

Describe the problem

On v26.3, pg_catalog.pg_tablespace returns pg_default with OID 1663, but pg_catalog.pg_database.dattablespace is still 0 for every database. In PostgreSQL dattablespace references pg_tablespace.oid, so the usual join between the two tables now returns no rows.

On v26.2 both were 0, so the join matched. It looks like #167973 changed pg_tablespace.oid to 1663 but left dattablespace as oidZero:

To Reproduce

SELECT oid, spcname FROM pg_catalog.pg_tablespace;
--  oid  |  spcname
-- ------+------------
--  1663 | pg_default

SELECT datname, dattablespace FROM pg_catalog.pg_database WHERE datname = 'defaultdb';
--   datname  | dattablespace
-- -----------+---------------
--  defaultdb |             0

SELECT count(*) FROM pg_catalog.pg_database AS db
JOIN pg_catalog.pg_tablespace AS ts ON db.dattablespace = ts.oid
WHERE db.datname = 'defaultdb';
--  count
-- -------
--      0

Expected behavior

dattablespace is 1663, matching pg_tablespace.oid as in PostgreSQL, so the join returns a row for every database.

Environment:

  • CockroachDB version: v26.3.5 (CockroachDB Cloud)
  • Server OS: Linux (aarch64)
  • Client app: psql

Additional context

Clients that join these catalogs see no databases. For example, crossplane-contrib/provider-sql checks whether a database exists with ... FROM pg_database AS db, pg_tablespace AS ts WHERE db.datname=$1 AND db.dattablespace = ts.oid. On v26.3 that returns no rows for existing databases, so the provider treats them as missing and never drops them on delete.

Jira issue: CRDB-71460

Dominant language
Go
Stars
32.6k
Forks
4.2k
PR merge metrics
No merged PRs in 30d

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from cockroachdb/cockroach

All issues in cockroachdb/cockroach

Similar issues

More Go issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.