CODEWITHSUNDEEP
rpostgresql
JaKu
JaKu

Reputation: 1166

Problems with connecting to local postgres database from R through RPostgreSQL

I have to following code

drv <- RPostgreSQL::PostgreSQL()
con <- DBI::dbConnect(drv, dbname = 'dbname', user = 'user', 
                      host = 'host.name', port = 5432, password = 'password')

When I run it on server (Ubuntu server 16.04 with latest updates) running the database I get the following error:

Error in .valueClassTest(ans, "data.frame", "dbGetQuery") :
  invalid value from generic function ‘dbGetQuery’, class “NULL”, expected “data.frame”

But when I run R from commandline with sudo, it works, when I run it from different my laptop connecting to the DB on the server it also works. So it shouldn't be connection problem. I am thinking about problem with access rights to some libraries/executables/configs on the system? Any help will be appreciated.

When I run the dbConnect multiple times and it ends with the error, when I run drv_info <- RPostgreSQL::dbGetInfo(drv), I still get multiple connectionIds in the drv_info: 

drv_info <- RPostgreSQL::dbGetInfo(drv)
> drv_info
$drvName
[1] "PostgreSQL"

$connectionIds
$connectionIds[[1]]
<PostgreSQLConnection>

$connectionIds[[2]]
<PostgreSQLConnection>

$fetch_default_rec
[1] 500

$managerId
<PostgreSQLDriver>

$length
[1] 16

$num_con
[1] 2

$counter
[1] 2

Upvotes: 2

Views: 1903

Answers (1)

r2evans
r2evans

Reputation: 160417

Found a source of confusion, but not necessarily the root problem. (I was assuming RPostgres, while you are using RPostgreSQL (github mirror).)

If you check the source, you'll find that the method is calling postgresqlNewConnection, which includes a call to dbGetQuery. The problem you're seeing is that your call to dbConnect is failing (my guess is at line 100) and returns something unexpected, but postgresqlNewConnection continues.

I see three options for you:

  • try calling dbConnect(..., forceISOdate=FALSE) to bypass that one call to dbGetQuery (note that this doesn't fix the connection problem, but it at least will not give you the unexpected query error on connection attempt);
  • raise an issue for the package maintainers; or
  • switch to using RPostgres, still DBI-based and actively developed (it looks like RPostgreSQL has not had significant commits in the last few years, not sure if that's a sign of good code stability or development stagnation)

One lesson you may take from this is that you should check the value returned from dbConnect; if is.null(con), something is wrong. (Again, this does not solve the connection problem.)

Upvotes: 1

Related Questions