tomoakin / rpostgresql Goto Github PK

If there is no schema given in the table name, dbExistsTable('tabelName') 
returns true if there is a table with name 'tabelName' in any Schema.

This is a problem as the usage of Schemas and rpostgresql is a bit tricky, as 
postgresqlQuoteId() puts the quotes around the the whole table name, so that 
e.g. dbWriteTable( con, "mySchema.myTable", ...) creates a table 
'mySchema.myTable' in the current schema and not the table 'myTable' in the 
schema 'myTable'. If you use "SET search_path TO mySchema" then  dbWriteTable( 
con, "myTable", ...) will fail if ther is a table named '
myTable' in any other schema.

I propose to use, so that it is possible to create tables in a schema with 
dbWriteTable():


setMethod("dbExistsTable",
          signature(conn="PostgreSQLConnection", name="character"),
          def = function(conn, name, ...){
              ## Edd 09 Oct 2009: Fusion of patches by Joe Conway and Prasenjit Kapat
              names <- strsplit(name, ".", fixed=TRUE)[[1]]
              if (length(names) == 2) {     # format was "public.sometable"
                  res <- dbGetQuery(conn,
                                    paste("select schemaname,tablename from pg_tables where ",
                                          "schemaname !='information_schema' ",
                                          "and schemaname !='pg_catalog' and schemaname='",
                                          names[1], "' and tablename='", postgresqlEscapeStrings(conn, names[2]), "'", sep=""))
              } else {
                  cShemas <- dbGetQuery( conn, "SHOW search_path;")
                  ccShemas <- gsub(" ", "", strsplit(cShemas[[1]], ",", fixed=TRUE)[[1]])
                  res <- dbGetQuery(conn,
                                    paste("select tablename from pg_tables where ",
                                          "schemaname !='information_schema' and schemaname !='pg_catalog' ",
                                          "and schemaname='", ccShemas[1], "' ",
                                          "and tablename='", postgresqlEscapeStrings(conn, names[1]), "'", sep=""))
              }
              return(as.logical(dim(res)[1]))
          },
          valueClass = "logical"
          )

Create issues for any problems found.

Very table in greenplum must be creaed with as distributed by(col1,...) cluase, 
can you add a parameter to the current dbWriteTable function to support 
Greenplum, such as

dbWriteTable(...., disCol=c("col1","col2"));

Then the table will be created as 

Create table t(...) distribued by(col1,col2);

What steps will reproduce the problem?
> # Test file attached.
> test <- read.csv("~/Desktop/test.csv")
> test
         Company_Name Stock_Price
1              Big Co       23.21
2            Old Corp       33.16
3 Slasher Inc. \\DE\\       55.55
4            Last One       13.13
> dbWriteTable(pg,"test",test)
[1] TRUE
> dbGetQuery(pg,"SELECT * FROM test")
data frame with 0 columns and 0 rows
> # A regular expression gsub() that "double-doubles" backslashes seems to fix 
this
> # Note that it shows as a single backslash on the PostgreSQL side.
> test <- 
as.data.frame(sapply(test,gsub,pattern="\\\\",replacement="\\\\\\\\",perl=TRUE))
> dbWriteTable(pg,"test",test,overwrite=TRUE)
[1] TRUE
> dbGetQuery(pg,"SELECT * FROM test")
  row_names        Company_Name Stock_Price
1         1              Big Co       23.21
2         2            Old Corp       33.16
3         3 Slasher Inc. \\DE\\       55.55
4         4            Last One       13.13

What is the expected output? What do you see instead?
Expected output above after gsub() fix. Otherwise table is empty.

What version of the product are you using? On what operating system?
> R.version
               _                            
platform       x86_64-apple-darwin9.8.0     
arch           x86_64                       
os             darwin9.8.0                  
system         x86_64, darwin9.8.0          
status                                      
major          2                            
minor          13.0                         
year           2011                         
month          04                           
day            13                           
svn rev        55427                        
language       R                            
version.string R version 2.13.0 (2011-04-13)

Please provide any additional information below.

On 5 January 2010 at 12:49, Nils B. Weidmann wrote:
Neil, Dirk,

thanks for your help! I finally managed to compile and install the 
package. Two issues:

* Setting the location of the PG header files as shown on the Wiki page 
does not seem to have any effect. Also, changing the search lists in 
configure and configure.in still gives the same error. Due to my lack of 
knowledge about (auto)configure, I manually override the paths for the 
libs and includes in configure by adding

PG_INCDIR=/Library/PostgresPlus/8.4SS/include
PG_LIBDIR=/Library/PostgresPlus/8.4SS/lib

at around line 2675 (after the directories are tested). These are the 
paths for the EnterprizeDB Postgres Standard Server (8.4) I am using. 
Compilation now starts (but see below). Still, this suggests to me that 
there is a problem in the configure script, which causes the PG_*DIR 
variables not to be set at all. This would also explain why the CRAN 
version does not compile (with the Fink PG libs installed).

* For OS X Users, R needs to be called with the arch flag correctly set, 
as in

 > sudo R --arch=i386 CMD INSTALL RPostgreSQL

The default R call compiles a 64-bit package, but installs it under 
32-bit R, which results in an error when the package is loaded. Maybe 
this is worth adding to the Wiki.

The problem occurs when library(RPostgreSQL) is issued on R. This issue has 
previously appeared on R mailing list without any robust solution. The error 
message issued by R:

Loading required package: RPostgreSQL
Loading required package: DBI
Error in inDL(x, as.logical(local), as.logical(now), ...) : 
  unable to load shared library 'C:/PROGRA~1/R/R-210~1.1/library/RPostgreSQL/libs/RPostgreSQL.dll':
  LoadLibrary failure:  Unable to locate the specified module.
Error: package 'RPostgreSQL' could not be loaded

At the same time an error box appears saying that a given DLL does not exists 
which makes RPostgreSQL loading to fail. The message appears for any of the 
following DLLs (that actually exist on 
X:/PostgreSQL_installation_directory/bin):

libpq.dll
ssleay32.dll
libeay32.dll
libintl-8.dll
libiconv-2.dll
krb5_32.dll
comerr32.dll
k5sprt32.dll
msvcr71.dll
gssapi32.dll

To "solve" this problem the actual DLLs from the PostgreSQL installation 
directory must be copied into the X:/WINDOWS/System32 shared libraries folder 
in order to make the package operational.
Is there a way to solve this from the package internals without having to copy 
the DLLs? Am I missing something here?

I'm using R-2.11.1 on WinXP/7.

I have a database, which uses Postgis for spatial data.
The data is kept according to OpenGIS standard.

When I load a table containing spatial data using RPostgreSQL,
then I am getting

    Warning message:
    In postgresqlExecStatement(conn, statement, ...) :
      RS-DBI driver warning: (unrecognized PostgreSQL field type 34799 in column 0)

Would it be possible to enhance RPostgreSQL to support
Postgis types?

I believe spatial data (the table columns, which contain
spatial data) should be converted to appropriate R spatial
types, see http://cran.r-project.org/web/packages/sp/index.html

Then such simple code would be possible:

require('RPostgreSQL')
require('sp')

cn <- dbConnect(PostgreSQL(), dbname='gis')

