TROE example #1397

MarkusPfundstein · 2023-06-27T14:54:37Z

Hi,

I was wondering if there is any example available on how to work with the TROE implementation. I got Orion-LD to start with a connection to postgres. I created entities in mongodb and i PATCH their attributes via Postman. But how do I activate the temporal sink for those PATCH updates? So that every time I patch one the attr numValue, the change is being stored in TimescaleDB so that I can query it with the temporal interface.

Thanks a lot.

Creation of Measurement entity:

{
    "id": "{{mtpicnic-measurement}}",
    "refDevice":{
        "type": "Property",
        "value": "{{mtpicnic}}"
    },
    "type": "DeviceMeasurement",
    "measurementType": {
        "type": "Property",
        "value": "PeopleCount"
    },
    "numValue": {
        "type": "Property",
        "value": 27
    },
    "dateObserved": {
        "type": "Property",
        "value": "2023‐06‐26T18:43:30Z"
    },
    "description": {
        "type": "Property",
        "value": "Observed numbers of people"
    }
}

measurement PATCH (update of numValue)

{
    "numValue": {
        "type": "Property",
        "value": 28
    }
}

orion Startup:

=Tuesday 27 Jun 14:40:09 2023.212Z | lvl=INFO | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1274]:main | msg=Startup completed
time=Tuesday 27 Jun 14:40:09 2023.212Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1289]:main | msg=Initialization is Done
time=Tuesday 27 Jun 14:40:09 2023.212Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1290]:main | msg=  Accepting REST requests on port 1026 (experimental API endpoints are disabled)
time=Tuesday 27 Jun 14:40:09 2023.212Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1291]:main | msg=  TRoE:                    Enabled
time=Tuesday 27 Jun 14:40:09 2023.212Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1292]:main | msg=  Distributed Operation:   Disabled
time=Tuesday 27 Jun 14:40:09 2023.212Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1293]:main | msg=  Health Check:            Disabled
time=Tuesday 27 Jun 14:40:09 2023.212Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1296]:main | msg=  Postgres Server Version: 12.0.15
time=Tuesday 27 Jun 14:40:09 2023.212Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1298]:main | msg=  Mongo Server Version:    4.4.22
time=Tuesday 27 Jun 14:40:09 2023.212Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1312]:main | msg=  Mongo Driver:            Legacy C++ Driver (deprecated by mongodb)

The text was updated successfully, but these errors were encountered:

kzangeli · 2023-06-27T15:31:53Z

Hi,
as pushing all this data to postgres is quite costly, and not everybody wants this features, it is not on by default.
You have to tell the broker to do it, by using the CLI parameter "-troe" when starting the broker.
The corresponding env var is called "ORIONLD_TROE":

% export ORIONLD_TROE=TRUE

There's a bunch more options you might want to use:

% orionld -u
Usage: orionld  [option '-U' (extended usage)]
                [option '-u' (usage)]
                ...
                [option '-troe' (enable TRoE - temporal representation of entities)]
                [option '-troeHost' <host for troe database db server>]
                [option '-troePort' <port for troe database db server>]
                [option '-troeUser' <username for troe database db server>]
                [option '-troePwd' <password for troe database db server>]
                [option '-troePoolSize' <size of the connection pool for TRoE Postgres database connections>]

Try the "extended usage" (-U) to see the names of the corresponding env vars (and more things, like default values).

Good luck!

kzangeli · 2023-06-27T15:34:07Z

This tutorial should help:
https://ngsi-ld-tutorials.readthedocs.io/en/latest/short-term-history.html

MarkusPfundstein · 2023-06-27T18:14:31Z

Hi kzangeli,

thanks a lot for your quick response.
I started the server eith Troe support. I also seeded postgres with initial.sql.
But I dont see anything happen within postgres. The attributes and entities table stays empty.
Thats why I thought I maybe need to add some kind of 'link' between my mongodb entity and the Troe interface. So that orion knows it has to persist each change in Timescale.

