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?
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
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.
On 7 June 2009 at 06:40, Neil Tiffin wrote:
| I am adding your note to google code issues
(http://code.google.com/p/rpostgresql/issues/list
| ), issue Number 1. Normally I monitor R-SIG-DB
(https://stat.ethz.ch/mailman/listinfo/r-sig-db
| ).
That list seems most appropriate.
| Hope to look at these in the next couple of weeks.
|
| Thanks, for the suggested changes.
Thanks from my end too.
It does compile, and it passes the existing tests that R CMD check runs so it
looks good so far. It also passes simple
select foo from bar
select foo as fii from bar
where the second form bombed without the patch. So thanks!!
[ Incidentally, I am having issues with googlecode.com which no longer wants
me to connect / update / initialise from home behind NAT and a simple
firewall:
edd at ron:~/svn/rpostgresql> svn up
svn: OPTIONS of 'https://rpostgresql.googlecode.com/svn/trunk': could
not connect to server (https://rpostgresql.googlecode.com)
edd at ron:~/svn/rpostgresql>
Does anybody know what's happening there? I can check out etc fine from
another machine somewhere else. I can connect and authenticate fine to the
https url using a browswer, it is just svn that croaks. Ideas ? Does this
now need rpc or portmap back to me? ]
Dirk
| Neil
|
| On Jun 6, 2009, at 11:18 PM, Joe Conway wrote:
|
| > Dylan Beaudette wrote:
| >> After some further investigation, I see that the query works fine
| >> if I *do not use column aliases* :
| >
| > Looks like *any* query using a column alias will segfault unless the
| > alias exactly matches the column name (in which case why bother).
| > The code starting at line 423 in RS-PostgreSQL.c looks like:
| >
| > 8<-------------------------------
| > if(PQftablecol(my_result,j) !=0) {
| >
| > /* Code to find whether a row can be nullable or not */
| > sprintf(buff,
| > "select attnotnull from pg_attribute
| > where attrelid=%d and attname='%s'",
| > PQftable(my_result,j),(char*)PQfname(my_result,j));
| > res = PQexec (conn, buff );
| >
| > if(strcmp(PQgetvalue(res,0,0),"f")==0) {
| > 8<-------------------------------
| > The crash occurs at line 430 (the strcmp()) because PQgetvalue(res,
| > 0,0) returns NULL.
| >
| > PQfname() will return the column alias, not the actual column name,
| > therefore the PQexec() here returns no results. At the very least,
| > PQresultStatus(res) or perhaps PQntuples(res) should be used
| > immediately after PQexec() to ensure you have a good result before
| > trying to use it in strcmp().
| >
| > In any case, I think the simple fix (untested) is something like:
| >
| > 8<-------------------------------
| > if(PQftablecol(my_result,j) !=0) {
| >
| > /* Code to find whether a row can be nullable or not */
| > sprintf(buff,
| > "select attnotnull from pg_attribute
| > where attrelid=%d and attnum=%d",
| > PQftable(my_result,j),PQftablecol(my_result,j));
| > 8<-------------------------------
| > i.e. use the table column number and pg_attribute.attnum field.
| >
| > This is beyond what is appropriate for r-help, so I suggest any
| > further discussion go off-list (or is there somewhere more
| > appropriate, e.g. r-devel?)
| >
| > HTH,
| >
| > Joe
| >
|
--
Three out of two people have difficulties with fractions.