d <- dbGetQuery(cn, "select location from table')

pdf('map.pdf')
plot(data)
dev.off()


Or other project should be started in order to
support spatial types of data fetched from
Postgresql databases enhanced with Postgis?

Much of *my* use of RPostgreSQL is likely to follow the following pattern:

(1) Have a relatively small R data set for which I want to obtain matching data 
from PostgreSQL
(2) Send R data to PostgreSQL. Ask latter to match up and compile related data.
(3) Pull data back into R for further analysis.

It would be aesthetically pleasing if this left little trace in PostgreSQL. One 
option is to DROP TABLE at the end. An alternative would be to use CREATE TEMP 
TABLE. To do this with dbWriteTable, I changed two lines in 
PostgreSQLSupport.R. First, this one:

  postgresqlWriteTable <- function(con, name, value, field.types, row.names = TRUE,
    overwrite = FALSE, append = FALSE, temp.table=FALSE, ..., allow.keywords = FALSE) {

then, this one:

  sql0 <- if (temp.table) "create temp table " else "create table "
  sql1 <- paste(sql0, postgresqlQuoteId(name), "\n(\n\t", sep="")

I am not sure if this is a very useful (or desirable) feature (for example, can 
one eke performance improvements out of this?), but it seems to work, so I 
thought I'd share it.

What steps will reproduce the problem?
1.query <- "select * from ratings"
2.drv <- dbDriver("PostgreSQL")
3.rating.ff <- read.dbi.ffdf(query = query, 
                           dbConnect.args=list(drv,user="postgres",
                                               password="mypassword"),
                           verbose = TRUE)

What is the expected output? What do you see instead?
There is no Output

What version of the product are you using? On what operating system?
R 2.15.2 in Ubuntu-Linux

Please provide any additional information below.

The error I get:

`Error en postgresqlExecStatement(conn, statement, ...) : 
     RS-DBI driver: (could not Retrieve the result : 
     out of memory for query result)`

If I select 10 million rows from the table ratings I don't have a problem, but 
with more I have a problem.

What steps will reproduce the problem?
1. Run check_with_vars.sh

What is the expected output? What do you see instead?

Expected that all test run on MAC OS X.  They do not.

All test fail like the following:
==== Running RPostgreSQL/tests/connectWithNull.R
Loading required package: RPostgreSQL
Loading required package: DBI
Error in dyn.load(file, DLLpath = DLLpath, ...) : 
  unable to load shared library '/Library/Frameworks/R.framework/Resources/library/RPostgreSQL/libs/x86_64/RPostgreSQL.so':
  dlopen(/Library/Frameworks/R.framework/Resources/library/RPostgreSQL/libs/x86_64/RPostgreSQL.so, 6): no suitable image found.  Did find:
    /Library/Frameworks/R.framework/Resources/library/RPostgreSQL/libs/x86_64/RPostgreSQL.so: mach-o, but wrong architecture
Error: require(RPostgreSQL) is not TRUE
Execution halted

To make the tests run change the line from
        R --slave < $f

to
        R --arch=i386 --slave < $f

Investigating the right way to build this into the script.

What steps will reproduce the problem?
1. In R type ?PostgreSQL to look at the documentation on the PostgreSQL object, 
for example.
2. This page looks OK.  Now try the links at the bottom to look at the 
PostgreSQL versions of the DBI methods - for example, click on dbConnect
3. Note that you have linked to the dbConnect page describing the DBI base 
method, not the PostgreSQL version of this method.
4. If instead you click on the index link at the bottom of the PostgreSQL page, 
then you get to the correct table of documentation for RPostgreSQL.  For 
example, click on dbConnect-methods.  This brings you to the correct page 
describing dbConnect() for PostgreSQl.  However, if you click on the links on 
this page (e.g. click on dbCommit) you are back at the DBI base class version 
of dbCommit().

What is the expected output? What do you see instead?
I would have expected to see info on the RPostgreSQL versions of the DBI 
methods when I click on the links on the documentation pages for RPostgreSQL 
methods in the package.

What version of the product are you using? On what operating system?
Using Eclipse 4.2,1 on Windows 7 64-bit.  Using R 2.15.2.  RPostgreSQL 0.4 
compiled on R 2.15.3.  

Please provide any additional information below.

I was looking at analyzing some log data using RPostgreSQL, but noticed it
did not know how to handle an INET column in my log table. Can you consider
supporting those as well? It should be a fairly simple matter of treating
INETOID and MACADDROID as standard string types.

I have managed to build 0.1.7 of RPostgreSQL for WindowsX64, but the faild to 
build the latest trunk of
it, I have attached the building guide for the old version, and the error 
message when building trunk
is :

$ R CMD build --binary RPostgreSQL/
--binary
* checking for file 'RPostgreSQL/DESCRIPTION' ... OK
* preparing 'RPostgreSQL':
* checking DESCRIPTION meta-information ... ERROR


'R''R'DESCRIPTION.


dell@dell-PC /d/Amber/DevProjects/R/packagesrc/RPostgreSQL/trunk-export
$

Some data type mapping thoughts:
logical -> boolean
Data -> date

Also, sometimes text doesn't seem to work well (e.g., storing firm identifiers 
like CUSIPs as text seems to make indexing difficult). The attached has some 
ideas here.

Note that the attached is a kluge I'm using to deal with the fact that 
dbWriteTable doesn't handle backslashes well. But performance of the 
pipe-to-psql approach seems pretty good; do you think could some aspects of 
this be replicated in the library/package?

 load("~/Desktop/test.data.Rdata")
> source('~/Desktop/dbWriteTable.fast.R')
> library(RPostgreSQL) # using r181
> drv <- dbDriver("PostgreSQL")
> pg <- dbConnect(drv, dbname = "crsp", host="localhost")
> system.time(rs <- dbWriteTable(pg,"testdata",test.data, overwrite=TRUE, 
row.names=FALSE))
Error in postgresqlgetResult(new.con) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  invalid input syntax for type double precision: "Chairman and Chief Executive Officer"
CONTEXT:  COPY testdata, line 707, column Bonus: "Chairman and Chief Executive 
Officer"
)
Timing stopped at: 0.047 0.002 0.075 
> # Problem is backslashes (show here as "\\")
> test.data[707,]
         fyend gvkey   Executive_ID fyear Company_ID       Executive               Company  Bonus
707 2000-06-30  <NA> 3061.7.N.23191  2000   3061.7.N KIGHT, PETER J. CHECKFREE 
CORP \\GA\\ 551850
                                   Title salary
707 Chairman and Chief Executive Officer 420000
> # Note that following produces "CHECKFREE CORP \GA\" on PostgreSQL side, as 
expected
> system.time(rs <- dbWriteTable.fast(pg,"testdata",test.data, overwrite=TRUE, 
row.names=FALSE))
   user  system elapsed 
  0.026   0.004   0.061 
> dbGetQuery(pg,"SELECT * FROM testdata LIMIT 2")
       fyend  gvkey   executive_id fyear company_id           executive       company bonus
1 2000-06-30 010777 7265.7.N.72074  2000   7265.7.N PELLIGRINO, PAUL A. TWIN 
DISC INC 24400
2 2000-06-30 009023     3309.3.N.6  2000   3309.3.N        OSTEN, JOZEF       
DVI INC 40000
                       title salary
1 Vice President Engineering 134846
2             Vice President 279796
> dbDisconnect(pg)
[1] TRUE
> # Now try a remote connection
> pg <- dbConnect(drv, db="crsp", host=my.office.computer)
> system.time(rs <- dbWriteTable(pg,"testdata",test.data, overwrite=TRUE, 
row.names=FALSE))
Error in postgresqlgetResult(new.con) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  invalid input syntax for type double precision: "Chairman and Chief Executive Officer"
CONTEXT:  COPY testdata, line 707, column Bonus: "Chairman and Chief Executive 
Officer"
)
Timing stopped at: 0.059 0.004 0.626 
> system.time(rs <- dbWriteTable.fast(pg,"testdata",test.data,  overwrite=TRUE, 
row.names=FALSE))
   user  system elapsed 
  0.028   0.007   0.523 
> dbDisconnect(pg)
[1] TRUE
> # Now try a clean data set
> pg <- dbConnect(drv, dbname = "crsp", host="localhost")
> test.data.2 <- rnorm(1e6)
> dim(test.data.2)[1] <- 100000
> dim(test.data.2) <- c(100000,10)
> test.data.2 <- as.data.frame(test.data.2)
> pg <- dbConnect(drv, db="crsp", host="localhost")
> system.time(rs <- dbWriteTable(pg,"testdata2",test.data.2, overwrite=TRUE, 
row.names=FALSE))
   user  system elapsed 
  1.996   0.110   3.509 
> system.time(rs <- dbWriteTable.fast(pg,"testdata2",test.data.2, 
overwrite=TRUE, row.names=FALSE))
   user  system elapsed 
  2.080   0.072   2.245 
> 

What steps will reproduce the problem?
1. Load the following time series as test into your postgresql database
http://dl.free.fr/fcmgS9phU
sorry, this is a big file, but it needs to be big to find the bug.

2.
Once you connected to the database to which you loaded the data, make two tests 
in R:
(1) 
datebeg <-  "2012-02-01 00:00:00"    
dateend <-  "2012-03-30 00:00:00"

