pool: Object Pooling

Description

Enables the creation of object pools, which make it less computationally expensive to fetch a new object. Currently the only supported pooled objects are 'DBI' connections.

Author(s)

Maintainer: Hadley Wickham hadley@posit.co

Authors:

• Joe Cheng joe@posit.co

• Barbara Borges

Other contributors:

• Posit Software, PBC [copyright holder, funder]

See Also

Useful links:

• https://github.com/rstudio/pool

• https://rstudio.github.io/pool/

• Report bugs at https://github.com/rstudio/pool/issues

Unsupported DBI methods

Description

Most pool methods for DBI generics check out a connection, perform the operation, and the return the connection to the pool, as described in DBI-wrap.

This page describes the exceptions:

• DBI::dbSendQuery() and DBI::dbSendStatement() can't work with pool because they return result sets that are bound to a specific connection. Instead use DBI::dbGetQuery(), DBI::dbExecute(), or localCheckout().

• DBI::dbBegin(), DBI::dbRollback(), DBI::dbCommit(), and DBI::dbWithTransaction() can't work with pool because transactions are bound to a connection. Instead use poolWithTransaction().

• DBI::dbDisconnect() can't work because pool handles disconnection. Use poolClose() instead.

• DBI::dbGetInfo() returns information about the pool, not the database connection.

• DBI::dbIsValid() returns whether or not the entire pool is valid (i.e. not closed).

Usage

## S4 method for signature 'Pool'
dbSendQuery(conn, statement, ...)

## S4 method for signature 'Pool,ANY'
dbSendStatement(conn, statement, ...)

## S4 method for signature 'Pool'
dbDisconnect(conn, ...)

## S4 method for signature 'Pool'
dbGetInfo(dbObj, ...)

## S4 method for signature 'Pool'
dbIsValid(dbObj, ...)

## S4 method for signature 'Pool'
dbBegin(conn, ...)

## S4 method for signature 'Pool'
dbCommit(conn, ...)

## S4 method for signature 'Pool'
dbRollback(conn, ...)

## S4 method for signature 'Pool'
dbWithTransaction(conn, code)

Arguments

DBI methods (simple wrappers)

Description

These pool method for DBI generics methods check out a connection (with poolCheckout()), re-call the generic, then return the connection to the pool (with poolReturn()). See DBI-custom for DBI methods that do not work with pool objects.

Usage

## S4 method for signature 'Pool'
dbDataType(dbObj, obj, ...)

## S4 method for signature 'Pool,ANY'
dbGetQuery(conn, statement, ...)

## S4 method for signature 'Pool,ANY'
dbExecute(conn, statement, ...)

## S4 method for signature 'Pool,ANY'
dbListFields(conn, name, ...)

## S4 method for signature 'Pool'
dbListTables(conn, ...)

## S4 method for signature 'Pool'
dbListObjects(conn, prefix = NULL, ...)

## S4 method for signature 'Pool,ANY'
dbReadTable(conn, name, ...)

## S4 method for signature 'Pool,ANY'
dbWriteTable(conn, name, value, ...)

## S4 method for signature 'Pool'
dbCreateTable(conn, name, fields, ..., row.names = NULL, temporary = FALSE)

## S4 method for signature 'Pool'
dbAppendTable(conn, name, value, ..., row.names = NULL)

## S4 method for signature 'Pool,ANY'
dbExistsTable(conn, name, ...)

## S4 method for signature 'Pool,ANY'
dbRemoveTable(conn, name, ...)

## S4 method for signature 'Pool'
dbIsReadOnly(dbObj, ...)

## S4 method for signature 'Pool'
sqlData(con, value, row.names = NA, ...)

## S4 method for signature 'Pool'
sqlCreateTable(con, table, fields, row.names = NA, temporary = FALSE, ...)

## S4 method for signature 'Pool'
sqlAppendTable(con, table, values, row.names = NA, ...)

## S4 method for signature 'Pool'
sqlInterpolate(conn, sql, ..., .dots = list())

## S4 method for signature 'Pool'
sqlParseVariables(conn, sql, ...)

## S4 method for signature 'Pool,ANY'
dbQuoteIdentifier(conn, x, ...)

## S4 method for signature 'Pool'
dbUnquoteIdentifier(conn, x, ...)

## S4 method for signature 'Pool'
dbQuoteLiteral(conn, x, ...)

## S4 method for signature 'Pool,ANY'
dbQuoteString(conn, x, ...)

## S4 method for signature 'Pool'
dbAppendTableArrow(conn, name, value, ...)

## S4 method for signature 'Pool'
dbCreateTableArrow(conn, name, value, ..., temporary = FALSE)

## S4 method for signature 'Pool'
dbGetQueryArrow(conn, statement, ...)

## S4 method for signature 'Pool'
dbReadTableArrow(conn, name, ...)

