RPostgres query works interactively, in notebook, but will not knit

The following code works interactively in RStudio giving a count of 46520 from a Run All.

One can Preview Notebook | Run Restart R and clear output | Run All | Preview

The .nb.html file looks fine with a count of 46520. [I'd attach it here, but that's not allowed.]

count is displayed as a "S3: integer64" if that matters.

But any attempt to Knit to HTML fails. The Output tab shows (with the same text showing in the Issues tab):

Quitting from lines 23-25 (Querying-MIMIC-III-problem.Rmd)
Error in if (n2%%n1) warning("length(e2) not a multiple length(e1)") :
  argument is not interpretable as logical
Calls: <Anonymous> ... withVisible -> eval -> eval -> ==.integer64 -> binattr
Execution halted
---
title: "Querying MIMIC-III"
output:
  html_document:
    toc: yes
  html_notebook:
    toc: yes
---

```{r}
library(DBI)
library(RPostgres)

MimicDB <- dbConnect(RPostgres::Postgres(),
                     host     = "localhost",
                     dbname   = "mimic",
                     user     = Sys.getenv("MIMIC_User"),
                     password = Sys.getenv("MIMIC_Password"),
                     options  = "-c search_path=mimiciii")
```

```{sql, connection=MimicDB}
SELECT COUNT(*)
FROM   patients
```

## Close database

```{r, comment=NA}
dbDisconnect(MimicDB)
```

Why can Preview work, but knit fails?

Any suggestions on how to troubleshoot?

What is the options statement doing? Can you write around it? It’s the only thing that stands out.

Sorry for my slow reply.

I followed these instructions to install the MIMIC-III database:
https://mimic.physionet.org/tutorials/install-mimic-locally-windows/

In the "Create the tables in the database" section they suggest using "CREATE SCHMEA mimiciii;" and then say "You will need to do this every time you launch psql".

The "options" passed to dbConnect deals with this mimiciii schema configuration. Without this option, the database is not found.

The problem described in the Rbloggers posting, Take Care If Trying the RPostgres Package suggested a fix that solved this problem.

That posting suggested this work-around: add the argument bigint = "numeric" to your dbConnect() call. I can now knit the Rmd file.

2 Likes

Should be fixed by @Randy_Lai's PR: https://github.com/yihui/knitr/pull/1837 Please try

remotes::install_github('yihui/knitr')

Thanks!