test_data <- dbGetQuery(con,paste("Select date,data from ecoqu.test_data where 
date >= '",datebeg,"' and date < '",dateend,"' order by date",sep=""))

--> everything should work fine

(2)      
datebeg <-  "2012-02-01 00:00:00"    
dateend <-  "2012-08-30 00:00:00"

test_data <- dbGetQuery(con,paste("Select date,data from ecoqu.test_data where 
date >= '",datebeg,"' and date < '",dateend,"' order by date",sep=""))

--> here you only get the date and not the time for the data column

3.

What is the expected output? What do you see instead?

I expect to see the datetimes (which are timestamp without timezone in 
Postgresql) as: '2012-01-01 00:00:00' 
which I get for test (1), but for test (2) I get '2012-01-01' instead and I 
loose the time information, which I really need.


What version of the product are you using? On what operating system?
postgresql 8.3, R 2.14.1 on windows xp


Please provide any additional information below.

I am trying to connect to a Postgresql 9.1.5 database. Usually the syntax below 
works, but for some reason I am getting a strange message now. I was wondering 
if this was a known issue. Thanks for your help. 

What steps will reproduce the problem?
1.drv = dbDriver('PostgreSQL')
2.con = dbConnect(drv, user = "user", password = "password", dbname = "dbname", 
host = "localhost", port = 5432)
3. con

Error in function (classes, fdef, mtable)  : 
  unable to find an inherited method for function "show", for signature "PostgreSQLConnection"


What is the expected output? What do you see instead?
Normally I would type "con" and it would tell me the drivername and reference a 
connection to the database.


What version of the product are you using? On what operating system?
ubuntu 11.10 64 bit, R 2.15.1, Postgresql 9.1.5, RPostgreSQL version--latest on 
CRAN.


Please provide any additional information below.

What steps will reproduce the problem?
1. in pgsql: create schema foo;
2. dbWriteTable(con,"foo.tmp",data)

What is the expected output? What do you see instead?

I expect a table tmp in schema foo, instead I get the table "foo.tmp" in schema 
public.

What version of the product are you using? On what operating system?
R-2.12/Linux

Please provide any additional information below.

I think this would work (although I did not test it)...

postgresqlQuoteId <- function(identifier){
  ret <- paste('"', gsub('\\.','"."',gsub('"','""',identifier)), '"', sep="")
  ret
}

What steps will reproduce the problem?

1. PostgreSQL Server 9.1 on Windows Server 2003 R2, datestyle = 'German'
2. R version 2.15.0, LC_TIME "German_Germany.1252"
3. > rs <- dbSendQuery(con,"select anytimefield from anytable")
4. > fetch(rs,-1)
   Fehler in as.POSIXlt.character(x, tz, ...) : 
   character string is not in a standard unambiguous format

Queries without temporal fields are aptly transmitted, so there seems to be an 
isolated problem with time conversion. Also, previous conversion of the 
timefield to varchar within the query works fine; e.g. "select 
anytimefield::varchar from anytable" returns a resultset. I would, however, 
rather not use this workaround, as my tables contain lot of date and timestamp 
fields.

Any suggestions on how to coax R into correctly converting dates?

Thank you in advance for helpful hints.

Sandor

What steps will reproduce the problem?
1. create a postgresql database and user that can only be accessed with local 
unix domain connection.
2. confirm that you can access with psql the_database
3. try to connect from RPostgreSQL but any trial fails.

What is the expected output? What do you see instead?

What version of the product are you using? On what operating system?
postgresql84 on centos 5.5 installed with yum
R-2.11.1
RPostgreSQL_0.1-6.tar.gz from CRAN
(the last patch is against latest svn code that doesn't seem to differ)

Please provide any additional information below.

PQsetdbLogin()
should accept NULL parameter, as well as empty string,
which is treated as default value.

PQhost()
returns NULL for unix domain connection and
the return value requires check before copying.
presumably ok to use empty string "" when the return value is NULL.

references:
http://www.postgresql.org/docs/8.3/static/libpq-connect.html
http://archives.postgresql.org/pgsql-interfaces/2006-04/msg00026.php

Suggested change:

$ diff -u  rpostgresql-read-only/RPostgreSQL/src/RS-PostgreSQL.c 
RPostgreSQL/src/RS-PostgreSQL.c 
--- rpostgresql-read-only/RPostgreSQL/src/RS-PostgreSQL.c       2010-09-09 
16:22:51.000000000 +0900
+++ RPostgreSQL/src/RS-PostgreSQL.c     2010-09-09 16:16:04.000000000 +0900
@@ -1,7 +1,7 @@
 /*
  *    RS-PostgreSQL.c
  *
- * Last Modified: $Date: 2009-10-14 09:16:35 +0900 (Wed, 14 Oct 2009) $
+ * Last Modified: $Date: 2009-10-13 19:16:35 -0500 (Tue, 13 Oct 2009) $
  *
  * This package was developed as a part of Summer of Code program organized by Google.
  * Thanks to David A. James & Saikat DebRoy, the authors of RMySQL package.
@@ -199,27 +199,7 @@
     if (!IS_EMPTY(CHR_EL(con_params, 6))) {
         options = (char *) CHR_EL(con_params, 6);
     }
-    if (user == NULL) {
-        user = "";
-    }
-    if (password == NULL) {
-        password = "";
-    }
-    if (host == NULL) {
-        host = "localhost";
-    }
-    if (port == NULL) {
-        port = "";
-    }
-    if (options == NULL) {
-        options = "";
-    }
-    if (tty == NULL) {
-        tty = "";
-    }
-    if (dbname == NULL) {
-        dbname = "template1";
-    }
+
     my_connection = PQsetdbLogin(host, port, options, tty, dbname, user, password);

     if (PQstatus(my_connection) != CONNECTION_OK) {
@@ -233,7 +213,14 @@
     /* save actual connection parameters */
     conParams->user = RS_DBI_copyString(PQuser(my_connection));
     conParams->password = RS_DBI_copyString(PQpass(my_connection));
-    conParams->host = RS_DBI_copyString(PQhost(my_connection));
+    {
+      const char *tmphost = PQhost(my_connection);
+      if(tmphost){
+        conParams->host = RS_DBI_copyString(tmphost);
+      }else{
+        conParams->host = RS_DBI_copyString("");
+      }
+    }
     conParams->dbname = RS_DBI_copyString(PQdb(my_connection));
     conParams->port = RS_DBI_copyString(PQport(my_connection));
     conParams->tty = RS_DBI_copyString(PQtty(my_connection));

  I build the binary package file of RPostgreSQL 0.1.7 for Windows 2003 Server R2 64 bit SP2, the software environments are as following:
         R 2.12.1 for Win64
         RTools212 for Win64
         DBI 0.2.5
         RPostgreSQL 0.1.7
         Postgresql related binaries shipped with postgresql-9.0.2-1-windows_x64.exe from EnterpriseDB

The package can be loaded, and driver can be created, but the dbConnect 
function causes the whole RGui crashes,

driver <- dbDriver("PostgreSQL")
con <- dbConnect(driver, dbname="demo", host="192.168.8.1", user="postgres", 
password="postgres", port=5432)
I have attached the binary package file for you to test.

See emails below.

==========================================================

Paul Gilbert wrote:
What I consider to be not ok is (from you example)

   res <- dbSendQuery(con, "create table Foo1 (f1 int)")
...
[1] "Foo1 Table does not exist.\n"

Right -- as I said in my response to Neil, this case *should* result in
"foo1 Table exists.\n"

Note that the Foo1 was case-folded to foo1

So the user has to know that Postgres is going to translate the name to
foo1 and also has to know that when they ask about the existence of Foo1
Postgres is not going to translate that to foo1 like it did when the
table was created.

I think if create without quotes does not distinguish case, then
checking about existence with quotes should not distinguish case.

And in my original response I show how it ought to be corrected within
RPostgreSQL:


-- user passed "Xeta" *with* the quotes
contrib_regression=# select tablename from pg_tables where
   schemaname !='information_schema' and schemaname !='pg_catalog'
   and tablename='Xeta';
tablename
-----------
Xeta
(1 row)

-- user passed Xeta *without* quotes
contrib_regression=# create table Xeta (f1 int);
CREATE TABLE
contrib_regression=# select tablename from pg_tables where
   schemaname !='information_schema' and schemaname !='pg_catalog'
   and tablename=lower('Xeta');
tablename
-----------
xeta
(1 row)

Joe
==========================================================
What I consider to be not ok is (from you example)

   res <- dbSendQuery(con, "create table Foo1 (f1 int)")
...
[1] "Foo1 Table does not exist.\n"

So the user has to know that Postgres is going to translate the name to
foo1 and also has to know that when they ask about the existence of Foo1
Postgres is not going to translate that to foo1 like it did when the
table was created.

I think if create without quotes does not distinguish case, then
checking about existence with quotes should not distinguish case.

Paul



==========================================================
Neil Tiffin wrote:
I am not sure I understand the problem.

the following R code

   res <- dbSendQuery(con, "create table Foo1 (f1 int)")
   res <- dbSendQuery(con, "create table \"Foo2\" (f1 int)")

   if (dbExistsTable(con, "Foo1")) {
       print("Foo1 Table exists.\n")
   }
   else {
       print("Foo1 Table does not exist.\n")
   }

   if (dbExistsTable(con, "foo1")) {
       print("foo1 Table exists.\n")
   }
   else {
       print("foo1 Table does not exist.\n")
   }

   if (dbExistsTable(con, "Foo2")) {
       print("Foo2 Table exists.\n")
   }
   else {
       print("Foo2 Table does not exist.\n")
   }

   if (dbExistsTable(con, "foo2")) {
       print("foo2 Table exists.\n")
   }
   else {
       print("foo2 Table does not exist.\n")
   }

results in

[1] "Foo1 Table does not exist.\n"
[1] "foo1 Table exists.\n"

both of these ought to result in "foo1 Table exists.\n"

[1] "Foo2 Table exists.\n"
[1] "foo2 Table does not exist.\n"

both of these ought to result in "foo2 Table does not exist.\n"

This seems like it is ok.  What am I missing?

And there should be a third case:

    if (dbExistsTable(con, "\"Foo2\"")) {
        print("\"Foo2\" Table exists.\n")
    }
    else {
        print("\"Foo2\" Table does not exist.\n")
    }

    if (dbExistsTable(con, "\"foo2\"")) {
        print("\"foo2\" Table exists.\n")
    }
    else {
        print("\"foo2\" Table does not exist.\n")
    }

and it should return:

[1] "\"Foo2\" Table exists.\n"
[1] "\"foo2\" Table does not exist.\n"

Joe

==========================================================
Paul Gilbert wrote:

Dirk suggested you guys may have some ideas.  I have a PostgreSQL
problem with capitalized table names, so in R I get bad results when
checking is a table exists. Below are details from a message I sent
Dirk. If you have any suggestions I would appreciate hearing.

but it is double quotes (“) that Postgres treats as an indication that
the name should be kept with the capitalization as specified, not the
single quotes (‘) you used.  The double quotes (“) is very non-standard

Actually the double quotes for identifiers (table and other database
object names) *is* in accordance with the SQL standard. Or rather the
ability to double quote identifiers is standard.

The only non-standard aspect of Postgres behavior is that it
automatically folds non-quoted identifiers to lowercase rather than
uppercase as the SQL standard would require.

So, if you specific a table name like:
 create table Foo (f1 int);
it is exactly the same as:
 create table FOO (f1 int);
and
 create table fOO (f1 int);
and
 create table foo (f1 int);
In all of these cases the actual table created will be named
 foo
i.e. all in lower case.
------------------------

On the other hand, if you specific a table name like:
 create table "Foo" (f1 int);
the actual table created will be named
 Foo
i.e. exactly as you specified.
------------------------


General guidance is that you should either *always* quote your
identifiers, or *never* quote your identifiers.
------------------------


Now there is a complication here as we have RPostgreSQL in between the
user and PostgreSQL. I don't have time today to look closely at how
RPostgreSQL goes about its business, but what it ought to do is:

1) if the identifier provided by the user was quoted, search the
information schema for exactly that identifier but without the quotes

2) if the identifier provided by the user was not quoted, search the
information schema for lower(identifier)
------------------------


For example:

-- user passed "Xeta" *with* the quotes
contrib_regression=# select tablename from pg_tables where
   schemaname !='information_schema' and schemaname !='pg_catalog'
   and tablename='Xeta';
tablename
-----------
Xeta
(1 row)

-- user passed Xeta *without* quotes
contrib_regression=# create table Xeta (f1 int);
CREATE TABLE
contrib_regression=# select tablename from pg_tables where
   schemaname !='information_schema' and schemaname !='pg_catalog'
   and tablename=lower('Xeta');
tablename
-----------
xeta
(1 row)
------------------------


Hope this helps

Joe

==========================================================
Joe, Neil

Dirk suggested you guys may have some ideas.  I have a PostgreSQL problem with 
capitalized table names, so in R I get bad results when checking is a table 
exists. Below are details from a message I sent Dirk. If you have any 
suggestions I would appreciate hearing.

Thanks,
Paul Gilbert
___

Dirk
I’m still struggling a bit with the capitalization problem. The difficulty 
seems to be that Postgres is not consistent.  I thought it was the quotes 
around the table name  in your new dbExistsTable() code, but it is double 
quotes (“) that Postgres treats as an indication that the name should be kept 
with the capitalization as specified, not the single quotes (‘) you used.  
The double quotes (“) is very non-standard and gets really messy because they 
then always have to be used in reference to the table, so I am not suggesting 
that direction.

The problem is that I would like consistent behaviour with regard to asking if 
Xeta exists, ie, if create table Xeta actually creates xeta then don't 
distinguish Xeta from xeta when asking if the table exists. I can see 
workarounds in the R code (as was probably in the previous version if you were 
using the RMySQL model), but I think it should be possible to do this in the 
SQL, however I cannot. Using psql:

CREATE TABLE Xeta (id CHAR(40) , v INT);
# next both work
\d Xeta ;
\d xeta ;

# this gives 1
select tablename from pg_tables where
    schemaname !='information_schema' and schemaname !='pg_catalog'
    and tablename='xeta';

# this gives 0
select tablename from pg_tables where
    schemaname !='information_schema' and schemaname !='pg_catalog'
    and tablename='Xeta';

#This gives 1
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' 
AND table_name = 'xeta';

#This gives 0
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' 
AND table_name = 'Xeta';

DROP TABLE Xeta;

# I don’t suggest this approach, but here is what happens
CREATE TABLE "Xeta" (id CHAR(40) , v INT);
# next both fail
\d Xeta ;
\d xeta ;
# this works
\d "Xeta" ;
# this fails
\d "xeta" ;
# this gives 0
select tablename from pg_tables where
    schemaname !='information_schema' and schemaname !='pg_catalog'
    and tablename='xeta';

# this gives 1
select tablename from pg_tables where
    schemaname !='information_schema' and schemaname !='pg_catalog'
    and tablename='Xeta';

# This gives 0
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' 
AND table_name = 'xeta';

# This gives 1
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' 
AND table_name = 'Xeta';


The double quotes are an error in MySQL, but MySQL is consistent with the first 
create, in that any capitalization combination works and the table is indicated 
as existing. The RMySQL code overdoes this by converting everything to upper or 
lower, but this does not seem necessary as a MySQL query can be constructed to 
give a consistent result. (However, there may be information_schema permission 
issues for normal users, but that is a different story.)

I’m not sure what to suggest, other than going back to the R workaround. I 
might be able to work around this in my code by specifying all the tables in 
lower case, but I have not tested it with the other the database engines I use, 
and in any case, that seems to be relying on the others to compensate for a 
Postgres problem. Also, this does not solve the problem for other RPostgreSQL 
users.

Best,
Paul


================================================================================
====

La version française suit le texte anglais.

--------------------------------------------------------------------------------
----

This email may contain privileged and/or confidential information, and the Bank 
of
Canada does not waive any related rights. Any distribution, use, or copying of 
this
email or the information it contains by other than the intended recipient is
unauthorized. If you received this email in error please delete it immediately 
from
your system and notify the sender promptly by email that you have done so. 

--------------------------------------------------------------------------------
----

Le présent courriel peut contenir de l'information privilégiée ou 
confidentielle.
La Banque du Canada ne renonce pas aux droits qui s'y rapportent. Toute 
diffusion,
utilisation ou copie de ce courriel ou des renseignements qu'il contient par une
personne autre que le ou les destinataires désignés est interdite. Si vous 
recevez
ce courriel par erreur, veuillez le supprimer immédiatement et envoyer sans 
délai à
l'expéditeur un message électronique pour l'aviser que vous avez éliminé de 
votre
ordinateur toute copie du courriel reçu.

Install from source fails under OS X Lion (Xcode 4.2). Used to work perfectly 
under Snow Leopard and Xcode 3.2. The error message is 

> make: gcc-4.2: No such file or directory

I guess this has to do with Apple's switch to a new compiler,  llvm-gcc-4.2. 
gcc-4.2 does not seem to exist anymore. Would it be possible to set the 
compiler path manually?

 Using dbConnect() with null or incorrect parameters forces R to shutdown
unexpectedly without issuing any error message. Example:

library(RPostgreSQL)
db.driver<-dbDriver("PostgreSQL")
db.con<-dbConnect(db.driver,user=NULL,password=NULL,dbname=NULL)

 I'm using R 2.8.1 on a WinXP 32-bit machine.

What steps will reproduce the problem?
0.  Define a normal user in postgres (EG, not just the superuser
    "postgres" account.
1.  As that user, create a new table in the DB with 'create 
    table foo ...'
2.  Verify that the user can insert a record with 'insert into table foo'
    in postgres.
2.  Make a compatible data frame in R
3.  In R, connect as the user account and run 'dbWriteTable()'

What is the expected output? What do you see instead?

Expected output is that if a user can write to the table,
dbWriteTable() should work.

Actual result is this:

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  must be superuser
to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also
works for anyone.
)
[1] FALSE
Warning message:
In postgresqlWriteTable(conn, name, value, ...) :
  could not load data into table


What version of the product are you using? On what operating system?

This is R 2.10 using RPosgresSQL 0.1-6 on Windows 7,
working against Postgres 8.4.1.


Please provide any additional information below.

ps.  Thanks for creating RPostgreSQL.  It's nice to be able to use two good
tools together.

What steps will reproduce the problem?
See 
http://www.r-project.org/nosvn/R.check/r-release-macosx-ix86/RPostgreSQL-00insta
ll.html

But is seems easy enough to fix. See:
http://iangow.wordpress.com/2011/04/23/installing-rpostgresql-with-r-2-13-on-a-m
ac/

Regards,

-Ian

What steps will reproduce the problem?
1. install.packages("RPostgreSQL", type="source")

2. library(RPostgreSQL)
3. con <- dbConnect(PostgreSQL(), user= "*", host="*", dbname="*", password="*"

What is the expected output? What do you see instead?
>

Traceback:
 1: .Call("RS_PostgreSQL_newConnection", drvId, con.params, PACKAGE = .PostgreSQLPkgName)
 2: postgresqlNewConnection(drv, ...)
 3: is(object, Cl)
 4: is(object, Cl)
 5: .valueClassTest(standardGeneric("dbConnect"), "DBIConnection",     "dbConnect")
 6: dbConnect(PostgreSQL(), user = "ipluta", host = "kiwi.cmr.net.pl",     dbname = "wafel", password = "")
Error in postgresqlNewConnection(drv, ...) : 
  caught access violation - continue with care
>


What version of the product are you using? On what operating system?

R x64 2.14.0 on Windows 7

Please provide any additional information below.

dlls of c:\program files\postgresql\9.1\bin manually copied to 
c:\windows\system32

Hi!

 Using queries with aliases on fields and/or tables (e.g.: "SELECT a.gid AS
id FROM sptable AS a") forces R to shutdown unexpectedly without issuing
any error message.
 I'm using R 2.8.1 on a WinXP 32-bit machine.

What steps will reproduce the problem?
1. prepare table like that:

    Column    |       Type       | Modifiers 
--------------+------------------+-----------
 zwr          | double precision | 
 var          | double precision | 
 typ          | bigint           | 
 nrsym        | bigint           | 
 variable     | text             | 
 value        | double precision | 
 value2       | double precision | 
The structure of table id PostgreSQL has been automatically obtained using
structure of data.frame.

2. Using dbWriteTable insert into it about 8000 records once of data in a
loop, process it using SQL command and purge content of table. This loop
iterates 100 times. The problem occurs at about 40-50 iteration. In the
loop, there is some other updates of other tables.

3. Structure of data is like that:
>class(hh1)
[1] "data.frame"
>head(hh1)
          zwr          var typ          nrsym variable      value     value2
1 0.001952104 0.0004735093            1     1    15942 0.18993642          20
2 0.004668214 0.0008133745            2     1    15942 0.34396592          20
3 0.003748000 0.0005804836            3     1    15942 0.36278915          20
4 0.001952104 0.0004735093            1     1    15951 0.07493939          20
5 0.004668214 0.0008133745            2     1    15951 0.41027399          20
6 0.003748000 0.0005804836            3     1    15951 0.07603849          20

The structure of table id PostgreSQL has been automatically obtained using
this structure of data.frame.


What is the expected output? What do you see instead?

*** buffer overflow detected ***: /usr/lib64/R/bin/exec/R terminated
======= Backtrace: =========
/lib/libc.so.6(__fortify_fail+0x37)[0x7f0e4db435f7]
/lib/libc.so.6[0x7f0e4db425a0]
/lib/libc.so.6[0x7f0e4db41a09]
/lib/libc.so.6(_IO_default_xsputn+0x98)[0x7f0e4dac0448]
/lib/libc.so.6(_IO_vfprintf+0x3972)[0x7f0e4da94712]
/lib/libc.so.6(__vsprintf_chk+0x99)[0x7f0e4db41aa9]
/lib/libc.so.6(__sprintf_chk+0x7f)[0x7f0e4db419ef]
/usr/local/lib/R/site-library/RPostgreSQL/libs/RPostgreSQL.so(RS_PostgreSQL_exec
+0xff)[0x7f0e4b50e0ef]
/usr/lib64/R/lib/libR.so[0x7f0e4e0a6618]
/usr/lib64/R/lib/libR.so(Rf_eval+0x696)[0x7f0e4e0d9a36]
/usr/lib64/R/lib/libR.so[0x7f0e4e0db840]
/usr/lib64/R/lib/libR.so(Rf_eval+0x4a0)[0x7f0e4e0d9840]
/usr/lib64/R/lib/libR.so[0x7f0e4e0db8cc]
/usr/lib64/R/lib/libR.so(Rf_eval+0x4a0)[0x7f0e4e0d9840]
/usr/lib64/R/lib/libR.so(Rf_applyClosure+0x2e2)[0x7f0e4e0dcff2]
/usr/lib64/R/lib/libR.so(Rf_eval+0x394)[0x7f0e4e0d9734]
/usr/lib64/R/lib/libR.so[0x7f0e4e0dc8a4]
/usr/lib64/R/lib/libR.so(R_execMethod+0x238)[0x7f0e4e0dcc18]
/usr/lib64/R/library/methods/libs/methods.so[0x7f0e4bcfcac5]
/usr/lib64/R/lib/libR.so[0x7f0e4e11b0b9]
/usr/lib64/R/lib/libR.so(Rf_eval+0x5bd)[0x7f0e4e0d995d]
/usr/lib64/R/lib/libR.so[0x7f0e4e0d9b87]
/usr/lib64/R/lib/libR.so(Rf_eval+0x23c)[0x7f0e4e0d95dc]
/usr/lib64/R/lib/libR.so(Rf_eval+0x613)[0x7f0e4e0d99b3]
/usr/lib64/R/lib/libR.so[0x7f0e4e0d9b87]
/usr/lib64/R/lib/libR.so(Rf_eval+0x23c)[0x7f0e4e0d95dc]
/usr/lib64/R/lib/libR.so(Rf_eval+0x613)[0x7f0e4e0d99b3]
/usr/lib64/R/lib/libR.so[0x7f0e4e0daba4]
/usr/lib64/R/lib/libR.so(Rf_eval+0x55b)[0x7f0e4e0d98fb]
/usr/lib64/R/lib/libR.so[0x7f0e4e0db840]
/usr/lib64/R/lib/libR.so(Rf_eval+0x4a0)[0x7f0e4e0d9840]
/usr/lib64/R/lib/libR.so[0x7f0e4e0db8cc]
/usr/lib64/R/lib/libR.so(Rf_eval+0x4a0)[0x7f0e4e0d9840]
/usr/lib64/R/lib/libR.so(Rf_applyClosure+0x2e2)[0x7f0e4e0dcff2]
/usr/lib64/R/lib/libR.so(Rf_eval+0x394)[0x7f0e4e0d9734]
/usr/lib64/R/lib/libR.so[0x7f0e4e0dc3f3]
/usr/lib64/R/lib/libR.so(Rf_eval+0x4a0)[0x7f0e4e0d9840]
/usr/lib64/R/lib/libR.so[0x7f0e4e0dbd96]
/usr/lib64/R/lib/libR.so(Rf_eval+0x4a0)[0x7f0e4e0d9840]
/usr/lib64/R/lib/libR.so[0x7f0e4e0db8cc]
/usr/lib64/R/lib/libR.so(Rf_eval+0x4a0)[0x7f0e4e0d9840]
/usr/lib64/R/lib/libR.so(Rf_eval+0x4a0)[0x7f0e4e0d9840]
/usr/lib64/R/lib/libR.so[0x7f0e4e0db8cc]
/usr/lib64/R/lib/libR.so(Rf_eval+0x4a0)[0x7f0e4e0d9840]
/usr/lib64/R/lib/libR.so(Rf_applyClosure+0x2e2)[0x7f0e4e0dcff2]
/usr/lib64/R/lib/libR.so(Rf_eval+0x394)[0x7f0e4e0d9734]
/usr/lib64/R/lib/libR.so(Rf_applyClosure+0x2e2)[0x7f0e4e0dcff2]
/usr/lib64/R/lib/libR.so(Rf_eval+0x394)[0x7f0e4e0d9734]
/usr/lib64/R/lib/libR.so[0x7f0e4e0d9b87]
/usr/lib64/R/lib/libR.so(Rf_eval+0x23c)[0x7f0e4e0d95dc]
/usr/lib64/R/lib/libR.so(Rf_eval+0x613)[0x7f0e4e0d99b3]
/usr/lib64/R/lib/libR.so[0x7f0e4e0d9b87]
/usr/lib64/R/lib/libR.so(Rf_eval+0x23c)[0x7f0e4e0d95dc]
/usr/lib64/R/lib/libR.so(Rf_eval+0x613)[0x7f0e4e0d99b3]
/usr/lib64/R/lib/libR.so[0x7f0e4e0d9b87]
/usr/lib64/R/lib/libR.so(Rf_eval+0x23c)[0x7f0e4e0d95dc]
/usr/lib64/R/lib/libR.so(Rf_eval+0x613)[0x7f0e4e0d99b3]
/usr/lib64/R/lib/libR.so[0x7f0e4e0d9b87]
/usr/lib64/R/lib/libR.so(Rf_eval+0x23c)[0x7f0e4e0d95dc]
/usr/lib64/R/lib/libR.so(Rf_eval+0x613)[0x7f0e4e0d99b3]
/usr/lib64/R/lib/libR.so[0x7f0e4e0da74d]
/usr/lib64/R/lib/libR.so[0x7f0e4e0da972]
/usr/lib64/R/lib/libR.so(Rf_eval+0x4a0)[0x7f0e4e0d9840]
======= Memory map: ========
00400000-00401000 r-xp 00000000 08:03 198771                            
/usr/lib/R/bin/exec/R
00600000-00601000 r--p 00000000 08:03 198771                            
/usr/lib/R/bin/exec/R
00601000-00602000 rw-p 00001000 08:03 198771                            
/usr/lib/R/bin/exec/R
02395000-06480000 rw-p 00000000 00:00 0                                  [heap]
7f0e482b5000-7f0e482cb000 r-xp 00000000 08:03 37118                     
/lib/libgcc_s.so.1
7f0e482cb000-7f0e484ca000 ---p 00016000 08:03 37118                     
/lib/libgcc_s.so.1
7f0e484ca000-7f0e484cb000 r--p 00015000 08:03 37118                     
/lib/libgcc_s.so.1
7f0e484cb000-7f0e484cc000 rw-p 00016000 08:03 37118                     
/lib/libgcc_s.so.1
7f0e484cc000-7f0e485d0000 rw-p 00000000 00:00 0 
7f0e487d8000-7f0e487db000 r-xp 00000000 08:06 120878037                 
/home/michal/R/x86_64-pc-linux-gnu-library/2.9/multicore/libs/multicore.so
7f0e487db000-7f0e489da000 ---p 00003000 08:06 120878037                 
/home/michal/R/x86_64-pc-linux-gnu-library/2.9/multicore/libs/multicore.so
7f0e489da000-7f0e489db000 r--p 00002000 08:06 120878037                 
/home/michal/R/x86_64-pc-linux-gnu-library/2.9/multicore/libs/multicore.so
7f0e489db000-7f0e489dc000 rw-p 00003000 08:06 120878037                 
/home/michal/R/x86_64-pc-linux-gnu-library/2.9/multicore/libs/multicore.so
7f0e489dc000-7f0e489df000 r-xp 00000000 08:03 86379                     
/lib/libgpg-error.so.0.4.0
7f0e489df000-7f0e48bde000 ---p 00003000 08:03 86379                     
/lib/libgpg-error.so.0.4.0
7f0e48bde000-7f0e48bdf000 r--p 00002000 08:03 86379                     
/lib/libgpg-error.so.0.4.0
7f0e48bdf000-7f0e48be0000 rw-p 00003000 08:03 86379                     
/lib/libgpg-error.so.0.4.0
7f0e48be0000-7f0e48c55000 r-xp 00000000 08:03 90488                     
/lib/libgcrypt.so.11.5.2
7f0e48c55000-7f0e48e54000 ---p 00075000 08:03 90488                     
/lib/libgcrypt.so.11.5.2
7f0e48e54000-7f0e48e55000 r--p 00074000 08:03 90488                     
/lib/libgcrypt.so.11.5.2
7f0e48e55000-7f0e48e58000 rw-p 00075000 08:03 90488                     
/lib/libgcrypt.so.11.5.2
7f0e48e58000-7f0e48e68000 r-xp 00000000 08:03 96075                     
/usr/lib/libtasn1.so.3.1.5
7f0e48e68000-7f0e49067000 ---p 00010000 08:03 96075                     
/usr/lib/libtasn1.so.3.1.5
7f0e49067000-7f0e49068000 r--p 0000f000 08:03 96075                     
/usr/lib/libtasn1.so.3.1.5
7f0e49068000-7f0e49069000 rw-p 00010000 08:03 96075                     
/usr/lib/libtasn1.so.3.1.5
7f0e49069000-7f0e49104000 r-xp 00000000 08:03 24900                     
/usr/lib/libgnutls.so.26.14.10
7f0e49104000-7f0e49304000 ---p 0009b000 08:03 24900                     
/usr/lib/libgnutls.so.26.14.10
7f0e49304000-7f0e4930a000 r--p 0009b000 08:03 24900                     
/usr/lib/libgnutls.so.26.14.10
7f0e4930a000-7f0e4930b000 rw-p 000a1000 08:03 24900                     
/usr/lib/libgnutls.so.26.14.10
7f0e4930b000-7f0e49324000 r-xp 00000000 08:03 94559                     
/usr/lib/libsasl2.so.2.0.23
7f0e49324000-7f0e49523000 ---p 00019000 08:03 94559                     
/usr/lib/libsasl2.so.2.0.23
7f0e49523000-7f0e49524000 r--p 00018000 08:03 94559                     
/usr/lib/libsasl2.so.2.0.23
7f0e49524000-7f0e49525000 rw-p 00019000 08:03 94559                     
/usr/lib/libsasl2.so.2.0.23Aborted

What version of the product are you using? On what operating system?

Ubuntu 9.10 Karmic, PostgreSQL 8.4, R version 2.9.2 (2009-08-24),
RPostgreSQL 0.1-6

Please provide any additional information below.
When I comment all lines with DB-functions in my script, it goes nicely to
the very end.

checking whether package 'RPostgreSQL' can be installed ... ERROR
Installation failed.
See 
'/Volumes/Tiger/Builds/Rdev-web/QA/Simon/packages/leopard-universal/results/2.11
/RPostgreSQL.Rcheck/00install.out' for details.
See 
http://www.r-project.org/nosvn/R.check/r-release-macosx-ix86/RPostgreSQL-00insta
ll.html for details.

=========================
from the above link.

checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes
checking whether we are cross compiling... no
checking for suffix of executables... 
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for pg_config... no
configure: checking for PostgreSQL header files
checking for "/libpq-fe.h"... no
configure: error: File libpq-fe.h not in ; installation may be broken.
ERROR: configuration failed for package 'RPostgreSQL'
* removing 
'/Volumes/Tiger/Builds/Rdev-web/QA/Simon/packages/leopard-universal/results/2.11
/RPostgreSQL.Rcheck/RPostgreSQL'


=========================
r-release-macosx-ix86    r-release   MacOS X     ix86    Mac OS X 10.5.8 (9L31a)     
MacPro, Intel Xeon XXXX (Gainstown) @ 2.65GHz    GCC 4.2.1

=========================

Also causes TSPostgreSQL to fail.  RpgSQL passes on Mac.  Need to find out if 
it actually checks that postgresql is installed or not and if so how it does it.

What steps will reproduce the problem?
1. create a table containing capital letter like Foo2
2. call dbRemoveTable(con, "Foo2")
3. check if it is really removed with psql or
dbExistsTable(con, "Foo2")

What is the expected output? What do you see instead?

Expects the table being removed. Actually fails to do so.
Its also dangerous because it can infact delete lowercase letter
named table if exists.

What version of the product are you using? On what operating system?
RPostgreSQL_0.1-6 plus some patches
R-2.11.1 
Posgresql-8.4.4

Please provide any additional information below.

Test code and patch is attached.

Output after patch:

==== Running RPostgreSQL/tests/dbRemoveTableTest.R
Loading required package: RPostgreSQL
Loading required package: DBI

Create foo1 table
Pass - foo1 Table exists. created
removing foo1
Pass - foo1 Table does not exist. i.e. deleted

Create Foo2 table
Pass - Foo2 Table exists. created
removing Foo2
Pass - Foo2 Table does not exist. i.e. deleted

Create Fo"o3 table
Pass - Fo"o3 Table exists. created
removing Fo"o3
Pass - Fo"o3 Table does not exist. i.e. deleted
[1] TRUE

-------------- versions                                      
----------------------
ProductName:    Mac OS X
ProductVersion: 10.6.4
BuildVersion:   10F569
RPostgreSQL svn version: 132:134
psql (PostgreSQL) 9.0beta3

R version 2.11.1 (2010-05-31) 
x86_64-apple-darwin9.8.0 

locale:

Package: RPostgreSQL
Version: 0.1-6
Packaged: NA
Built: R 2.11.1; universal-apple-darwin9.8.0; 2010-08-24 16:29:16 UTC;

--------------start RPostgreSQL/tests/selectWhereZero.R      
----------------------
Loading required package: RPostgreSQL
Loading required package: DBI
./check_with_vars.sh: line 74:  5075 Abort trap              R --arch=i386 
--slave < RPostgreSQL/tests/selectWhereZero.R

What steps will reproduce the problem?
1. Bring up the dbConnect() man page for RPostgreSQL (For example, try 
?PosgreSQL in the R command line, click on the index list at the bottom, then 
click on dbConnect-Methods
2. Note that the Method section is blank.  This is supposed to describe all the 
arguments of the method.
3. If you look at the source code for the dbConnect() documentation file the 
description is there - it just doesn't appear in the readable documentation.

What is the expected output? What do you see instead?
see above

What version of the product are you using? On what operating system?
R 2.15.2, RPostgreSQL 0.4 (compiled on R 2.15.3), Windows 7 64-bit.

Please provide any additional information below.

Hi,
As of RPostgreSQL 0.1-7, dbWriteTable (postgresqlWriteTable) no longer returns 
FALSE or errors when table writes are unsuccessful:

## Here's an example where I try to insert the USArrests dataset into 
## table 'tmp' containing incompatible columns.
## Ex 0.1-7, write fails, but success is reported
> dbGetQuery(pcon, "CREATE TABLE tmp (a int, b varchar(5));")
> dbWriteTable(pcon, "tmp", USArrests, append=TRUE, overwrite=FALSE)
[1] TRUE
> dbGetQuery(pcon, "select * from tmp;")
data frame with 0 columns and 0 rows

## In 0.1-6, write fails, error and failure reported
> dbWriteTable(pcon, "tmp", USArrests, append=TRUE, overwrite=FALSE)
Error in postgresqlExecStatement(conn, statement, ...) :
  RS-DBI driver: (could not Retrieve the result : ERROR:  extra data after last expected column
CONTEXT:  COPY tmp, line 1: "Alabama    13.2    236     58      21.2"
)
Warning in postgresqlWriteTable(conn, name, value, ...) :
  could not load data into table
[1] FALSE


> R.version
               _                            
platform       x86_64-apple-darwin9.8.0     
arch           x86_64                       
os             darwin9.8.0                  
system         x86_64, darwin9.8.0          
status                                      
major          2                            
minor          11.1                         
year           2010                         
month          05                           
day            31                           
svn rev        52157                        
language       R                            
version.string R version 2.11.1 (2010-05-31)

What steps will reproduce the problem?
0.  Have a home directory on windows like C:\Users\richard,
    where \r is part of the pathname.
1.  Make a table in postgres with 'create table foo ...'
2.  Make a compatible data frame in R.
3.  As the superuser postgres account ("postgres"),
    try to write the data frame to the table.

What is the expected output? What do you see instead?

Should write data to table.

Actual output is this:

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  could not open
file "C:Users
eitmrAppDataLocalTempRtmpmXy6xu
sdbi52db525b" for reading: Invalid argument
)
[1] FALSE
Warning message:
In postgresqlWriteTable(conn, name, value, ...) :
  could not load data into table


This very much looks like a call to get a temporary filename,
which picks C:\Users\richard\.... and then a subsequent passing
of that filename without escaping \ characters in it.

If there was a way to avoid using a filename at all and adopt
stdin, that might fit better with how postgres works.  Current
implementation error messages suggest the current approach
won't work well for remote connections.

ps.  Thank you for creating / working on RPOstgreSQL.  It's
very nice to be able to use two good tools together.


What version of the product are you using? On what operating system?


Please provide any additional information below.

For many months I've been getting intermittent errors and segfaults running 
long jobs that combine multicore (mcparallel/mclapply) and RPostgreSQL calls. 
The below code shows how to reproduce some of these problems by using gctorture 
to ferret out memory protection and connection problems. I've labelled five 
bugs below, but likely there are only one or two distinct bugs. The first three 
produce errors  for code that should likely run correctly and the last two 
segfaults.

## Bug 1. Query 'SELECT 1;' with gctorture on.
> p <- dbDriver("PostgreSQL")
> con <- dbConnect(p, host="localhost", user="user", password="pwd, 
dbname="template1")
> gctorture(TRUE)
> for (i in 1:10) dbGetQuery(con, "SELECT 1;")
Error in if (flds[[i]] == 1114) { : argument is of length zero

## Bug 2. Query column info with gctorture on.
> rs <- dbSendQuery(con, "SELECT 1;")
> gctorture(TRUE)
> for (i in 1:10) dbColumnInfo(rs)
Error in postgresqlDescribeFields(res, ...) : 
  RS-DBI driver: (internal error in RS_DBI_SclassNames: input S types must be nonNULL)

## Bug 3. Run #2 again, but inside a 'try' to expose a different error
> for (i in 1:10) try(dbColumnInfo(rs))
Error : protect(): protection stack overflow

## I think Bugs 1-3 may all be the same since they all come from dbColumnInfo 
in one manner or another.

## Bug 4. Here we use both gctorture and mclapply on a 40-column table, _xyz, 
until we get a segfault on one or more of the cores.
p <- dbDriver("PostgreSQL")
con <- dbConnect(p, host=host, user=user, password=password, dbname=dbname)
qry <- paste("CREATE TABLE _xyz (", # Create 40 column table
             paste("x", 1:40, " real", sep="", collapse=", "), ");")
dbSendQuery(con, qry)
gctorture(TRUE)
mclapply(1:16, function(x) {
    p <- dbDriver("PostgreSQL")
    con <- dbConnect(p, host=host, user=user, password=password, dbname=dbname)
    dbGetQuery(con, "SELECT * FROM _xyz limit 0;")
    dbDisconnect(con)
}, mc.cores=8)
gctorture(FALSE)

 *** caught segfault ***
address 0xfe65b88, cause 'memory not mapped'

 *** caught segfault ***
address 0xfe65b88, cause 'memory not mapped'
*** glibc detected *** /data/gentoo/usr/lib/R/bin/exec/R: double free or 
corruption (!prev): 0x000000000f490b10 ***
======= Backtrace: =========
/lib64/libc.so.6[0x377a870baf]
/lib64/libc.so.6(cfree+0x4b)[0x377a87100b]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585d03ee0]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_cons+0xda)[0x2ad585d0542a]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_allocList+0x2f)[0x2ad585d055ff]
/data/gentoo/usr/lib/R/lib/libR.so(R_GetTraceback+0x65)[0x2ad585cb3c15]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585cfd021]
/lib64/libpthread.so.0[0x377b80ebe0]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585d00c76]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585d026fd]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_cons+0xda)[0x2ad585d0542a]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_defineVar+0x274)[0x2ad585cadd14]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585ccc639]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x55b)[0x2ad585cc96eb]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585ccc790]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x55b)[0x2ad585cc96eb]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_applyClosure+0x340)[0x2ad585ccdac0]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x430)[0x2ad585cc95c0]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585ccd3b7]
/data/gentoo/usr/lib/R/lib/libR.so(R_execMethod+0x225)[0x2ad585ccd6e5]
/data/gentoo/usr/lib/R/library/methods/libs/methods.so[0x2ad588637cdb]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585d14283]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x68f)[0x2ad585cc981f]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_applyClosure+0x340)[0x2ad585ccdac0]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x430)[0x2ad585cc95c0]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585ccb2bf]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585d92e32]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x55b)[0x2ad585cc96eb]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585ccaf79]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x629)[0x2ad585cc97b9]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585ccd3b7]
/data/gentoo/usr/lib/R/lib/libR.so(R_execMethod+0x225)[0x2ad585ccd6e5]
/data/gentoo/usr/lib/R/library/methods/libs/methods.so[0x2ad588637cdb]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585d14283]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x68f)[0x2ad585cc981f]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585cc9a6b]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585cc8ae5]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x17a)[0x2ad585cc930a]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585cc9a6b]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585cc8ae5]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x17a)[0x2ad585cc930a]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_applyClosure+0x340)[0x2ad585ccdac0]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585cc5514]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x17a)[0x2ad585cc930a]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_applyClosure+0x340)[0x2ad585ccdac0]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x430)[0x2ad585cc95c0]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_applyClosure+0x340)[0x2ad585ccdac0]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x430)[0x2ad585cc95c0]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585ccc923]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x55b)[0x2ad585cc96eb]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585ccc790]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x55b)[0x2ad585cc96eb]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_applyClosure+0x340)[0x2ad585ccdac0]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x430)[0x2ad585cc95c0]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585ccd3b7]
/data/gentoo/usr/lib/R/lib/libR.so(R_execMethod+0x225)[0x2ad585ccd6e5]
/data/gentoo/usr/lib/R/library/methods/libs/methods.so[0x2ad588637cdb]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585d14283]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x68f)[0x2ad585cc981f]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_applyClosure+0x340)[0x2ad585ccdac0]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x430)[0x2ad585cc95c0]
/data/gentoo/usr/lib/R/lib/libR.so[0x2ad585ccc790]
/data/gentoo/usr/lib/R/lib/libR.so(Rf_eval+0x55b)[0x2ad585cc96eb]
======= Memory map: ========
00400000-00401000 r-xp 00
 *** caught segfault ***
