Position attributes as jsonb type

Yury GPS 8 months ago

Hello everyone! I want to know why attributes field from tc_positions table has type character varying(4000)?

traccar=# \d tc_positions
                                     Table "public.tc_positions"
   Column    |            Type             | Collation | Nullable |             Default
-------------+-----------------------------+-----------+----------+----------------------------------
 id          | integer                     |           | not null | generated by default as identity
 protocol    | character varying(128)      |           |          |
 deviceid    | integer                     |           | not null |
 servertime  | timestamp without time zone |           | not null | now()
 devicetime  | timestamp without time zone |           | not null |
 fixtime     | timestamp without time zone |           | not null |
 valid       | boolean                     |           | not null |
 latitude    | double precision            |           | not null |
 longitude   | double precision            |           | not null |
 altitude    | double precision            |           | not null |
 speed       | double precision            |           | not null |
 course      | double precision            |           | not null |
 address     | character varying(512)      |           |          |
 attributes  | character varying(4000)     |           |          |
 accuracy    | double precision            |           | not null | 0
 network     | character varying(4000)     |           |          |
 geofenceids | character varying(128)      |           |          |

Since it stores a JSON object with additional message parameters (sensor values, etc.), why is it stored as a string and not a JSONB type? JSONB type would allow to perform much faster selections/filters/expressions with fields and values from this object directly through SQL queries. Is it possible to modify the table schema with this type when starting a new traccar server without breaking the functionality?

Anton Tananaev 8 months ago

Because we support almost any SQL database, not just Postgres.

Yury GPS 8 months ago

Ok. Can you say how to change type for JSONB for attributes without issues?
Is I can just use

ALTER TABLE tc_positions ALTER COLUMN attributes TYPE jsonb USING attributes::jsonb;

or need to do changes in code?

Anton Tananaev 8 months ago

I don't know. You would have to try it.

Yury GPS 8 months ago

Conversion to JSONB type worked without problems via SQL query. But after conversion new messages from devices stop coming. When I converted back to strings - everything starts working again. No errors in the console. Perhaps you can remember where the attributes column type can be critical in the server code base?

Anton Tananaev 8 months ago

If messages stop coming, there must be some errors.

Yury GPS 8 months ago

I found an error:

2025-05-19 07:37:31  WARN: Failed to store position - ERROR: column "attributes" is of type jsonb but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 259 - PSQLException (... < QueryBuilder:469 < DatabaseStorage:95 < DatabaseHandler:44 < BasePositionHandler:34 < ...)
Yury GPS 8 months ago

And this is full trace:

