Sequence number

After upgrading a database from v13 to v17R6 I’ve noticed that Sequence number now behaves differently.
I’ve read through the discussion https://forums.4d.com/Post/EN/33687919/2/33812150here>, however I’m not using Sequence number with ORDA, I’m using ‘classic 4D’ which I would have expected to work as it did previously.

Stripped back to bare basics, we’ve got something like this happening.

<code 4D>
$lID:=Sequence number([Table_1])
ARRAY LONGINT($alIDs;10)
For ($i;1;10)
$alIDs{$i}:=$lID+$i-1
End for
ARRAY TO SELECTION($alIDs;[Table_1]ID)
$lID:=Sequence number([Table_1])
</code 4D>

In v13, $lID ends up being 11 (assuming that the table is new and the initial sequence number was 1), which is what I expect reading the https://doc.4d.com/4Dv18/4D/18/About-Record-Numbers.300-4505069.en.htmldocumentation>.

In v17 (and v18) however, $lID ends up as 1 which can’t be correct since I just created 10 records.

If I change the code to be as below, $lID ends up as 2 in v17 and v18 which also isn’t correct.

<code 4D>
$lID:=Sequence number([Table_1])
For ($i;1;10)
CREATE RECORD([Table_1])
[Table_1]ID:=$lID+$i-1
SAVE RECORD([Table_1])
End for
$lID:=Sequence number([Table_1])
</code 4D>

If I change to this, $lID ends up as 1.

<code 4D>
For ($i;1;10)
CREATE RECORD([Table_1])
[Table_1]ID:=$i
SAVE RECORD([Table_1])
End for
$lID:=Sequence number([Table_1])
</code 4D>

So it looks like the sequence number is only incremented if you call Sequence number and then save a record. If you save a record without first calling Sequence number it doesn’t get incremented. Additionally ARRAY TO SELECTION has no effect on the sequence number. Is that the new intended behavior for classic 4D?

Note that we aren’t doing anything like the second and third examples, only the first. We use something like the first example in a semaphore to bulk add records to the database because a long time ago (circa 4D 2003) we found that to be more efficient.

Hi Adam,
in v2003 there was no “simple” way to read or write the internal counter of tables sequence numbers, it came in v2004 with GET/SET DATABASE PARAMETER.

It’s normal you don’t see the counter growing if you don’t save the record. The engine delivers a value, but will store it only if the record is saved. Works perfectly, C/S, transactions, etc.

Since v11, my approach is “let the 4D engine do”. I mean all fields needing a unique value (longint or uuid) have the property checked in the structure, and that’s all.

and have the “auto increment” activated :slight_smile:

Hi Bruno,
my bad, I meant auto increment / auto generate property, not unique property (but check it too, BTW :wink: )

Thank you for the reply Arnaud. Auto Id is definitely the preferred
way of assigning a unique Id to a record now, however unfortunately
for us the database layer of our application was primarily built
before auto Ids were available. Thankfully we don’t have many areas
that bulk create records like this, so it’s not a big deal for me to
fix it.

That being said, based on the documentation the Sequence number
command should still work in the same way as it did in v13.

The sequence number is a unique non-repeating number that may be
assigned to a field of a record (via the Autoincrement property, the
SQL AUTO_INCREMENT attribute or the Sequence number command). It is
not automatically stored with each record. It starts by default at 1
and is incremented for each new record that is created. Unlike record
numbers, a sequence number is not reused when a record is deleted or
when a database is compacted or repaired. Sequence numbers provide a
way to have unique ID numbers for records. If a sequence number is
incremented during a transaction, the number is not decremented if
the transaction is canceled.

In all 3 of my examples I’m creating new records but the sequence number is not incrementing.

Possibly you need to call Sequence number while creating a record in order to get it to increment now. However I don’t remember reading that in any of the upgrade references.

: Adam STORCK

however unfortunately for us the database layer of our application
was primarily built before auto Ids were available.
Why don’t you grab this opportunity to upgrade this behaviour? It takes time to achieve the job but finaly you leverage with better newer conditions

In an ideal world I agree 100%. In a world with deliverables and business pressures though I can’t afford the time right now.

Probably I’ll make time for it when we upgrade to v18 ( which we’ll need to do by the end of the year).

The upgrade from v13 to v17 has already taken a significant amount of time because we were so heavily coupled to 4D Draw. The light at the end of the tunnel is so close now.

Hi Adam,
in what you cite from the doc, I suppose is incremented for each new record that is created would be more exact with is incremented for each new record that is saved (save ≠ create). Anyway I don’t remember of a growing sequence number if no record is saved.

The way I see it working since a while:

• internal value 100
user A executes CREATE RECORD : sn->101
user B executes CREATE RECORD : sn->102
both don’t save
• internal value 100 (unchanged)
user A executes CREATE RECORD : sn->101
user B executes CREATE RECORD : sn->102
user A doesn’t save
user B executes SAVE RECORD
• internal value 102

You can easily verify that using 2 process to mimic each user.

: Arnaud DE MONTARD

• internal value 100
user A executes CREATE RECORD : sn->101
user B executes CREATE RECORD : sn->102
both don’t save
• internal value 100 (unchanged)
user A executes CREATE RECORD : sn->101
user B executes CREATE RECORD : sn->102
user A doesn’t save
user B executes SAVE RECORD
• internal value 102

Quelques lignes que tout le monde devrait avoir en mémoire, plus limpide que n’importe quelle explication et qui montre combien il serait lourd de reproduire le même comportement multi utilisateur via du code 4D

Another noticeable point about Sequence number is the “cost” in C/S:
<code 4D>
$duration_l:=1000
$count_l:=0
$ms_l:=Milliseconds+$duration_l
Repeat
$count_l:=$count_l+1
CREATE RECORD([A_TABLE])
$value:=Sequence number([A_TABLE]) //***
Until (Milliseconds>$ms_l)
</code 4D>
Using a client connected through WAN:

  • with line 7, $count_l ~26
  • without line 7, $count_l turns to >120K
: Herve LE MARCHAND

combien il serait lourd de reproduire le même comportement multi
utilisateur via du code 4D
Salut Hervé,
je me souviens avoir supprimé une table et un paquet de code compliqué et loin d’être parfait grâce à cela, sans parler du temps d’exécution. Et avec les uuid c’est probablement beaucoup plus simple pour le moteur !

J’ai encore plein d’ID entiers longs ; dette technique, tu me tiens ! combien de virus faudra-t-il pour l’éponger ?
Y a-t-il des recommandations pour migrer ?

  • création du champ alpha UUID avec génération automatique, en parallèle de l’ID existant
  • remplissage du champ : faut-il le générer par Generate UUID ?
  • en cas de lien, remplissage des UUID de la table N
  • faut-il activer les liens ?
  • transfert de la clé primaire sur l’UUID
  • quand tout est au point, suppression des ID.

Tous conseils bienvenus…

Hi Arnaud,

: Arnaud DE MONTARD

Anyway I don’t remember of a growing sequence number if no record is
saved.

I’m not expecting it to grow if I don’t save. In all three of my examples I’m saving 10 records but it is not growing.

Anyway, I’m going to make plans to replace it with auto Ids as soon as I can. Much safer solutions.