[sqlite] Problem with memory using Tcl-defined SQL functions

[Miguel Muñoz Arancón]

Hello,

I am having a problem with the memory usage of SQLite, when using from Tcl.
Could you tell me if any of you has had similar problems?

The problem is that, when I send a query that uses a Tcl-defined SQL
function to the database, it starts allocating memory that is not freed
afterwards, until I close the Tcl interpreter. After a number of queries,
the paging file grows over 600MB, and Windows complains that it has run out
of virtual memory.

The configuration of my system is:
- P4 3GHz, 768MB RAM
- Windows XP Professional, SP1
- ActiveState ActiveTcl 8.4.7
- SQLite 3.2.1 (tclsqlite)

I attach a simple script that reproduces the problem in my system. It
creates a simple database with one table storing pairs (index, text string).
I define also a function in the database using the Tcl function command.
Insertions run without problem. When I send a SELECT query that uses the Tcl
function:
- if the string legth is up to 91, everything goes all right
- if it is 92 or greater, the program begins to consume all the memory

I haven't looked at the SQLite sources, but my guess is that maybe memory
used to pass arguments to the Tcl function is allocated in blocks, and then
only the first block is freed. Do you think that this can be the problem?

Please, tell me if someone has had this kind of problem before. And, any
idea of how to solve it?

Thanks a lot.

Miguel Muñoz
GMV S.A.
http://www.gmv.es/

Well, here goes the script:

##########################################################

# Source file and call:
#   test MODE STRING_LENGTH NUM_RECORDS
# where
#   MODE = function | nofunction
#   STRING_LENGTH < 120
# examples:
# test function 91 300000       ---> ok
# test function 92 300000       ---> memory leak
# test nofunction 92 300000     ---> ok

# My problem occurs when STRING_LENGTH > 91.
# I've tried with a number of records between 100,000 and 1,000,000.
# Memory leak starts when the SQL query is launched.

package require sqlite3


proc test { mode strlen max } {

  set template
{abcdefghjk01234567890123456789012345678901234567890123456789012345678901234
567890123456789012345678901234567890123456789}
  #             0       10        20        30        40        50        60
70        80        90       100       110       120

  set eventmsg [string range $template 0 [expr {$strlen - 1}]]

  # create database
  puts "CREATING DATABASE"
  catch { file delete sqlite-example.db }
  sqlite3 db1 sqlite-example.db
  db1 eval { CREATE TABLE t1(serial int, edata text) }

  # create SQL function
  db1 function checklen { check-length }

  # insert
  db1 eval { BEGIN }
  set serial 1
  for { set i 0 } { $i < $max } { incr i } {
    db1 eval {INSERT INTO t1 VALUES($serial,$eventmsg)}
    incr serial
  }
  db1 eval { COMMIT }

  # send query
  puts "SENDING QUERY"
  switch $mode {
    nofunction {
      db1 eval { SELECT * FROM t1 WHERE length(edata) > 1000 LIMIT 1 } \
                 found { puts "Found $found(serial): [string range
$found(edata) 0 30]" }
    }
    function {
      db1 eval { SELECT * FROM t1 WHERE checklen(edata,1000) LIMIT 1 } \
                 found { puts "Found $found(serial): [string range
$found(edata) 0 30]" }
    }
    default {
      puts "Error: mode must be 'function' or 'nofunction'."
    }
  }

  # close
  db1 close

  return
}


proc check-length { event len } {
  return [expr {[string length $event] > $len}]
}



proc check-db {} {
  sqlite3 db1 sqlite-example.db
  set last [db1 eval {SELECT count(*) FROM t1}]
  db1 eval { SELECT * FROM t1 WHERE serial <= 10 ORDER BY serial } {
    puts "$serial: [string range $edata 0 50] . . ."
  }
  puts " . . . "
  db1 eval { SELECT * FROM t1 \
             WHERE (serial > $last - 10) AND (serial <= $last) \
             ORDER BY serial } {
    puts "$serial: [string range $edata 0 50] . . ."
  }
  db1 close
}

# END of the script
##########################################



______________________
Este mensaje, y en su caso, cualquier fichero anexo al mismo,
 puede contener informacion clasificada por su emisor como confidencial
 en el marco de su Sistema de Gestion de Seguridad de la 
Informacion siendo para uso exclusivo del destinatario, quedando 
prohibida su divulgacion copia o distribucion a terceros sin la 
autorizacion expresa del remitente. Si Vd. ha recibido este mensaje 
 erroneamente, se ruega lo notifique al remitente y proceda a su borrado. 
Gracias por su colaboracion.
______________________
This message including any attachments may contain confidential 
information, according to our Information Security Management System,
 and intended solely for a specific individual to whom they are addressed.
 Any unauthorised copy, disclosure or distribution of this message
 is strictly forbidden. If you have received this transmission in error,
 please notify the sender immediately and delete it.
______________________