Problems with connecting to local postgres database from R through RPostgreSQL

Question

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

Also, when I am running the command from rstudio server running on the same server, the connection works. — JaKu
– JaKu, Commented May 7, 2017 at 22:14
Postgres has different authentication mechanisms depending on the originating host. It might be that the hostname you are connecting with resolves to 127.0.0.1 or 127.1.0.1 on the server, which demands that only certain users can connect. Try hard-coding the external IP address. — r2evans
– r2evans, Commented May 8, 2017 at 6:04
I think the problem lies somewhere after connection, because when I try different password (on 127.0.0.1 or external IP) I get different error: Error in postgresqlNewConnection(drv, ...) : RS-DBI driver: (could not connect username@host on dbname "dbname" — JaKu
– JaKu, Commented May 8, 2017 at 6:59
Ok ... then you'll really need to show more code: dbGetQuery is certainly post-connection, but you don't show what you did to trigger that error. — r2evans
– r2evans, Commented May 8, 2017 at 7:57
These are all the commands I am using (I've updated the parameters I use). Should I try to split the dbConnect into more commands, so I can pinpoint more exactly the point of error? — JaKu
– JaKu, Commented May 8, 2017 at 8:59

r2evans · Accepted Answer · 2017-05-08 19:59:33Z

1

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.)

answered May 8, 2017 at 19:59

r2evans

167k8 gold badges92 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Teletubbi-OS X Over a year ago

forceISOdate=FALSE in is the key!

con <- dbConnect(drv, dbname = 'about_psql',                  host = 'localhost', port = 5432,                  user = 'TheDonald', password = 'Tweet!', forceISOdate=FALSE)

Teletubbi-OS X Over a year ago

oh yes! My intention was only to show my example that was fixed by the "forceISOdate".

Collectives™ on Stack Overflow

Problems with connecting to local postgres database from R through RPostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related