orion=> \d
               List of relations
 Schema |       Name        | Type  |  Owner   
--------+-------------------+-------+----------
 public | attributes        | table | markus
 public | entities          | table | markus
 public | geography_columns | view  | postgres
 public | geometry_columns  | view  | postgres
 public | spatial_ref_sys   | table | postgres
 public | subattributes     | table | markus

orion=> select * from attributes
orion-> ;
 instanceid | id | opmode | entityid | observedat | subproperties | unitcode | datasetid | valuetype | text | boolean | number | datetime | compound | geopoint | geopolygon | geomultipolygon | geolinestring | geomultilinestring | ts 
------------+----+--------+----------+------------+---------------+----------+-----------+-----------+------+---------+--------+----------+----------+----------+------------+-----------------+---------------+--------------------+----
(0 rows)

orion=> select * from entities;
 instanceid | ts | opmode | id | type 
------------+----+--------+----+------
(0 rows)

I also checked the tutorial that you have linked. Thanks. But It seems to be more about querying and not about how to configure everything.

I think my definite question is: If I have a mongodb entity with an attr that I regurarily PATCH, and I want to use Troe for this entity. What do I have to do besides enabling troe in orion?

Thanks a lot :-)

kzangeli · 2023-06-27T18:22:51Z

Ok, then something seems to go wrong somewhere.
With troe enabled, the broker adds to postgres for every creation/modification/deletion of entities/attributes.

Do the traces give any hint?

MarkusPfundstein · 2023-06-27T19:06:19Z

Hi Kzangeli,

hereby the log. I executed three PATCH requests.

markus@deep-field-01:~/git/context.Orion-LD$ orionld -logLevel DEBUG  -fg -troe -troeUser orion -troePwd <redacted> -troeHost 127.0.0.1
time=Tuesday 27 Jun 19:03:50 2023.448Z | lvl=INFO | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1082]:main | msg=Orion Context Broker is running
time=Tuesday 27 Jun 19:03:50 2023.448Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[782]:versionInfo | msg=Version Info:
time=Tuesday 27 Jun 19:03:50 2023.448Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[783]:versionInfo | msg=-----------------------------------------
time=Tuesday 27 Jun 19:03:50 2023.448Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[784]:versionInfo | msg=orionld version:    post-v1.2.0
time=Tuesday 27 Jun 19:03:50 2023.448Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[785]:versionInfo | msg=based on orion:     1.15.0-next
time=Tuesday 27 Jun 19:03:50 2023.448Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[786]:versionInfo | msg=git hash:           a13e7ed3ecf1c66ccd5f7763f2fa4a87f50ade58
time=Tuesday 27 Jun 19:03:50 2023.448Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[787]:versionInfo | msg=build branch:       develop
time=Tuesday 27 Jun 19:03:50 2023.448Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[788]:versionInfo | msg=compiled by:        root
time=Tuesday 27 Jun 19:03:50 2023.448Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[789]:versionInfo | msg=compiled in:        deep-field-01
time=Tuesday 27 Jun 19:03:50 2023.448Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[790]:versionInfo | msg=-----------------------------------------
time=Tuesday 27 Jun 19:03:50 2023.449Z | lvl=WARN | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1090]:main | msg=Error initializing Prometheus Metrics library
NOTICE:  extension "postgis" already exists, skipping
time=Tuesday 27 Jun 19:03:50 2023.458Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=mongocInit.cpp[291]:mongocInit | msg=Connecting to mongo for the C driver
time=Tuesday 27 Jun 19:03:50 2023.466Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=mongoConnectionPool.cpp[313]:mongoConnectionPoolInit | msg=Connecting to mongo for the C++ legacy driver
time=Tuesday 27 Jun 19:03:50 2023.469Z | lvl=INFO | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=MongoGlobal.cpp[243]:mongoInit | msg=Connected to mongo at localhost:orion
time=Tuesday 27 Jun 19:03:50 2023.469Z | lvl=INFO | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1274]:main | msg=Startup completed
time=Tuesday 27 Jun 19:03:50 2023.470Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1289]:main | msg=Initialization is Done
time=Tuesday 27 Jun 19:03:50 2023.470Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1290]:main | msg=  Accepting REST requests on port 1026 (experimental API endpoints are disabled)
time=Tuesday 27 Jun 19:03:50 2023.470Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1291]:main | msg=  TRoE:                    Enabled
time=Tuesday 27 Jun 19:03:50 2023.470Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1292]:main | msg=  Distributed Operation:   Disabled
time=Tuesday 27 Jun 19:03:50 2023.470Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1293]:main | msg=  Health Check:            Disabled
time=Tuesday 27 Jun 19:03:50 2023.470Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1296]:main | msg=  Postgres Server Version: 12.0.15
time=Tuesday 27 Jun 19:03:50 2023.470Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1298]:main | msg=  Mongo Server Version:    4.4.22
time=Tuesday 27 Jun 19:03:50 2023.470Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionld.cpp[1312]:main | msg=  Mongo Driver:            Legacy C++ Driver (deprecated by mongodb)
time=Tuesday 27 Jun 19:03:54 2023.338Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionldMhdConnectionInit.cpp[982]:orionldMhdConnectionInit | msg=------------------------- Servicing NGSI-LD request 001: PATCH /ngsi-ld/v1/entities/urn:ngsi-ld:Devices:public-eye/Waterfront-measurement/attrs --------------------------
time=Tuesday 27 Jun 19:04:06 2023.080Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionldMhdConnectionInit.cpp[982]:orionldMhdConnectionInit | msg=------------------------- Servicing NGSI-LD request 002: PATCH /ngsi-ld/v1/entities/urn:ngsi-ld:Devices:public-eye/Boardwalk-measurement/attrs --------------------------
time=Tuesday 27 Jun 19:04:44 2023.798Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionldMhdConnectionInit.cpp[982]:orionldMhdConnectionInit | msg=------------------------- Servicing NGSI-LD request 003: PATCH /ngsi-ld/v1/entities/urn:ngsi-ld:Devices:public-eye/MT-Picnic-measurement/attrs --------------------------

