Modifying FloatArray field results in Error on PostgreSQL

[IrkenZim]

Hi,

appending to a FloatArray causes a pony.orm.dbapiprovider.ProgrammingError when using a Postgres DB, while it works with a SQLite DB.

Example with three different demos:

from pony.orm import *

useDemo = 1
# With postgres, demos 1 and 2 do not work. Demo 3 works, although it should be just the same as Demo 2.

dbtype = 'sqlite'
# dbtype = 'postgres'
# With dbtype = sqlite, everything works. With dbtype = postgres (and a postgres 12 database), demos 1 and 2 do not work.

db = Database()

class FloatArrayTest(db.Entity):
    id = PrimaryKey(int, auto=True)
    myFloats = Optional(FloatArray)

if dbtype == 'sqlite':
    db.bind('sqlite', filename='test.sqlite', create_db=True)
elif dbtype == 'postgres':
    db.bind('postgres', host='myhost', user='myuser', port='5432', password='mypw', database='mydb')

db.generate_mapping(create_tables=True)

with db_session:
    FloatArrayTest.select(id=0).delete()
with db_session:
    FloatArrayTest(id=0)
print("Object created.")

if useDemo == 1:
    # Modifying FloatArray by directly appending to it
    with db_session:
        fa = FloatArrayTest[0]
        print(fa.myFloats)
        fa.myFloats.append(42.0)
        print(fa.myFloats)

    with db_session:
        print("Now appending another number")
        fa = FloatArrayTest[0]
        fa.myFloats.append(2.71828)
        print(fa.myFloats)

if useDemo == 2:
    # Modifying FloatArray by casting to list, appending, writing back to DB object
    with db_session:
        fa = FloatArrayTest[0]
        fa.myFloats = [42.0, 2.71828]
        print(fa.myFloats)

    with db_session:
        fa = FloatArrayTest[0]
        floatList = list(fa.myFloats)
        floatList.append(3.1415)
        print("My new list: {}".format(floatList))
        fa.myFloats = floatList

if useDemo == 3:
    # Modifying FloatArray by just assigning a new, longer list to it (which is, incidentally, identical to the new list in Demo 2)
    with db_session:
        fa = FloatArrayTest[0]
        fa.myFloats = [42.0, 2.71828]
        print(fa.myFloats)

    with db_session:
        fa = FloatArrayTest[0]
        floatList = [42.0, 2.71828, 3.1415]
        print("My new list: {}".format(floatList))
        fa.myFloats = floatList

Using sqlite, everything works, using postgres results in the output:

python3 floatArrayMinimal.py 
Object created.
[]
[42.0]
Now appending another number
[42.0, 2.71828]
Traceback (most recent call last):
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/orm/dbapiprovider.py", line 52, in wrap_dbapi_exceptions
    return func(provider, *args, **kwargs)
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/orm/dbproviders/postgres.py", line 250, in execute
    else: cursor.execute(sql, arguments)
psycopg2.errors.UndefinedFunction: operator does not exist: double precision[] = numeric[]
LINE 4:   AND "myfloats" = ARRAY[42.0]
                         ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "floatArrayMinimal.py", line 55, in <module>
    print(fa.myFloats)
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/orm/core.py", line 467, in __exit__
    db_session._commit_or_rollback(exc_type, exc, tb)
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/orm/core.py", line 481, in _commit_or_rollback
    commit()
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/orm/core.py", line 372, in commit
    rollback_and_reraise(sys.exc_info())
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/orm/core.py", line 361, in rollback_and_reraise
    reraise(*exc_info)
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/utils/utils.py", line 88, in reraise
    try: raise exc.with_traceback(tb)
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/orm/core.py", line 370, in commit
    cache.flush()
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/orm/core.py", line 1891, in flush
    if obj is not None: obj._save_()
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/orm/core.py", line 5425, in _save_
    elif status == 'modified': obj._save_updated_()
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/orm/core.py", line 5344, in _save_updated_
    cursor = database._exec_sql(sql, arguments, start_transaction=True)
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/orm/core.py", line 945, in _exec_sql
    connection = cache.reconnect(e)
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/orm/core.py", line 1779, in reconnect
    if not provider.should_reconnect(exc): reraise(*sys.exc_info())
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/utils/utils.py", line 88, in reraise
    try: raise exc.with_traceback(tb)
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/orm/core.py", line 943, in _exec_sql
    try: new_id = provider.execute(cursor, sql, arguments, returning_id)
  File "<string>", line 2, in execute
  File "/home/irkenzim/.local/lib/python3.8/site-packages/pony/orm/dbapiprovider.py", line 64, in wrap_dbapi_exceptions
    raise ProgrammingError(e)
pony.orm.dbapiprovider.ProgrammingError: operator does not exist: double precision[] = numeric[]
LINE 4:   AND "myfloats" = ARRAY[42.0]
                         ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

It seems like, when updating the entry, the db row to update is not just identified by the primary key, but also by myFloats, and postgres does not have an operator for that.

I am using pony 0.7.16, python 3.8, PostgreSQL 12. Help would greatly be appreciated.