sql: unexpected partial unique index in pg_constraint query

[timgraham]

Describe the problem

A partial unique index unexpectedly (not matching PostgreSQL) appears in an introspection query that Django uses:

To Reproduce

CREATE TABLE "introspection_uniqueconstraintconditionmodel" ("id" serial NOT NULL PRIMARY KEY, "name" varchar(255) NOT NULL, "color" varchar(32) NULL);

CREATE UNIQUE INDEX "cond_name_without_color_uniq" ON "introspection_uniqueconstraintconditionmodel" ("name") WHERE "color" IS NULL;

SELECT
    c.conname,
    array(
        SELECT attname
        FROM unnest(c.conkey) WITH ORDINALITY cols(colid, arridx)
        JOIN pg_attribute AS ca ON cols.colid = ca.attnum
        WHERE ca.attrelid = c.conrelid
        ORDER BY cols.arridx
    ),
    c.contype,
    (SELECT fkc.relname || '.' || fka.attname
    FROM pg_attribute AS fka
    JOIN pg_class AS fkc ON fka.attrelid = fkc.oid
    WHERE fka.attrelid = c.confrelid AND fka.attnum = c.confkey[1]),
    cl.reloptions
FROM pg_constraint AS c
JOIN pg_class AS cl ON c.conrelid = cl.oid
WHERE cl.relname = 'introspection_uniqueconstraintconditionmodel' AND pg_catalog.pg_table_is_visible(cl.oid)

            conname            | array  | contype | ?column? | reloptions
-------------------------------+--------+---------+----------+-------------
  primary                      | {id}   | p       | NULL     | NULL
  cond_name_without_color_uniq | {name} | u       | NULL     | NULL

Expected behavior

To match PostgreSQL, cond_name_without_color_uniq shouldn't appear in the result.

Environment:

• CockroachDB version 21.1 alpha 3 and 20.2.5

Additional context

The Django test added in https://github.com/django/django/commit/69a585eb878901eedb8809e4fada42e4d7b01b04 fails:

======================================================================
ERROR: test_get_constraints_unique_indexes_orders (introspection.tests.IntrospectionTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/test/testcases.py", line 1314, in skip_wrapper
    return test_func(*args, **kwargs)
  File "/home/tim/code/django/tests/introspection/tests.py", line 235, in test_get_constraints_unique_indexes_orders
    self.assertEqual(constraint['orders'], ['ASC'])
KeyError: 'orders'

Jira issue: CRDB-3069

[blathers-crl[bot]]

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:

• @solongordon (found keywords: JOIN,pg_)
• @rafiss (found keywords: JOIN,pg_, found keywords: Django)

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.

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

[rafiss]

This is because CockroachDB treats a unique index and a unique constraint as identical, but in PostgreSQL they are not (even though it always uses a unique index to implement a unique constraint).

[rafiss]

This seems like it would get some of the way towards separating constraints from indexes: https://github.com/cockroachdb/cockroach/pull/65825

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!

[timgraham]

Still a valid issue.