Sensing + Hypertables + STAPlus FK problem

[aiguesmanresa]

Hi, in our scenario, we've all 3 plugins installed (Sensing, STAPlus, Tasking Core), and we're experiencing an issue when we're converting OBSERVATIONS to hypertable.

We are trying to follow the instructions described in this manual https://ediaqi.eu/sites/default/files/resources/03%20-%20FROST%20Deployment.pdf

So, we've executed the first SQL instruction of manual's Step 04C related to convert table OBSERVATIONS to hypertable using TimeScale plugin:

ALTER TABLE public."OBSERVATIONS" DROP CONSTRAINT "OBSERVATIONS_pkey";

But we're getting the following error:

SQL Error [2BP01]: ERROR: cannot drop constraint OBSERVATIONS_pkey on table OBSERVATIONS because other objects depend on it Detail: constraint RELATIONS_SUBJECT_ID_FKEY on table "RELATIONS" depends on index "OBSERVATIONS_pkey" constraint RELATIONS_OBJECT_ID_FKEY on table "RELATIONS" depends on index "OBSERVATIONS_pkey" constraint GROUPS_OBSERVATIONS_OBSERVATION_ID_FKEY on table "GROUPS_OBSERVATIONS" depends on index "OBSERVATIONS_pkey" Hint: Use DROP ... CASCADE to drop the dependent objects too.

Then, we went to all troublesome FKs and executed:

ALTER TABLE "RELATIONS" DROP CONSTRAINT "RELATIONS_SUBJECT_ID_FKEY";
ALTER TABLE "RELATIONS" DROP CONSTRAINT "RELATIONS_OBJECT_ID_FKEY";
ALTER TABLE "GROUPS_OBSERVATIONS" DROP CONSTRAINT "GROUPS_OBSERVATIONS_OBSERVATION_ID_FKEY";

This way we were able to delete all annoying FKs, and it was possible to do:

SELECT * FROM create_hypertable('OBSERVATIONS', 'PHENOMENON_TIME_START');

Now we got our OBSERVATIONS table converted to hypertable, but now we must create FKs again in order to ensure data integrity but from now on, we won't be able to restore FKs because we won't be able to create OBSERVATIONS("ID") as PRIMARY KEY anymore.
From this moment on, OBSERVATIONS("PHENOMENON_TIME_START", "ID") must be the PRIMARY KEY (or whatever involving "PHENOMENON_TIME_START", but never again a single "ID" field).

Aside from primary key, we've also tried to create a unique index to allow us create FKs referencing "ID" as previously were defined:

CREATE UNIQUE INDEX "OBSERVATIONS_ID_unique" ON public."OBSERVATIONS"("ID");

But we got the following error:

SQL Error [TS103]: ERROR: cannot create a unique index without the column "PHENOMENON_TIME_START" (used in partitioning)

We asked for help here FraunhoferIOSB/FROST-Server#2058

But it seems there's no solution for this scenario.

Is it really impossible to restore previous FKs referencing OBSERVATIONS' "ID" field to ensure data integrity once OBSERVATIONS has been converted to hypertable? There's no chance to recreate "RELATIONS_SUBJECT_ID_FKEY", "RELATIONS_OBJECT_ID_FKEY" and "GROUPS_OBSERVATIONS_OBSERVATION_ID_FKEY" foreign keys after OBSERVATIONS hypertable conversion?

Thanks