Primary index key skipping numbers about directus HOT 6 CLOSED

Different databases (Azure PG/Ubuntu PG)

(I also added detail above)

from directus.

After looking at how the auto increment is set up in Postgres I have to tell you that this is indeed expected behavior and won't be changed. The primary key is drawn from a SEQUENCE object and sequences are only guaranteed to be incrementing in nature but not gap-less.

To avoid blocking concurrent transactions that obtain numbers from the same sequence, the value obtained by nextval is not reclaimed for re-use if the calling transaction later aborts. This means that transaction aborts or database crashes can result in gaps in the sequence of assigned values. That can happen without a transaction abort, too. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Thus, PostgreSQL sequence objects cannot be used to obtain “gapless” sequences.¹

As such I'll be closing this issue. If you need a strictly gapless ID for some reason (which to me seems like a weird requirement) you could resort to populating a different field after the fact in a flow (you would have to account for concurrent insert yourself then). And this is just something I can come up with on the top off my head, not a recommendation :D

from directus.

Since I'm not seeing this problem on my local instance with the SQLite driver, I've got a few clarifying questions:

• What DB vendor and version are you using?
• You're talking about two instances, are you running Directus as a cluster or are you seeing the same problems on two individual instances?
• Can you share your configuration (without sharing any secrets are API keys) to help us reproduce this issue.

from directus.

You're talking about two instances, are you running Directus as a cluster or are you seeing the same problems on two individual instances?

• There are two individual instances

What DB vendor and version are you using?
Can you share your configuration (without sharing any secrets are API keys) to help us reproduce this issue.

Instance 1

• Azure App service
• Docker image: directus:10.10.5
• Azure Database for PostgreSQL 16.2 - Flexible Server
• DB_SSL: true
• Collection: primary key (bigint), various inputs (text, multiple images, M20 relation, radio), boolean

Instance 2: air gapped network

• Ubuntu 22.04
• Postgres 16.2
• Docker image: directus:10.10.5
• DB_SSL: false
• Collection: primary key (bigint), various inputs (text, multiple images, M20 relation, radio), boolean

from directus.

And just to confirm, are you pointing both instances to the same DB instance or different ones as well?

from directus.

Additionally, the M2O related collection also has bigint primary key

from directus.