Postgres ARRAY ORM column isn't persisted by session, unless value is converted to tuple.

[Andrija Zarić]

If a value of a column declared as sqlalchemy.dialects.postgresql.ARRAY is altered during the scope of a session, it doesn't get persisted (flushed). This is happening to a value of type list. I tried to change the list object, it still behaves the same.

However, if value is changed to type tuple, it will get persisted. Once the object is queried back from the DB, column value will be list again.

The three cases are enclosed in attached file (commented out: change list object, line 58; change type to tuple line 61).

[Mike Bayer]

native python collections such as lists and dicts don't normally have any mechanism of alerting the ORM's attribute instrumentation that a change occurred. To get this behavior, you need to supply collections that produce these alerts on change, as documented at http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/mutable.html.

The attached script illustrates the changes needed to your script.

[Mike Bayer]

also, if you're not using mutable, then you'd need to assign a value to the attribute that differs from it's current value. So this would work:

session = Session()
h = session.query(History).first()
# Change the name just for fun...
h.name = "Blebleble"

h.time = h.time + [5, 1)](datetime(2013,)

this does not:

session = Session()
h = session.query(History).first()
# Change the name just for fun...
h.name = "Blebleble"
h.time.append(datetime(2013, 5, 1))

h.time = list(h.time)

because in the second case, h.time is being assigned a value that is identical to it's current "unchanged" value (because the ORM is not aware of the append()). this is also why tuples work, since you can't mutate the original a difference is guaranteed to exist.

[Andrija Zarić]

Thanks for a quick response and thorough explanation! I'm ashamed that I missed the documentation on mutation tracking - it must be I'm spoiled by "just works" SQLAlchemy, I searched for a solution just at postgresql part of the docs (maybe a link there to ext.mutable will help some other spoiled fellow developer?)

I knew that there wasn't a way for ORM to be aware of the change, initial thought was that maybe a comparison was done for columns like ARRAY. Explanation helped with what actually puzzled me (and I should've phrased bug accordingly) - that different list object was not persisted, but tuple was.

Thanks again!

[Mike Bayer]

up until version 0.7 we did actually do comparisons on all objects that had a "mutable" type - the approach was removed because it was very inefficient and also made the codebase a lot more complicated.

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)