address 0xfe65b88, cause 'memory not mapped'
...

## Sometimes I get this error as well
[1] "Error in postgresqlResultInfo(dbObj, ...) : \n  SET_STRING_ELT() can only 
be applied to a 'character vector', not a 'NULL'\n"

## Bug 5: Here we cause a segfault without either gctorture or mclapply... It 
just takes some patience. It helps if we have a query with lots of columns, so 
we'll make the table _xyz have 500 columns and run for several minutes:

> p <- dbDriver("PostgreSQL")
> con <- dbConnect(p, host=host, user=user, password=password, dbname=dbname)
> dbSendQuery(con, "DROP TABLE _xyz;")
> qry <- paste("CREATE TABLE _xyz (", # Create 40 column table o
             paste("x", 1:500, " real", sep="", collapse=", "), ");")
> dbSendQuery(con, qry)
> rs <- dbSendQuery(con, "SELECT * FROM _xyz limit 0;")
> while(TRUE) dbColumnInfo(rs) # This can take awhile.

Warning in postgresqlDescribeFields(res, ...) :
  RS-DBI driver warning: (unknown (10192))
Warning in postgresqlDescribeFields(res, ...) :
  RS-DBI driver warning: (unknown (12240))
Warning in postgresqlDescribeFields(res, ...) :
  RS-DBI driver warning: (unknown (0))

 *** caught segfault ***
