Additional index on table breaks Mathesar's primary key recognition

[zackkrida]

Description

Using the following table and constraints:

CREATE TABLE public.index_test (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    col_a text NOT NULL
);

-- Primary Key
ALTER TABLE public.index_test
    ADD CONSTRAINT index_test_pkey PRIMARY KEY (id);

-- Index on col_a and id
CREATE INDEX ix_index_test_col_a_id ON public.index_test (col_a, id);

Mathesar does not detect this table as having a Primary key:

Removing the ix_index_test_col_a_id index fixes the problem.

Expected behavior

Mathesar correctly identifies the table's primary key.

To Reproduce

Load this SQL into a local Mathesar instance (docker exec -it mathesar_dev_db psql -U mathesar) and verify the behavior.

[seancolsen]

I've spent some time troubleshooting this, and I've narrowed the problem down to the msar.get_column_info DB function. Here's why:

1. With the sample data above, run

   select msar.get_column_info('index_test'::regclass);
   

2. Expect a JSON array of two objects.

3. Instead, observe a JSON array of three objects:

   Results

   [
     {
       "id": 1,
       "name": "id",
       "type": "uuid",
       "default": {
         "value": "gen_random_uuid()",
         "is_dynamic": true
       },
       "nullable": false,
       "description": null,
       "primary_key": true,
       "type_options": null,
       "has_dependents": false,
       "current_role_priv": [
         "SELECT",
         "INSERT",
         "UPDATE",
         "REFERENCES"
       ]
     },
     {
       "id": 1,
       "name": "id",
       "type": "uuid",
       "default": {
         "value": "gen_random_uuid()",
         "is_dynamic": true
       },
       "nullable": false,
       "description": null,
       "primary_key": false,
       "type_options": null,
       "has_dependents": false,
       "current_role_priv": [
         "SELECT",
         "INSERT",
         "UPDATE",
         "REFERENCES"
       ]
     },
     {
       "id": 2,
       "name": "col_a",
       "type": "text",
       "default": null,
       "nullable": false,
       "description": null,
       "primary_key": false,
       "type_options": null,
       "has_dependents": false,
       "current_role_priv": [
         "SELECT",
         "INSERT",
         "UPDATE",
         "REFERENCES"
       ]
     }
   ]

4. The duplicate objects in the results cause confusion in the front end because we're putting the columns into a map keyed on the attnum. The second object listed takes precedence in the map, and that's the one which has primary_key set to false.

We need to eliminate the duplicate objects.

I imagine will be quick to fix. I'd fix it myself, but instead I'll leave it to @mathemancer or @Anish9901 since sometimes we end up blocked on front end work near the end of the release cycle.

@zackkrida I've triaged this into the High Priority milestone since it's user reported. But I think this would be a good one to include in 0.5.0 since it's user-reported, blocking functionality, and very likely quick to fix. I'll leave it to you to put into a specific release milestone though.

[peterbuga]

+1 on this, having the same problem.