The PATCH requests look like this:

Database dumb:

~/git/context.Orion-LD$ psql -d orion;
psql (12.15 (Ubuntu 12.15-0ubuntu0.20.04.1))
Type "help" for help.

orion=> select * from attributes;
 instanceid | id | opmode | entityid | observedat | subproperties | unitcode | datasetid | valuetype | text | boolean | number | datetime | compound | geopoint | geopolygon | geomultipolygon | geolinestring | geomultilinestring | ts 
------------+----+--------+----------+------------+---------------+----------+-----------+-----------+------+---------+--------+----------+----------+----------+------------+-----------------+---------------+--------------------+----
(0 rows)

orion=> select * from entities;
 instanceid | ts | opmode | id | type 
------------+----+--------+----+------
(0 rows)

orion=>

kzangeli · 2023-06-27T19:20:53Z

No errors in the logs, that's good, I guess.
You wouldn't be using a tenant, would you?
That would explain why you find nothing un the default database.

Let's up the traces, add these two options:
-logLevel DEBUG -t 0-255

MarkusPfundstein · 2023-06-27T19:25:26Z

Nope, no tenants.

I added the trace option. Hereby a copy of the output:

time=Tuesday 27 Jun 19:24:09 2023.419Z | lvl=TMP | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionldMhdConnectionInit.cpp[982]:orionldMhdConnectionInit | msg=------------------------- Servicing NGSI-LD request 003: PATCH /ngsi-ld/v1/entities/urn:ngsi-ld:Devices:public-eye/Boardwalk-measurement/attrs --------------------------
time=Tuesday 27 Jun 19:24:09 2023.419Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionldMhdConnectionInit.cpp[406]:orionldHttpHeaderReceive | msg=Got an HTTP Header: 'X-Forwarded-For': '127.0.0.1'
time=Tuesday 27 Jun 19:24:09 2023.419Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionldMhdConnectionInit.cpp[406]:orionldHttpHeaderReceive | msg=Got an HTTP Header: 'Host': 'localhost'
time=Tuesday 27 Jun 19:24:09 2023.419Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionldMhdConnectionInit.cpp[406]:orionldHttpHeaderReceive | msg=Got an HTTP Header: 'X-Real-IP': '127.0.0.1'
time=Tuesday 27 Jun 19:24:09 2023.419Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionldMhdConnectionInit.cpp[406]:orionldHttpHeaderReceive | msg=Got an HTTP Header: 'Connection': 'close'
time=Tuesday 27 Jun 19:24:09 2023.419Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionldMhdConnectionInit.cpp[406]:orionldHttpHeaderReceive | msg=Got an HTTP Header: 'Content-Length': '158'
time=Tuesday 27 Jun 19:24:09 2023.419Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionldMhdConnectionInit.cpp[406]:orionldHttpHeaderReceive | msg=Got an HTTP Header: 'User-Agent': 'Go-http-client/1.1'
time=Tuesday 27 Jun 19:24:09 2023.419Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionldMhdConnectionInit.cpp[406]:orionldHttpHeaderReceive | msg=Got an HTTP Header: 'Accept': '*/*'
time=Tuesday 27 Jun 19:24:09 2023.419Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionldMhdConnectionInit.cpp[406]:orionldHttpHeaderReceive | msg=Got an HTTP Header: 'Content-Type': 'application/json'
time=Tuesday 27 Jun 19:24:09 2023.419Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionldMhdConnectionInit.cpp[406]:orionldHttpHeaderReceive | msg=Got an HTTP Header: 'Accept-Encoding': 'gzip'
time=Tuesday 27 Jun 19:24:09 2023.419Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=orionldMhdConnectionTreat.cpp[1113]:orionldMhdConnectionTreat | msg=Request Payload Body: {"numValue":{"type":"Property","value":1.73154},"dateObserved":{"type":"Property","value":"2023-06-27 21:24:09.414888"}}
time=Tuesday 27 Jun 19:24:09 2023.420Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=sem.cpp[312]:cacheSemTake | msg=addTriggeredSubscriptions_withCache taking the 'cache' semaphore for 'match subs for notifications'
time=Tuesday 27 Jun 19:24:09 2023.420Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=sem.cpp[333]:cacheSemTake | msg=addTriggeredSubscriptions_withCache has the 'cache' semaphore
time=Tuesday 27 Jun 19:24:09 2023.420Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=sem.cpp[393]:cacheSemGive | msg=addTriggeredSubscriptions_withCache gives the 'cache' semaphore for 'match subs for notifications'
time=Tuesday 27 Jun 19:24:09 2023.420Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=restReply.cpp[77]:restReply | msg=Response Body: 'None'
time=Tuesday 27 Jun 19:24:09 2023.420Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=restReply.cpp[78]:restReply | msg=Response Code:  204
time=Tuesday 27 Jun 19:24:09 2023.420Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=pgCommands.cpp[59]:pgCommands | msg=SQL: INSERT INTO attributes(instanceId,id,opMode,entityId,observedAt,subProperties,unitCode,datasetId,valueType,text,boolean,number,datetime,compound,geoPoint,geoMultiPoint,geoPolygon,geoMultiPolygon,geoLineString,geoMultiLineString,ts) VALUES ('urn:ngsi-ld:attribute:instance:30a344b8-1520-11ee-baf1-934f03f1c222', 'https://uri.etsi.org/ngsi-ld/default-context/numValue', 'Replace', 'urn:ngsi-ld:Devices:public-eye/Boardwalk-measurement', null, false, null, 'None', 'Number', null, null, 1.731540, null, null, null, null, null, null, null, null, '2023-06-27T19:24:09.419Z'),('urn:ngsi-ld:attribute:instance:30a344b9-1520-11ee-baf1-934f03f1c222', 'https://uri.etsi.org/ngsi-ld/default-context/dateObserved', 'Replace', 'urn:ngsi-ld:Devices:public-eye/Boardwalk-measurement', null, false, null, 'None', 'String', '2023-06-27 21:24:09.414888', null, null, null, null, null, null, null, null, null, null, '2023-06-27T19:24:09.419Z');

