Cannot read property 'field' of null

[darioguarascio]

Preflight Checklist

• I have completed all Troubleshooting Steps.
• I'm on the latest version of Directus.
• There's no other issue that already describes my problem.

Describe the Bug

I suspect there are issues identifying the primary key of some specific tables, which cascades to more complex issues that i cannot point out exactly.

I use postgres as database, one table which I imported out of directus had a primary key set but not using as default nextval().

I found out this snippet in the code that made me believe this is how the pkey is identified
column.default_value?.startsWith('nextval(') in https://github.com/directus/directus/blob/27037f95c7dae030d656d7a7ca1f30832b347211/packages/schema/src/dialects/postgres.ts

and even after manually setting the column value defaulting to it ( with an ALTER TABLE statement ), directus cannot recognize the primary key of such table (proof is, in data model there is no key icon on id field and in api calls, is_primary_key is false

Is there a better way to let directus detect a primary key?

Possible consequences of this issue:

• data became uneditable. Whenever I try to access the collection, I get a blank page.
• other collections referencing this one via relations show an uneditable field
• trying to create a m2o field in another table referencing to the unrecognized pkey field result in a blank "Relationship" window

To Reproduce

Dropping the nextval() default value of a primary key

What version of Directus are you using?

9.0.0-rc.88

What version of Node.js are you using?

the one delivered with docker

What database are you using?

postgres:13

What browser are you using?

Chrome

What operating system are you using?

Ubuntu

How are you deploying Directus?

Docker

[rijkvanzanten]

It's using this second query to fetch the primary keys:

directus/packages/schema/src/dialects/postgres.ts

Lines 33 to 48 in 27037f9

	SELECT
	relname as table_name,
	pg_attribute.attname as column_name
	FROM
	pg_index,
	pg_class,
	pg_attribute,
	pg_namespace
	WHERE
	indrelid = pg_class.oid
	AND nspname IN (?)
	AND pg_class.relnamespace = pg_namespace.oid
	AND pg_attribute.attrelid = pg_class.oid
	AND pg_attribute.attnum = ANY (pg_index.indkey)
	AND indisprimary
	`,

Does your collection have an index for the primary key?

For example, this is what the primary key index would look like by default:

[rijkvanzanten]

Related to #6279

[darioguarascio]

@rijkvanzanten Everything is set up correctly. Index is there, primary key is there

postgres=# \d city
                                                Table "public.city"
       Column       |            Type             | Collation | Nullable |                 Default                  
--------------------+-----------------------------+-----------+----------+------------------------------------------
 id                 | integer                     |           | not null | nextval('city_id_seq'::regclass)

....

Indexes:
    "city_pk" PRIMARY KEY, btree (id)

I cannot find anything at db level that might be misplaced

[rijkvanzanten]

@darioguarascio Could you send over a dump of just that exact table + column + index combo? That should make it super easy to reproduce and fix 🙂

[darioguarascio]

@rijkvanzanten I hope this is enough.

here a pg_dump -s

CREATE TABLE public.city (
    id integer NOT NULL,
    country_id integer NOT NULL,
    population integer DEFAULT 0 NOT NULL
);

CREATE SEQUENCE public.city_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE ONLY public.city ALTER COLUMN id SET DEFAULT nextval('public.city_id_seq'::regclass);


ALTER TABLE ONLY public.city
    ADD CONSTRAINT city_pk PRIMARY KEY (id);

and queries:

postgres=# SELECT 
           relname as table_name, 
           pg_attribute.attname as column_name 
         FROM 
           pg_index, 
           pg_class, 
           pg_attribute, 
           pg_namespace 
         WHERE 
           indrelid = pg_class.oid 
           AND nspname IN ('public') 
           AND pg_class.relnamespace = pg_namespace.oid 
           AND pg_attribute.attrelid = pg_class.oid 
           AND pg_attribute.attnum = ANY (pg_index.indkey) 
           AND indisprimary  AND relname = 'city';
 table_name | column_name 
------------+-------------
 city       | id
(1 row)

postgres=# SELECT                                
          c.table_name,
          c.column_name,
          c.column_default as default_value,
          c.is_nullable,
          c.data_type,
 c.character_maximum_length as max_length,
          c.is_identity
        FROM
          information_schema.columns c
        LEFT JOIN information_schema.tables t
          ON c.table_name = t.table_name
        WHERE
          t.table_type = 'BASE TABLE' AND c.table_name = 'city'; 
 table_name |    column_name     |               default_value                | is_nullable |          data_type          | max_length | is_identity 
------------+--------------------+--------------------------------------------+-------------+-----------------------------+------------+-------------
 city       | id                 | nextval('city_id_seq'::regclass)           | NO          | integer                     |            | NO
 city       | country_id         |                                            | NO          | integer                     |            | NO
 city       | population         | 0                                          | NO          | integer                     |            | NO

[rijkvanzanten]

That's perfect, thanks!

[rijkvanzanten]

Hmm, I just ran your sql dump to add the table to my instance, and everything seems to show up as expected:

And was able to create a new item without issues too:

I think the root cause of the problem might be something else than the primary key of this particular table 🤔 Would you be able to share a full dump of everything either here or through rijk@directus.io?

[darioguarascio]

@rijkvanzanten I managed to solve the problem by dropping 3 extra indexes that involved id.

....
    "idx_city_id_country_id" btree (id, country_id)
    "uniq_city_id_active" UNIQUE CONSTRAINT, btree (id, active)
    "uniq_city_id_country_id" UNIQUE CONSTRAINT, btree (id, country_id)
....

by dropping them, id is now correctly identified as primary key and everything went back to normal.

My guess is that when the primary key is involved in other indexes (maybe unique ones, even if it makes no sense but pg allows it) this could create conflicts in the pk recognition.

I hope it helps

here the schema of mentioned columns

CREATE TABLE public.city (
    id integer NOT NULL,
    country_id integer NOT NULL,
    active boolean DEFAULT false NOT NULL
);

CREATE SEQUENCE public.city_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ONLY public.city ALTER COLUMN id SET DEFAULT nextval('public.city_id_seq'::regclass);

ALTER TABLE ONLY public.city
    ADD CONSTRAINT city_pk PRIMARY KEY (id);


ALTER TABLE ONLY public.city
    ADD CONSTRAINT uniq_city_id_active UNIQUE (id, active);


ALTER TABLE ONLY public.city
    ADD CONSTRAINT uniq_city_id_country_id UNIQUE (id, country_id);

CREATE INDEX idx_city_id_country_id ON public.city USING btree (id, country_id);

[rijkvanzanten]

Odd! Not sure what I'm missing here 🤔

[darioguarascio]

One of those classic super-edge cases that are impossible to reproduce.
My last operations before seeing it working were the 3 DROPs.
Is there anything else I can do to help debugging?

[rijkvanzanten]

@darioguarascio If you could send over a full pg_dump of everything (including the directus_ system tables), I should be able to see the same error 🤔 (If you need to send it in private, you can email it to rijk@directus.io)

I think you're very close by mentioning the multiple indexes, as there is another known problem around the subject (#6204), but we can't know for sure without an accurate reproduction of course 🤔

[darioguarascio]

@rijkvanzanten unfortunately I cannot send the full dump as it's a production db.
If you point out exact tables I'll try to cut it out

[rijkvanzanten]

So just to confirm 100%, this is the confirmed way to make the error disappear/show up again?

I managed to solve the problem by dropping 3 extra indexes that involved id.

Just trying to think through other ways I can reproduce this on my end.. We tried the above, so there must be something i'm missing here 🤔

[darioguarascio]

@rijkvanzanten Not sure if this is the way, but dropping the 3 extra indexes helped getting back to normal.

[rijkvanzanten]

Gotcha. Strange that I can't get the same error to pop up using the dumps of the indexes provided above..

Can you reproduce the error on a fresh installation with the SQL you provided above?

[PeeraJ]

@rijkvanzanten I got this issue all the time with PostgreSQL (AWS RDS) I also can't reproduce it in my local as well. the workaround that I found is to REINDEX the whole database and it temporarily fixed the problem.

[rijkvanzanten]

I just tried reproducing it again using the instructions available, but still aren't able to get the original issue to show up..

I'll have to close this for now. Once a clear way to reproduce the error is available, we can reopen and reinvestigate.

[rijkvanzanten]

Thank you for taking the time to submit this! Unfortunately, our team was not been able to reproduce it based on the information provided. Therefore, we will close this ticket for now, but will happily re-open it if new details are provided that allow us to reliably replicate the problem.

As a reminder:

• Provide as much detail as possible in the issue description
• Video screen captures and clear steps are very helpful
• A full SQL dump is the fastest way for us to test/resolve (can be sent privately)
• Make sure you are on the latest version of Directus
• Did you try turning it off and on again?

---

This is an automated response.