address 0x1fd3, cause 'memory not mapped'

Traceback:
 1: postgresqlDescribeFields(res, ...)
 2: is(object, Cl)
 3: is(object, Cl)
 4: .valueClassTest(standardGeneric("dbColumnInfo"), "data.frame",     "dbColumnInfo")
 5: dbColumnInfo(rs)

Possible actions:
1: abort (with core dump, if enabled)
2: normal R exit
3: exit R without saving workspace
4: exit R saving workspace


Let me know if you need any other information. These examples work similarly on 
my Mac and Linux.

Robert

> R.version
               _                            
platform       x86_64-pc-linux-gnu          
arch           x86_64                       
os             linux-gnu                    
system         x86_64, linux-gnu            
status                                      
major          2                            
minor          15.1                         
year           2012                         
month          06                           
day            22                           
svn rev        59600                        
language       R                            
version.string R version 2.15.1 (2012-06-22)
nickname       Roasted Marshmallows         

----------------
        Information on package 'RPostgreSQL'
Description:

Package:            RPostgreSQL
Version:            0.3-2
Date:               $Date: 2012-01-10 17:51:04 +0900 (Tue, 10 Jan 2012) $
Title:              R interface to the PostgreSQL database system
Author:             Joe Conway, Dirk Eddelbuettel, Tomoaki Nishiyama, Sameer 
Kumar
                    Prayaga (during 2008), Neil Tiffin
