memory usage indicators wrap around over 2GiB

[reidpr]

I'm seeing negative numbers from memoryused(), memoryhighwatermark(), and status(SQLITE_STATUS_MEMORY_USED) once the values go over 2GiB. This is on a 64-bit install of Ubuntu Utopic.

To reproduce, please run the following program:

#!/usr/bin/env python3

import apsw
import itertools

db = apsw.Connection("./dbfile")
curs = db.cursor()
data = "a" * 2**24  # 16MiB of text

print('APSW version:', apsw.apswversion())
print('SQLite version:', apsw.sqlitelibversion())

curs.execute("pragma cache_size = -4194304;")
curs.execute("create table a (a int primary key, b text) without rowid")

curs.execute("begin")
for i in itertools.count():
   curs.execute("insert into a values (?, ?)", (i, data))
   print('iteration %d, %d bytes written' % (i+1, (i+1) * len(data)))
   memoryused = apsw.memoryused()
   memoryhighwater = apsw.memoryhighwater()
   status_memory_used = apsw.status(apsw.SQLITE_STATUS_MEMORY_USED)
   print('  memoryused():', memoryused)
   print('  memoryhighwater():', memoryhighwater)
   print('  status(apsw.SQLITE_STATUS_MEMORY_USED):', status_memory_used)
   if (    memoryused < 0
       and memoryhighwater < 0
       and status_memory_used[0] < 0
       and status_memory_used[1] < 0):
      print('all negative, stopping')
      break

I get the following output:

APSW version: 3.8.9-r1
SQLite version: 3.8.9
iteration 1, 16777216 bytes written
  memoryused(): 38047248
  memoryhighwater(): 54870776
  status(apsw.SQLITE_STATUS_MEMORY_USED): (38047248, 54870776)
iteration 2, 33554432 bytes written
  memoryused(): 59232520
  memoryhighwater(): 76104440
  status(apsw.SQLITE_STATUS_MEMORY_USED): (59232520, 76104440)
[...]
iteration 100, 1677721600 bytes written
  memoryused(): 2125971072
  memoryhighwater(): 2142748752
  status(apsw.SQLITE_STATUS_MEMORY_USED): (2125971072, 2142748752)
iteration 101, 1694498816 bytes written
  memoryused(): 2146894200
  memoryhighwater(): -2131295416
  status(apsw.SQLITE_STATUS_MEMORY_USED): (2146894200, -2131295416)
iteration 102, 1711276032 bytes written
  memoryused(): -2127149968
  memoryhighwater(): -2110372288
  status(apsw.SQLITE_STATUS_MEMORY_USED): (-2127149968, -2110372288)
all negative, stopping

Note that the output of the functions has apparently wrapped around. I was expecting it to give increasing positive values either indefinitely or until about 4GiB.

SQLite 3.8.9 seems to have had some related changes (3rd item).

Please let me know what additional information would be helpful.

Thanks for APSW. It is incredibly useful to me.

[rogerbinns]

Yeah, sadly the SQLite developers refuse to use standard types like size_t despite a many year campaign by me to fix this. I'll switch to the new -64 interface to fix this issue. Thanks for reporting it.

[rogerbinns]

On a completely unrelated point, you are the first person to ever do a bug report supplying test code as Python 3, and not Python 2!

[rogerbinns]

Note that memoryused and memoryhighwater are both defined to return 64 bits, and that APSW does use PyLong_FromLongLong in order to the 64 bit value in C into a Python number object. On looking at their code, they call the 32 bit version of sqlite3_status. Reported to the SQLite developers at http://permalink.gmane.org/gmane.comp.db.sqlite.general/94544

[rogerbinns]

The SQLite authors fixed memoryused/memoryhighwater with that fix being in the next SQLite release. http://www.sqlite.org/src/info/8a0d5d5e9a451560

[reidpr]

Very much appreciated, Roger. I can confirm status() works for me under APSW 3.8.10.1-r1; memoryused() and memoryhighwater() still wrap around, which as you note is expected.