Very interesting that it does construct a INSERT query. But it doesnt seem to execute it?

kzangeli · 2023-06-27T19:26:07Z

And, for clarity, "-troe" is all you need for the broker to persist to the historical database.

kzangeli · 2023-06-27T19:28:25Z

So, we even see that it "tries" to persist.
Yet, nothing in your db ... Weird.
I'll have to add traces. Perhaps some error trace is missing. Shouldn't be but...

What about tenants?
Are you using the default tenant only?

MarkusPfundstein · 2023-06-27T19:34:27Z

I didnt do anything out of the ordinary. I just created my three entities and thats it.

Btw! I created the entities before I enabled TROE. Maybe thats it?

kzangeli · 2023-06-27T19:35:05Z

Ok, I just remembered I had problems with the postgres driver to give back errors.
Often it said "all ok" but the server has errors in its log.

Check the logfile.of the postgres server
The answer is probably there.

/var/log/postgres/something, I assume ...

kzangeli · 2023-06-27T19:36:47Z

The patch would add to postgres regardless of when the entity was created .
I think something goes wrong in postgres. But, the driver doesn't tell the broker about it .
Seen it many times

MarkusPfundstein · 2023-06-27T19:38:40Z

Ok. I also confirmed that adding entities doesnt add it. It constructs again two queries but no change in db:

