Update application IDs in the web-interface

Hey Guys,

is there any possibilty to update the ID in the LoRa-App Server Web-Interface for an Application ?

For example, if i create 3 Applications and delete the Applications with id 1 and 2 , i want the my third Application gets ID=1 but it stays 3.

Thanks for your help, i appreciate it.

35

The ID is presumably generated by a Postgres sequence. You could go in and modify it yourself via SQL, but expect that you next generated ID would be 4 (unless you also modify the sequence).

I tried to modify it directly in Postgres SQL but there are several table-entrees linked together , so sql doesn’t allow me to change. Maybe I do it the wrong way.

Does anyone else know how to change the ID ?

Thank you four your time and help !

Application ids are indeed of type bigserial and autogenerated by Postgres. I wouldn’t recommend modifying this, but it may be done. For starters, foreign references to the applications table are these:

Referenced by:
    TABLE "device" CONSTRAINT "device_application_id_fkey" FOREIGN KEY (application_id) REFERENCES application(id)
    TABLE "integration" CONSTRAINT "integration_application_id_fkey" FOREIGN KEY (application_id) REFERENCES application(id) ON DELETE CASCADE

You may alter those constraints to add ON UPDATE CASCADE, which should update the references when changing the id.

Also, if you list sequences for the autoincrement columns in your DB with \ds, you’ll see that the application one is called application_id_seq. You may have it be reset with some starting number, e.g.:

ALTER SEQUENCE application_id_seq RESTART WITH 123

So after deleting an application, you may programatically shift all ids so that they cover the [1, len(applications)] interval and reset the sequence to restart from there.

Again, I wouldn’t recommend doing this: the id column is indexed and that makes writes expensive, and it’s just an internal identifier to create references, you should probably have good names for your applications to identify them.

2 Likes

Hi @iegomez ,

I totally agree with you. However, what if you need a total reset of applications? Would it be nice to do a reset of the parameter “application_id_seq”? In that case, how could it be done without affecting negatively to the related tables and indexes?

Regards,
Pablo.

Why would you need such a reset? In any case, you can do exactly what I wrote before: update the constraints, then alter the sequence. You’ll probably want to make a backup of your DB before doing this just in case.

1 Like

Thanks for the answer. The only thing it comes to my mind is that maybe you want to change from a test environment to a production envrionment. So, its like you already learnt how Chirpstack works and instead of installing a new server, you just need to reset everything. What do you think?

Then I’d say just drop the DB, recreate it and let the binary do all the needed migrations. I think that’s way easier and cleaner if you’re starting fresh for production.

1 Like