db_load_itis() fails after successful install with "psql not found on your computer"

[twest820]

Presumably this is related to this statement in the current readme.md: "Remember to start your PostgreSQL database for ITIS". It is unclear, however, what steps need to be taken to perform this task. For example, installation of taxizedb's dependencies doesn't create the C:\Program Files\PostgreSQL directory referred to here. It's also unclear if the RPostgreSQL package would be helpful or whether use of taxizedb requires compilation of PostgreSQL as described in its installation instructions.

My use cases for taxizedb are mainly checking ITIS records at off-internet field sites and infrequent bulk operations to check if any of a couple thousand binomial names are no longer accepted ITIS names. #14 appears adjacent to this issue.

library('dplyr') library('dbplyr') library('taxizedb') itisPath = db_download_itis() db_load_itis(itisPath) checking if path exists... checking if Postgres installed... Error in db_installed("psql") : psql not found on your computer Install the missing tool(s) and try again

sessionInfo() R version 3.5.1 (2018-07-02) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows >= 8 x64 (build 9200)

Matrix products: default

locale: [1] LC_COLLATE=English_Canada.1252 LC_CTYPE=English_Canada.1252
[3] LC_MONETARY=English_Canada.1252 LC_NUMERIC=C
[5] LC_TIME=English_Canada.1252

attached base packages: [1] stats graphics grDevices utils datasets methods base

other attached packages: [1] devtools_1.13.6 dbplyr_1.2.1 dplyr_0.7.6 taxizedb_0.1.4

loaded via a namespace (and not attached): [1] Rcpp_0.12.17 magrittr_1.5 bindr_0.1.1 rappdirs_0.3.1
[5] tidyselect_0.2.4 munsell_0.4.3 bit_1.1-14 colorspace_1.3-2 [9] R6_2.2.2 rlang_0.2.0 hoardr_0.2.0 blob_1.1.1
[13] plyr_1.8.4 tools_3.5.1 grid_3.5.1 gtable_0.2.0
[17] DBI_1.0.0 withr_2.1.2 bit64_0.9-7 RMySQL_0.10.15
[21] lazyeval_0.2.1 RPostgreSQL_0.6-2 digest_0.6.15 assertthat_0.2.0 [25] tibble_1.4.2 bindrcpp_0.2.2 purrr_0.2.5 ggplot2_2.2.1
[29] curl_3.2 glue_1.2.0 memoise_1.1.0 RSQLite_2.1.1
[33] compiler_3.5.1 pillar_1.2.3 scales_0.5.0 pkgconfig_2.0.1

[sckott]

thanks @twest820 for this!

thanks for sharing what your use case is, that's really helpful.

Do you know if you have the database software Postgresql installed already? if not you could install from https://www.postgresql.org/download/windows/ i think

[twest820]

Hi Scott, PostgreSQL 10.4 x64 is installed and running as a service on port 5432 (its default settings). Behaviour is the same with db_load_itis(itispath, "postgres", pwd = "").

If it's helpful, it appears db_installed() can't be run directly for a closer look.

db_installed("psql") Error in db_installed("psql") : could not find function "db_installed"

[sckott]

For the db_installed error, i'm guessing you didn't have taxizedb loaded?

[chulmelowe]

I'm running into this same issue and wondering if it was ever resolved. I have PostgreSQL running on my computer. My guess is that the Sys.which command is not finding the executable, but at this point I am guessing.

With respect to the behavior of the db_installed function, I also can't get it to run independently. Trying to call it from the taxizedb namespace also doesn't work (Error: 'db_installed' is not an exported object from 'namespace:taxizedb'). However, if I use the getAnywhere function it does say that it's from the taxizedb namespace. I've seen this happen before, but I've never been able to figure out why.

[sckott]

db_installed is not an exported function https://github.com/ropensci/taxizedb/blob/master/R/zzz.R - so you can get to it by taxizedb:::db_installed

what does Sys.which('psql') give you?

[chulmelowe]

It gives me an empty string. I think that the issue is that the PostgreSQL executable is not in the SYSTEM32 folder so R isn't seeing it.

[sckott]

interesting. can you share your session info. what does which psql give you (run in console)

[chulmelowe]

Here's my session info:

R version 3.6.1 (2019-07-05) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows 10 x64 (build 16299)

Matrix products: default

Random number generation: RNG: Mersenne-Twister Normal: Inversion Sample: Rounding

locale: [1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C
[5] LC_TIME=English_United States.1252

attached base packages: [1] stats graphics grDevices utils datasets methods base

other attached packages: [1] dplyr_0.8.3 taxizedb_0.1.4

loaded via a namespace (and not attached): [1] Rcpp_1.0.2 magrittr_1.5 rappdirs_0.3.1 tidyselect_0.2.5 bit_1.1-14
[6] R6_2.4.0 rlang_0.4.0 hoardr_0.5.2 blob_1.2.0 tools_3.6.1
[11] DBI_1.0.0 dbplyr_1.4.2 bit64_0.9-7 RMySQL_0.10.17 RPostgreSQL_0.6-2 [16] digest_0.6.20 assertthat_0.2.1 tibble_2.1.3 crayon_1.3.4 purrr_0.3.2
[21] vctrs_0.2.0 curl_4.0 zeallot_0.1.0 memoise_1.1.0 glue_1.3.1
[26] RSQLite_2.1.2 compiler_3.6.1 pillar_1.4.2 backports_1.1.4 pkgconfig_2.0.2

Since I'm running in a Windows environment, 'which' doesn't work through the command prompt. The 'where' command tells me it can't find a file with the psql pattern.

[sckott]

im thinking of moving all databases to sqlite, which I don't think will present problems on any platforms ... will let you know when I have something

[sckott]

all databases moved to sqlite, closing