[BioC] dbWriteTable Failure

[Martin Morgan]

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