REFCURSOR[] columns can be uniquely indexed and FK'd but cause unexpected errors

[chrisseto]

Describe the problem

Currently, it's not possible to create a unique refcusor column.

demo@127.0.0.1:26257/demoapp/movr> CREATE TABLE t(a refcursor UNIQUE);
ERROR: unimplemented: column a is of type refcursor and thus is not indexable
SQLSTATE: 0A000
HINT: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/35730/v24.1

Therefore it's not possible to make a foreign key between refcursors as well.

demo@127.0.0.1:26257/demoapp/movr> CREATE TABLE t(a refcursor);
CREATE TABLE

Time: 11ms total (execution 11ms / network 0ms)

demo@127.0.0.1:26257/demoapp/movr> alter table t add constraint test foreign key (a) references t(a);
ERROR: there is no unique constraint matching given keys for referenced table t
SQLSTATE: 23503

However, recursor[] columns can be uniquely index and foreign key'd. Though attempting to add a foreign key constraint will fail with the following error:

demo@127.0.0.1:26257/demoapp/movr> CREATE TABLE t(a refcursor[] unique);
CREATE TABLE

Time: 10ms total (execution 10ms / network 0ms)

demo@127.0.0.1:26257/demoapp/movr> alter table t add constraint test foreign key (a) references t(a);
ERROR: validate fk constraint: could not identify a comparison function for type refcursor
SQLSTATE: 42883

Expected behavior
I would expect that creating a unique refcursor[] column would fail with the same error as a unique refcursor column.

Jira issue: CRDB-34159

[mgartner]

An index on a REFCURSOR[] column should also be disallowed:

CREATE TABLE t(a refcursor[], index (a));

It currently succeeds during creation.

[blathers-crl]

Hi @mgartner, please add branch-* labels to identify which branch(es) this release-blocker affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[mgartner]

Marking as a GA-blocker for now, but this is probably unlikely to be hit. I think the fix should be easy though.

[mgartner]

Doesn't look like this will cause many problems besides unexpected errors:

defaultdb> CREATE TABLE t(a refcursor[], index (a));

defaultdb> select * from t where a = ARRAY['foo'];
ERROR: could not identify a comparison function for type refcursor

Some of our randomized tests will probably hit this at some point, so we should fix it, but I don't think it's a blocker.