I’m using CrateDB 5.5.2, and I create a dynamic objects table nifi.nifi_provenance to store json objects. But I encounter error Can't map PGType with oid=-272646673 to Crate type when inserting some records.
cr> show create table nifi.nifi_provenance;
+-------------------------------------------------------+
| SHOW CREATE TABLE nifi.nifi_provenance |
+-------------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "nifi"."nifi_provenance" ( |
| "content" OBJECT(DYNAMIC) AS ( |
| "schedule" OBJECT(DYNAMIC) AS ( |
| "summary" TEXT, |
| "reminders" OBJECT(DYNAMIC) AS ( |
| "is_remind" BIGINT, |
| "remind_before_event_secs" BIGINT, |
| "is_repeat" BIGINT, |
| "timezone" TEXT |
| ), |
| "start_time" BIGINT, |
| "organizer" TEXT, |
| "attendees" ARRAY(OBJECT(DYNAMIC) AS ( |
| "userid" TEXT |
| )), |
| "end_time" BIGINT, |
| "description" TEXT, |
| "location" TEXT, |
| "schedule_id" TEXT |
| ), |
| "agentid" BIGINT |
| ) |
| ) |
| CLUSTERED INTO 40 SHARDS |
| WITH ( |
| "allocation.max_retries" = 5, |
| "blocks.metadata" = false, |
| "blocks.read" = false, |
| "blocks.read_only" = false, |
| "blocks.read_only_allow_delete" = false, |
| "blocks.write" = false, |
| codec = 'default', |
| column_policy = 'strict', |
| "mapping.total_fields.limit" = 1000, |
| max_ngram_diff = 1, |
| max_shingle_diff = 3, |
| number_of_replicas = '0-1', |
| refresh_interval = 1000, |
| "routing.allocation.enable" = 'all', |
| "routing.allocation.total_shards_per_node" = -1, |
| "store.type" = 'fs', |
| "translog.durability" = 'REQUEST', |
| "translog.flush_threshold_size" = 536870912, |
| "translog.sync_interval" = 5000, |
| "unassigned.node_left.delayed_timeout" = 60000, |
| "write.wait_for_active_shards" = '1' |
| ) |
+-------------------------------------------------------+
Error Message
16:52:58 CST - p-nifi-s-002:9443 - ERROR
PutSQL[id=940c1a82-018f-1000-ffff-fffff49b2e2b] Failed to update database due to a failed batch update, Batch entry 0 insert into nifi.nifi_provenance values ('{
"schedule": {
"organizer": "00061300",
"schedule_id": "��Q",
"start_time": 1716281100,
"end_time": 1716288300,
"attendees": [{"userid":"00057939"},{"userid":"00061300"}],
"summary": "1",
"description": 61746076\n",
"reminders": {
"is_remind": 1,
"remind_before_event_secs": 900,
"is_repeat": 0,
"timezone" : "8"
},
"location": "903"
},
"agentid": 0
}') was aborted: ERROR: Can't map PGType with oid=-272646673 to Crate type
Where: io.crate.exceptions.SQLExceptions.esToCrateException(SQLExceptions.java:152)
io.crate.exceptions.SQLExceptions.prepareForClientTransmission(SQLExceptions.java:141)
io.crate.protocols.postgres.Messages.sendErrorResponse(Messages.java:190)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:304)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:281)
io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:346)
io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:333)
io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:454)
io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:290)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:440)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
Call getNextException to see other errors in the batch.. There were a total of 1 FlowFiles that failed, 0 that succeeded, and 0 that were not execute and will be routed to retry;