ime=Tuesday 27 Jun 19:38:05 2023.247Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=sem.cpp[312]:cacheSemTake | msg=addTriggeredSubscriptions_withCache taking the 'cache' semaphore for 'match subs for notifications'
time=Tuesday 27 Jun 19:38:05 2023.248Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=sem.cpp[333]:cacheSemTake | msg=addTriggeredSubscriptions_withCache has the 'cache' semaphore
time=Tuesday 27 Jun 19:38:05 2023.248Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=sem.cpp[393]:cacheSemGive | msg=addTriggeredSubscriptions_withCache gives the 'cache' semaphore for 'match subs for notifications'
time=Tuesday 27 Jun 19:38:05 2023.248Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=restReply.cpp[77]:restReply | msg=Response Body: 'None'
time=Tuesday 27 Jun 19:38:05 2023.248Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=restReply.cpp[78]:restReply | msg=Response Code:  201
time=Tuesday 27 Jun 19:38:05 2023.248Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=pgCommands.cpp[59]:pgCommands | msg=SQL: INSERT INTO entities(instanceId,ts,opMode,id,type) VALUES ('urn:ngsi-ld:attribute:instance:22d46ebe-1522-11ee-baf1-934f03f1c222', '2023-06-27T19:38:05.246Z', 'Create', 'urn:ngsi-ld:Devices:public-eye/Boardwalk', 'https://smartdatamodels.org/dataModel.Device/Device');
time=Tuesday 27 Jun 19:38:05 2023.248Z | lvl=DEBUG | corr=N/A | trans=N/A | from=N/A | srv=N/A | subsrv=N/A | comp=Orion | op=pgCommands.cpp[59]:pgCommands | msg=SQL: INSERT INTO attributes(instanceId,id,opMode,entityId,observedAt,subProperties,unitCode,datasetId,valueType,text,boolean,number,datetime,compound,geoPoint,geoMultiPoint,geoPolygon,geoMultiPolygon,geoLineString,geoMultiLineString,ts) VALUES ('urn:ngsi-ld:attribute:instance:22d46ebf-1522-11ee-baf1-934f03f1c222', 'https://smartdatamodels.org/dataModel.Device/category', 'Create', 'urn:ngsi-ld:Devices:public-eye/Boardwalk', null, false, null, 'None', 'String', 'sensor', null, null, null, null, null, null, null, null, null, null, '2023-06-27T19:38:05.246Z'),('urn:ngsi-ld:attribute:instance:22d46ec0-1522-11ee-baf1-934f03f1c222', 'https://uri.etsi.org/ngsi-ld/description', 'Create', 'urn:ngsi-ld:Devices:public-eye/Boardwalk', null, false, null, 'None', 'String', 'Public Eye Camera for monitoring Boardwalk area', null, null, null, null, null, null, null, null, null, null, '2023-06-27T19:38:05.246Z');

