Creating a persistent primary key with auto increment

General
1

Hi,
I’m using the library persistent and I could not figure out a good way to generate primary keys with auto increment.
I tried the code below using the relation name as a key. However, users who access different tables will be blocked when someone is generating a new value for a specific table. How can I solve this?

:- module( key, [ pk/2 ]).

:- use_module(library(persistency)).

:- persistent
     key(name:atom,
         value:positive_integer).

:- initialization( db_attach('tbl_key.pl', []) ).

pk(Name, Value):-
    key(Name, _), !,
    with_mutex(key,
               ( 
                 key(Name, OldValue),
                 Value is OldValue + 1,
                 retractall_key(Name,_), 
                 assert_key(Name, Value) )).

pk(Name, 1):-     % Creates a new key with initial value 1
  with_mutex(key,
             assert_key(Name, 1)).
2

gensym/2 might do what you want for generating keys, although I think it would still need a mutex. It’s documented as thread-safe: two threads generating identifiers from the same Base will never generate the same identifier.
(the code is here: https://github.com/SWI-Prolog/swipl-devel/blob/13001415dc35cffaa952cd15d7e1d29fae05c57c/library/gensym.pl )

As for blocking, could you use Name as the mutex or something created from Name with atom_concat/3?

3

You can use the key name as mutex. Your code is not thread-safe though as two threads calling this for the first time at the same moment will both produce 1.

Finally, library(persistency) is not well suited for volatile storage as its persistency model is based on a journal. So, each update will create two records in the journal that are replayed if you attach the DB. The library targets persistent relations that grow over time with a small percentage of deletions.

I’d just use the Prolog DB and if the counter has to survive sessions, save the counter at the end using at_halt/1. If this needs to be 100% reliable you do need some recovery to compute the current key from the data in the case saving the value failed. This is probably not too hard though.

Note that instead of counting, you can also opt for a UUID or in some cases derive the key from the data using variant_sha1/2, computing a secure hash.

1 Like
4

Thanks Jan and Peter for the answers.

Now I think my solution is thread-safe and non-blocking for threads accessing other tables.

:- module( key, [ pk/2 ]).

:- use_module(library(persistency)).

:- persistent
     key( name:atom,
          value:positive_integer ).

:- initialization( ( db_attach('tbl_key.pl', []),
                     at_halt(db_sync(gc(always))) )).

pk(Name, Value):-
    with_mutex(Name,
               ( 
                 (key(Name, CurrentValue) ->
                     OldValue = CurrentValue ;
                     OldValue = 0),
                 Value is OldValue + 1,
                 retractall_key(Name,_), 
                 assert_key(Name, Value) )).