Foreign key references to hash sharded primary key fail

[mgoddard]

Describe the problem

When creating a table containing a foreign key reference to a table with a hash sharded primary key, the operation fails with this error message: ERROR: there is no unique constraint matching given keys for referenced table ...

To Reproduce

• Set up a single node CockroachDB instance (or, a 3 node local instance)
• Enable hash sharded indexes: set experimental_enable_hash_sharded_indexes = on;
• Run this DDL:

CREATE TABLE wsb
(
	id UUID NOT NULL DEFAULT gen_random_uuid(),
	bl INT4 NOT NULL,
	di UUID NOT NULL,
	sa TIMESTAMPTZ NOT NULL,
  /* PRIMARY KEY (id) */
  PRIMARY KEY (id) USING HASH WITH BUCKET_COUNT = 8
);

CREATE TABLE wsbi
(
	id UUID NOT NULL,
	i_id UUID NOT NULL,
	PRIMARY KEY (id ASC, i_id ASC),
  INDEX ws_b_idx (id ASC, i_id ASC)
);

ALTER TABLE wsbi ADD CONSTRAINT fk_id_ref_wsb FOREIGN KEY (id) REFERENCES wsb (id) ON DELETE CASCADE;

• Note the error message: ERROR: there is no unique constraint matching given keys for referenced table wsb

Expected behavior

The expectation is that this would yield the two tables with the foreign key reference constraint connecting them.

Additional data / screenshots

Environment:

• CockroachDB CCL v20.2.6 (x86_64-unknown-linux-gnu, built 2021/03/15 16:04:08, go1.13.14)
• Server OS: Running the Docker container
• Client app: psql CLI

Epic: CRDB-7363

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-schema (found keywords: ALTER TABLE)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

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

[vy-ton]

@ajwerner @postamar Before the larger non-experimental hash-sharded index project, what would it take to fix this and backport? Customer experiencing hotspots if hitting this.

[mgoddard]

I'm on CockroachDB CCL v21.2.7 (x86_64-unknown-linux-gnu, built 2022/03/14 16:37:26, go1.16.6) and getting the same issue:

CREATE TABLE useracct (
    user_id                  serial,
    user_name                varchar(255) NOT NULL DEFAULT '',
    user_real_name           varchar(255) NOT NULL DEFAULT '',
    user_password            varchar(255) NOT NULL,
    user_newpassword         varchar(255) NOT NULL,
    user_newpass_time        varchar(14)           DEFAULT NULL,
    user_email               varchar(255) NOT NULL,
    user_options             varchar(255) NOT NULL,
    user_touched             varchar(14)  NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
    user_token               varchar(32)  NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
    user_email_authenticated varchar(14)           DEFAULT NULL,
    user_email_token         varchar(32)           DEFAULT NULL,
    user_email_token_expires varchar(14)           DEFAULT NULL,
    user_registration        varchar(14)           DEFAULT NULL,
    user_editcount           int                   DEFAULT NULL,
    PRIMARY KEY (user_id) USING HASH WITH BUCKET_COUNT = 8,
    UNIQUE (user_name)
);
CREATE INDEX idx_user_email_token ON useracct (user_email_token);

CREATE TABLE user_groups (
    ug_user  int         NOT NULL DEFAULT '0',
    ug_group varchar(16) NOT NULL DEFAULT '',
    FOREIGN KEY (ug_user) REFERENCES useracct (user_id) ON DELETE CASCADE,
    UNIQUE (ug_user, ug_group)
);

Error message: org.postgresql.util.PSQLException: ERROR: there is no unique constraint matching given keys for referenced table useracct