Maintainer:         Tomoaki Nishiyama <[email protected]>
Packaged:           2012-03-21 19:07:56 UTC; a347549
Repository:         CRAN
Date/Publication:   2012-01-29 08:27:40
Built:              R 2.15.0; x86_64-pc-linux-gnu; 2012-06-27 20:40:32 UTC; unix

--------
PostgreSQL version 9.2.0

The second had to do with dbWriteTable and dbReadTable.  Postgresql has a 
protocol for slurping 
up text data via the connection in a VERY fast manner; this functionality (last 
I checked) was not 
being used to deal with dbWriteTable and dbReadTable.  It would be very nice to 
use that api for the 
reading and (particularly) writing of tables, since it can be an order of 
magnitude or more faster 
than inserts.  

From email Sean Davis

Supposing that variable "foo" is of character type, and the following query
will execute correctly:

   dbGetQuery(con,"SELECT * FROM table WHERE foo='00'")

Then the following will crash R:

   dbGetQuery(con,"SELECT * FROM table WHERE foo=0")

The attached file contains the output that appeared after a crash of this type.

The error occured using R 2.9.1 on Ubuntu Jaunty 9.04, 64-bit version.

What steps will reproduce the problem?
1. On OS X with Macports gcc-mp-4.7 and Macports R
2. install.packages('RPostgreSQL')