## S4 method for signature 'Pool'
dbSendQueryArrow(conn, statement, ...)

## S4 method for signature 'Pool'
dbWriteTableArrow(conn, name, value, ...)

Arguments

Examples

mtcars1 <- mtcars[ c(1:16), ] # first half of the mtcars dataset
mtcars2 <- mtcars[-c(1:16), ] # second half of the mtcars dataset

pool <- dbPool(RSQLite::SQLite())

# write the mtcars1 table into the database
dbWriteTable(pool, "mtcars", mtcars1, row.names = TRUE)

# list the current tables in the database
dbListTables(pool)

# read the "mtcars" table from the database (only 16 rows)
dbReadTable(pool, "mtcars")

# append mtcars2 to the "mtcars" table already in the database
dbWriteTable(pool, "mtcars", mtcars2, row.names = TRUE, append = TRUE)

# read the "mtcars" table from the database (all 32 rows)
dbReadTable(pool, "mtcars")

# get the names of the columns in the databases's table
dbListFields(pool, "mtcars")

# use dbExecute to change the "mpg" and "cyl" values of the 1st row
dbExecute(pool,
  paste(
    "UPDATE mtcars",
    "SET mpg = '22.0', cyl = '10'",
    "WHERE row_names = 'Mazda RX4'"
  )
)

# read the 1st row of "mtcars" table to confirm the previous change
dbGetQuery(pool, "SELECT * FROM mtcars WHERE row_names = 'Mazda RX4'")

# drop the "mtcars" table from the database
dbRemoveTable(pool, "mtcars")

# list the current tables in the database
dbListTables(pool)

poolClose(pool)

Create a pool of reusable objects

Description

A generic pool class that holds objects. These can be fetched from the pool and released back to it at will, with very little computational cost. The pool should be created only once and closed when it is no longer needed, to prevent leaks.

Every usage of poolCreate() should always be paired with a call to poolClose() to avoid "leaking" resources. In shiny app, you should create the pool outside of the server function and close it on stop, i.e. onStop(function() pool::poolClose(pool)).

See dbPool() for an example of object pooling applied to DBI database connections.

Usage

poolCreate(
  factory,
  minSize = 1,
  maxSize = Inf,
  idleTimeout = 60,
  validationInterval = 60,
  state = NULL
)

poolClose(pool)

## S4 method for signature 'Pool'
poolClose(pool)

Arguments

Create a pool of database connections

Description

dbPool() is a drop-in replacement for DBI::dbConnect() that provides a shared pool of connections that can automatically reconnect to the database if needed. See DBI-wrap for methods to use with pool objects, and DBI-custom for unsupported methods and the "pool" way of using them.

Usage

dbPool(
  drv,
  ...,
  minSize = 1,
  maxSize = Inf,
  onCreate = NULL,
  idleTimeout = 60,
  validationInterval = 60,
  validateQuery = NULL
)

Arguments

Details

A new connection is created transparently

• if the pool is empty

• if the currently checked out connection is invalid (checked at most once every validationInterval seconds)

• if the pool is not full and the connections are all in use

Use poolClose() to close the pool and all connections in it. See poolCreate() for details on the internal workings of the pool.

Examples

# You use a dbPool in the same way as a standard DBI connection
pool <- dbPool(RSQLite::SQLite(), dbname = demoDb())
pool

dbGetQuery(pool, "SELECT * FROM mtcars LIMIT 4")

# Always close a pool when you're done using it
poolClose(pool)

Create a demo SQLite database

Description

This function creates a temporary SQLite database for demonstration purposes. It populates the database with two tables: 'mtcars' and 'faithful'.

Usage

demoDb()

Pooled object methods.

Description

For backend authors only. Authors should implement all of these, which are then called by the Pool class methods. These should not be called directly either by backend authors or by the end users.

Usage

onActivate(object)

onPassivate(object)

onDestroy(object)

onValidate(object, query)

## S4 method for signature 'ANY'
onActivate(object)

## S4 method for signature 'ANY'
onPassivate(object)

## S4 method for signature 'ANY'
onDestroy(object)

## S4 method for signature 'ANY'
onValidate(object, query)

## S4 method for signature 'DBIConnection'
onPassivate(object)

## S4 method for signature 'DBIConnection'
onDestroy(object)

## S4 method for signature 'DBIConnection'
onValidate(object)

Arguments

Check out and return object from the pool

Description

Use poolCheckout() to check out an object from the pool and poolReturn() to return it. You will receive a warning if all objects aren't returned before the pool is closed.

localCheckout() is a convenience function that can be used inside functions (and other function-scoped operations like shiny::reactive() and local()). It checks out an object and automatically returns it when the function exits

Note that validation is only performed when the object is checked out, so you generally want to keep the checked out around for as little time as possible.

