Position attributes as jsonb type

Yury GPSa month 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 Tananaeva month ago

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

Yury GPSa month 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 Tananaeva month ago

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

Yury GPS25 days 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 Tananaev25 days ago

If messages stop coming, there must be some errors.

Yury GPS20 days 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 GPS20 days 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 Tananaev20 days ago

This means you have to change the code.