[R] RPostgreSQL segfault with LEFT JOIN

[Dirk Eddelbuettel]

On 4 June 2009 at 16:17, Dylan Beaudette wrote:
| Hi,
| 
| I recently upgraded to R 2.9.0 on linux x86. After doing so, I switched to the 
| RPostgreSQL package for interfacing with a postgresql database. I am using 
| postgresql  8.3.7.
| 
| A query that works from the postgresql terminal is causing a segfault when 
| executed from R.
| 
| My sessionInfo, the error message, and the R code used to generate the error 
| are listed below.
| 
| I have noticed that a trivial query (SELECT 1 as value) or other queries seem 
| to work fine. It is only when I enable the LEFT JOIN (see below) that I get a 
| segfault. Could this be related to the treatment of null values?

As per some recent messages on the r-sig-db list, I think that the error is
due to a bug in the handling of 'schema.table' queries.  If you just use
'select ... from table' you're fine.

Not sure if this helps you -- someone has to go in and fix the bug.

Dirk

| 
| Any ideas?
| Thanks!
| Dylan
| 
| Here is the code that caused the error
| ----------------------------------------------------------------------------------------------------
| # libs
| library(RPostgreSQL)
| 
| ## query DB
| q <- "
| SELECT deb_lab_data.*
| -- matrix_wet_color_hue as hue, matrix_wet_color_value as value, 
| matrix_wet_color_chroma as chroma
| FROM deb_lab_data
| -- LEFT JOIN horizon USING (pedon_id, hz_number)
| WHERE deb_lab_data.pedon_id ~~ '%SJER%'
| ORDER BY deb_lab_data.pedon_id, deb_lab_data.top ASC "
| 
| # create an PostgreSQL instance and create one connection.
| drv <- dbDriver("PostgreSQL")
| conn <- dbConnect(drv, host="localhost", dbname="XXX", user="XXX")
| query <- dbSendQuery(conn, q)
| x <- fetch(query, n = -1) # extract all rows
| ----------------------------------------------------------------------------------------------------
| 
| Here is the error message in R:
| ----------------------------------------------------------------------------------------------------
| row number 0 is out of range 0..-1
| 
|  *** caught segfault ***
| address (nil), cause 'memory not mapped'
| 
| Traceback:
|  1: .Call("RS_PostgreSQL_exec", conId, statement, PACKAGE 
| = .PostgreSQLPkgName)
|  2: postgresqlExecStatement(conn, statement, ...)
|  3: is(object, Cl)
|  4: is(object, Cl)
|  
| 5: .valueClassTest(standardGeneric("dbSendQuery"), "DBIResult",     "dbSendQuery")
|  6: dbSendQuery(conn, q)
| ----------------------------------------------------------------------------------------------------
| 
| 
| 
| Here are the details on my R install:
| ----------------------------------------------------------------------------------------------------
| R version 2.9.0 (2009-04-17) 
| 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] stats     graphics  grDevices utils     datasets  methods   base     
| 
| other attached packages:
| [1] RPostgreSQL_0.1-4 DBI_0.2-4
| ----------------------------------------------------------------------------------------------------
| 
| 
| 
| -- 
| Dylan Beaudette
| Soil Resource Laboratory
| http://casoilresource.lawr.ucdavis.edu/
| University of California at Davis
| 530.754.7341
| 
| ______________________________________________
| R-help at r-project.org mailing list
| https://stat.ethz.ch/mailman/listinfo/r-help
| PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
| and provide commented, minimal, self-contained, reproducible code.

-- 
Three out of two people have difficulties with fractions.