I have an issue with the datastore sequencer. It seems that if kbmMWDataStoreSequencer.Value[‘some_seq’] is called from multiple threads the value of some_seq is not guaranteed increased after calling kbmMWDataStoreSequencer.Value. This makes sense because my database (postgres) has a standard isolation level of read-commit (which I think is good). The result is that I get duplicate primary keys, which is not good 🙁
I suppose you are using the generic SQL metadata. It basically translates the get value to an SQL UPDATE followed by a SELECT. For this to work, its run within a transaction, but it require that the transaction mode of the database actually blocks others completely, otherwise you will not get correct unique values.
PostgreSQL supports a special syntax UPDATE… RETURNING …. which I suppose would be a much better solution, but require a PostgreSQL specific Metadata component.
It can be done fairly easily by descending from TkbmMWCustomSQLMetaData or TkbmMWGenericSQLMetaData, and overwrite GenerateValueSequenceSQL method.
Or, probably even better, but not directly backwards compatible with your existing database since it currently use a pivot table, is to create a PostgreSQL Metadata component, which use the sequencer features built into PostgreSQL. You could look at kbmMWOracleMetaData.pas to see how that is done. I may have a go at it at some point, but Im pretty busy at the moment.