[BioC] dbWriteTable Failure

[Elliot Joel Bernstein]

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)

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