mORMot Open Source

Kabiri

Member

Registered: 2024-06-22

Posts: 94

PostgreSQL ORM behavior: Reusing deleted record IDs after insert

I defined my model like this:

TOrmTestA = class(TOrm)
private
  fMSG: RawUTF8;
published
  property MSG: RawUTF8 read fMSG write fMSG;
end;

The tables were created in PostgreSQL using CreateMissingTables.

I’m using the interface and ORM to operate on the table.

When I insert a record and then delete it, and then create a new record, the new record takes the ID of the deleted one.

Is this standard behavior? How can I change it?

Last edited by Kabiri (2026-05-03 16:54:05)

ttomas

Member

Registered: 2013-03-08

Posts: 161

Re: PostgreSQL ORM behavior: Reusing deleted record IDs after insert

Yes if you restart service/application after delete. On start of your app, mormot ORM get max(ID) and keep ID in memory and increment before add new record.

vs

Member

Registered: 2019-10-21

Posts: 59

Re: PostgreSQL ORM behavior: Reusing deleted record IDs after insert

Look towards TSynUniqueIdentifier from mormot.crypt.secure
- 64-bit TSynUniqueIdentifier and its efficient Generator

type
  /// 64-bit integer unique identifier, as computed by TSynUniqueIdentifierGenerator
  // - they are increasing over time (so are much easier to store/shard/balance
  // than UUID/GUID), and contain generation time and a 16-bit process ID
  // - mapped by TSynUniqueIdentifierBits memory structure
  // - bits 0..14 map a 15-bit increasing counter (collision-free)
  // - bits 15..30 map a 16-bit process identifier
  // - bits 31..63 map a 33-bit UTC time, encoded as seconds since Unix epoch
  // - may be used on client side for something similar to a MongoDB ObjectID,
  // but compatible with TOrm.ID: TID properties
  TSynUniqueIdentifier = type TID

and TOrmModel function SetIDGenerator from mormot.orm.core

Kabiri

Member

Registered: 2024-06-22

Posts: 94

Re: PostgreSQL ORM behavior: Reusing deleted record IDs after insert

Thanks.
This happened several times during testing.
Since the database wasn't generating the auto-increment key, I thought the problem was related to my class definition.
Anyway, for now I used soft delete.