cannot suppressMessages() for SQL Raise Notice

[austensen]

The messages created from RAISE NOTICE cannot be suppressed with suppressMessages().

It would be nice to be able to suppress these messages for "if not exists" type statements.

con <- DBI::dbConnect(RPostgres::Postgres())

suppressMessages(
  DBI::dbExecute(con, "
    DO language plpgsql $$
      BEGIN
        RAISE NOTICE 'hello, world!';
      END
    $$;")
)
#> NOTICE:  hello, world!
#> [1] 0

^{Created on 2019-01-25 by the reprex package (v0.2.1)}

Session info

devtools::session_info()
#> - Session info ----------------------------------------------------------
#>  setting  value                       
#>  version  R version 3.5.2 (2018-12-20)
#>  os       Windows 7 x64 SP 1          
#>  system   x86_64, mingw32             
#>  ui       RTerm                       
#>  language (EN)                        
#>  collate  English_United States.1252  
#>  ctype    English_United States.1252  
#>  tz       America/New_York            
#>  date     2019-01-25                  
#> 
#> - Packages --------------------------------------------------------------
#>  package     * version    date       lib source                          
#>  assertthat    0.2.0      2017-04-11 [1] CRAN (R 3.5.1)                  
#>  backports     1.1.3      2018-12-14 [1] CRAN (R 3.5.1)                  
#>  bindr         0.1.1      2018-03-13 [1] CRAN (R 3.5.1)                  
#>  bindrcpp      0.2.2      2018-03-29 [1] CRAN (R 3.5.1)                  
#>  bit           1.1-14     2018-05-29 [1] CRAN (R 3.5.0)                  
#>  bit64         0.9-7      2017-05-08 [1] CRAN (R 3.5.0)                  
#>  blob          1.1.1      2018-03-25 [1] CRAN (R 3.5.1)                  
#>  callr         2.0.4      2018-05-15 [1] CRAN (R 3.5.1)                  
#>  cli           1.0.1      2018-09-25 [1] CRAN (R 3.5.1)                  
#>  crayon        1.3.4      2017-09-16 [1] CRAN (R 3.5.1)                  
#>  DBI           1.0.0.9001 2019-01-26 [1] Github (r-dbi/DBI@be695e2)      
#>  desc          1.2.0      2018-05-01 [1] CRAN (R 3.5.1)                  
#>  devtools      2.0.1      2018-10-26 [1] CRAN (R 3.5.1)                  
#>  digest        0.6.18     2018-10-10 [1] CRAN (R 3.5.1)                  
#>  dplyr         0.7.8      2018-11-10 [1] CRAN (R 3.5.1)                  
#>  evaluate      0.11       2018-07-17 [1] CRAN (R 3.5.1)                  
#>  fcr           0.0.0.9000 2019-01-16 [1] local                           
#>  fs            1.2.6      2018-08-23 [1] CRAN (R 3.5.1)                  
#>  glue          1.3.0      2018-07-17 [1] CRAN (R 3.5.1)                  
#>  hms           0.4.2      2018-03-10 [1] CRAN (R 3.5.1)                  
#>  htmltools     0.3.6      2017-04-28 [1] CRAN (R 3.5.1)                  
#>  knitr         1.20       2018-02-20 [1] CRAN (R 3.5.1)                  
#>  magrittr      1.5        2014-11-22 [1] CRAN (R 3.5.1)                  
#>  memoise       1.1.0      2017-04-21 [1] CRAN (R 3.5.1)                  
#>  pillar        1.3.0      2018-07-14 [1] CRAN (R 3.5.1)                  
#>  pkgbuild      1.0.2      2018-10-16 [1] CRAN (R 3.5.1)                  
#>  pkgconfig     2.0.2      2018-08-16 [1] CRAN (R 3.5.1)                  
#>  pkgload       1.0.2      2018-10-29 [1] CRAN (R 3.5.1)                  
#>  prettyunits   1.0.2      2015-07-13 [1] CRAN (R 3.5.1)                  
#>  processx      3.2.0      2018-08-16 [1] CRAN (R 3.5.1)                  
#>  ps            1.1.0      2018-08-10 [1] CRAN (R 3.5.1)                  
#>  purrr         0.2.5      2018-05-29 [1] CRAN (R 3.5.1)                  
#>  R6            2.3.0      2018-10-04 [1] CRAN (R 3.5.1)                  
#>  Rcpp          1.0.0      2018-11-07 [1] CRAN (R 3.5.1)                  
#>  remotes       2.0.2      2018-10-30 [1] CRAN (R 3.5.1)                  
#>  rlang         0.3.1      2019-01-08 [1] CRAN (R 3.5.2)                  
#>  rmarkdown     1.10       2018-06-11 [1] CRAN (R 3.5.1)                  
#>  RPostgres     1.1.1.9002 2019-01-26 [1] Github (r-dbi/RPostgres@b64d367)
#>  rprojroot     1.3-2      2018-01-03 [1] CRAN (R 3.5.1)                  
#>  sessioninfo   1.1.1      2018-11-05 [1] CRAN (R 3.5.1)                  
#>  stringi       1.2.4      2018-07-20 [1] CRAN (R 3.5.1)                  
#>  stringr       1.3.1      2018-05-10 [1] CRAN (R 3.5.1)                  
#>  testthat      2.0.0      2017-12-13 [1] CRAN (R 3.5.1)                  
#>  tibble        1.4.2      2018-01-22 [1] CRAN (R 3.5.1)                  
#>  tidyselect    0.2.5      2018-10-11 [1] CRAN (R 3.5.1)                  
#>  usethis       1.4.0      2018-08-14 [1] CRAN (R 3.5.1)                  
#>  withr         2.1.2      2018-03-15 [1] CRAN (R 3.5.1)                  
#>  yaml          2.2.0      2018-07-25 [1] CRAN (R 3.5.1)                  
#> 
#> [1] C:/Users/austensen/Documents/R/win-library/3.5
#> [2] C:/Program Files/R/R-3.5.2/library

[krlmlr]

Thanks. Need to provide a notice receiver that forwards to Rf_message(): https://www.postgresql.org/docs/11/libpq-notice-processing.html.

[r2evans]

@austensen while not optimal (in case you weren't aware of this), you can suppress it on the postgres side with:

DBI::dbExecute(con, "drop table if exists quux")
# NOTICE:  table "quux" does not exist, skipping
# [1] 0
DBI::dbExecute(con, "drop table if exists quux")
# NOTICE:  table "quux" does not exist, skipping
# [1] 0

DBI::dbExecute(con, "set client_min_messages to WARNING")
# [1] 0

DBI::dbExecute(con, "drop table if exists quux")
# [1] 0
DBI::dbExecute(con, "drop table if exists quux")
# [1] 0

(repeated just for demonstration). I found this in https://stackoverflow.com/a/3531274.

(Ultimately it would be awesome to be able to capture this into a message or similar structure/condition for which R has existing methods.)

[austensen]

Thanks @r2evans That's helpful to know!

[krlmlr]

Requires RcppCore/Rcpp#1145.

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.