sql: pg_database.dattablespace is 0 but pg_tablespace.oid is 1663, so they no longer join
Assessment
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Newbie friendliness
- 68/100
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
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:
pg_tablespace.oidis 1663.pg_database.dattablespaceisoidZero.- On v26.2.11,
pg_tablespace.oidanddattablespaceare bothoidZero.
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
- No Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from cockroachdb/cockroach
-
C-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
cockroachdb/cockroach#176245 · 1 comment ·
-
PG compat: overflow in integer power calculation triggers incompatible SQLSTATEMay be free again @darshpatel1052 claimed this 18 days ago, and no pull request is open. OpenC-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
cockroachdb/cockroach#176223 · 2 comments ·
-
util/log: bufferedSinkCloser.Close leaks a goroutine after timeoutPossibly taken @dajiaohuang claimed this 14 days ago. OpenC-bug O-community X-blathers-untriaged
Difficulty 1/5 Under an hour Newbie friendliness 84/100
cockroachdb/cockroach#176210 · 1 comment ·
-
C-bug O-community T-sql-queries X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
cockroachdb/cockroach#176150 · 1 comment ·
-
C-bug O-community X-blathers-triaged
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
cockroachdb/cockroach#176137 · 1 comment ·
All issues in cockroachdb/cockroach
Similar issues
-
area/import-export area/projects concern/ux
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
go-vikunja/vikunja#4455 · 1 comment ·
Maintainers usually reply within 2 days
-
Dependabot PRs no longer merge automaticallyPossibly taken @dwisiswant0 claimed this today. Open
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
projectdiscovery/urlfinder#160 ·
Maintainers usually reply within 1 day
-
database
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
chnm/legal-modernism#450 ·
Maintainers usually reply within 1 day
-
tech-debt
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
Maintainers usually reply within 1 day
-
bug ready-for-triage ready-to-code
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
RedHatInsights/insights-ingress-go#946 · 4 comments ·
Maintainers usually reply within 1 day