SQLALchemy 1.0.13: JSONB insert into PostgreSQL fails with internal error 'unsupported jsonb version number 123'

[Andrew M]

Hi,

Thanks for SQLAlchemy. I'm trying to insert some JSONB into a PostgreSQL database. My insert succeeds if the column type is JSON but fails if the type is JSONB.

SQLAlchemy version: sqlalchemy-1.0.13
PostgreSQL: 9.5.3
DBAPI: py-postgresql
Python: 3.4
OS: Windows 8.1 64-bit

Here's my test code:

import postgresql
import sqlalchemy
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import MetaData
from sqlalchemy.dialects.postgresql import JSON, JSONB
from datetime import datetime, tzinfo, timedelta

engine=create_engine("postgresql+pypostgresql://postgres:*******@localhost:5432/dbname")

NAMING_CONVENTION = {
"ix": 'ix_%(column_0_label)s',
"uq":"uq_%(table_name)s_%(column_0_name)s",
"ck":"ck_%(table_name)s_%(constraint_name)s",
"fk":"fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk":"pk_%(table_name)s"
}

metadata = MetaData(naming_convention=NAMING_CONVENTION)
Base = declarative_base(metadata=metadata)

class JSON_test(Base):
    __tablename__ = 'json_test'
    json_id = Column(Integer, primary_key=True)
    history = Column(JSON)

class JSONB_test(Base):
    __tablename__ = 'jsonb_test'
    jsonb_id = Column(Integer, primary_key=True)
    history = Column(JSONB)

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

session.add(JSON_test(history={"entry":{"user":"andrew","datetime" : "{datetime}".format(datetime=datetime.now()),"reason":"init","description":"init","source":"andrew"}}))
session.commit()

# This JSON object is identical to the last
session.add(JSONB_test(history={"entry":{"user":"andrew","datetime" : "{datetime}".format(datetime=datetime.now()),"reason":"init","description":"init","source":"andrew"}}))
session.commit()

After running this code, the JSON_test commit succeeds but the JSONB_test commit throws this error:

>>> session.add(JSONB_test(history={"entry":{"user":"andrew","datetime" : "{datetime}".format(datetime=datetim
e.now()),"reason":"init","description":"init","source":"andrew"}}))
>>> session.commit()
Traceback (most recent call last):
  File "C:\Python34\lib\site-packages\postgresql\driver\dbapi20.py", line 92, in __next__
    r = self.buf[self.pos]
IndexError: list index out of range

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Python34\lib\site-packages\sqlalchemy\engine\result.py", line 1019, in fetchone
    row = self._fetchone_impl()
  File "C:\Python34\lib\site-packages\sqlalchemy\engine\result.py", line 900, in _fetchone_impl
    return self.cursor.fetchone()
  File "C:\Python34\lib\site-packages\postgresql\driver\dbapi20.py", line 187, in fetchone
    return next(self._portal)
  File "C:\Python34\lib\site-packages\postgresql\driver\dbapi20.py", line 100, in __next__
    self.buf = next(self.chunks)
  File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 1009, in __next__
    self.database._pq_complete()
  File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 2604, in _pq_complete
    self.typio.raise_error(x.error_message, cause = getattr(x, 'exception', None))
  File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 541, in raise_error
    self.raise_server_error(error_message, **kw)
  File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 532, in raise_server_error
    raise server_error
postgresql.exceptions.InternalError: unsupported jsonb version number 123
  CODE: XX000
  LOCATION: File 'jsonb.c', line 122, in jsonb_recv from SERVER
RESULT:
  chunksize: 4096
  type: MultiXactInsideBlock
  parameters:
    ('{"entry": {"user": "andrew", "datetime": "2016-06-01 09:07:30.502703", "reason": "init", "description":
"init", "source": "andrew"}}',)

  cursor_id: py:0x4289550
STATEMENT: [prepared]
  sql_parameter_types: ['"pg_catalog"."jsonb"']
  results: ('jsonb_id' 'INTEGER')
  statement_id: py:0x42d16a0
  string:
    INSERT INTO jsonb_test (history) VALUES ($1) RETURNING jsonb_test.jsonb_id
CONNECTION: [idle in block]
  client_address: 127.0.0.1/32
  client_port: 64593
  version: PostgreSQL 9.5.3, compiled by Visual C++ build 1800, 64-bit
CONNECTOR: [Host] pq://postgres:***@localhost:5432/products
  category: None
