Re: [R] RPostgreSQL segfault with LEFT JOIN
On Sunday 07 June 2009, Dirk Eddelbuettel wrote:
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 foofrom 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:
e...@ron:~/svn/rpostgresql svn up
svn: OPTIONS of 'https://rpostgresql.googlecode.com/svn/trunk': could not
connect to server (https://rpostgresql.googlecode.com)
e...@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
Thanks for looking into this. Is there any follow-up that I should do? Any
idea on when the package will be updated on CRAN?
Cheers,
Dylan
--
Dylan Beaudette
Soil Resource Laboratory
http://casoilresource.lawr.ucdavis.edu/
University of California at Davis
530.754.7341
__
R-help@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.
Re: [R] RPostgreSQL segfault with LEFT JOIN
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 foofrom 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:
e...@ron:~/svn/rpostgresql svn up
svn: OPTIONS of 'https://rpostgresql.googlecode.com/svn/trunk': could not
connect to server (https://rpostgresql.googlecode.com)
e...@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.
__
R-help@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.
Re: [R] RPostgreSQL segfault with LEFT JOIN
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
__
R-help@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.
[R] RPostgreSQL segfault with LEFT JOIN
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 __ R-help@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.
Re: [R] RPostgreSQL segfault with LEFT JOIN
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@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. __ R-help@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.
Re: [R] RPostgreSQL segfault with LEFT JOIN
On Thursday 04 June 2009, Dirk Eddelbuettel wrote: 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 Thanks Dirk, After some further investigation, I see that the query works fine if I *do not use column aliases* : # segfaults: 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 # works fine: q - SELECT deb_lab_data.* , matrix_wet_color_hue, matrix_wet_color_value, matrix_wet_color_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 Very strange... Dylan | 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.UT |F-8;LC_MONETARY=C;LC_MESSAGES=en_US.UTF-8;LC_PAPER=en_US.UTF-8;LC_NAME=C;L |C_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@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. -- Dylan Beaudette Soil Resource Laboratory http://casoilresource.lawr.ucdavis.edu/ University of California at Davis 530.754.7341 __ R-help@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.
