pgsql + jsonb + orm update = possible bug?

[Richard Gerd Kuesters]

Hello! I was working with a JSONB column in postgres and I noticed that no updates were issued when changing some inside value, so I have to issue "flag_modified" everytime I change my JSONB attribute. Here's a sample code that shows this:

# -*- coding: utf-8 -*-

from __future__ import unicode_literals


from sqlalchemy import Column, Integer, create_engine
from sqlalchemy.orm import Session
from sqlalchemy.orm.attributes import flag_modified
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import JSONB


Base = declarative_base()


class EntityA(Base):
    __tablename__ = 'entity_a'

    a_id = Column(Integer, primary_key=True)
    a_value = Column(JSONB, nullable=False)


class EntityB(Base):
    __tablename__ = 'entity_b'

    b_id = Column(Integer, primary_key=True)
    _b_value = Column(JSONB, nullable=False)

    @hybrid_property
    def b_value(self):
        return self._b_value

    @b_value.setter
    def b_value(self, value):
        self._b_value = value


class EntityC(Base):
    __tablename__ = 'entity_c'

    c_id = Column(Integer, primary_key=True)
    _c_value = Column(JSONB, nullable=False)

    @hybrid_property
    def c_value(self):
        return self._c_value

    @c_value.setter
    def c_value(self, value):
        self._c_value = value
        flag_modified(self, '_c_value')


if __name__ == '__main__':
    engine = create_engine('postgresql://test:tes...@127.0.0.1/testing', echo=False)
    Base.metadata.create_all(engine)
    session = Session(engine)

    # testing A, without hybrid property

    a_test = EntityA()
    a_test.a_value = dict(hello='world', test=1, enabled=True)

    session.add(a_test)
    session.commit()

    print('EntityA, after insert:', a_test.a_value)

    json_obj_a = a_test.a_value
    json_obj_a['hello'] = 'foo'
    a_test.a_value = json_obj_a

    session.commit()

    # assert a_test.a_value.get('hello') == 'foo'  # error
    assert a_test.a_value.get('hello') == 'world'  # works

    print('EntityA, after update:', a_test.a_value)

    # testing B, with hybrid property and no flag_modified

    b_test = EntityB()
    b_test.b_value = dict(hello='world', test=1, enabled=True)

    session.add(b_test)
    session.commit()

    print('EntityB, after insert:', b_test.b_value)

    json_obj_b = b_test.b_value
    json_obj_b['hello'] = 'bar'
    b_test.b_value = json_obj_b

    session.commit()

    # assert b_test.b_value.get('hello') == 'bar'  # error
    assert b_test.b_value.get('hello') == 'world'  # works

    print('EntityB, after update:', b_test.b_value)

    # testing C, with hybrid property and flag_modified

    c_test = EntityC()
    c_test.c_value = dict(hello='world', test=1, enabled=True)

    session.add(c_test)
    session.commit()

    print('EntityC, after insert:', c_test.c_value)

    json_obj_c = c_test.c_value
    json_obj_c['hello'] = 'baz'
    c_test.c_value = json_obj_c

    session.commit()

    assert c_test.c_value.get('hello') == 'baz'  # works
    # assert c_test.c_value.get('hello') == 'world'  # error

    print('EntityC, after update:', c_test.c_value)

    # end test
    session.close()

    Base.metadata.drop_all(engine)

extra data, if needed:

$ pip freeze
psycopg2==2.6.1
SQLAlchemy==1.0.8

$ postgres --version
postgres (PostgreSQL) 9.4.4

$ python --version
Python 2.7.10

$ uname -a
Linux marrow.polluxnet 4.2.3-1-ARCH #1 SMP PREEMPT Sat Oct 3 18:52:50 CEST 2015 x86_64 GNU/Linux

thanks a lot, if any other information is needed, please let me know.


best regards,
richard.

[Mike Bayer]

On 10/9/15 8:33 AM, Richard Gerd Kuesters wrote:

Hello! I was working with a JSONB column in postgres and I noticed that no updates were issued when changing some inside value, so I have to issue "flag_modified" everytime I change my JSONB attribute. Here's a sample code that shows this:

I see no usage of Mutable, which is required if you want to detect updates within a JSON value.   See the notes on the JSON(B) types for this:

http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html?highlight=jsonb#sqlalchemy.dialects.postgresql.JSONB

The JSON type, when used with the SQLAlchemy ORM, does not detect in-place mutations to the structure. In order to detect these, the sqlalchemy.ext.mutable extension must be used. This extension will allow “in-place” changes to the datastructure to produce events which will be detected by the unit of work. See the example at HSTORE for a simple example involving a dictionary.

--
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.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Richard Gerd Kuesters]

thanks Mike! I'm glad I used a "?" in the subject :) I was thinking that something may be missing, so there it is ...

cheers,
richard.