How to disable database triggers? And general question with bulk loads

[C-Loftus]

Thank you very much for your work on this project. I am working on migrating a big dataset of sensor data to use Sensorthings and had 2 questions.

If you have any other advice / guidance on bulking loading, please let me know.

Background:

• there are around 3 million observations in total in my old database
• For each station, I download the associated data as CSVs, transform them into sensorthings in Python, and bulk load the entire dataset with a $batch request to FROST
• Each dataset contains around ~5000 observations
• I am finding that FROST batch operations take the majority of the time (~70%) in my ETL pipeline

Questions

1. How should one disable database triggers?

• In Server overload with big request #115 and Big inserts too long because of postgres triggers #1576 there is mention of disabling database triggers. However, I did not see a setting in the docs.

FROST updates the Datastream.phenomenonTime, Datastream.resultTime and Datastream.observedArea with a trigger. If you
need faster inserts, especially when importing data, you can gain a lot of speed by disabling this trigger.

2. Is there a preferred way to track down what is causing an error in an insert?

Sometimes when I insert an observation, FROST says the key I am using for my @iot.id is invalid.

• I am unclear why since I am using persistence_idGenerationMode=ServerAndClientGenerated and
• i.e. frost-http | 19:34:15.903 [http-nio-8080-exec-2] DEBUG d.f.i.i.f.p.p.PostgresPersistenceManager - No valid @iot.id. Using server generated id.

Does this sort of error slow things down? I am finding that insert still works fine, but unclear if I should be fixing this.

• I have tried strings, ints, ensuring uniqueness, etc, but I wasn't clear what is wrong when it says no valid @iot.id

[hylkevds]

Disabling the triggers is done directly in PostgreSQL using ALTER TABLE "DATASTREAMS" DISABLE TRIGGER datastreams_actualization_insert;

no valid @iot.id probably means there was no @iot.id field at all, or it was null. In most cases it is best to use numeric IDs for Observations, Datastreams and Features and have FROST generate the values.

Importing different Datastreams can be done in parallel for increased performance.

[C-Loftus]

Thank you very much for your answer! I ran ALTER TABLE public."OBSERVATIONS" DISABLE TRIGGER datastreams_actualization_insert; and that seemed to help!

As a quick followup:

In most cases it is best to use numeric IDs for Observations, Datastreams and Features and have FROST generate the values.

I am using persistence_idGenerationMode=ServerAndClientGenerated since I don't want to generate the @iot.id for the observations, but I do want to specify @iot.id for everything else. I thought it is ok to omit the @iot.id in that case? I am mainly just trying to avoid the error logging of this. (while still allowing for verbose logging otherwise)

Importing different Datastreams can be done in parallel for increased performance.

When you say this, are you referring to parallel HTTP batch requests, or doing that inside the db?

In general I have lots of observations but only around 200 datastreams. Shouldn't this be relatively fast either way? Apologies if I am misunderstanding what you mean or how datastream vs observation imports work.

Thank you again very much

[hylkevds]

Parallel HTTP requests for different Datastreams. Since writing an Observation takes a lock on the Datastream & Feature ids, only one process can write an observation for a Datastream/Feature at the same time, but different Datastreams/Features can be written to at the same time.

[C-Loftus]

Thank you that makes sense. If you have any thoughts on the @iot.id, please let me know, otherwise I will close this. Thanks!

[hylkevds]

The No valid @iot.id. Using server generated id. is just a debug message. If you don't want to see it, don't set the log level to DEBUG... :)