First sensor ID upon a fresh db install is 2 instead of 1

[Flix6x]

Problem

I expect the first sensor created to be assigned id = 1. I seemed to have encountered an inconsistency in the starting value of the auto-increment. See tech spike below for details.

Possible resolution

• Somehow try to set is_called to f when the auto-increment table is created (the generic asset table does not seem to have this issue, i.e. the first asset has id = 1, so that could be a hint) OR
• We could advise to run flexmeasures db-ops reset (just after flexmeasures db upgrade) when setting up the database for the first time.

Tech spike

Set your flexmeasures.cfg:

SQLALCHEMY_DATABASE_URI = "postgresql://toy:toy@127.0.0.1/toy"

Create a fresh database (set password to 'toy'):

sudo -i -u postgres
createdb -U postgres toy
createuser --pwprompt -U postgres toy
exit
flexmeasures db upgrade

Let's check the sensor table, and specifically, its autoincrement:

psql -U toy --password -h 127.0.0.1 -d toy
select * from sensor;
select * from sensor_id_seq;

 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | t
(1 row)

Notice the last value is 1, and it has been called already, so the next call will yield the value 2. Now add the toy account:

\q
flexmeasures add toy-account --kind battery

Then check the battery sensor ID, which is the first to be created. Is it 1 or 2? 2!

Now let's test again after resetting the database:

flexmeasures db-ops reset

And check the sensor table again:

psql -U toy --password -h 127.0.0.1 -d toy
select * from sensor;
select * from sensor_id_seq;

 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | f
(1 row)

Notice the last value is 1, but it hasn't been called yet. The next call will yield the value 1. Now add the toy account again:

\q
flexmeasures add toy-account --kind battery

Is the battery 1 or 2? 1!

Clean up:

sudo -i -u postgres
dropdb toy
dropuser toy
exit

[Flix6x]

Suspected root cause

When calling flexmeasures db upgrade, one of the migrations actually sets the is_called value to true for the sensor_id_seq table. Specifically:
data/migrations/versions/a528c3c81506_unique_generic_sensor_ids.py.
It does so in the light of moving any present structural data from our old model into our new model (as part of the March 2021 data migration).

[Flix6x]

Suggested remedy

• Update the relevant migration file to set is_called to false (line 206).
• Make sure that after calling the migration's upgrade command, creating a new sensor does not fail (the first time) on a duplicate ID key violation.
• Update sensor IDs in the documentation (at least the toy tutorial is heavily affected).