What is the expected output? What do you see instead?
I expect t to install, but I see: 

/opt/local/bin/gcc-mp-4.7 -std=gnu99 -pipe -O3 -march=corei7 -m64  -Kthread 
-kthread -pthread -pthreads  -D_REENTRANT -D_THREAD_SAFE 
-D_POSIX_PTHREAD_SEMANTICS  -DFRONTEND -DUNSAFE_STAT_OK -I. -I../src/include   
-DSO_MAJOR_VERSION=5  -c -o fe-auth.o fe-auth.c
gcc-mp-4.7: error: unrecognized command line option '-Kthread'
gcc-mp-4.7: error: unrecognized command line option '-kthread'
gcc-mp-4.7: error: unrecognized command line option '-pthreads'
make[1]: *** [fe-auth.o] Error 1




What version of the product are you using? On what operating system?
OS X 10.8.2, R 2.15.2, gcc 4.7, postgresql 9.2.3,  MacPorts

Please provide any additional information below.
If I download the source, and take out those flags, it compiles with no problem.

What steps will reproduce the problem?

1. Run Postgres with some data in a table

2. Do dbGetQuery("query").  The query should return some rows from a table.

3. Get a bad error message once for each select "unrecognized PostgreSQL field 
type void (id: 2278) in column 0.

What is the expected output? What do you see instead?
The error message should not be there.

What version of the product are you using? On what operating system?
Postgres 9.1.  Running Mac OSX Lion R 2.14.1

Please provide any additional information below.
Simple fix. Add the lines

case VOIDOID:
    break;

after line 546 in RS-PostgreSQL.c.

Basically column 0 on every select is of type void.

What steps will reproduce the problem?
1. compile R with intel compilers
2. install R
3. try to make RPostgreSQL with
R CMD INSTALL RPostgreSQL

What is the expected output? What do you see instead?
...
* DONE (RPostgreSQL)

What version of the product are you using? On what operating system?
RPostgreSQL_0.1-6.tar.gz from CRAN or latest from svn today.
with R-2.11.1
on CentOS 5.5

compilation failed with error message below.

icc -std=c99 -I/usr/local/lib64/R/include -I/usr/include -I/usr/local/include  
-mp  -fpic  -g -O2 -std=c99 -c RS-DBI.c -o RS-DBI.o
icc: command line remark #10010: option '-mp' is deprecated and will be removed 
in a future release. See '-help deprecated'
RS-DBI.h(42): error: identifier "pid_t" is undefined
      pid_t getpid();
      ^

Please provide any additional information below.

man getpid on this system said
SYNOPSIS
       #include <sys/types.h>
       #include <unistd.h>

So, #include <sys/types.h> will resolve this problem.

dbWriteTable method is not functioning properly. It starts by creating the
database table but it doesn't populates it with data from the inputed df.
The following rcode produces:

dbWriteTable(pg.conn,"test_cars",cars)

> Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  could not open
file "C:DOCUME~1JOO~1DEFINI~1TempRtmpVtwbDRsdbi29f35ee8" for reading:
Invalid argument)

[1] FALSE
Warning message:
In postgresqlWriteTable(conn, name, value, ...) :
  could not load data into table 

Please notice that the path in the error message seems to have some problem.
I'm using R 2.8.0 (2008-10-20) on Windows XP Pro SP3.

Ref: https://stat.ethz.ch/pipermail/r-sig-db/2009q2/000618.html

On Mon, Apr 6, 2009 at 10:41 PM, Sean Davis <sdavis2 at mail.nih.gov> wrote:
> On Mon, Apr 6, 2009 at 9:23 PM, Prasenjit Kapat <kapatp at gmail.com> wrote:
>> On Mon, Apr 6, 2009 at 8:01 PM, Sean Davis <sdavis2 at mail.nih.gov> wrote:
>> > On Mon, Apr 6, 2009 at 7:42 PM, Dirk Eddelbuettel <edd at debian.org>
>> > wrote:
>> >> On 6 April 2009 at 19:18, Prasenjit Kapat wrote:
>> >> | Hi,
>> >> | dbExistsTable (conn, name="myschema.mytable") is FALSE, whereas
>> >> | dbExistsTable (conn, name="mytable") is TRUE ! 'mytable' is part of
>> >> | the 'myschema'.
>> >> | Is this as expected?
>> >>
>> >> Not sure. Check the Pg API doc of what is expected.
>>
>> Hmmm, I am not too familiar with postgres myself. I am trying to cover
>> as much as possible with RPostgreSQL.
>>
>> >> And in general, patches and debugging are welcome.  Sameer, who wrote
>> >> RPostgreSQL as part of last year's Google Summer of Code seems to have
>> >> lost
>> >> touch with the code.
>> >
>> > Looks like dbTableExists() method calls dbListTables() method, and that
>> > uses
>> > the SQL query:
>> >
>> > select tablename from pg_tables
>> > where schemaname !='information_schema'
>> > and schemaname !='pg_catalog'
>> >
>> > This returns the table name, but no schema name.  R code is then used to
>> > check for equality of strings.  This will lead to the incorrect behavior
>> > as
>> > noted.  I can provide a patch, probably, but it won't be immediate, so
>> > if
>> > someone else gets there first, just let us know.
>>
>> It looks like anything involving schema is buggy, probably the code
>> was not tested in such scenarios. For example:
>>
>> dbRemoveTable (con, name="myschema.mytable") is again FALSE and
>> dbRemoveTable (con, name="mytable") gives (which is expected as
>> 'public.mytable' is nonexistent):
>>
>> Error in postgresqlExecStatement(conn, statement, ...) :
>>  RS-DBI driver: (could not Retrieve the result : ERROR:  table
>> "mytable" does not exist
>> )
>> [1] FALSE
>>
>> I'll try to see if any proper solution exists.
>
> I was looking through the DBI docs and I didn't see a mention of schema,
> specifically.  I'm not very familiar with how other DB drivers deal with the
> issue of schema.  Is there a description of how DBI methods are meant to
> deal with schema, or is it up to the implementation to deal with the issue
> via the ... arguments?

