Как избежать множественных подключений к базе данных postgres при доступе с помощью R

Question

Я использую следующий код, однако он создает несколько соединений при вызове функции карты, и они не закрываются. В результате моя база данных rds переполняется соединениями. Есть ли способ изменить этот код, чтобы предотвратить такое количество подключений?

         connect.to.database <- function (dbname, schema = "public", host, port, user, pass) {
      con <- dbConnect(RPostgres::Postgres(),
                       dbname = dbname,
                       user = user,
                       password = pass,
                       host = host,
                       port = port)
      
      
      # this puts the schema in the search path, which means that instead of
      # having to use <schema name>.<table name> you can just write <table name>
      res <- dbSendQuery(con, paste0("SET search_path TO ",
                                     dbQuoteIdentifier(con, schema),
                                     ", public"))
      
      # check for errors
      dbFetch(res)
      dbClearResult(res)
      
      con
    }

    schemas <- dbGetQuery(connect.to.database(dbname, "public", host, port, user, password), paste0("SELECT schema_name FROM information_schema.schemata"))
    
    schema_names <- schemas %>% pull()
    
    schemas_tables <- map(.x = schema_names,~dbGetQuery(connect.to.database(dbname, "public", host, port, user, password), paste0("SELECT table_name FROM information_schema.tables WHERE table_schema = ","'",.x,"'")) %>% mutate(schema_name = .x)) %>%
                      bind_rows()

Answer 1

Создайте один объект глобального соединения и используйте его внутри . (удалю лишнее paste0из вашего первого запроса.)

      conn <- connect.to.database(dbname, "public", host, port, user, password)
schema <- dbGetQuery(conn, "SELECT schema_name FROM information_schema.schemata")

schemas_tables <- map(
  .x = schema$schema_name,
  ~ dbGetQuery(conn, paste0("SELECT table_name FROM information_schema.tables WHERE table_schema = ","'",.x,"'")) %>%
    mutate(schema_name = .x)
) %>%
  bind_rows()

Возможно, вы захотите рассмотреть параметризованные запросы вместо создания строк запроса вручную. Несмотря на то, что существуют проблемы с безопасностью в отношении злонамеренной инъекции SQL (например, XKCD Exploits of a Mom aka «Little Bobby Tables»), также существуют проблемы с искаженными строками или ошибками Unicode-VS-ANSI, даже если это один аналитик данных, работающий с запрос. Оба DBI(с odbc) а также RODBCподдержка параметризованных запросов как изначально, так и через надстройки.

Это изменило бы это на:

      schemas_tables <- map(
  .x = schema$schema_name,
  ~ dbGetQuery(conn, "SELECT table_name FROM information_schema.tables WHERE table_schema = ?",
               params = list(.x)) %>%
    mutate(schema_name = .x)
) %>%
  bind_rows()

Но, честно говоря, я думаю, что это может быть намного проще в использовании. INвместо =. Опять же, используя привязку параметров.

      schemas_tables <- dbGetQuery(conn, "SELECT table_name FROM information_schema.tables WHERE table_schema IN (?)",
                             params = list(schema$schema_name))

(Нет mapтребуется.)

Или я считаю, что вы можете сделать это в одном запросе, а не в двух.

      dbGetQuery(conn, "
    select table_name
    from information_schema.tables
    where table_schema in (
      select schema_name from information_schema.schemata
    )")

Запомнить

... чтобы закрыть соединение, когда вы закончите.

      dbDisconnect(conn)