Cygnus-ld fails to correctly formulate INSERT querries to postgress.

[Sceatha]

It seems that Cygnus-ld faces some issues when trying to create the querry for Postgress.

Subscriptions to Orion-ld seem to fire normally but Postgress throws an error and possibly closes the connection, as it stops any records from Cygnus.

The issue appears when Cygnus-ld is used for historical data persistence along with the Device Smart Data Model, where Cygnus has issues with the unitCode attribute, as well as attributes that are represented by arrays (eg. corrdinates in location).

Cygnus sends a requests with the value for these fields missing (see error example below).

An example of the error message in Postgress:

2021-01-26 14:57:08.189 | ERROR: syntax error at or near ","+| INSERT INTO test_debug.urn_ngsi_ld_device_risensor_ts001 (recvTime,entityId,entityType,ch6,ch6_observedAt,ch6_unitCode,ch5,ch5_observedAt,ch5_unitCode,ch7,ch7_observedAt,c h7_unitCode,ch0,ch0_observedAt,ch0_unitCode,ch2,ch2_observedAt,ch2_unitCode,ch1,ch1_observedAt,ch1_unitCode,ch4,ch4_observedAt,ch4_unitCode,ch3,ch3_observedAt,ch3_unitCode) VALUES ('2021-01-26T14:46:15.520Z','urn:ngsi-ld:Device:RISenso r-TS001','Device','3.006405','2021-01-26T16:46:15.000Z',,'2.036672','2021-01-26T16:46:15.000Z',,'2.278511','2021-01-26T16:46:15.000Z',,'3.78975','2021-01-26T16:46:15.000Z',,'2.919825','2021-01-26T16:46:15.000Z',,'3.704785','2021-01-26T 16:46:15.000Z',,'3.478503','2021-01-26T16:46:15.000Z',,'2.100996','2021-01-26T16:46:15.000Z',) | Position: 463 | 2021-01-26 14:57:08.199 | ERROR: syntax error at or near ","+| INSERT INTO test_debug.urn_ngsi_ld_device_risensor_ts001 (recvTime,entityId,entityType,ch6,ch6_observedAt,ch6_unitCode,ch5,ch5_observedAt,ch5_unitCode,ch7,ch7_observedAt,c h7_unitCode,ch0,ch0_observedAt,ch0_unitCode,ch2,ch2_observedAt,ch2_unitCode,ch1,ch1_observedAt,ch1_unitCode,ch4,ch4_observedAt,ch4_unitCode,ch3,ch3_observedAt,ch3_unitCode) VALUES ('2021-01-26T14:46:16.895Z','urn:ngsi-ld:Device:RISenso r-TS001','Device','2.453492','2021-01-26T16:46:16.000Z',,'3.891019','2021-01-26T16:46:16.000Z',,'2.519365','2021-01-26T16:46:16.000Z',,'1.903199','2021-01-26T16:46:16.000Z',,'1.735895','2021-01-26T16:46:16.000Z',,'3.754071','2021-01-26 T16:46:16.000Z',,'2.992609','2021-01-26T16:46:16.000Z',,'3.505311','2021-01-26T16:46:16.000Z',) | Position: 463 |

[fgalan]

Maybe @anmunoz, the author of the Cygnus-LD contribution, could help providing feedback on this issue.

[anmunoz]

Hi @fgalan ,

Yes some people from the community report this issue, some problems are generated when unit code values are present in the notification. I have been check, and I think the problem is in this line of code:

fiware-cygnus/cygnus-ngsi-ld/src/main/java/com/telefonica/iot/cygnus/sinks/NGSIPostgreSQLSink.java

Line 485 in a2d1345

if (!"observedAt".contentEquals(x2) && !"type".contentEquals(x2) && !"value".contentEquals(x2) && !"object".contentEquals(x2)) {

In where an extra condition for unitCode should be included. Other option is taking the approach used by Draco using if else conditions for this type of attributes:

https://github.com/ging/fiware-draco/blob/ead02accade3e75f6ac0c508598d730c767849ee/nifi-ngsi-bundle/nifi-ngsi-processors/src/main/java/org/apache/nifi/processors/ngsi/ngsi/utils/NGSIUtils.java#L106-L111

                            if ("type".equals(keyOne)){
                                // Do Nothing
                            } else if (!"observedAt".equals(keyOne) || !"unitCode".equals(keyOne)){
                                // TBD Do Something for unitCode and observedAt
                            } else if (!"value".equals(keyOne)){
                                JSONObject value2 = value.getJSONObject(keyOne);

I think this can solve the problem, Can someone from your team check and test that?

[fgalan]

Fixed by PR #2001

@Sceatha could you confirm so this issue can be closed, please?

[Sceatha]

Hello,
This issue has indeed been resolved.

Sorry for the delayed response, just had the chance to test it today.

Thank you for the fix!