Hopefully someone who knows the details can help on this.

Back here, the following is a quick-n-dirty solution (not a patch of course):

dbExistsTable <- function (con, name, ...)
{
  as.logical (
   dim (
    dbGetQuery (con,
     paste ("select schemaname,tablename from pg_tables where schemaname='",
      rev(strsplit(name, ".", fixed=TRUE)[[1]])[2],
      "' and tablename='",
      rev(strsplit(name, ".", fixed=TRUE)[[1]])[1], "'", sep="")
  )) [1])
}

dbRemoveTable <- function (con, name, ..., cascade=FALSE)
{
  if (dbExistsTable (con, name)) {
    dbGetQuery (con, paste ("drop table ", name, ifelse (cascade, "
cascade", ""), ";", sep=""))
  }
}

-- 
Prasenjit

> x <- dbSendQuery(con, "CREATE TEMPORARY TABLE xyz ON COMMIT DROP AS select * 
from abc limit 1; select * from xyz;")
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(con, "CREATE TEMPORARY TABLE xyz1 ON COMMIT DROP AS select * from qsp limit 1; select * from xyz1;")

Possible actions:
1: abort (with core dump, if enabled)
2: normal R exit
3: exit R without saving workspace
4: exit R saving workspace
Selection: 

Package:            RPostgreSQL
Version:            0.1-6
Date:               $Date: 2009-10-19 16:04:18 -0500 (Mon, 19 Oct 2009) $
> R.version
               _                            
platform       x86_64-unknown-linux-gnu     
arch           x86_64                       
os             linux-gnu                    
system         x86_64, linux-gnu            
status                                      
major          2                            
minor          11.1                         
year           2010                         
month          05                           
day            31                           
svn rev        52157                        
language       R                            
version.string R version 2.11.1 (2010-05-31)

Under ubuntu linux with the default installation of postgresql, simply doing 
install.packages('RPostgreSQL')in R fails with unspecified errors. 

The problem is solved by installing the postgres server dev package before 
retrying the install.packages command in R.  eg sudo apt-get install 
postgresql-server-dev-8.4 for postgresql version 8.4.

What steps will reproduce the problem?
1. Not easily reproducible. Hard to determine pattern of occurrence
2.
3.

What is the expected output? What do you see instead?
Call to dbGetQuery sometimes (rarely) results in segmentation fault. Does not 
appear to be linked to a specific query, but traceback seems to lead to 
RPostgreSQL functions (see below)  

What version of the product are you using? On what operating system?
R 2.15.1, RPostgreSQL 0.3.2 DBI 0.2-5
Platform: x86_64-pc-linux-gnu (Ubuntu, 64-bit)

Please provide any additional information below.
Running a long script (several days to process), which processes and parses 
many XML files, and then inserts processed data into PostgreSQL database. 
Several hours (5-36) through the run, R crashes with a segmentation fault, 
memory not mapped. Restarting the script, which picks up at the file where it 
left off, the file is processed with no issues, so I believe it is not the 
specific queries / or file. The traceback is here:


 *** caught segfault ***
address 0x7f100000021a, cause 'memory not mapped'

Traceback:
 1: paste(sig, collapse = "#")
 2: .sigLabel(signature)
 3: .findMethodInTable(c(from, to), methods)
 4: .quickCoerceSelect(thisClass, Class, coerceFun, coerceMethods,     where)
 5: as(obj, "integer")
 6: isPostgresqlIdCurrent(obj)
 7: postgresqlResultInfo(dbObj, ...)
 8: dbGetInfo(res, "rowCount")
 9: dbGetInfo(res, "rowCount")
10: is(object, Cl)
11: is(object, Cl)
12: .valueClassTest(standardGeneric("dbGetRowCount"), "numeric",     
"dbGetRowCount")
13: dbGetRowCount(res)
14: postgresqlFetch(res, n, ...)
15: is(object, Cl)
16: is(object, Cl)
17: .valueClassTest(standardGeneric("fetch"), "data.frame", "fetch")
18: fetch(rs, n = -1)
19: postgresqlQuickSQL(conn, statement, ...)
20: dbGetQuery(connObj, queryStr)
21: dbGetQuery(connObj, queryStr)
22: gsmProcessSeasonFile(connObj, sourceDetails, sfile)
23: gsmProcessSeasons(connObj, sourceDetails)

Possible actions:
1: abort (with core dump, if enabled)
2: normal R exit
3: exit R without saving workspace
4: exit R saving workspace

The following script shows the problem (PSql 8.4.1, R 2.9.2, RPostgresql
0.1-6, DBI 0.2-4):

require(RPostgreSQL)

# connect to some database
i.db <- dbConnect('PostgreSQL', host="localhost", dbname="xxx", user="yyy",
password='zzz')

# create temporary table (as a copy of any existing one)
dbSendQuery( i.db, "CREATE TEMP TABLE tmptest AS
                    SELECT * FROM pg_database")

# query temp table
dbGetQuery( i.db, "SELECT * FROM tmptest" )
# expected: contents of tmptest
# outcome: RS-DBI driver: (could not Retrieve the result : ERROR:
# relation "tmptest" does not exist

# now, a work-around:
# try to create tmptest once more
dbSendQuery( i.db, "CREATE TEMP TABLE tmptest AS
                    SELECT * FROM pg_database")
# Error in postgresqlExecStatement(conn, statement, ...) : 
#  RS-DBI driver: (could not Retrieve the result : ERROR:  relation
# "tmptest" already exists)

# this time "select * from tmptest" should work
dbGetQuery( i.db, "SELECT * FROM tmptest" )

Hello,
I am trying to use rpostgres to get native Postgres arrays (i.e. type #
1022 - float8[]) but at no avail:
only pg protocol character array representation is returned, i.e.:

> rs<-dbSendQuery(conn,"select array[1.1,2.1]::float8[] arr;")
Warning message:
In postgresqlExecStatement(conn, statement, ...) :
  RS-DBI driver warning: (unrecognized PostgreSQL field type 1022 in column 0)

 a<-fetch(rs,n=-1)
> a
        arr
1 {1.1,2.1}

> class(a$arr)
[1] "character"



Do you have any plans to add this mapping anytime soon?

Best regards,
Krzysztof

In RPostgreSQL 0.1-6, bulk copying using dbWriteTable fails if the backend
user (e.g. postgres) does not have read access to the (temporary) data file
created by the R user.

On my Mac OSX 10.5, dbWriteTable(con, name, value) creates a temporary file
in /tmp/Rtmp.../rsdbi... with the contents of 'value' saved as a
tab-delimited text file. The permissions on this file and the containing
directory are 0600, indicating that the file is unreadable except by the R
user. dbWriteTable then sends the COPY command to Postgres (COPY table_name
FROM 'filename'). If Postgres is running on a different user than R, then
the file is unreadable and a permission error will occur.

One solution is to explicitly change the permissions on the file before
calling copy. The below is a patch to hte postgresqlWriteTable function in
the R/PostgreSQLSupport.R file:

[MAC] > diff PostgreSQLSupport.R PostgreSQLSupport_edit.R
633,636c633
<     if(as.character(Sys.info()["sysname"])=="Linux")
<         fn <- tempfile("rsdbi","/tmp")
<     else
<         fn <- tempfile("rsdbi")

---
>     fn <- tempfile("rsdbi","/tmp")
638a636
>     Sys.chmod(fn, mode="0744")

--Robert

What steps will reproduce the problem?

drv <- dbDriver('PostgreSQL')
db <- dbConnect(drv, host='localhost', user='valid user', dbname='stocksdddd')

Error Reported:
Error in postgresqlNewConnection(drv, ...) : 
  RS-DBI driver: (could not connect 'valid user'@localhost on dbname "stocksdddd"
)


On OS X 'netstat -p tcp

tcp4       0      0  localhost.49188        localhost.postgresql   CLOSE_WAIT
tcp4       0      0  localhost.postgresql   localhost.49188        FIN_WAIT_2

Never times out.

dbUnloadDriver(drv)

Removes the FIN_WAIT_2 side of the connection.  CLOSE_WAIT remains.

Closing R removes the CLOSE_WAIT connection.

What is the expected output? What do you see instead?

What version of the product are you using? On what operating system?

Reported by Derek
I'm running Ubuntu 12.10.  R version is 2.15.1 and RPostgreSQL version is 
0.3-3.  The server is PostgreSQL 9.0.5.

Confirmed by Neil
Mac OS X 10.8.2, R version 2.15.3, RPostgreSQL_0.3-3, Postgresql 9.2.2

Please provide any additional information below.

What steps will reproduce the problem?
1. Run the configure script on a system with a POSIX-conforming test utility.
   See http://pubs.opengroup.org/onlinepubs/009695399/utilities/test.html
2. Use of a double equal sign (==) to compare strings is nonconformant.
3. Use a single equal sign (=) instead.

What version of the product are you using? On what operating system?

- RPostgreSQL v0.3-2 on NetBSD

Please provide any additional information below.

- Please apply the attached patches.