[BioC] dbWriteTable Failure

[Hervé Pagès]

On 01/14/2011 06:25 AM, Elliot Joel Bernstein wrote:
> Martin -
>
> Thanks for your reply. Is RdbiPgSQL no longer maintained at all? I have tried RPostgreSQL, but it has an even worse problem. In that package, dbWriteTable is implemented by calling write.table on the data frame to write it to a temporary file, and then load it into the database, which means that the formatting options set in R affect the result. Specifically, write.table by default formats some numbers in scientific notation, which causes the database load to fail.
>
> After looking into the problem with RdbiPgSQL a little more, it seems that it's caused by dbDisconnect calling the same function that is registered as the finalizer. I think it could be easily solved by using an indicator variable to keep track of whether PQfinish has been called. (I can also just call rm instead of dbDisconnect, but it's a little disconcerting that the package contains a function that causes memory errors when used as intended.)

Right. FWIW the problem seems to have very little to do with
dbWriteTable():

 > library(RdbiPgSQL)

 > gctorture()

 > conn <- dbConnect(PgSQL(), dbname='testing', user='testing', 
password='testing')

 > conn  # valid session (status = 0)
status = 0
database.name = testing
user.name = testing
password = testing
port = 5432
socket = 3
client.encoding = 6
backend.pid = 1403

 > dbListTables(conn)
                   relname
1                     dat
2            sql_features
3 sql_implementation_info
4           sql_languages
5            sql_packages
6               sql_parts
7              sql_sizing
8     sql_sizing_profiles
9                    toto

 > dbDisconnect(conn)

 > conn  # expired session (status = 1)