When pooling DBI database connections, you normally would not use poolCheckout(). Instead, for single-shot queries, treat the pool object itself as the DBI connection object and it will perform checkout/return for you. And for transactions, use poolWithTransaction(). See dbPool() for an example.

Usage

poolCheckout(pool)

## S4 method for signature 'Pool'
poolCheckout(pool)

poolReturn(object)

## S4 method for signature 'ANY'
poolReturn(object)

localCheckout(pool, env = parent.frame())

Arguments

Examples

pool <- dbPool(RSQLite::SQLite())
# For illustration only. You normally would not explicitly use
# poolCheckout with a DBI connection pool (see Description).
con <- poolCheckout(pool)
con
poolReturn(con)

f <- function() {
  con <- localCheckout(pool)
  # do something ...
}
f()

poolClose(pool)

Self-contained database transactions using pool

Description

This function allows you to use a pool object directly to execute a transaction on a database connection, without ever having to actually check out a connection from the pool and then return it. Using this function instead of the direct transaction methods will guarantee that you don't leak connections or forget to commit/rollback a transaction.

Usage

poolWithTransaction(pool, func)

Arguments

Details

This function is similar to DBI::dbWithTransaction(), but its arguments work a little differently. First, it takes in a pool object, instead of a connection. Second, instead of taking an arbitrary chunk of code to execute as a transaction (i.e. either run all the commands successfully or not run any of them), it takes in a function. This function (the func argument) gives you an argument to use in its body, a database connection. So, you can use connection methods without ever having to check out a connection. But you can also use arbitrary R code inside the func's body. This function will be called once we fetch a connection from the pool. Once the function returns, we release the connection back to the pool.

Like its DBI sister DBI::dbWithTransaction(), this function calls dbBegin() before executing the code, and dbCommit() after successful completion, or dbRollback() in case of an error. This means that calling poolWithTransaction always has side effects, namely to commit or roll back the code executed when func is called. In addition, if you modify the local R environment from within func (e.g. setting global variables, writing to disk), these changes will persist after the function has returned.

Also, like DBI::dbWithTransaction(), there is also a special function called dbBreak() that allows for an early, silent exit with rollback. It can be called only from inside poolWithTransaction.

Value

func's return value.

Examples

if (requireNamespace("RSQLite", quietly = TRUE)) {
  pool <- dbPool(RSQLite::SQLite(), dbname = ":memory:")

  dbWriteTable(pool, "cars", head(cars, 3))
  dbReadTable(pool, "cars")   # there are 3 rows

  ## successful transaction
  poolWithTransaction(pool, function(conn) {
    dbExecute(conn, "INSERT INTO cars (speed, dist) VALUES (1, 1);")
    dbExecute(conn, "INSERT INTO cars (speed, dist) VALUES (2, 2);")
    dbExecute(conn, "INSERT INTO cars (speed, dist) VALUES (3, 3);")
  })
  dbReadTable(pool, "cars")   # there are now 6 rows

  ## failed transaction -- note the missing comma
  tryCatch(
    poolWithTransaction(pool, function(conn) {
      dbExecute(conn, "INSERT INTO cars (speed, dist) VALUES (1, 1);")
      dbExecute(conn, "INSERT INTO cars (speed dist) VALUES (2, 2);")
      dbExecute(conn, "INSERT INTO cars (speed, dist) VALUES (3, 3);")
    }),
    error = identity
  )
  dbReadTable(pool, "cars")   # still 6 rows

  ## early exit, silently
  poolWithTransaction(pool, function(conn) {
    dbExecute(conn, "INSERT INTO cars (speed, dist) VALUES (1, 1);")
    dbExecute(conn, "INSERT INTO cars (speed, dist) VALUES (2, 2);")
    if (nrow(dbReadTable(conn, "cars")) > 7) dbBreak()
    dbExecute(conn, "INSERT INTO cars (speed, dist) VALUES (3, 3);")
  })
  dbReadTable(pool, "cars")   # still 6 rows

  poolClose(pool)

} else {
  message("Please install the 'RSQLite' package to run this example")
}

Objects exported from other packages

Description

These objects are imported from other packages. Follow the links below to see their documentation.

DBI

dbBreak

Use pool with dbplyr

Description

Wrappers for key dplyr (and dbplyr) methods so that pool works seemlessly with dbplyr.

Usage

tbl.Pool(src, from, ..., vars = NULL)

copy_to.Pool(dest, df, name = NULL, overwrite = FALSE, temporary = TRUE, ...)

Arguments

Examples

library(dplyr)

pool <- dbPool(RSQLite::SQLite())
# copy a table into the database
copy_to(pool, mtcars, "mtcars", temporary = FALSE)

# retrieve a table
mtcars_db <- tbl(pool, "mtcars")
mtcars_db
mtcars_db %>% select(mpg, cyl, disp)
mtcars_db %>% filter(cyl == 6) %>% collect()

poolClose(pool)