MarkusPfundstein · 2023-06-27T19:40:01Z

Btw. Executing the query directly in psql gives me this:

orion=> INSERT INTO attributes(instanceId,id,opMode,entityId,observedAt,subProperties,unitCode,datasetId,valueType,text,boolean,number,datetime,compound,geoPoint,geoMultiPoint,geoPolygon,geoMultiPolygon,geoLineString,geoMultiLineString,ts) VALUES ('urn:ngsi-ld:attribute:instance:4918ac5c-1522-11ee-baf1-934f03f1c222', 'https://uri.etsi.org/ngsi-ld/default-context/numValue', 'Replace', 'urn:ngsi-ld:Devices:public-eye/Boardwalk-measurement', null, false, null, 'None', 'Number', null, null, 6.884771, null, null, null, null, null, null, null, null, '2023-06-27T19:39:09.447Z'),('urn:ngsi-ld:attribute:instance:4918ac5d-1522-11ee-baf1-934f03f1c222', 'https://uri.etsi.org/ngsi-ld/default-context/dateObserved', 'Replace', 'urn:ngsi-ld:Devices:public-eye/Boardwalk-measurement', null, false, null, 'None', 'String', '2023-06-27 21:39:09.441378', null, null, null, null, null, null, null, null, null, null, '2023-06-27T19:39:09.447Z');
ERROR:  column "geomultipoint" of relation "attributes" does not exist
LINE 1: ...pe,text,boolean,number,datetime,compound,geoPoint,geoMultiPo...

I will reseed

kzangeli · 2023-06-27T19:43:02Z

Ok!
I also used to do stuff by hand.
I guess there you.have the error.
The expert on this kind of things is a colleague of mine.
I know we have scripts to merge an old db to a new one, I just don't know exactly how it works, I'm afraid.

MarkusPfundstein · 2023-06-27T19:52:53Z

Hi,

it works now. I used the current.sql script to reseed instead of initial.sql and see the attributes now appearing :-)

One observation. If you create an entity BEFORE temporarl storage is enabled, the entities table isnt being populated. Only attributes. So i deleted all entities and recreated them and now its all good

orion=> select * from entities;
                             instanceid                              |           ts            | opmode |                          id                           |                              type                              
---------------------------------------------------------------------+-------------------------+--------+-------------------------------------------------------+----------------------------------------------------------------
 urn:ngsi-ld:attribute:instance:de7e06a6-1523-11ee-baf1-934f03f1c222 | 2023-06-27 19:50:29.588 | Create | urn:ngsi-ld:Devices:public-eye/MT-Picnic              | https://smartdatamodels.org/dataModel.Device/Device
 urn:ngsi-ld:attribute:instance:df5bf466-1523-11ee-baf1-934f03f1c222 | 2023-06-27 19:50:31.043 | Create | urn:ngsi-ld:Devices:public-eye/Boardwalk              | https://smartdatamodels.org/dataModel.Device/Device
 urn:ngsi-ld:attribute:instance:e03a5e90-1523-11ee-baf1-934f03f1c222 | 2023-06-27 19:50:32.5   | Create | urn:ngsi-ld:Devices:public-eye/Waterfront             | https://smartdatamodels.org/dataModel.Device/Device
 urn:ngsi-ld:attribute:instance:e121926a-1523-11ee-baf1-934f03f1c222 | 2023-06-27 19:50:34.016 | Create | urn:ngsi-ld:Devices:public-eye/MT-Picnic-measurement  | https://uri.etsi.org/ngsi-ld/default-context/DeviceMeasurement
 urn:ngsi-ld:attribute:instance:e23056f0-1523-11ee-baf1-934f03f1c222 | 2023-06-27 19:50:35.79  | Create | urn:ngsi-ld:Devices:public-eye/Boardwalk-measurement  | https://uri.etsi.org/ngsi-ld/default-context/DeviceMeasurement
 urn:ngsi-ld:attribute:instance:e323832a-1523-11ee-baf1-934f03f1c222 | 2023-06-27 19:50:37.384 | Create | urn:ngsi-ld:Devices:public-eye/Waterfront-measurement | https://uri.etsi.org/ngsi-ld/default-context/DeviceMeasurement