status = 1
database.name = `�9

options = @�9

user.name = conn
password = ��9

tty =  �9

socket = -1
client.encoding = -1
backend.pid = 0

 > conn <- dbConnect(PgSQL(), dbname='testing', user='testing', 
password='testing')

 > conn  # still an expired session! (status = 1)
status = 1
database.name = 0

user.name =  ��

password = ������������

tty = ໟ

socket = -1
client.encoding = -1
backend.pid = 0

 > dbListTables(conn)
Error in dbSendQuery.PgSQL.conn(conn, ...) : No database connection

Looks like the session management code is seriously broken :-(

H.

>
> - Elliot
>
>
> On Tue, Jan 11, 2011 at 08:12:21PM -0800, Martin Morgan wrote:
>> On 01/11/2011 11:37 AM, Elliot Joel Bernstein wrote:
>>> I am having a problem using the dbWriteTable function in package RdbiPgSQL to insert data into a PostgreSQL table. Specifically, the following code sometimes works, but sometimes fails with an error message indicating that there is "no database connection":
>>>
>>>
>>>
>>> =====================================================================
>>> dbtest.R
>>> =====================================================================
>>>
>>> require(RdbiPgSQL)
>>
>> Hi Elliot -- not so much an answer, but unless RdbiPgSQL is central you
>> might try the CRAN package RPostgreSQL, which is actively maintained.
>>
>> Martin
>>
>>>
>>> test<- function(conn, n) {
>>>
>>>    dbSendQuery(conn, "create temp table temp (x int, y int)")
>>>
>>>    n<- 10000
>>>    dat<- data.frame(x=sample(1000,n,replace=T), y=sample(1000,n,replace=T))
>>>
>>>    for (i in 1:dim(dat)[1]) {
>>>
>>>      err<- try(dbSendQuery(conn, sprintf("insert into temp values (%d,%d)", dat$x[i], dat$y[i])))
>>>      if (inherits(err, "try-error")) {
>>>        print(conn)
>>>        break
>>>      }
>>>
>>>    }
>>>
>>>    print(dbGetQuery(conn, "select count(1) from temp"))
>>>
>>> }
>>>
>>> conn<- dbConnect(PgSQL(), dbname="gf", host="gf-host")
>>> test(conn, 10000)
>>> dbDisconnect(conn)
>>>
>>> =====================================================================
>>>
>>>
>>>
>>> The following is sample output from multiple runs of this script:
>>>
>>>
>>>
>>> =====================================================================
>>> Sample output
>>> =====================================================================
>>>
>>> R version 2.12.0 (2010-10-15)
>>> Copyright (C) 2010 The R Foundation for Statistical Computing
>>> ISBN 3-900051-07-0
>>> Platform: x86_64-redhat-linux-gnu (64-bit)
>>>
>>> R is free software and comes with ABSOLUTELY NO WARRANTY.
>>> You are welcome to redistribute it under certain conditions.
>>> Type 'license()' or 'licence()' for distribution details.
>>>
>>>    Natural language support but running in an English locale
>>>
>>> R is a collaborative project with many contributors.
>>> Type 'contributors()' for more information and
>>> 'citation()' on how to cite R or R packages in publications.
>>>
>>> Type 'demo()' for some demos, 'help()' for on-line help, or
>>> 'help.start()' for an HTML browser interface to help.
>>> Type 'q()' to quit R.
>>>
>>>> source("dbtest.R")
>>> Loading required package: RdbiPgSQL
>>> Loading required package: Rdbi
>>>    count
>>> 1 10000
>>>> source("dbtest.R")
>>> Error in dbSendQuery.PgSQL.conn(conn, sprintf("insert into temp values (%d,%d)",  :
>>>    No database connection
>>> status = 1
>>> database.name =  a
>>> host.name = p��
>>> options = 8}G
>>> password = P�
>>> port = �
>>> tty = ��
>>> socket = -1
>>> client.encoding = -1
>>> backend.pid = 0
>>> Error in dbSendQuery.PgSQL.conn(conn, ...) : No database connection
>>>
>>> =====================================================================
>>>
>>>
>>>
>>> Here is the result of a traceback:
>>>
>>>
>>>
>>> =====================================================================
>>> traceback
>>> =====================================================================
>>>
>>>> traceback()
>>> 11: .Call("PgSQLsendQuery", conn, query, PACKAGE = "RdbiPgSQL")
>>> 10: dbSendQuery.PgSQL.conn(conn, ...) at dbtest.R#21
>>> 9: dbSendQuery(conn, ...) at dbtest.R#21
>>> 8: dbGetResult(dbSendQuery(conn, ...)) at dbtest.R#21
>>> 7: dbGetQuery.PgSQL.conn(conn, "select count(1) from temp") at dbtest.R#21
>>> 6: dbGetQuery(conn, "select count(1) from temp") at dbtest.R#21
>>> 5: print(dbGetQuery(conn, "select count(1) from temp")) at dbtest.R#21
>>> 4: test(conn, 10000)
>>> 3: eval.with.vis(expr, envir, enclos)
>>> 2: eval.with.vis(ei, envir)
>>> 1: source("dbtest.R")
>>>
>>> =====================================================================
>>>
>>>
>>>
>>> And here is my sessionInfo:
>>>
>>>
>>>
>>> =====================================================================
>>> sessionInfo
>>> =====================================================================
>>>
>>>> sessionInfo()
>>> R version 2.12.0 (2010-10-15)
>>> Platform: x86_64-redhat-linux-gnu (64-bit)
>>>
>>> locale:
>>>   [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C
>>>   [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8
>>>   [5] LC_MONETARY=C              LC_MESSAGES=en_US.UTF-8
>>>   [7] LC_PAPER=en_US.UTF-8       LC_NAME=C
>>>   [9] LC_ADDRESS=C               LC_TELEPHONE=C
>>> [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C
>>>
>>> attached base packages:
>>> [1] stats     graphics  grDevices utils     datasets  methods   base
>>>
>>> other attached packages:
>>> [1] RdbiPgSQL_1.24.0 Rdbi_1.24.0
>>>
>>> =====================================================================
>>>
>>>
>>>
>>> I am using version 8.4.4 of the PostgreSQL-devel libraries, and the server is running version 8.4.1. The problem does not always occur on the second run; I am sometimes able to run the script multiple times before it eventually fails. Also, it does not always fail at the same row of the dataframe, nor is there anything obviously wrong with the data values on the row where it fails. I would appreciate any help in diagnosing the problem.
>>>
>>> Thank you very much.
>>>
>>> - Elliot
>>>
>>> _______________________________________________
>>> Bioconductor mailing list
>>> Bioconductor at r-project.org
>>> https://stat.ethz.ch/mailman/listinfo/bioconductor
>>> Search the archives: http://news.gmane.org/gmane.science.biology.informatics.conductor
>>
>>
>> --
>> Computational Biology
>> Fred Hutchinson Cancer Research Center
>> 1100 Fairview Ave. N. PO Box 19024 Seattle, WA 98109
>>
>> Location: M1-B861
>> Telephone: 206 667-2793
>
> _______________________________________________
> Bioconductor mailing list
> Bioconductor at r-project.org
> https://stat.ethz.ch/mailman/listinfo/bioconductor
> Search the archives: http://news.gmane.org/gmane.science.biology.informatics.conductor


-- 
Hervé Pagès

Program in Computational Biology
Division of Public Health Sciences
Fred Hutchinson Cancer Research Center
1100 Fairview Ave. N, M2-B876
P.O. Box 19024
Seattle, WA 98109-1024

E-mail: hpages at fhcrc.org
Phone:  (206) 667-5791
Fax:    (206) 667-1319