Pierre,
The name of the field is "reference" and the data type is VARCHAR(128).
The name of the "bool" is "active".
Here is the table (it is intentional that "date_created" and
"data_changed" are not of type "timestamp"):
CREATE TABLE text.metadata
(
id bigint NOT NULL DEFAULT
nextval('blockchain.metadata_id_seq'::regclass),
vid bigint NOT NULL,
reference character varying(128) NOT NULL,
reference_description character varying(512) NOT NULL,
reference_name character varying(100) NOT NULL,
reference_hash character varying(256) NOT NULL,
active boolean NOT NULL DEFAULT true,
ts timestamp with time zone NOT NULL DEFAULT now(),
process character varying(64) NOT NULL,
type character varying(64) NOT NULL,
reference_size bigint NOT NULL,
uplink character(36) NOT NULL,
downlink character(36) NOT NULL,
reference_created character varying(32) NOT NULL,
csid_created character varying(256) ,
csid_changed character varying(256),
date_created character varying(32),
date_changed character varying(32),
CONSTRAINT metadata_pkey PRIMARY KEY (id)
USING INDEX TABLESPACE ts_metadata_data,
CONSTRAINT metadata_vid_fkey FOREIGN KEY (vid)
REFERENCES metadata.all (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE
NOT VALID
)
Here is the JSON data:
{
"event":"add",
"vid":33,
"uid":"d3595259",
"uuid":"2f10f0ad-fd63-476b-90b8-7d059c5a7128",
"process":"nifi add",
"date_changed":"2021-03-25T14:33:39.591Z",
"csid_changed":"bMO33ZX9smu8Fvmgk6FRiVaZhmQ",
"uplink":"79e9ab3b-e302-4b9e-a120-b743e14a9cfe",
"reference":"hKaT0ytUPfgwNcjhDDtKRin42743t",
"date_created":"2021-03-25T14:33:39.591Z",
"csid_created":"bMO33ZX9smu8Fvmgk6FRiVaZhmQ",
"reference_description":"QYusH+cD8u0458vDG0m3qmi",
"reference_name":"WVp9qWLhAsd16L39d3tEr2M",
"reference_size":15,
"reference_hash":"36a12c1cd47016daa7b2786893678aa52a003656ae8aed484eaf5373ef1ce496a835449cc91ef2c3dd96ef14b9ed6c075e27b74b4dbd651fe8890acbaa3d6b4e",
"reference_created":"2021-03-25T14:32:50.344Z",
"type":"data",
"downlink":"354fed7e-1456-4248-a27f-93db18e68cd8"
}
Am 25.03.21 um 17:05 schrieb Pierre Villard:
I guess we could have a better error message but to me it sounds like there
is a field in your incoming JSON that contains
"hKaT0ytUPfgwNcjhDDtKRin42743t" and the name of this field is matching a
column in the Postgres table that is expecting a boolean. That's why the
casting does not work.
Can you share a sample of the JSON data you're pushing into postgres and
the create table statement for the destination table?
Pierre
Le jeu. 25 mars 2021 à 16:20, u...@moosheimer.com <u...@moosheimer.com> a
écrit :
Hi Dev-Team,
We still have a problem with PutDatabaseRecord on NiFi 1.13.2, which
used to run on NiFi 1.12.1.
We have a JSON that has both more attributes than exist in the table and
missing attributes that are defined in the table with default value.
The table has a bool value that is set "default true" and is not passed
in the JSON.
Processor settings are:
Record Reader - JsonTreeReader
Database Type - PostgreSQL (tried also with Generic)
Statement Type - INSERT
Translate Field Names - false
Quote Column Identifier - false
Quate Table identifier - false
Unmatched Field Behavior - Ignore Unmatched Fields
Unmatched Column Behavior - Ignore Unmatched Columns
As error message we get:
2021-03-25 15:00:24,509 ERROR [Timer-Driven Process Thread-7]
o.a.n.p.standard.PutDatabaseRecord
PutDatabaseRecord[id=a1ef9918-0177-1000-ffff-ffffba128239] Failed to put
Records to database for
StandardFlowFileRecord[uuid=47bacb24-718b-42e0-97a9-588ab628a4af,claim=StandardContentClaim
[resourceClaim=StandardResourceClaim[id=1616614332963-2,
container=default, section=2], offset=97327973,
length=877],offset=0,name=3699054725979545,size=877]. Routing to
failure.: org.postgresql.util.PSQLException: Cannot cast to boolean:
"hKaT0ytUPfgwNcjhDDtKRin42743t"
org.postgresql.util.PSQLException: Cannot cast to boolean:
"hKaT0ytUPfgwNcjhDDtKRin42743t"
at
org.postgresql.jdbc.BooleanTypeUtil.cannotCoerceException(BooleanTypeUtil.java:99)
at
org.postgresql.jdbc.BooleanTypeUtil.fromString(BooleanTypeUtil.java:67)
at
org.postgresql.jdbc.BooleanTypeUtil.castToBoolean(BooleanTypeUtil.java:43)
at
org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:655)
at
org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:935)
at
org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:529)
at
org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:529)
at jdk.internal.reflect.GeneratedMethodAccessor687.invoke(Unknown
Source)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
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.$Proxy357.setObject(Unknown Source)
at
org.apache.nifi.processors.standard.PutDatabaseRecord.executeDML(PutDatabaseRecord.java:736)
at
org.apache.nifi.processors.standard.PutDatabaseRecord.putToDatabase(PutDatabaseRecord.java:841)
at
org.apache.nifi.processors.standard.PutDatabaseRecord.onTrigger(PutDatabaseRecord.java:487)
at
org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
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.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at
java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305)
at
java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
at
java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at
java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Our test environment is:
NiFi 1.13.2 Cluster with 3 nodes
Postgres 13.2
openjdk version "11.0.10"
Ubuntu 20.04.1 LTS
Is this a known issue or an individual fate that makes us despair?
