postgres data can't be written

[SBlechmann]

Hey there,

I was just trying out the postgres tutorial and after creating a subscription I wanted to check the database. There was no schema "openiot", so I checked for the subscription. According to orion, the subscriptions were sent successfully. So I checked the logs of draco and found the following:

errors in the batch. at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:148) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2184) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:481) at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:840) at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1567) at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242) at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:254) at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.access$100(StandardControllerServiceInvocationHandler.java:38) at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler$ProxiedReturnObjectInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:240) at com.sun.proxy.$Proxy140.executeBatch(Unknown Source) at org.apache.nifi.processors.ngsi.NGSIToPostgreSQL.lambda$new$7(NGSIToPostgreSQL.java:328) at org.apache.nifi.processor.util.pattern.ExceptionHandler.execute(ExceptionHandler.java:127) at org.apache.nifi.processors.ngsi.NGSIToPostgreSQL.lambda$new$9(NGSIToPostgreSQL.java:326) at org.apache.nifi.processor.util.pattern.PutGroup.putFlowFiles(PutGroup.java:94) at org.apache.nifi.processor.util.pattern.Put.onTrigger(Put.java:103) at org.apache.nifi.processors.ngsi.NGSIToPostgreSQL.lambda$onTrigger$17(NGSIToPostgreSQL.java:540) at org.apache.nifi.processor.util.pattern.PartialFunctions.onTrigger(PartialFunctions.java:120) at org.apache.nifi.processor.util.pattern.RollbackOnFailure.onTrigger(RollbackOnFailure.java:184) at org.apache.nifi.processors.ngsi.NGSIToPostgreSQL.onTrigger(NGSIToPostgreSQL.java:540) at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1173) at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:214) at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117) at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) ... 33 common frames omitted Caused by: org.postgresql.util.PSQLException: ERROR: column "recvtimets" of relation "door_003_door" already exists at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:254) at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.access$100(StandardControllerServiceInvocationHandler.java:38) at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler$ProxiedReturnObjectInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:240) at com.sun.proxy.$Proxy141.execute(Unknown Source) at org.apache.nifi.processors.ngsi.NGSIToPostgreSQL.lambda$new$3(NGSIToPostgreSQL.java:276) at org.apache.nifi.processor.util.pattern.ExceptionHandler.execute(ExceptionHandler.java:127) at org.apache.nifi.processors.ngsi.NGSIToPostgreSQL.lambda$new$4(NGSIToPostgreSQL.java:265) at org.apache.nifi.processors.ngsi.NGSIToPostgreSQL.lambda$new$6(NGSIToPostgreSQL.java:314) at org.apache.nifi.processor.util.pattern.PutGroup.putFlowFiles(PutGroup.java:91) ... 16 common frames omitted

Hope you know what's going on here..

[jason-fox]

Could you raise this over at https://github.com/ging/fiware-draco/issues - it looks like an internal Draco processing error - probably connected to an update in the NIFI version.

[johncaipa]

I have the same problem @SBlechmann , the example with mongo works fine also mysql works, but the example with postgres does not store data, and the subscription is working but not reporting data
[ { "id": "623e2b3d5954eb099b6ae969", "description": "Notify Draco of all context changes", "status": "active", "subject": { "entities": [ { "idPattern": ".*" } ], "condition": { "attrs": [] } }, "notification": { "attrs": [], "onlyChangedAttrs": false, "attrsFormat": "normalized", "http": { "url": "http://draco:5050/v2/notify" } }, "throttling": 5 } ]