R help - Jan 2009 - crash on multiple queries to postgresql db [solved]

On Mon, Jan 12, 2009 at 6:31 PM, Martin Morgan <mtmorgan at fhcrc.org>
wrote:
> An off-list guess is, ironically, to NOT call dbDisconnect or gc().
>
> The C code in the package registers a 'finalizer' that gets called
when the
> object is garbage collected. This is the same code that gets called by
> dbDisconnect. Unfortunately, the code does not check to see if it's
been
> called already, so I think it's trying to double-close the db
connection. By
> removing dbDisconnect and gc(), the object gets garbage collected
eventually
> (when gc() is called, perhaps implicitly, after the function exits and
there
> are no longer any references to conn).
>
> I think the other strategy is to remove dbDisconnect() and instead say
> rm(conn); gc() so that the gc() is the only one that triggers the
finalizer.
>
> Martin
Thanks Martin. Your suggested appears to have made the difference! Great work.

Cheers,

Dylan

>
>
> Dylan Beaudette wrote:
>>
>> On Friday 09 January 2009, Joe Conway wrote:
>>>
>>> Dylan Beaudette wrote:
>>>>
>>>> Subsequent calls to:
>>>>
>>>> conn <- dbConnect(PgSQL(), host="localhost",
dbname="xxx", user="xxx")
>>>> query <- dbSendQuery(conn, query_text)
>>>> res <- dbGetResult(query)
>>>>
>>>> are resulting in this:
>>>>
>>>> *** glibc detected *** /usr/local/lib/R/bin/exec/R: realloc():
invalid
>>>>
>>>> other attached packages:
>>>> [1] RdbiPgSQL_1.8.0 Rdbi_1.8.0      lattice_0.17-20
>>>>
>>>> Any ideas?
>>>
>>> Well, first off, since you are apparently using RdbiPgSQL from
>>> bioconductor, you should probably try asking there.
>>>
>>> But in any case, you might try the latest release from
bioconductor:
>>>
>>>
http://www.bioconductor.org/packages/release/bioc/src/contrib/RdbiPgSQL_1.1
>>> 6.0.tar.gz
>>>
>>> or else try the recently released PostgreSQL DBI package on CRAN:
>>> http://cran.stat.ucla.edu/src/contrib/RPostgreSQL_0.1-3.tar.gz
>>>
>>> HTH,
>>>
>>> Joe
>>
>> Thanks Joe. I have updated all of my R packages, along with my
>> Bioconductor packages... and I still get the odd behavior:
>>
>> *** glibc detected *** /usr/local/lib/R/bin/exec/R: realloc(): invalid
old
>> size: 0xb7dad1e0 ***
>> ======= Backtrace: ========>>
/lib/i686/cmov/libc.so.6[0xb7cc3624]
>> /lib/i686/cmov/libc.so.6[0xb7cc71a1]
>> /lib/i686/cmov/libc.so.6(realloc+0x106)[0xb7cc7b46]
>> /usr/local/pgsql/lib/libpq.so.5[0xb7802166]
>> /usr/local/pgsql/lib/libpq.so.5[0xb780239c]
>> /usr/local/pgsql/lib/libpq.so.5[0xb77fbd31]
>> /usr/local/pgsql/lib/libpq.so.5(PQfinish+0x15)[0xb77fbe85]
>>
>>
/usr/local/lib/R/site-library/RdbiPgSQL/libs/RdbiPgSQL.so(PgSQLcloseConnection+0x38)
>> [0xb783bb98]
>>
>> Again, here is my session info:
>>
>> R version 2.8.0 (2008-10-20) i686-pc-linux-gnu
>> locale:
>>
>>
LC_CTYPE=en_US.UTF-8;LC_NUMERIC=C;LC_TIME=en_US.UTF-8;LC_COLLATE=en_US.UTF-8;LC_MONETARY=C;LC_MESSAGES=en_US.UTF-8;LC_PAPER=en_US.UTF-8;LC_NAME=C;LC_ADDRESS=C;LC_TELEPHONE=C;LC_MEASUREMENT=en_US.UTF-8;LC_IDENTIFICATION=C
>>
>> attached base packages:
>> [1] grid      stats     graphics  grDevices utils     datasets  methods
>>  [8] base
>> other attached packages:
>> [1] RdbiPgSQL_1.16.0 Rdbi_1.16.0      lattice_0.17-20
>>
>>
>> Here is the code that appears to be causing the problems:
>>
>> # load data from a PostGreSQL database, and return a dataframe
>> # this will get called whenever the parent function is called
>> # crashes after 3-5 subsequent calls
>> load_sensor_data <- function(query_text)
>> {
>>
>> # conn becomes an object which contains the DB connection:
>> conn <- dbConnect(PgSQL(), host="localhost",
dbname="SJER", user="dylan")
>>
>> ## pre-filter out possible bad data:
>> query <- dbSendQuery(conn, query_text)
>>
>> # fetch data according to query:
>> res <- dbGetResult(query)
>> # convert characters to factors
>> res <- transform(res, probe_id = factor(probe_id))
>> res <- transform(res, pedon_id = factor(pedon_id))
>>
>> # generate a new date-time object in the dataframe
>> res$date <- as.POSIXct(strptime(res$date_time, format="%a %b %e
%H:%M:%S
>> %Y"))
>>
>> # cleanup
>> dbDisconnect(conn)
>>
>> # doesn't completely fix problem crashing
>> gc()
>>
>> # return the resulting dataframe
>> return(res)
>> }
>>
>>
>> Thanks in advance,
>>
>> Dylan
>>
>>
>
>
> --
> Martin Morgan
> Computational Biology / Fred Hutchinson Cancer Research Center
> 1100 Fairview Ave. N.
> PO Box 19024 Seattle, WA 98109
>
> Location: Arnold Building M2 B169
> Phone: (206) 667-2793
>