cockroachdb / cockroachdb/cockroach

catalog/replication: SetupOrAdvanceStandbyReaderCatalog cannot ingest UDFs that reference system tables

Open
#152,978 2 comments 0 reactions 0 assignees View on GitHub
branch-release-24.3 C-bug T-sql-foundations
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

If the source tenant has a UDF that references a system table, the reader tenant cannot spin up.

To reproduce:
```
// Create a UDF that references a system table on your source tenant, like this one
CREATE OR REPLACE FUNCTION renumber_desc(oldID INT, newID INT) RETURNS BOOL AS
$$
BEGIN
-- Rewrite the ID within the descriptor
SELECT crdb_internal.unsafe_upsert_descriptor(
newid,
crdb_internal.json_to_pb(
'cockroach.sql.sqlbase.Descriptor',
d
),
true
)
FROM (
SELECT id,
json_set(
json_set(
crdb_internal.pb_to_json(
'cockroach.sql.sqlbase.Descriptor',
descriptor,
false
),
ARRAY['table', 'id'],
newid::STRING::JSONB
),
ARRAY['table', 'modificationTime'],
json_build_object(
'wallTime',
(
(
extract('epoch', now())
* 1000000
)::INT8
* 1000
)::STRING
)
) AS d
FROM system.descriptor
WHERE id IN (oldid,)
);
-- Update the namespace entry and delete the old descriptor.
SELECT crdb_internal.unsafe_upsert_namespace_entry("parentID", "parentSchemaID", name, newID, true) FROM (SELECT "parentID", "parentSchemaID", name, id FROM system.namespace where id =oldID) UNION ALL
SELECT crdb_internal.unsafe_delete_descriptor(oldID, true);

RETURN true;

END
$$ LANGUAGE PLpgSQL;

// Create a pcr stream
CREATE VIRTUAL CLUSTER standby FROM REPLICATION OF system ON '{system tenant pg url}' WITH READ VIRTUAL CLUSTER;

// let the initial scan complete, and then observe logs like this
E250904 13:11:04.679226 11726 jobs/registry.go:1679 ⋮ [T4,Vstandby2-readonly,n1] 559 job 1104022944036913153: running execution encountered retriable error: non-cancelable: running migration for 0.0-upgrading-step-004: bootstrap step ‹"maybe initialize replication standby read-only catalog"› failed: function ‹"renumber_desc"› (104): depends-on relation ‹"descriptor"› (3) has no corresponding depended-on-by back reference
```

Jira issue: CRDB-54135

Epic CRDB-2454

Contributor guide

Open the contributing guide

Research direction

Start with SetupOrAdvanceStandbyReaderCatalog and the failure reported at jobs/registry.go:1679, then trace the migration step that initializes the replication standby read-only catalog. Reproduce the issue with the supplied UDF and standby creation statements. Done means the reader tenant can complete its initial scan when the source UDF references a system table.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases, distributed-systems
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.