2025-05-19 07:43:43  WARN: Failed to store position -
org.traccar.storage.StorageException: org.postgresql.util.PSQLException: ERROR: column "attributes" is of type jsonb but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 259
        at org.traccar.storage.DatabaseStorage.addObject(DatabaseStorage.java:97)
        at org.traccar.handler.DatabaseHandler.onPosition(DatabaseHandler.java:44)
        at org.traccar.handler.BasePositionHandler.handlePosition(BasePositionHandler.java:34)
        at org.traccar.ProcessingHandler$1.processed(ProcessingHandler.java:172)
        at org.traccar.handler.PositionForwardingHandler.onPosition(PositionForwardingHandler.java:134)
        at org.traccar.handler.BasePositionHandler.handlePosition(BasePositionHandler.java:34)
        at org.traccar.ProcessingHandler$1.processed(ProcessingHandler.java:172)
        at org.traccar.handler.DriverHandler.onPosition(DriverHandler.java:44)
        at org.traccar.handler.BasePositionHandler.handlePosition(BasePositionHandler.java:34)
        at org.traccar.ProcessingHandler$1.processed(ProcessingHandler.java:172)
        at org.traccar.handler.EngineHoursHandler.onPosition(EngineHoursHandler.java:46)
        at org.traccar.handler.BasePositionHandler.handlePosition(BasePositionHandler.java:34)
        at org.traccar.ProcessingHandler$1.processed(ProcessingHandler.java:172)
        at org.traccar.handler.ComputedAttributesHandler.onPosition(ComputedAttributesHandler.java:205)
        at org.traccar.handler.BasePositionHandler.handlePosition(BasePositionHandler.java:34)
        at org.traccar.ProcessingHandler$1.processed(ProcessingHandler.java:172)
        at org.traccar.handler.MotionHandler.onPosition(MotionHandler.java:41)
        at org.traccar.handler.BasePositionHandler.handlePosition(BasePositionHandler.java:34)
        at org.traccar.ProcessingHandler$1.processed(ProcessingHandler.java:172)
        at org.traccar.handler.GeocoderHandler.onPosition(GeocoderHandler.java:72)
        at org.traccar.handler.BasePositionHandler.handlePosition(BasePositionHandler.java:34)
        at org.traccar.ProcessingHandler$1.processed(ProcessingHandler.java:172)
        at org.traccar.handler.GeofenceHandler.onPosition(GeofenceHandler.java:44)
        at org.traccar.handler.BasePositionHandler.handlePosition(BasePositionHandler.java:34)
        at org.traccar.ProcessingHandler$1.processed(ProcessingHandler.java:172)
        at org.traccar.handler.FilterHandler.onPosition(FilterHandler.java:279)
        at org.traccar.handler.BasePositionHandler.handlePosition(BasePositionHandler.java:34)
        at org.traccar.ProcessingHandler$1.processed(ProcessingHandler.java:172)
        at org.traccar.handler.DistanceHandler.onPosition(DistanceHandler.java:74)
        at org.traccar.handler.BasePositionHandler.handlePosition(BasePositionHandler.java:34)
        at org.traccar.ProcessingHandler$1.processed(ProcessingHandler.java:172)
        at org.traccar.handler.HemisphereHandler.onPosition(HemisphereHandler.java:56)
        at org.traccar.handler.BasePositionHandler.handlePosition(BasePositionHandler.java:34)
        at org.traccar.ProcessingHandler$1.processed(ProcessingHandler.java:172)
        at org.traccar.handler.OutdatedHandler.onPosition(OutdatedHandler.java:53)
        at org.traccar.handler.BasePositionHandler.handlePosition(BasePositionHandler.java:34)
        at org.traccar.ProcessingHandler$1.processed(ProcessingHandler.java:172)
        at org.traccar.handler.ComputedAttributesHandler.onPosition(ComputedAttributesHandler.java:205)
        at org.traccar.handler.BasePositionHandler.handlePosition(BasePositionHandler.java:34)
        at org.traccar.ProcessingHandler.processPositionHandlers(ProcessingHandler.java:167)
        at org.traccar.ProcessingHandler.onReleased(ProcessingHandler.java:161)
        at org.traccar.database.BufferingManager.accept(BufferingManager.java:111)
        at org.traccar.ProcessingHandler.channelRead(ProcessingHandler.java:141)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
        at org.traccar.handler.network.RemoteAddressHandler.channelRead(RemoteAddressHandler.java:52)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
        at org.traccar.ExtendedObjectDecoder.channelRead(ExtendedObjectDecoder.java:88)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
        at org.traccar.WrapperContext.fireChannelRead(WrapperContext.java:102)
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:107)
        at org.traccar.WrapperInboundHandler.channelRead(WrapperInboundHandler.java:56)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
        at org.traccar.WrapperContext.fireChannelRead(WrapperContext.java:102)
        at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:346)
        at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:318)
        at org.traccar.WrapperInboundHandler.channelRead(WrapperInboundHandler.java:56)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
        at io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:93)
        at org.traccar.handler.network.StandardLoggingHandler.channelRead(StandardLoggingHandler.java:62)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:442)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
        at org.traccar.handler.network.NetworkMessageHandler.channelRead(NetworkMessageHandler.java:36)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:442)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
        at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1357)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:440)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
        at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:868)
        at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:166)
        at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:788)
        at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:724)
        at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:650)
        at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:562)
        at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:997)
        at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
        at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
        at java.base/java.lang.Thread.run(Thread.java:840)
Caused by: org.postgresql.util.PSQLException: ERROR: column "attributes" is of type jsonb but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 259
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)
        at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:180)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
        at org.traccar.storage.QueryBuilder.executeUpdate(QueryBuilder.java:469)
        at org.traccar.storage.DatabaseStorage.addObject(DatabaseStorage.java:95)
        ... 88 more
Anton Tananaev 8 months ago

This means you have to change the code.