Thanks a lot for your help

kzangeli · 2023-06-27T21:04:34Z

ok!
Happy to hear all is ok now.
Pity the stupid postgres driver does such a poor job of reporting errors.
Would be nice to have the broker directly give you the error you got from manually trying the INSERT ...

And yes, the broker only adds historical stuff for every update/create/delete.
Your case gets quite weird as the attributes exist in their table but their "parent" entity does not.
I might have to do something about that.
A check to see whether the entity exists in troe and if not, take that operation (probably an update), and pretend the entity was created at that time. Not perfect but a lot better than the way it is right now.
I'll add it to my ever growing ToDo :)

Thanks for reporting!

wistefan · 2023-06-28T06:42:25Z

Hi, see the documentation on db-migration: https://github.com/FIWARE/context.Orion-LD/blob/develop/doc/manuals-ld/troe.md#database-migration
The "intial.sql" is the first version of the db, corresponding to Orion-LD 0.7.0

kzangeli closed this as completed Jun 28, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

TROE example #1397

TROE example #1397

MarkusPfundstein commented Jun 27, 2023 •

edited

Loading

kzangeli commented Jun 27, 2023 •

edited

Loading

kzangeli commented Jun 27, 2023

MarkusPfundstein commented Jun 27, 2023 •

edited

Loading

kzangeli commented Jun 27, 2023 •

edited

Loading

MarkusPfundstein commented Jun 27, 2023

kzangeli commented Jun 27, 2023

MarkusPfundstein commented Jun 27, 2023

kzangeli commented Jun 27, 2023

kzangeli commented Jun 27, 2023

MarkusPfundstein commented Jun 27, 2023

kzangeli commented Jun 27, 2023

kzangeli commented Jun 27, 2023

MarkusPfundstein commented Jun 27, 2023

MarkusPfundstein commented Jun 27, 2023

kzangeli commented Jun 27, 2023

MarkusPfundstein commented Jun 27, 2023

kzangeli commented Jun 27, 2023

wistefan commented Jun 28, 2023

TROE example #1397

TROE example #1397

Comments

MarkusPfundstein commented Jun 27, 2023 • edited Loading

kzangeli commented Jun 27, 2023 • edited Loading

kzangeli commented Jun 27, 2023

MarkusPfundstein commented Jun 27, 2023 • edited Loading

kzangeli commented Jun 27, 2023 • edited Loading

MarkusPfundstein commented Jun 27, 2023

kzangeli commented Jun 27, 2023

MarkusPfundstein commented Jun 27, 2023

kzangeli commented Jun 27, 2023

kzangeli commented Jun 27, 2023

MarkusPfundstein commented Jun 27, 2023

kzangeli commented Jun 27, 2023

kzangeli commented Jun 27, 2023

MarkusPfundstein commented Jun 27, 2023

MarkusPfundstein commented Jun 27, 2023

kzangeli commented Jun 27, 2023

MarkusPfundstein commented Jun 27, 2023

kzangeli commented Jun 27, 2023

wistefan commented Jun 28, 2023

MarkusPfundstein commented Jun 27, 2023 •

edited

Loading

kzangeli commented Jun 27, 2023 •

edited

Loading

MarkusPfundstein commented Jun 27, 2023 •

edited

Loading

kzangeli commented Jun 27, 2023 •

edited

Loading