[unixODBC-support] Problem in using cursor to fetchmillionsofrows

Jerry Lam jlam at sandvine.com
Thu Apr 7 17:13:02 BST 2005


Hi,

I recompiled unixodbc and got the libodbcpsql.so. 
My odbcinst.ini is :

[PostgreSQL]
Description     = PostgreSQL driver
Driver          = /usr/local/lib/libodbcpsql.so
Port            = 5432

Then, I ran the same code:
% package require tclodbc
2.3.1
% database db "DRIVER=PostgreSQL;Servername=localhost;UID=jerry;PWD=;Database=foo"
db
% set sql "select id from bar"
select id from bar
%  db statement stmt $sql
stmt
% stmt execute

This took :
last pid: 68433;  load averages:  0.77,  0.86,  0.86      up 3+02:39:37  16:09:43
46 processes:  1 running, 45 sleeping
CPU states:  0.0% user,  0.0% nice,  0.3% system,  0.8% interrupt, 98.9% idle
Mem: 192M Active, 541M Inact, 167M Wired, 46M Cache, 112M Buf, 57M Free
Swap: 1056M Total, 9864K Used, 1046M Free

  PID USERNAME     PRI NICE  SIZE    RES STATE  C   TIME   WCPU    CPU COMMAND
68405 jlam           2   0   166M   164M poll   0   6:01 39.75% 39.75% tclsh8.4
68419 pgsql          2   0 17556K 10972K sbwait 0   0:19  0.39%  0.39% postgres

166M of memory is used.

That's hurt and the memory is never released. Any idea why it does that? the stmt execute took awhile ~3 minutes before it returns ok.


Jerry


-----Original Message-----
From: unixodbc-support-bounces at easysoft.com
[mailto:unixodbc-support-bounces at easysoft.com]On Behalf Of Nick Gorham
Sent: Thursday, April 07, 2005 10:14 AM
To: Support for the unixODBC project
Subject: Re: [unixODBC-support] Problem in using cursor to
fetchmillionsofrows


Jerry Lam wrote:

>Hi,
>
>Here is the list of the packages installed in my freebsd machine. This morning I'm thinking of going from the other direction. I'm wondering if there is something that I can turn on/off postgresql to fetch one row at a time. However I browse through a lot of web sites, it doesn't seem there is a configuration option for that. Unless it is permanently turn off in the postgresqlodbc driver. 
>
>apache+mod_ssl-1.3.33+2.8.22 The Apache 1.3 webserver with SSL/TLS functionality
>bash-2.05b.007      The GNU Bourne Again Shell
>bash-completion-20031112 Programmable completion library for Bash 2.04 and up
>expat-1.95.6_1      XML 1.0 parser written in C
>freevrrpd-0.8.7_1   This a VRRP RFC2338 Compliant implementation under FreeBSD
>gettext-0.12.1      GNU gettext package
>gnupg-1.2.3_4       The GNU Privacy Guard
>htmldoc-1.8.23      Converts HTML to PDF and/or PostScript
>latprobe-BSD-1.02.0003 Active latency probe
>libgnugetopt-1.2    GNU getopt library
>libiconv-1.9.1_3    A character set conversion library
>licenseSigner-BSD-1.00.0066 Sandvine License Signer
>mm-1.3.1            Shared memory allocation library for pre-forked process mod
>net-snmp-5.21.0056  An extendable SNMP implementation
>openssl-0.9.7c      SSL and crypto library
>pamd-BSD-2.01.0001  Pluggable Authentication Module Daemon
>postgresql-7.4.6_0_001 The most advanced open-source database available anywhere
>postgresql-unixodbc-07.03.0200_010 PostgreSQL ODBC client support
>rc_subr-1.31        Common startup and shutdown subroutines used by scripts
>socket-1.1          Create tcp socket and connect to stdin/out
>sudo-1.6.7.5_0_004  Allow others to run commands as root
>syslog-ng-1.6.0.r4  A powerfull syslogd replacement
>tclkit-BSD-2.09.0005 Tool Control Language and packages
>unixODBC-2.2.7_005  ODBC library suite for Unix
>vim-lite-6.1.48     Vi "workalike", with many additional features
>
>  
>
Ok, you have the PG driver from the postgres project, that "may" (I 
don't know) consume the memory. You could try (out of interest) the 
postgres driver in unixODBC, its from the same code base, but older, and 
that doesn not AFAIK save any row information.

That driver is normally called

libodbcpsql.so

-- 
Nick
_______________________________________________
unixODBC-support mailing list
unixODBC-support at easysoft.com
http://mail.easysoft.com/mailman/listinfo/unixodbc-support




More information about the unixODBC-support mailing list