Use jsonb datatype within postgres/timescale historian

[craig8]

Is your feature request related to a problem? Please describe. I changed my local database to use jsonb for the metadata column in the topics table. When the agent starts up for the first time everything is fine, however on subsequent starts the agent fails to start due to jsonb returning a dictionary rather than the text for the metadata.

As seen in the following stack trace.

2022-10-19 11:51:25,490 (sqlhistorianagent-4.0.0 954) volttron.platform.agent.base_historian ERROR: Failed to setup historian!
Traceback (most recent call last):
  File "/home/volttron/volttron_2222/volttron/platform/agent/base_historian.py", line 1298, in _historian_setup
    self.historian_setup()
  File "/home/volttron/volttron_2222/volttron/utils/docs.py", line 47, in f
    return self.mthd(obj, *args, **kwargs)
  File "/home/volttron/.volttron_2222/agents/06fd34af-72e6-4d25-b944-c18c38f48c06/sqlhistorianagent-4.0.0/sqlhistorian/historian.py", line 352, in historian_setup
    topic_meta_map = self.bg_thread_dbutils.get_topic_meta_map()
  File "/home/volttron/volttron_2222/volttron/platform/dbutils/postgresqlfuncts.py", line 353, in get_topic_meta_map
    meta_map = {tid: jsonapi.loads(meta) if meta else None for tid, meta in rows}
  File "/home/volttron/volttron_2222/volttron/platform/dbutils/postgresqlfuncts.py", line 353, in <dictcomp>
    meta_map = {tid: jsonapi.loads(meta) if meta else None for tid, meta in rows}
  File "/usr/lib/python3.8/json/__init__.py", line 341, in loads
    raise TypeError(f'the JSON object must be str, bytes or bytearray, '
TypeError: the JSON object must be str, bytes or bytearray, not dict

Describe the solution you'd like I think it would be easily handled in the current code structure by determining if the passed column is a dictionary or not before attempting to load it.

Describe alternatives you've considered Remove all of the text and go with jsonb for all

[craig8]

@schandrika thoughts? @ntenney this fits with our issue.

[craig8]

Instead of meta_map = {tid: jsonapi.loads(meta) if meta else None for tid, meta in rows}

if meta and isinstance(meta, str): jsonload elif isinstance(meta, dict) do dict map else meta = None

[schandrika]

I like checking isinstance than the alternate solution of forcing jsonb.

[craig8]

This is from @ntenney that we are using in our deployment.

meta_map = {tid: meta if meta and type(meta) == dict else jsonapi.loads(meta) if meta else None for tid, meta in rows}