DRIVER: postgresql.driver.pq3.Driver

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 801, in commit
    self.transaction.commit()
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 392, in commit
    self._prepare_impl()
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 372, in _prepare_impl
    self.session.flush()
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 2019, in flush
    self._flush(objects)
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 2137, in _flush
    transaction.rollback(_capture_exception=True)
  File "C:\Python34\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Python34\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 2101, in _flush
    flush_context.execute()
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 373, in execute
    rec.execute(self)
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 532, in execute
    uow
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\persistence.py", line 174, in save_obj
    mapper, table, insert)
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\persistence.py", line 800, in _emit_insert_statements
    execute(statement, params)
  File "C:\Python34\lib\site-packages\sqlalchemy\engine\base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "C:\Python34\lib\site-packages\sqlalchemy\sql\elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Python34\lib\site-packages\sqlalchemy\engine\base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\Python34\lib\site-packages\sqlalchemy\engine\base.py", line 1159, in _execute_context
    result = context._setup_crud_result_proxy()
  File "C:\Python34\lib\site-packages\sqlalchemy\engine\default.py", line 826, in _setup_crud_result_proxy
    row = result.fetchone()
  File "C:\Python34\lib\site-packages\sqlalchemy\engine\result.py", line 1028, in fetchone
    self.cursor, self.context)
  File "C:\Python34\lib\site-packages\sqlalchemy\engine\base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "C:\Python34\lib\site-packages\sqlalchemy\util\compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Python34\lib\site-packages\sqlalchemy\util\compat.py", line 185, in reraise
    raise value.with_traceback(tb)
  File "C:\Python34\lib\site-packages\sqlalchemy\engine\result.py", line 1019, in fetchone
    row = self._fetchone_impl()
  File "C:\Python34\lib\site-packages\sqlalchemy\engine\result.py", line 900, in _fetchone_impl
    return self.cursor.fetchone()
  File "C:\Python34\lib\site-packages\postgresql\driver\dbapi20.py", line 187, in fetchone
    return next(self._portal)
  File "C:\Python34\lib\site-packages\postgresql\driver\dbapi20.py", line 100, in __next__
    self.buf = next(self.chunks)
  File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 1009, in __next__
    self.database._pq_complete()
  File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 2604, in _pq_complete
    self.typio.raise_error(x.error_message, cause = getattr(x, 'exception', None))
  File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 541, in raise_error
    self.raise_server_error(error_message, **kw)
  File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 532, in raise_server_error
    raise server_error
sqlalchemy.exc.InternalError: (postgresql.exceptions.InternalError) unsupported jsonb version number 123
  CODE: XX000
  LOCATION: File 'jsonb.c', line 122, in jsonb_recv from SERVER
RESULT:
  chunksize: 4096
  type: MultiXactInsideBlock
  parameters:
    ('{"entry": {"user": "andrew", "datetime": "2016-06-01 09:07:30.502703", "reason": "init", "description":
"init", "source": "andrew"}}',)

  cursor_id: py:0x4289550
STATEMENT: [prepared]
  sql_parameter_types: ['"pg_catalog"."jsonb"']
  results: ('jsonb_id' 'INTEGER')
  statement_id: py:0x42d16a0
  string:
    INSERT INTO jsonb_test (history) VALUES ($1) RETURNING jsonb_test.jsonb_id
CONNECTION: [failed block]
  client_address: 127.0.0.1/32
  client_port: 64593
  version: PostgreSQL 9.5.3, compiled by Visual C++ build 1800, 64-bit
CONNECTOR: [Host] pq://postgres:***@localhost:5432/products
  category: None
DRIVER: postgresql.driver.pq3.Driver

My apologies if I'm handling the JSONB insert incorrectly, but from the docs, it seems that the JSON and JSONB inserts are exactly the same syntax?

Thanks,
Andrew

[Mike Bayer]

have you tried psycopg2? pypostgresql is not a well supported dialect.

> <http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#postgresql-data-types>,

> it seems that the JSON and JSONB inserts are exactly the same syntax?
>
> Thanks,
> Andrew
>

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Andrew M]

On Wednesday, June 1, 2016 at 10:39:22 AM UTC+10, Mike Bayer wrote:

have you tried psycopg2?  pypostgresql is not a well supported dialect.

That's fixed it - thanks Mike (and thanks for creating SQLAlchemy and Mako).